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

Connect

PgQuerierPool takes node-postgres’ PoolConfig verbatim:

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

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

setQuerierPool(pool);

The one default UQL changes is keepAlive: true, so a managed database’s idle connections are not silently dropped by a NAT. Create the pool once per process at module scope and end() it on shutdown; nothing connects until the first query.

Keep max below the server’s max_connections divided by your instance count. Behind PgBouncer in transaction mode, 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 { firstId, created } = await pool.upsertOne(User, { email: true }, { email, name });
// created: true when inserted, false when an existing row was updated

Only Postgres reports this per row; the SQLite family, MariaDB and CockroachDB leave created as undefined.

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

To-many relations cannot be populated on the stream path. See Streaming.

Cloudflare Hyperdrive

Workers cannot open a raw socket, but Hyperdrive terminates the Postgres protocol at the edge and keeps a warm pool to your database, so 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 { Product } from './models';

export default {
  async fetch(request: Request, env: Env, ctx: ExecutionContext) {
    const pool = new PgQuerierPool({ connectionString: env.HYPERDRIVE.connectionString, max: 5 });
    try {
      const products = await pool.findMany(Product, { $limit: 20 });
      return Response.json(products);
    } 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.