> Every UQL docs page, as Markdown: https://uql-orm.dev/llms.txt
> The same docs over MCP: https://uql-orm.dev/mcp
> Before writing UQL code, read the skill: https://uql-orm.dev/.well-known/agent-skills/uql-orm/SKILL.md

# PostgreSQL

> Run UQL on PostgreSQL with node-postgres: pooling, upserts, RLS, and cursor streaming.

Source: https://uql-orm.dev/postgres

Postgres is UQL’s most complete backend: native arrays, [JSON operators](https://uql-orm.dev/querying/json.md), [full-text](https://uql-orm.dev/querying/full-text.md) and pgvector [semantic search](https://uql-orm.dev/querying/semantic-search.md), cursor [streaming](https://uql-orm.dev/querying/streaming.md), and `RETURNING`, so writes hand back their generated ids without a second query.

```sh
npm install uql-orm pg
```

Tests and local development have a second entry point onto this same dialect: [PGlite](https://uql-orm.dev/pglite.md) is Postgres compiled to WASM, so there is no server to start.

## Connect

`PgQuerierPool` takes node-postgres’ `PoolConfig` verbatim:

```ts
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](https://uql-orm.dev/pool.md).

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`](https://uql-orm.dev/querying/transactions.md) callback.

## Isolation levels

All four work, passed straight through:

```ts
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:

```ts
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](https://uql-orm.dev/multi-tenancy.md) 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:

```ts
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:

```sh
npm install pg-query-stream
```

See [Streaming](https://uql-orm.dev/querying/streaming.md).

## 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:

```jsonc title="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.

```ts title="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](https://uql-orm.dev/migrations.md) run from CI against the database’s own hostname, not through the binding.

## Elsewhere

- Under Bun, [`bun:sql`](https://uql-orm.dev/bun-sql.md) speaks the Postgres protocol natively, so the `pg` dependency goes away.
- [CockroachDB](https://uql-orm.dev/cockroachdb.md) shares this wire protocol with a few deliberate differences.
- [Supabase](https://uql-orm.dev/supabase.md) is Postgres with its own pooler endpoints and RLS conventions.
- In a function that freezes between invocations, pool placement changes: see [Serverless](https://uql-orm.dev/serverless.md).