Connect to a PostgreSQL database with Cloudflare Workers

Last reviewed: about 1 month ago

In this tutorial, you will learn how to create a Cloudflare Workers application and connect it to a PostgreSQL database using TCP Sockets and Hyperdrive. The Workers application you create in this tutorial will interact with a product database inside of PostgreSQL.

Prerequisites

To continue:

Sign up for a Cloudflare account ↗ if you have not already.
Install npm ↗.
Install Node.js ↗. Use a Node version manager like Volta ↗ or nvm ↗ to avoid permission issues and change Node.js versions. Wrangler requires a Node version of 16.17.0 or later.
Make sure you have access to a PostgreSQL database.

1. Create 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:

npm create cloudflare@latest -- postgres-tutorial

yarn create cloudflare@latest postgres-tutorial

pnpm create cloudflare@latest postgres-tutorial

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

For setup, select the following options:

For What would you like to start with?, choose Hello World example.
For Which template would you like to use?, choose Hello World Worker.
For Which language do you want to use?, choose TypeScript.
For Do you want to use git for version control?, choose Yes.
For Do you want to deploy your application?, choose No (we will be making some changes before deploying).

If you choose to deploy, you will be asked to authenticate (if not logged in already), and your project will be deployed. If you deploy, you can still modify your Worker code and deploy again at the end of this tutorial.

Now, move into the newly created directory:

cd postgres-tutorial

Enable Node.js compatibility

When creating your Worker application, enable Node.js APIs by including the “nodejs_compat_v2” compatibility flag to your wrangler.toml. This will be needed to use the library to connect to your PostgreSQL database.

compatibility_flags = ["nodejs_compat_v2"]

2. Add the PostgreSQL connection library

To connect to a PostgreSQL database, you will need the postgres library. In your Worker application directory, run the following command to install the library:

npm install postgres

Make sure you are using postgres (Postgres.js) version 3.4.4 or higher. Postgres.js is compatible with both Pages and Workers.

3. Configure the connection to the PostgreSQL database

Choose one of the two methods to connect to your PostgreSQL database:

Use a connection string.
Set explicit parameters.

Use a connection string

A connection string contains all the information needed to connect to a database. It is a URL that contains the following information:

postgresql://username:password@host:port/database

Replace username, password, host, port, and database with the appropriate values for your PostgreSQL database.

Set your connection string as a secret so that it is not stored as plain text. Use wrangler secret put with the example variable name DB_URL:

npx wrangler secret put DB_URL

➜  wrangler secret put DB_URL
-------------------------------------------------------
? Enter a secret value: › ********************
✨ Success! Uploaded secret DB_URL

Set your DB_URL secret locally in a .dev.vars file as documented in Local Development with Secrets.

DB_URL="<ENTER YOUR POSTGRESQL CONNECTION STRING>"

Set explicit parameters

Configure each database parameter as an environment variable via the Cloudflare dashboard or in your wrangler.toml file. Refer to an example of awrangler.toml file configuration:

[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 = "productsdb"

To set your password as a secret so that it is not stored as plain text, use wrangler secret put. DB_PASSWORD is an example variable name for this secret to be accessed in your Worker:

npx wrangler secret put DB_PASSWORD

-------------------------------------------------------
? Enter a secret value: › ********************
✨ Success! Uploaded secret DB_PASSWORD

4. Connect to the PostgreSQL database in the Worker

Open your Worker’s main file (for example, worker.ts) and import the Client class from the pg library:

import postgres from "postgres";

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

Use a connection string

const sql = postgres(env.DB_URL);

Set explicit parameters

const sql = postgres({
  username: env.DB_USERNAME,
  password: env.DB_PASSWORD,
  host: env.DB_HOST,
  port: env.DB_PORT,
  database: env.DB_NAME,
});

5. Interact with the products database

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

If you are following along in your own PostgreSQL instance, set up the products using the following SQL CREATE TABLE statement. This statement defines the columns and their respective data types for the products table:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price DECIMAL(10, 2) NOT NULL
);

Replace the existing code in your worker.ts file with the following code:

import postgres from "postgres";

export default {
  async fetch(request, env, ctx): Promise<Response> {
    const sql = postgres(env.DB_URL);

    // Query the products table
    const result = await sql`SELECT * FROM products;`;

    // Return the result as JSON
    const resp = new Response(JSON.stringify(result), {
      headers: { "Content-Type": "application/json" },
    });

    return resp;
  },
} satisfies ExportedHandler<Env>;

This code establishes a connection to the PostgreSQL database within your Worker application and queries the products table, returning the results as a JSON response.

6. Deploy 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 products database.

7. Insert a new row into the products database

To insert a new row into the products table, create a new API endpoint in your Worker that handles a POST request. When a POST request is received with a JSON payload, the Worker will insert a new row into the products table with the provided data.

Assume the products table has the following columns: id, name, description, and price.

Add the following code snippet inside the fetch event handler in your worker.ts file, before the existing query code:

import postgres from "postgres";

export default {
  async fetch(request, env, ctx): Promise<Response> {
    const sql = postgres(env.DB_URL);

    const url = new URL(request.url);
    if (request.method === "POST" && url.pathname === "/products") {
      // Parse the request's JSON payload
      const productData = await request.json();

      // Insert the new product into the database
      const values = {
        name: productData.name,
        description: productData.description,
        price: productData.price,
      };
      const insertResult = await sql`
        INSERT INTO products ${sql(values)}
        RETURNING *
      `;

      // Return the inserted row as JSON
      const insertResp = new Response(JSON.stringify(insertResult), {
        headers: { "Content-Type": "application/json" },
      });

      // Clean up the client
      return insertResp;
    }

    // Query the products table
    const result = await sql`SELECT * FROM products;`;

    // Return the result as JSON
    const resp = new Response(JSON.stringify(result), {
      headers: { "Content-Type": "application/json" },
    });

    return resp;
  },
} satisfies ExportedHandler<Env>;

This code snippet does the following:

Checks if the request is a POST request and the URL path is /products.
Parses the JSON payload from the request.
Constructs an INSERT SQL query using the provided product data.
Executes the query, inserting the new row into the products table.
Returns the inserted row as a JSON response.

Now, when you send a POST request to your Worker’s URL with the /products path and a JSON payload, the Worker will insert a new row into the products table with the provided data. When a request to / is made, the Worker will return all products in the database.

After making these changes, deploy the Worker again by running:

npx wrangler deploy

You can now use your Cloudflare Worker to insert new rows into the products table. To test this functionality, send a POST request to your Worker’s URL with the /products path, along with a JSON payload containing the new product data:

{
  "name": "Sample Product",
  "description": "This is a sample product",
  "price": 19.99
}

You have successfully created a Cloudflare Worker that connects to a PostgreSQL database and handles fetching data and inserting new rows into a products table.

8. Use Hyperdrive to accelerate queries

Create a Hyperdrive configuration using the connection string for your PostgreSQL database.

npx wrangler hyperdrive create <NAME_OF_HYPERDRIVE_CONFIG> --connection-string="postgres://user:password@HOSTNAME_OR_IP_ADDRESS:PORT/database_name"

You can also use explicit parameters by following the wrangler documentation for Hyperdrive.

This command outputs the Hyperdrive configuration id that will be used for your Hyperdrive binding. Set up your binding by specifying the id in the wrangler.toml file.

name = "hyperdrive-example"
main = "src/index.ts"
compatibility_date = "2024-08-21"
compatibility_flags = ["nodejs_compat"]

# Pasted from the output of `wrangler hyperdrive create <NAME_OF_HYPERDRIVE_CONFIG> --connection-string=[...]` above.
[[hyperdrive]]
binding = "HYPERDRIVE"
id = "<ID OF THE CREATED HYPERDRIVE CONFIGURATION>"

Create the types for your Hyperdrive binding using the following command:

npx wrangler types

Replace your existing connection string in your Worker code with the Hyperdrive connection string.

export default {
  async fetch(request, env, ctx): Promise<Response> {
    const sql = postgres(env.HYPERDRIVE.connectionString)

    const url = new URL(request.url);

    //rest of the routes and database queries
  },
} satisfies ExportedHandler<Env>;

9. Redeploy your Worker

Run the following command to deploy your Worker:

npx wrangler deploy

Your Worker application is now live and accessible at <YOUR_WORKER>.<YOUR_SUBDOMAIN>.workers.dev, using Hyperdrive. Hyperdrive accelerates database queries by pooling your connections and caching your requests across the globe.

Next steps

To build more with databases and Workers, refer to Tutorials and explore the Databases documentation.

If you have any questions, need assistance, or would like to share your project, join the Cloudflare Developer community on Discord ↗ to connect with fellow developers and the Cloudflare team.

Cloudflare Dashboard Discord Community Learning Center Support Portal