Postgresql

Create a database

After creating your database, you must allow access from outside the private network, due to limitations on accessing databases behind cloudflared. Read more here

Your Database URL should be accessible inside the worker, using this format postgresql://user:password@hostname:5432/db_name

The best way is to define it as a secret like this:

wrangler secret put DB_URL

You may also define it as a variable in the wrangler.toml, but this is not recommended

----
filename: wrangler.toml
----

[vars]
DB_URL = "postgresql://user:password@hostname:5432/db_name"

Setting up the Worker

You must also enable node_compat = true in your wrangler.toml

----
filename: wrangler.toml
----

node_compat = true

You need to install node-postgres:

npm install pg --save

Write queries within your Worker

Remember to close the connection using ctx.waitUntil(qb.close()); or await qb.close(); at the end of your request. You may also reuse this connection to execute multiple queries, or share it between multiple requests if you are using a connection pool in front of your PostgreSQL.

import { PGQB } from 'workers-qb'
import { Client } from 'pg'

export interface Env {
  DB_URL: string
}

export default {
  async fetch(request: Request, env: Env, ctx: ExecutionContext): Promise<Response> {
    const qb = new PGQB(new Client(env.DB_URL))
    await qb.connect()

    const fetched = await qb
      .fetchOne({
        tableName: 'employees',
        fields: 'count(*) as count',
        where: {
          conditions: 'active = ?1',
          params: [true],
        },
      })
      .execute()

    ctx.waitUntil(qb.close())
    return Response.json({
      activeEmployees: fetched.results?.count || 0,
    })
  },
}