Cloudflare Docs
Workers
Cloudflare Docs
Workers
GitHub icon
Visit Workers on GitHub
Set theme to dark (⇧+D)
  1. Products
  2. Workers
  3. Tutorials
  4. Query Postgres from Workers using a database connector

Query Postgres from Workers using a database connector

​​ Overview

In this tutorial, you will learn how to retrieve data in your Cloudflare Workers applications from a PostgreSQL database using Postgres database connector.

For a quick start, you will use Docker to run a local instance of Postgres and PgBouncer, and to securely expose the stack to the Internet using Cloudflare Tunnel.

​​ Basic project scaffolding

To get started:

  1. Run the following git command to clone a basic Postgres database connector project.
  2. After running the git clone command, cd into the new project.
$ git clone https://github.com/cloudflare/worker-template-postgres/

$ cd worker-template-postgres

​​ Cloudflare Tunnel authentication

To create and manage secure Cloudflare Tunnels, you first need to authenticate cloudflared CLI. Skip this step if you already have authenticated cloudflared locally.

$ docker run -v ~/.cloudflared:/etc/cloudflared cloudflare/cloudflared:2021.11.0 login

Running this command will:

  • Prompt you to select your Cloudflare account and hostname.
  • Download credentials and allow cloudflared to create Tunnels and DNS records.

​​ Start and prepare Postgres database

​​ Start the Postgres server

You can find a prepared docker-compose file that does not require any changes in scripts/postgres with the following services:

  1. postgres
  2. pgbouncer - Placed in front of Postgres to provide connection pooling.
  3. cloudflared - Allows your applications to connect securely, through a encrypted tunnel, without opening any local ports.

Run the following commands to start all services. Replace postgres-tunnel.example.com with a hostname on your Cloudflare zone to route traffic through this tunnel.

$ cd scripts/postgres

$ export TUNNEL_HOSTNAME=postgres-tunnel.example.com

$ docker compose up



# Alternative: Run `docker compose up -D` to start docker-compose detached

docker-compose will spin up and configure all the services for you, including the creation of the Tunnel’s DNS record. The DNS record will point to the Cloudflare Tunnel, which keeps a secure connection between a local instance of cloudflared and the Cloudflare network.

​​ Import example dataset

Once Postgres is up and running, seed the database with a schema and a dataset. For this tutorial, you will use the Pagila schema and dataset. Use docker exec to execute a command inside the running Postgres container and import Pagila schema and dataset.

$ curl https://raw.githubusercontent.com/devrimgunduz/pagila/master/pagila-schema.sql | docker exec -i postgres_postgresql_1 psql -U postgres -d postgres

$ curl https://raw.githubusercontent.com/devrimgunduz/pagila/master/pagila-data.sql | docker exec -i postgres_postgresql_1 psql -U postgres -d postgres

The above commands will download the SQL schema and dataset files from Pagila’s GitHub repository and execute them in your local Postgres database instance.

​​ Edit Worker and query Pagila dataset

​​ Database connection settings

In src/index.ts, replace https://dev.example.com with your Cloudflare Tunnel hostname, ensuring that it is prefixed with the https:// protocol:

src/index.ts
const client = new Client({
  user: 'postgres',
  database: 'postgres',
  hostname: 'https://REPLACE_WITH_TUNNEL_HOSTNAME',
  password: '',
  port: 5432,

});

At this point, you can deploy your Worker and make a request to it to verify that your database connection is working.

​​ Query Pagila dataset

The template script includes a simple query to select a number (SELECT 42;) that is executed in the database. Edit the script to query the imported Pagila dataset if the pagila-table query parameter is present.

// Query the database.



// Parse the URL, and get the 'pagila-table' query parameter (which may not exist)

const url = new URL(request.url);

const pagilaTable = url.searchParams.get('pagila-table');



let result;

// if pagilaTable is defined, run a query on the Pagila dataset

if (
  [
    'actor',
    'address',
    'category',
    'city',
    'country',
    'customer',
    'film',
    'film_actor',
    'film_category',
    'inventory',
    'language',
    'payment',
    'payment_p2020_01',
    'payment_p2020_02',
    'payment_p2020_03',
    'payment_p2020_04',
    'payment_p2020_05',
    'payment_p2020_06',
    'rental',
    'staff',
    'store',
  ].includes(pagilaTable)

) {
  result = await client.queryObject(`SELECT * FROM ${pagilaTable};`);

} else {
  const param = 42;
  result = await client.queryObject(`SELECT ${param} as answer;`);

}



// Return result from database.

return new Response(JSON.stringify(result));

​​ Worker deployment

In wrangler.toml, enter your Cloudflare account ID in the line containing account_id:

wrangler.toml
name = "worker-postgres-template"

type = "javascript"

account_id = ""

Publish your function:

$ wrangler publish
✨  Built successfully, built project size is 10 KiB.
✨  Successfully published your script to
 https://workers-postgres-template.example.workers.dev

​​ Set secrets

Create and save a Client ID and a Client Secret to Worker secrets in case your Tunnel is protected by Cloudflare Access.

$ wrangler secret put CF_CLIENT_ID

$ wrangler secret put CF_CLIENT_SECRET

​​ Test the Worker

Request some of the Pagila tables by adding the ?pagila-table query parameter with a table name to the URL of the Worker.

$ curl https://example.workers.dev/?pagila-table=actor

$ curl https://example.workers.dev/?pagila-table=address

$ curl https://example.workers.dev/?pagila-table=country

$ curl https://example.workers.dev/?pagila-table=language

​​ Cleanup

Run the following command to stop and remove the Docker containers and networks:

$ docker compose down



# Stop and remove containers, networks

If you found this tutorial useful, continue building with other Cloudflare Workers tutorials below.