How to query Postgres from Cloudflare Workers with Neon serverless driver

Cloudflare Workers is a serverless platform that allows developers to build and deploy applications on the edge. By leveraging Cloudflare Workers, you can execute your code in close proximity to your users, reducing latency and improving performance. However, one challenge developers face when building serverless applications is accessing databases. This article will explore how to query a PostgreSQL database from Cloudflare Workers using the Neon serverless driver.

The Neon serverless driver is a PostgreSQL client library specifically designed for serverless environments, such as Cloudflare Workers. It provides a lightweight and efficient way to connect to a PostgreSQL database within your Cloudflare Workers code. The driver is built on top of the popular node-postgres library, but with some optimizations and modifications to make it work better in serverless environments.

Prerequisites

Before getting started, you’ll need to make sure that you have the following:

1. A Cloudflare account.
2. A Neon Cloud account
3. NPM installed with it’s package runner NPX.
4. A PostgreSQL database.

Here is the link to the GitHub project repository to make it easy to follow along.

Create a Neon project

To create your first Neon project, follow these steps:

1. Sign in to your Neon account.
2. Once signed in, you will be redirected to the Neon console.

3. On the console, you will see a project creation dialog. This dialog allows us to specify the following details for our project:

         -  **Project name**: Choose a descriptive name for your project.
         -  **Postgres version**: Select the desired version of PostgreSQL.
         -  **Database name**: Provide a name for your database.
         -  **Region**: Choose the region where your project is to be hosted.
   

After creating a project, you get a redirect to the Neon console, and a pop-up modal with a connection string appears. Copy the connection string, you’ll use it in the next step.

The connection string is used to connect to your project's database, which is automatically created when you create a new project.

Loading demo data into the database

For demonstration purposes, you’ll populate the database with sample product data for querying.
To load the data into your database, you must connect to it using the connection string. Open a terminal window and run the following command:

    psql 'postgresql://xxx:[e-mail]/xxx?sslmode=require'

This command connects to the database using the provided connection string.

Once you're connected to the database, you can load the demo data by following the steps below:

• Create a folder named sql in your project directory.
• Download the products.sql from here.
• Move the products.sql file into the "sql" folder.

Once you have set up the sql folder and added the products.sql file, you can run the following command to load the demo data into your database.

    psql 'postgresql://xxx:[e-mail]/xxx?sslmode=require' < products.sql

This command loads the products.sql file into the database. The products.sql file contains SQL statements that create a table and insert demo data into the table.

After running the command, you should see some output in your terminal, indicating that the demo data has been successfully loaded into the database. The output should look something like this:

The output CREATE SEQUENCE, CREATE TABLE, and INSERT 0 5 indicates that the sequence, the table, and the 5 rows were inserted into the table, respectively.

Creating a worker application

First, use the create-cloudflare CLI to create a new Worker application. To do this, open a terminal window and run the following command:

    npx wrangler init neon-query-db

This will prompt you to install the create-cloudflare package and lead you through a setup wizard.

To continue with this guide, follow these steps:

• When prompted, provide a name for your new Worker application.
• Select "Hello World" Worker as the type of application.
• Choose "Yes" to use TypeScript.
• Select "No" when asked if you want to deploy your application.

If you choose to deploy your application, you may be asked to authenticate if you're not logged in already. Your project will then be deployed. Even if you deploy, you can modify your Worker code and redeploy it at the end of this tutorial.

The above command creates a new directory named neon-query-db containing the scaffolding for a new Cloudflare Worker project. Navigate into this directory by running cd neon-query-db.

After running npx wrangler init in your project directory, the following files have been generated:

• wrangler.toml: This file is your Wrangler configuration file.
• src/index.ts: It contains a minimal Hello World Worker written in TypeScript.
• package.json: This file is a minimal Node dependencies configuration file. It will be generated if you select "Yes" when prompted during the wrangler init command.
• tsconfig.json: This file is the TypeScript configuration that includes Worker types. It is only generated if specified in the wrangler init command.

For this tutorial, you only need to focus on the wrangler.toml and src/index.ts files. You don't need to edit the other files; they should be left as they are.

Installing the Neon package

To install the Neon package, run the following code on the terminal.

    npm install @neondatabase/serverless

Configuring connection to Postgres database

There are two methods to connect to your PostgreSQL database: using a connection string or setting explicit parameters.

Use a connection string

To connect, run the following code:

    npx wrangler secret put DATABASE_URL

Paste in your connection string when prompted (you’ll find this in your Neon dashboard).

Set explicit parameters

Configure each database parameter as an environment variable via the Cloudflare dashboard or in your wrangler.toml file.

The example below shows how to configure the parameters in the wrangler.toml file.

    //wrangler.toml file

    [vars]
    DB_USERNAME = "postgres"
    # Set your password by creating a secret so it is not stored as plain text
    DB_HOST = "ep-aged-sound-175961.us-east-2.aws.neon.tech"
    DB_PORT = "5432"
    DB_NAME = "products"

To set a secret for your worker, use npx wrangler secret put DB_PASSWORD :

    npx wrangler secret put DB_PASSWORD

After executing the command, you’ll be prompted to enter the value of DB_PASSWORD into the terminal to securely store the database password as a secret in your Cloudflare Workers environment variables.

Connecting to the Postgres database in the worker

Next, import the Client class into your Worker's main file from the pg library. Depending on your chosen connection method, use either the connection string or explicit parameters to establish a connection to the PostgreSQL database.

    // src/index.ts file

    import { Client } from '@neondatabase/serverless';
    interface Env { DATABASE_URL: string; }

In the fetch event handler, connect to the PostgreSQL database using your chosen method, either the connection string or the explicit parameters.

Using a connection string

    const client = new Client(env.DATABASE_URL);
    await client.connect();

Setting explicit parameters

    const client = new Client({
      user: env.DB_USERNAME,
      password: env.DB_PASSWORD,
      host: env.DB_HOST,
      port: env.DB_PORT,
      database: env.DB_NAME
    });
    await client.connect();

Querying the database

To demonstrate how to interact with the products database, let’s fetch data from the products table by querying the table when a request is received.

To fetch data from the products table, add the following code snippet inside the fetch event handler in your index.ts file, after the existing query code:

    export default {
      async fetch(request: Request, env: Env, ctx: ExecutionContext) {
        const client = new Client(env.DATABASE_URL);
        await client.connect();
        const { rows } = await client.query(`
        select * from products,
        );
        ctx.waitUntil(client.end());  // this doesn’t hold up the response
        return new Response(JSON.stringify(rows), {
          headers: { "content-type": "application/json"},
        });
      }
    }

This code snippet does the following:

• Checks if the request is a GET request and if the URL path is /products.
• Constructs a SELECT SQL query that fetches all rows from the "products" table.
• Executes the query and fetches all rows from the "products" table.
• Returns all rows as a JSON response.

When you send a GET request to your Worker’s URL with the /products path, the Worker will fetch all rows from the products table and return them as JSON.

Deploying your Worker

Run the following command to deploy your Worker:

    npx wrangler deploy

Your application is now live and accessible at <YOUR_WORKER>.<YOUR_SUBDOMAIN>.workers.dev.

After deploying, you can interact with your PostgreSQL products database using your Cloudflare Worker. Whenever a request is made to your Worker’s URL, it will fetch data from the products table and return it as a JSON response. You can modify the query as needed to retrieve the desired data from your product database.

The product data is successfully returned from the database! You can confirm that your Cloudflare Worker successfully connected to the PostgreSQL database using the Neon serverless driver.

Conclusion

In this article, you've explored how to query a PostgreSQL database from Cloudflare Workers using the Neon serverless driver. By leveraging this driver, you can efficiently connect to your PostgreSQL databases and retrieve data from within your serverless applications. This enables you to build powerful and performant applications that utilize Cloudflare's edge network.

Resources

• Build your first Worker
• Neon serverless driver