Today let’s have deeper look at Cloudflare workers postgresql article by our Server management support services at Bobcares.

PostgreSQL from Cloudflare Workers using a database connector

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

To get started:

• First, run the below git command to clone a basic Postgres database connector project.

• After running the git clone command, open 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 need to authenticate cloudflared CLI. You can skip this step if you have already authenticated cloudflared on your system.

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

Running this command will:

• Prompts to select your Cloudflare account and server name.

• Download credentials and allowcloudflared to create Tunnels and DNS records.

Initiate and prepare the Postgres database

Start the Postgres server

You can get access to a prepared docker-compose file that does not require any changes in scripts/postgres with the below services:

• postgres

• pgbouncer : to provide connection pooling that placed in front of Postgres .

• Cloudflare : Allows your applications to connect securely, through an encrypted tunnel. Can be done without opening any local ports.

Run the following commands to start all services. Replace postgres-tunnel.example.com with server name 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

Thedocker-compose will spin up and configure all the services. This includes the installation of Tunnel’s DNS record. The DNS record will point to the Cloudflare Tunnel which secures the connection between a local instance of cloudflared and the Cloudflare network.

​​ Import example dataset

Once Postgres is up and running, set the database with a schema and a dataset. Here the Pagila schema and dataset used in this article. Use docker exec to execute a command for running the Postgres container and to 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

This will download the SQL schema and dataset files from Pagila’s GitHub repository, and execute them in a local Postgres database instance.

​​ Edit Worker and query Pagila dataset

​​ Database connection settings

In src/index.ts, replace https://dev.example.com with Cloudflare Tunnel servername, 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 Worker, and makes a request 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 will execute in the database. Edit the script to import the 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;`);
}

​​ Worker deployment

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

wrangler.tomlname = "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

Next, create and save a Client ID. Here are the Client Secret Worker, which 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 for Pagila tables to add the ?pagila-table parameter with a table name to 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 below command to stop and remove the Docker containers and networks:

docker compose down

# Stop and remove containers, networks

[Looking for a solution to another query? We are just a click away.]

Conclusion

To conclude, From this Cloudflare workers PostgreSQL article, you have seen how to fetch data in your Cloudflare Workers applications from a PostgreSQL database using a Postgres database connector.