# @constructive-io/query-builder
AST-backed PostgreSQL query builder powered by `pg-ast` and `pgsql-deparser`. Builds parameterized SQL from typed AST nodes — no string concatenation, no manual escaping.
## Install
```sh
npm install @constructive-io/query-builder
```
## Features
- All SQL generated from `pg-ast` AST nodes, deparsed via `pgsql-deparser`
- Auto-parameterized values (`$1`, `$2`, ...) with a separate `values` array
- Fluent, chainable API
- SDK-style JSON filters for `WHERE`/`HAVING` (`{ status: { equalTo: 'active' } }`), matching the generated ORM/SDK filter grammar
- Composable expressions (`col()`, `fn()`, `add()`, `eq()`, ...) usable in SELECT, WHERE, SET, ON CONFLICT, and RETURNING
- Full support for: `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `JOIN`, `CTE`, `ON CONFLICT`, `RETURNING`, `GROUP BY`, `HAVING`, `ORDER BY`, `LIMIT`/`OFFSET`, `FOR UPDATE`/`FOR SHARE`, `DISTINCT`, function/procedure calls
## Usage
### SELECT
```ts
import { QueryBuilder } from '@constructive-io/query-builder';
const { text, values } = new QueryBuilder()
.table('users')
.select(['id', 'name', 'email'])
.where({ age: { greaterThan: 18 } })
.limit(10)
.build();
// text:
// SELECT
// id,
// name,
// email
// FROM users
// WHERE
// age > $1
// LIMIT 10
//
// values: [18]
```
### WHERE filters (SDK JSON style)
`.where()` takes the same JSON filter shape as the generated ORM/SDK clients. Multiple `.where()` calls AND-combine.
```ts
new QueryBuilder()
.table('jobs')
.select(['id'])
.where({
status: { in: ['queued', 'retry'] },
attempts: { lessThan: 5 },
completed_at: { isNull: true },
or: [
{ priority: { greaterThan: 0 } },
{ escalated: { equalTo: true } },
],
})
.build();
// WHERE status IN ($1, $2) AND attempts < $3
// AND completed_at IS NULL
// AND (priority > $4 OR escalated = $5)
```
Supported field operators:
| Operator | SQL |
|----------|-----|
| `equalTo` / `notEqualTo` | `=` / `<>` |
| `lessThan` / `lessThanOrEqualTo` | `<` / `<=` |
| `greaterThan` / `greaterThanOrEqualTo` | `>` / `>=` |
| `in` / `notIn` | `IN (...)` / `NOT IN (...)` (array or subquery) |
| `isNull: true` / `isNull: false` | `IS NULL` / `IS NOT NULL` |
| `distinctFrom` / `notDistinctFrom` | `IS DISTINCT FROM` / `IS NOT DISTINCT FROM` |
| `like` / `notLike` | `LIKE` / `NOT LIKE` |
| `likeInsensitive` / `notLikeInsensitive` | `ILIKE` / `NOT ILIKE` |
| `includes`, `startsWith`, `endsWith` (+ `not*` / `*Insensitive` variants) | `LIKE`/`ILIKE` with auto `%` patterns |
Boolean combinators: `and: [...]`, `or: [...]`, `not: {...}` — nest arbitrarily. Unknown operators and empty `in` arrays throw at build time.
Operands can be plain values (parameterized), expressions, or subqueries:
```ts
// Column-to-column and function comparisons
import { col, fn } from '@constructive-io/query-builder';
new QueryBuilder()
.table('jobs')
.select(['id'])
.where({
updated_at: { lessThan: fn('now') },
a: { equalTo: col('b') },
})
.build();
// Subqueries
const teamIds = new QueryBuilder()
.table('teams')
.select(['id'])
.where({ active: { equalTo: true } });
new QueryBuilder()
.table('users')
.select(['id'])
.where({ team_id: { in: teamIds } })
.build();
```
### Expression predicates
`.where()` / `.having()` also accept expressions directly, for predicates the JSON grammar can't express (column-to-column, arithmetic, function calls). Filters and expressions mix freely and AND-combine:
```ts
import {
and, or, not, eq, neq, lt, lte, gt, gte,
isNull, isNotNull, col, fn,
} from '@constructive-io/query-builder';
new QueryBuilder()
.table('jobs')
.select(['id'])
.where(and(
lte(col('attempts'), col('max_attempts')),
or(gt(col('priority'), 5), isNull(col('locked_at')))
))
.build();
new QueryBuilder()
.table('orders')
.select(['customer_id'])
.groupBy(['customer_id'])
.having(gt(fn('sum', [col('total')]), 1000))
.build();
```
### INSERT with RETURNING
```ts
const { text, values } = new QueryBuilder()
.table('users')
.insert({ name: 'Alice', email: 'alice@example.com' })
.returning(['id'])
.build();
// text:
// INSERT INTO users (
// name,
// email
// ) VALUES
// ($1, $2) RETURNING id
//
// values: ['Alice', 'alice@example.com']
```
### UPDATE
```ts
const { text, values } = new QueryBuilder()
.table('users')
.update({ name: 'Alice Updated' })
.where({ id: { equalTo: 1 } })
.build();
// text: UPDATE users SET name = $1 WHERE id = $2
// values: ['Alice Updated', 1]
```
SET values accept expressions too:
```ts
import { add, col, fn } from '@constructive-io/query-builder';
new QueryBuilder()
.table('jobs')
.update({
attempts: add(col('attempts'), 1),
updated_at: fn('now'),
})
.where({ completed_at: { isNull: true } })
.returning(['id'])
.build();
// UPDATE jobs SET attempts = attempts + $1, updated_at = now()
// WHERE completed_at IS NULL RETURNING id
```
### DELETE
```ts
const { text, values } = new QueryBuilder()
.table('users')
.delete()
.where({ id: { equalTo: 1 } })
.build();
// text: DELETE FROM users WHERE id = $1
// values: [1]
```
### ON CONFLICT (upsert)
```ts
const { text, values } = new QueryBuilder()
.table('users')
.insert({ name: 'Alice', email: 'alice@example.com' })
.onConflict({
columns: ['email'],
action: 'update',
updateColumns: { name: 'Alice Updated' },
})
.returning(['id'])
.build();
// text:
// INSERT INTO users (
// name,
// email
// ) VALUES
// ($1, $2) ON CONFLICT (email) DO UPDATE SET
// name = $3 RETURNING id
//
// values: ['Alice', 'alice@example.com', 'Alice Updated']
```
`updateColumns` values accept expressions, and `where` accepts a JSON filter:
```ts
import { add, col } from '@constructive-io/query-builder';
new QueryBuilder()
.table('counters')
.insert({ key: 'k', count: 1 })
.onConflict({
columns: ['key'],
action: 'update',
updateColumns: { count: add(col('counters.count'), col('excluded.count')) },
where: { locked: { equalTo: false } },
})
.build();
```
### RETURNING expressions
`.returning()` accepts column names or aliased expressions:
```ts
import { col, fn } from '@constructive-io/query-builder';
new QueryBuilder()
.table('users')
.insert({ name: 'Alice' })
.returning(['id', { expr: fn('lower', [col('name')]), as: 'name_lower' }])
.build();
// INSERT INTO users (name) VALUES ($1) RETURNING id, lower(name) AS name_lower
```
INSERT values accept expressions too:
```ts
new QueryBuilder()
.table('jobs')
.insert({ name: 'job-1', created_at: fn('now') })
.returning(['id'])
.build();
// INSERT INTO jobs (name, created_at) VALUES ($1, now()) RETURNING id
```
### JOINs
```ts
const { text, values } = new QueryBuilder()
.table('orders')
.select(['orders.id', 'customers.name'])
.innerJoin('customers', 'orders.customer_id', '=', 'customers.id')
.build();
// text:
// SELECT
// orders.id,
// customers.name
// FROM orders
// JOIN customers ON orders.customer_id = customers.id
```
Supported join types: `.innerJoin()`, `.leftJoin()`, `.rightJoin()`, `.fullJoin()`.
ON conditions also accept a JSON filter or an expression, for multi-condition or value-comparing joins:
```ts
import { and, eq, col } from '@constructive-io/query-builder';
new QueryBuilder()
.table('orders', 'o')
.select(['o.id'])
.innerJoin('customers', and(
eq(col('o.customer_id'), col('customers.id')),
eq(col('customers.region'), 'us')
))
.build();
// JOIN customers ON o.customer_id = customers.id AND customers.region = $1
new QueryBuilder()
.table('orders', 'o')
.select(['o.id'])
.leftJoin('customers', { 'c.active': { equalTo: true } }, { schema: 'crm', alias: 'c' })
.build();
// LEFT JOIN crm.customers AS c ON c.active = $1
```
### Set-returning functions (fromFunction)
Use a function as the FROM source with a range alias:
```ts
new QueryBuilder()
.select(['r.id', 'r.name'])
.fromFunction('get_rows', [7], { as: 'r' })
.where({ 'r.status': { equalTo: 'ok' } })
.build();
// SELECT r.id, r.name FROM get_rows($1) AS r WHERE r.status = $2
```
Joins, WHERE filters, ORDER BY, etc. compose over the function source like a table.
### Cloning
Builders are mutable; `.clone()` copies the configured state so variants never affect the base:
```ts
const base = new QueryBuilder()
.table('jobs')
.select(['id'])
.where({ status: { equalTo: 'queued' } });
const urgent = base.clone().where({ priority: { greaterThan: 5 } }).limit(1);
// base is unchanged
```
### CTEs (WITH clauses)
```ts
const activeCte = new QueryBuilder()
.table('users')
.select(['id', 'name'])
.where({ active: { equalTo: true } });
const { text, values } = new QueryBuilder()
.with('active_users', activeCte)
.table('active_users')
.select(['*'])
.build();
// text:
// WITH
// active_users AS (SELECT
// id,
// name
// FROM users
// WHERE
// active = $1)
// SELECT *
// FROM active_users
//
// values: [true]
```
Use `.withRecursive('name', subquery)` for recursive CTEs.
### Schema-qualified tables
```ts
const { text } = new QueryBuilder()
.schema('app_public')
.table('users')
.select(['id', 'name'])
.build();
// text:
// SELECT
// id,
// name
// FROM app_public.users
```
### Function calls
```ts
// Positional args
const { text, values } = new QueryBuilder()
.call('my_function', [42, 'test'])
.build();
// text: SELECT my_function($1, $2)
// values: [42, 'test']
// Named args
const { text, values } = new QueryBuilder()
.schema('auth')
.call('authenticate', { email: 'alice@example.com', password: 'secret' })
.build();
// text: SELECT auth.authenticate(email => $1, password => $2)
// values: ['alice@example.com', 'secret']
// Result alias — rows come back under a stable key instead of the function name
const { text } = new QueryBuilder()
.call('rollup_compute_daily', { day: '2026-01-01' }, { schema: 'private', as: 'result' })
.build();
// text: SELECT private.rollup_compute_daily(day => $1) AS result
```
Named args accept expressions (`col()`, `param()`, `lit()`, `fn()`) as well as plain values:
```ts
import { col, lit, param } from '@constructive-io/query-builder';
new QueryBuilder()
.schema('priv')
.call('secrets_get', {
owner_id: col('s.owner_id'),
ns: param('1111-...'),
default_value: lit(null),
})
.build();
// SELECT priv.secrets_get(owner_id => s.owner_id, ns => $1, default_value => NULL)
```
### Computed SELECT columns
```ts
import { col, fn } from '@constructive-io/query-builder';
new QueryBuilder()
.schema('priv')
.table('secrets', 's')
.select(['s.name'])
.selectCall('decrypted_value', 'secrets_get', {
secret_name: col('s.name'),
}, { schema: 'priv' })
.build();
// SELECT s.name, priv.secrets_get(secret_name => s.name) AS decrypted_value
// FROM priv.secrets AS s
// Or with an arbitrary expression:
new QueryBuilder()
.table('t')
.selectExpr('v', fn('f', { a: param('A'), b: col('t.b') }))
.build();
```
### Additional clauses
```ts
// GROUP BY + HAVING
new QueryBuilder()
.table('orders')
.select(['customer_id'])
.groupBy(['customer_id'])
.having({ total: { greaterThan: 1000 } })
.build();
// ORDER BY with NULLS
new QueryBuilder()
.table('users')
.select(['*'])
.orderBy('name', 'ASC', 'FIRST')
.build();
// ORDER BY / GROUP BY expressions
new QueryBuilder()
.table('events')
.select([{ expr: fn('date_trunc', [lit('day'), col('created_at')]), as: 'day' }])
.groupBy([fn('date_trunc', [lit('day'), col('created_at')])])
.orderBy(fn('lower', [col('name')]), 'DESC')
.build();
// DISTINCT
new QueryBuilder()
.table('events')
.select(['type'])
.distinct()
.build();
// OFFSET
new QueryBuilder()
.table('products')
.select(['*'])
.limit(10)
.offset(20)
.build();
// FOR UPDATE — lock the row a read-then-write is about to change
new QueryBuilder()
.schema('app_public')
.table('runs')
.select(['id', 'status'])
.where({ id: { equalTo: runId } })
.lock()
.build();
// FOR UPDATE SKIP LOCKED — a queue claim moves on rather than waiting
new QueryBuilder()
.table('jobs')
.select(['id'])
.where({ status: { equalTo: 'pending' } })
.orderBy('created_at', 'ASC')
.limit(1)
.lock('update', { skipLocked: true })
.build();
```
## API
### `QueryBuilder`
| Method | Description |
|--------|-------------|
| `.table(name)` | Set the target table |
| `.schema(name)` | Set the schema (e.g. `app_public`) |
| `.select(columns)` | Build a SELECT with given columns (use `['*']` for all) |
| `.selectCall(as, fnName, args?, opts?)` | Append a computed function-call column |
| `.selectExpr(as, expr)` | Append a computed expression column |
| `.insert(data)` | Build an INSERT (single row object or array of rows; values may be `Expr`) |
| `.update(data)` | Build an UPDATE with `{ column: value \| Expr }` SET pairs |
| `.delete()` | Build a DELETE |
| `.where(...predicates)` | JSON filters and/or expressions (AND-combined across calls) |
| `.innerJoin(table, on, opts?)` | INNER JOIN; `on` is `(leftCol, op, rightCol)` or a `Filter`/`Expr` |
| `.leftJoin(table, on, opts?)` | LEFT JOIN |
| `.rightJoin(table, on, opts?)` | RIGHT JOIN |
| `.fullJoin(table, on, opts?)` | FULL JOIN |
| `.orderBy(col \| expr, dir?, nulls?)` | ORDER BY clause |
| `.groupBy(columns)` | GROUP BY clause (column names or `Expr`s) |
| `.having(...predicates)` | HAVING clause (JSON filters and/or expressions) |
| `.limit(n)` | LIMIT clause |
| `.offset(n)` | OFFSET clause |
| `.lock(strength?, opts?)` | Row lock: `'update'` (default), `'noKeyUpdate'`, `'share'`, `'keyShare'`; `opts: { skipLocked?, noWait? }` |
| `.distinct()` | SELECT DISTINCT |
| `.returning(columns)` | RETURNING clause (column names or `{ expr, as }` items) |
| `.onConflict(opts)` | ON CONFLICT (DO NOTHING or DO UPDATE) |
| `.with(name, subquery)` | CTE (WITH clause) |
| `.withRecursive(name, subquery)` | Recursive CTE |
| `.call(fnName, args?, opts?)` | Function/procedure call; `opts: { schema?, as? }`; args positional array or named record |
| `.fromFunction(fnName, args?, opts?)` | Set-returning function as FROM source; `opts: { schema?, as? }` |
| `.clone()` | Copy the builder for safe composition |
| `.build()` | Returns `{ text: string, values: SqlValue[] }` |
| `.toSQL()` | Returns just the SQL string |
### Expression helpers
| Helper | Description |
|--------|-------------|
| `col('t.column')` | Column reference (never parameterized) |
| `param(value)` | Explicitly bound parameter |
| `lit(value)` | Inline literal (e.g. `lit(null)` → `NULL`) |
| `fn(name, args?, opts?)` | Function call expression |
| `eq, neq, lt, lte, gt, gte` | Comparisons |
| `add, sub, mul, div` | Arithmetic |
| `isNull(x), isNotNull(x)` | Null tests |
| `and(...), or(...), not(x)` | Boolean combinators |
All helpers accept plain values (auto-parameterized) or other expressions.
## How it works
The builder constructs `pg-ast` AST nodes (`SelectStmt`, `InsertStmt`, `UpdateStmt`, `DeleteStmt`, etc.) and depars them via `pgsql-deparser`'s `deparseSync()`. All values are replaced with `ParamRef` nodes (`$1`, `$2`, ...) and collected into a separate `values` array for safe parameterized execution.
## Pairs well with `pgsql-test`
```ts
import { getConnections } from 'pgsql-test';
import { QueryBuilder } from '@constructive-io/query-builder';
const { db, teardown } = await getConnections();
const { text, values } = new QueryBuilder()
.table('users')
.select(['id', 'name'])
.where({ active: { equalTo: true } })
.build();
const rows = await db.any(text, values);
```
## Running Tests
```sh
pnpm test
pnpm test:watch
```
---
## Education and Tutorials
1. 🚀 [Quickstart: Getting Up and Running](https://constructive.io/learn/quickstart)
Get started with modular databases in minutes. Install prerequisites and deploy your first module.
2. 📦 [Modular PostgreSQL Development with Database Packages](https://constructive.io/learn/modular-postgres)
Learn to organize PostgreSQL projects with pgpm workspaces and reusable database modules.
3. ✏️ [Authoring Database Changes](https://constructive.io/learn/authoring-database-changes)
Master the workflow for adding, organizing, and managing database changes with pgpm.
4. 🧪 [End-to-End PostgreSQL Testing with TypeScript](https://constructive.io/learn/e2e-postgres-testing)
Master end-to-end PostgreSQL testing with ephemeral databases, RLS testing, and CI/CD automation.
5. ⚡ [Supabase Testing](https://constructive.io/learn/supabase)
Use TypeScript-first tools to test Supabase projects with realistic RLS, policies, and auth contexts.
6. 💧 [Drizzle ORM Testing](https://constructive.io/learn/drizzle-testing)
Run full-stack tests with Drizzle ORM, including database setup, teardown, and RLS enforcement.
7. 🔧 [Troubleshooting](https://constructive.io/learn/troubleshooting)
Common issues and solutions for pgpm, PostgreSQL, and testing.
## Related Constructive Tooling
### 📦 Package Management
* [pgpm](https://github.com/constructive-io/constructive/tree/main/pgpm/pgpm): **🖥️ PostgreSQL Package Manager** for modular Postgres development. Works with database workspaces, scaffolding, migrations, seeding, and installing database packages.
### 🧪 Testing
* [pgsql-test](https://github.com/constructive-io/constructive/tree/main/postgres/pgsql-test): **📊 Isolated testing environments** with per-test transaction rollbacks—ideal for integration tests, complex migrations, and RLS simulation.
* [pglite-test](https://github.com/constructive-io/constructive/tree/main/postgres/pglite-test): **🪶 Drop-in pgsql-test replacement backed by PGlite** — in-process Postgres, no server required, instance-per-suite isolation.
* [pgsql-seed](https://github.com/constructive-io/constructive/tree/main/postgres/pgsql-seed): **🌱 PostgreSQL seeding utilities** for CSV, JSON, SQL data loading, and pgpm deployment.
* [supabase-test](https://github.com/constructive-io/constructive/tree/main/postgres/supabase-test): **🧪 Supabase-native test harness** preconfigured for the local Supabase stack—per-test rollbacks, JWT/role context helpers, and CI/GitHub Actions ready.
* [graphile-test](https://github.com/constructive-io/constructive/tree/main/graphile/graphile-test): **🔐 Authentication mocking** for Graphile-focused test helpers and emulating row-level security contexts.
* [pg-query-context](https://github.com/constructive-io/constructive/tree/main/postgres/pg-query-context): **🔒 Session context injection** to add session-local context (e.g., `SET LOCAL`) into queries—ideal for setting `role`, `jwt.claims`, and other session settings.
### 🧠 Parsing & AST
* [pgsql-parser](https://www.npmjs.com/package/pgsql-parser): **🔄 SQL conversion engine** that interprets and converts PostgreSQL syntax.
* [libpg-query-node](https://www.npmjs.com/package/libpg-query): **🌉 Node.js bindings** for `libpg_query`, converting SQL into parse trees.
* [pg-proto-parser](https://www.npmjs.com/package/pg-proto-parser): **📦 Protobuf parser** for parsing PostgreSQL Protocol Buffers definitions to generate TypeScript interfaces, utility functions, and JSON mappings for enums.
* [@pgsql/enums](https://www.npmjs.com/package/@pgsql/enums): **🏷️ TypeScript enums** for PostgreSQL AST for safe and ergonomic parsing logic.
* [@pgsql/types](https://www.npmjs.com/package/@pgsql/types): **📝 Type definitions** for PostgreSQL AST nodes in TypeScript.
* [@pgsql/utils](https://www.npmjs.com/package/@pgsql/utils): **🛠️ AST utilities** for constructing and transforming PostgreSQL syntax trees.
### 📚 Documentation & Skills
* [constructive-skills](https://github.com/constructive-io/constructive-skills): **📖 Platform documentation and AI agent skills** — feature catalog, blueprint reference, SDK guides (i18n, billing, limits, events, uploads, security, entities, search, AI), and deployment guides.
Install skills for AI coding agents:
```bash
# All platform skills (security, blueprints, codegen, billing, etc.)
npx skills add constructive-io/constructive-skills
# Individual repo skills (pgpm, testing, CLI, search, etc.)
npx skills add https://github.com/constructive-io/constructive --skill pgpm
npx skills add https://github.com/constructive-io/constructive --skill constructive-testing
```
## Credits
**🛠 Built by the [Constructive](https://constructive.io) team — creators of modular Postgres tooling for secure, composable backends. If you like our work, contribute on [GitHub](https://github.com/constructive-io).**
## Disclaimer
AS DESCRIBED IN THE LICENSES, THE SOFTWARE IS PROVIDED "AS IS", AT YOUR OWN RISK, AND WITHOUT WARRANTIES OF ANY KIND.
No developer or entity involved in creating this software will be liable for any claims or damages whatsoever associated with your use, inability to use, or your interaction with other users of the code, including any direct, indirect, incidental, special, exemplary, punitive or consequential damages, or loss of profits, cryptocurrencies, tokens, or anything else of value.