PostgreSQL

Postgres is UQL’s most complete backend: native arrays, JSON operators, full-text and pgvector semantic search, cursor streaming, and RETURNING, so writes hand back their generated ids without a second query.

npm install uql-orm pg

Tests and local development have a second entry point onto this same dialect: PGlite is Postgres compiled to WASM, so there is no server to start.

Connect

PgQuerierPool takes node-postgres’ PoolConfig verbatim:

import { PgQuerierPool } from 'uql-orm/postgres';

export const pool = new PgQuerierPool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
});

The one default UQL changes is keepAlive: true, so a managed database’s idle connections are not silently dropped by a NAT. Where to build the pool, how to size max and when to end() it are the same on every driver: see Pool.

Behind PgBouncer or another transaction-mode pooler, session state does not survive between statements, so SET LOCAL, advisory locks and temp tables belong inside one transaction callback.

Isolation levels

All four work, passed straight through:

await pool.transaction(run, { isolationLevel: 'serializable' });

serializable and repeatable read can fail with a retryable serialization error (40001); retry the whole transaction, never a statement inside it.

Upserts know what they did

Postgres upserts compile to INSERT ... ON CONFLICT DO UPDATE, and UQL adds (xmax = 0) AS "_created" to the RETURNING clause, an MVCC trick that reports which branch ran:

const { id, created } = await pool.upsertOne(
  User,
  { email: true },
  { email, name },
);
// created: true when inserted, false when an existing row was updated

Only Postgres reports created per row. MySQL tells insert from update through its affectedRows convention, so it is reliable for a single-row upsert only, and the SQLite family, MariaDB and CockroachDB leave created as undefined. upsertMany reports no created at all: a batch’s row count is a weighted sum where it is reported, and a batch of mixed shapes is several statements.

Row-level security

UQL’s security filters scope every query the ORM generates. Postgres RLS is the backstop under them, enforced even for raw SQL. Set the tenant inside the transaction that uses it, so it is discarded on commit:

await pool.transaction(async (querier) => {
  await querier.run('SET LOCAL app.tenant_id = $1', [tenantId]);
  return querier.findMany(Invoice, { $limit: 50 });
});

SET LOCAL is per-transaction, which is what pooled connections need: a plain SET would leak the tenant to whoever gets that connection next.

Streaming

Cursor streaming needs pg-query-stream, imported lazily on first use:

npm install pg-query-stream

See Streaming.

Cloudflare Hyperdrive

A Worker cannot hold a connection between requests, so every request would otherwise pay a full handshake across the internet. Hyperdrive terminates the Postgres protocol at the edge and keeps the warm pool to your database on its side, and pg runs there unchanged. It needs nodejs_compat and a compatibility date of 2024-09-23 or later:

wrangler.jsonc

{
  "compatibility_date": "2026-08-01",
  "compatibility_flags": ["nodejs_compat"],
  "hyperdrive": [{ "binding": "HYPERDRIVE", "id": "<id>" }],
}

The binding only exists inside a request, so the pool is built there. Hyperdrive owns the real pooling, which is why the local max stays small: the Worker’s own concurrent-connection budget is the limit that matters.

src/index.ts

import { PgQuerierPool } from 'uql-orm/postgres';
import { Item } from './models';

export default {
  async fetch(request: Request, env: Env, ctx: ExecutionContext) {
    const pool = new PgQuerierPool({
      connectionString: env.HYPERDRIVE.connectionString,
      max: 5,
    });
    try {
      const items = await pool.findMany(Item, { $limit: 20 });
      return Response.json(items);
    } finally {
      ctx.waitUntil(pool.end());
    }
  },
};

Migrations run from CI against the database’s own hostname, not through the binding.

Elsewhere

• Under Bun, bun:sql speaks the Postgres protocol natively, so the pg dependency goes away.
• CockroachDB shares this wire protocol with a few deliberate differences.
• Supabase is Postgres with its own pooler endpoints and RLS conventions.
• In a function that freezes between invocations, pool placement changes: see Serverless.