How to query Postgres from Cloudflare Workers with Neon serverless driver
Published: 2024-02-15 16:24:25

Cloudflare Workers is a serverless platform that allows developers to build and deploy applications on the edge. By leveraging [Cloudflare Workers](https://developers.cloudflare.com/workers/?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity), 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](https://neon.tech/?ref=hm) 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](https://node-postgres.com/?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity) 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](https://dash.cloudflare.com/sign-up/workers-and-pages?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity).
2. A [Neon Cloud](https://neon.tech/?ref=hm) account
3. [NPM](https://docs.npmjs.com/getting-started) installed with it’s package runner NPX.
4. A PostgreSQL database.

Here is the link to the [GitHub project repository](https://github.com/Davidelvis/Query-Postgres-from-Cloudflare-workers-with-Neon-serverless-driver?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity) to make it easy to follow along.


## Create a Neon project

To create your first Neon project, follow these steps:


1. [Sign in](https://console.neon.tech/login) 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.

![Neon project creation](https://paper-attachments.dropboxusercontent.com/s_E5DFD9AAD2B93DCFCEDE3C785055C7D8015AC8E87BD28019E97637CA43906324_1702337729090_neon-tech.png)

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.

![Connection string](https://paper-attachments.dropboxusercontent.com/s_E5DFD9AAD2B93DCFCEDE3C785055C7D8015AC8E87BD28019E97637CA43906324_1702337739034_neon-tech1.png)

## 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:
```bash
    psql 'postgresql://xxx:xxxx@ep-xxx-xxx-29168360.us-east-2.aws.neon.tech/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.

```bash
    psql 'postgresql://xxx:xxxx@ep-xxx-xxx-29168360.us-east-2.aws.neon.tech/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:

![Data loaded response](https://paper-attachments.dropboxusercontent.com/s_E5DFD9AAD2B93DCFCEDE3C785055C7D8015AC8E87BD28019E97637CA43906324_1701177894777_Screenshot+from+2023-11-28+16-24-35.png)


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.


![Data loaded in Neon database](https://paper-attachments.dropboxusercontent.com/s_E5DFD9AAD2B93DCFCEDE3C785055C7D8015AC8E87BD28019E97637CA43906324_1701178104819_Screenshot+from+2023-11-28+16-27-56.png)

## 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:

```typescript
    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.

```typescript
    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:

```typescript
    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.

```typescript
    //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` :

```typescript
    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.

```typescript
    // 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

```typescript
    const client = new Client(env.DATABASE_URL);
    await client.connect();
```
### Setting explicit parameters

```typescript
    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:

```typescript
    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:

```typescript
    npx wrangler deploy
```

![Screenshot](https://paper-attachments.dropboxusercontent.com/s_E5DFD9AAD2B93DCFCEDE3C785055C7D8015AC8E87BD28019E97637CA43906324_1702038762591_Screenshot+from+2023-12-08+15-31-41.png)


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.

![Screenshot](https://paper-attachments.dropboxusercontent.com/s_E5DFD9AAD2B93DCFCEDE3C785055C7D8015AC8E87BD28019E97637CA43906324_1702067189656_Screenshot+from+2023-12-08+23-26-05.png)


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](https://developers.cloudflare.com/workers/get-started/guide/?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity)
- [Neon serverless driver](https://neon.tech/docs/serverless/serverless-driver?ref=hm)