@axiomdb/core

Core types, AST nodes, and query builder for AxiomDB. This package provides the immutable, type-safe query builder API that all other AxiomDB packages build on.

Installation

npm install @axiomdb/core

API Reference

Every method in an AxiomDB chain compiles to a SQL string and a params array. No filtering, sorting, joining, or aggregation ever happens in JavaScript — every operation is a SQL clause. The final object passed to your DB driver is always { text: string, params: any[] }.

Table of Contents

1. Table References
2. SELECT
3. WHERE conditions
4. JOINs
5. GROUP BY and HAVING
6. ORDER BY
7. LIMIT, OFFSET, and FETCH
8. INSERT
9. UPDATE
10. DELETE
11. RETURNING
12. CTEs (WITH)
13. Subqueries
14. Window functions
15. Set operations
16. LOCK
17. Escape hatch
18. Composability

1. Table References

All queries begin with a table() call. This is what the Babel plugin recognises as a query root.

const users   = table('users')
const orders  = table('orders', { schema: 'sales' })   // → sales.orders
const archive = table('events',  { schema: 'logs', alias: 'e' })  // → logs.events AS e

table(name, options?) options:

| Option | Type | Description | |--------|------|-------------| | schema | string | Prepends schema. to the table name | | alias | string | Adds AS alias to the FROM clause |

2. SELECT

2.1 Basic columns

users.select({ id: true, name: true, email: true })
// → SELECT id, name, email FROM users

2.2 All columns

users.select('*')
// → SELECT * FROM users

2.3 Column aliases

The value is the alias string instead of true.

users.select({ id: true, name: 'username', created_at: 'joined' })
// → SELECT id, name AS username, created_at AS joined FROM users

2.4 Qualified columns (after a JOIN)

users.select({ 'u.id': true, 'o.total': true })
// → SELECT u.id, o.total FROM users

2.5 Aggregate functions

The value is an object with a fn key.

orders.select({
  user_id: true,
  total:   { fn: 'SUM',   as: 'total_spend' },
  id:      { fn: 'COUNT', as: 'order_count' },
  amount:  { fn: 'AVG',   as: 'avg_order'   },
  amount:  { fn: 'MAX',   as: 'largest'     },
  amount:  { fn: 'MIN',   as: 'smallest'    },
})
// → SELECT user_id, SUM(total) AS total_spend, COUNT(id) AS order_count,
//          AVG(amount) AS avg_order, MAX(amount) AS largest, MIN(amount) AS smallest
//   FROM orders

Supported aggregate functions: COUNT, SUM, AVG, MIN, MAX, BOOL_AND, BOOL_OR, ARRAY_AGG, JSON_AGG, JSONB_AGG, STRING_AGG.

STRING_AGG requires a separator:

users.select({
  name: { fn: 'STRING_AGG', separator: ', ', order: { col: 'name', dir: 'ASC' }, as: 'names' }
})
// → SELECT STRING_AGG(name, ', ' ORDER BY name ASC) AS names FROM users

2.6 DISTINCT

users.select({ country: true }).distinct()
// → SELECT DISTINCT country FROM users

2.7 DISTINCT ON

orders.select({ user_id: true, total: true }).distinctOn(['user_id'])
// → SELECT DISTINCT ON (user_id) user_id, total FROM orders

2.8 Expressions and casts

For computed columns that don't fit the fn shorthand, use raw() (see Escape hatch):

users.select({
  id:         true,
  age_group:  raw("CASE WHEN age < 18 THEN 'minor' WHEN age < 65 THEN 'adult' ELSE 'senior' END"),
  score_text: raw("score::text"),
})

3. WHERE conditions

3.1 Single condition

users.select('*').where({ column: 'age', op: GT, value: 18 })
// → SELECT * FROM users WHERE age > $1   params: [18]

3.2 Chained .where() — implicit AND

Each additional .where() call appends to the WHERE clause with AND.

users.select('*')
  .where({ column: 'age',    op: GT,  value: 18     })
  .where({ column: 'active', op: EQ,  value: true   })
  .where({ column: 'role',   op: NEQ, value: 'banned' })
// → WHERE age > $1 AND active = $2 AND role != $3
//   params: [18, true, 'banned']

3.3 OR — explicit combinator

users.select('*').where(
  or(
    { column: 'role',  op: EQ, value: 'admin' },
    { column: 'role',  op: EQ, value: 'owner' },
  )
)
// → WHERE (role = $1 OR role = $2)

3.4 AND — explicit combinator (for nesting inside OR)

users.select('*').where(
  or(
    and(
      { column: 'age',  op: GTE, value: 18 },
      { column: 'tier', op: IN,  values: ['gold', 'platinum'] },
    ),
    { column: 'role', op: EQ, value: 'admin' },
  )
)
// → WHERE ((age >= $1 AND tier IN ($2, $3)) OR role = $4)

3.5 NOT

users.select('*').where(
  not({ column: 'status', op: EQ, value: 'banned' })
)
// → WHERE NOT (status = $1)

3.6 Full operator reference

| Operator constant | SQL emitted | Notes | |-------------------|-------------|-------| | EQ | col = $n | | | NEQ | col != $n | | | GT | col > $n | | | GTE | col >= $n | | | LT | col < $n | | | LTE | col <= $n | | | LIKE | col LIKE $n | | | ILIKE | col ILIKE $n | Case-insensitive LIKE | | NOT_LIKE | col NOT LIKE $n | | | NOT_ILIKE | col NOT ILIKE $n | | | IN | col IN ($n, $n+1, …) | Pass values array | | NOT_IN | col NOT IN ($n, …) | Pass values array | | BETWEEN | col BETWEEN $n AND $n+1 | Pass min and max | | NOT_BETWEEN | col NOT BETWEEN $n AND $n+1 | | | IS_NULL | col IS NULL | No value needed | | IS_NOT_NULL | col IS NOT NULL | No value needed | | IS_TRUE | col IS TRUE | | | IS_FALSE | col IS FALSE | | | IS_UNKNOWN | col IS UNKNOWN | | | OVERLAPS | col && $n | Array/range overlap | | CONTAINS | col @> $n | Array/JSONB contains | | CONTAINED_BY | col <@ $n | Array/JSONB contained by | | ANY | $n = ANY(col) | Value in array column | | SIMILAR_TO | col SIMILAR TO $n | SQL regex | | POSIX_MATCH | col ~ $n | Case-sensitive POSIX regex | | POSIX_IMATCH | col ~* $n | Case-insensitive POSIX regex |

3.7 Qualified column names

For conditions on joined tables, prefix the column with the table name or alias.

.where({ column: 'orders.status', op: EQ, value: 'active' })
// → WHERE orders.status = $1

4. JOINs

All joins are declared before .select() resolves column qualification, but may appear anywhere in the chain — the compiler always emits them after FROM and before WHERE.

4.1 INNER JOIN (default)

users
  .join(orders, { on: { left: 'users.id', right: 'orders.user_id' } })
  .select({ 'users.name': true, 'orders.total': true })
// → SELECT users.name, orders.total
//   FROM users
//   INNER JOIN orders ON users.id = orders.user_id

4.2 Join types

The type option controls the join keyword.

users.join(orders, { type: 'LEFT',  on: { left: 'users.id', right: 'orders.user_id' } })
users.join(orders, { type: 'RIGHT', on: { left: 'users.id', right: 'orders.user_id' } })
users.join(orders, { type: 'FULL',  on: { left: 'users.id', right: 'orders.user_id' } })
users.join(orders, { type: 'CROSS' })  // No ON clause

4.3 Join alias

users.join(orders, { alias: 'o', on: { left: 'users.id', right: 'o.user_id' } })
// → INNER JOIN orders AS o ON users.id = o.user_id

4.4 Multi-condition ON

For joins with compound keys or inequality conditions, use onRaw():

users.join(orders, {
  onRaw: raw('users.id = orders.user_id AND orders.deleted_at IS NULL')
})

4.5 Chained joins

users
  .join(orders,   { alias: 'o',  on: { left: 'users.id',       right: 'o.user_id'    } })
  .join(products, { alias: 'p',  on: { left: 'o.product_id',   right: 'p.id'         } })
  .join(categories, { alias: 'c', type: 'LEFT', on: { left: 'p.category_id', right: 'c.id' } })
  .select({ 'users.name': true, 'p.name': 'product', 'c.name': 'category', 'o.total': true })

4.6 LATERAL JOIN

For joins that reference columns from earlier in the FROM list, including subqueries.

users.joinLateral(
  recentOrders.select({ user_id: true, total: true }).orderBy({ column: 'created_at', direction: 'DESC' }).limit(5),
  { alias: 'recent', type: 'LEFT', on: { left: 'users.id', right: 'recent.user_id' } }
)
// → LEFT JOIN LATERAL (...) AS recent ON users.id = recent.user_id

5. GROUP BY and HAVING

5.1 GROUP BY single column

orders.select({ user_id: true, total: { fn: 'SUM', as: 'spend' } })
  .groupBy('user_id')
// → SELECT user_id, SUM(total) AS spend FROM orders GROUP BY user_id

5.2 GROUP BY multiple columns

orders.select({ user_id: true, region: true, total: { fn: 'SUM', as: 'spend' } })
  .groupBy(['user_id', 'region'])
// → GROUP BY user_id, region

5.3 GROUP BY with ROLLUP / CUBE / GROUPING SETS

.groupBy(['region', 'product_id'], { modifier: 'ROLLUP' })
// → GROUP BY ROLLUP (region, product_id)

.groupBy(['region', 'product_id'], { modifier: 'CUBE' })
// → GROUP BY CUBE (region, product_id)

.groupBy({ sets: [['region', 'product_id'], ['region'], []] })
// → GROUP BY GROUPING SETS ((region, product_id), (region), ())

5.4 HAVING

Uses the same condition shape as .where(), including all combinators.

orders.select({ user_id: true, total: { fn: 'SUM', as: 'spend' } })
  .groupBy('user_id')
  .having({ column: 'spend', op: GT, value: 1000 })
// → HAVING SUM(total) > $1

.having(
  and(
    { column: 'spend', op: GT,  value: 1000 },
    { column: 'spend', op: LTE, value: 50000 },
  )
)
// → HAVING (SUM(total) > $1 AND SUM(total) <= $2)

6. ORDER BY

6.1 Single column

users.select('*').orderBy({ column: 'created_at', direction: 'DESC' })
// → ORDER BY created_at DESC

6.2 Multiple columns

users.select('*').orderBy([
  { column: 'score',      direction: 'DESC' },
  { column: 'created_at', direction: 'ASC'  },
])
// → ORDER BY score DESC, created_at ASC

6.3 NULLS FIRST / NULLS LAST

users.select('*').orderBy({ column: 'deleted_at', direction: 'ASC', nulls: 'LAST' })
// → ORDER BY deleted_at ASC NULLS LAST

6.4 Order by expression

users.select('*').orderBy({ raw: raw('LOWER(name)'), direction: 'ASC' })
// → ORDER BY LOWER(name) ASC

6.5 Order by ordinal

users.select({ name: true, created_at: true }).orderBy({ ordinal: 2, direction: 'DESC' })
// → ORDER BY 2 DESC

7. LIMIT, OFFSET, and FETCH

7.1 LIMIT

users.select('*').limit(25)
// → LIMIT $1   params: [25]

7.2 OFFSET

users.select('*').limit(25).offset(50)
// → LIMIT $1 OFFSET $2   params: [25, 50]

7.3 FETCH (SQL-standard alternative to LIMIT)

users.select('*').offset(50).fetch(25)
// → OFFSET $1 ROWS FETCH NEXT $2 ROWS ONLY   params: [50, 25]

7.4 ALL (no limit)

users.select('*').limit('ALL')
// → LIMIT ALL

8. INSERT

8.1 Single row

users.insert({ name: 'Alice', age: 30, role: 'user' })
// → INSERT INTO users (name, age, role) VALUES ($1, $2, $3)
//   params: ['Alice', 30, 'user']

8.2 Multiple rows

users.insertMany([
  { name: 'Alice', age: 30 },
  { name: 'Bob',   age: 25 },
])
// → INSERT INTO users (name, age) VALUES ($1, $2), ($3, $4)
//   params: ['Alice', 30, 'Bob', 25]

8.3 ON CONFLICT DO NOTHING

users.insert({ email: '[email protected]' }).onConflict({ do: 'NOTHING' })
// → INSERT INTO users (email) VALUES ($1) ON CONFLICT DO NOTHING

8.4 ON CONFLICT DO UPDATE (upsert)

users.insert({ email: '[email protected]', name: 'Alice', login_count: 1 })
  .onConflict({
    column:  'email',
    do:      'UPDATE',
    update:  { name: 'Alice', login_count: raw('users.login_count + 1') },
  })
// → INSERT INTO users (email, name, login_count)
//   VALUES ($1, $2, $3)
//   ON CONFLICT (email) DO UPDATE SET name = $4, login_count = users.login_count + 1

8.5 ON CONFLICT on constraint name

.onConflict({ constraint: 'users_email_unique', do: 'NOTHING' })
// → ON CONFLICT ON CONSTRAINT users_email_unique DO NOTHING

8.6 ON CONFLICT with WHERE (partial index)

.onConflict({
  column: 'email',
  where:  { column: 'active', op: IS_TRUE },
  do:     'NOTHING',
})
// → ON CONFLICT (email) WHERE active IS TRUE DO NOTHING

8.7 INSERT ... SELECT

archived_users.insertFrom(
  users.select({ id: true, name: true, email: true })
       .where({ column: 'deleted_at', op: IS_NOT_NULL })
)
// → INSERT INTO archived_users (id, name, email)
//   SELECT id, name, email FROM users WHERE deleted_at IS NOT NULL

9. UPDATE

9.1 Basic update

A .where() is required. A bare .update() with no .where() is a compile-time error.

users.update({ name: 'Bob', role: 'admin' })
  .where({ column: 'id', op: EQ, value: userId })
// → UPDATE users SET name = $1, role = $2 WHERE id = $3
//   params: ['Bob', 'admin', userId]

9.2 Expressions in SET

users.update({ login_count: raw('login_count + 1'), last_login: raw('NOW()') })
  .where({ column: 'id', op: EQ, value: userId })
// → UPDATE users SET login_count = login_count + 1, last_login = NOW() WHERE id = $1

9.3 UPDATE ... FROM (join-based update)

users.update({ role: 'verified' })
  .from(verifications)
  .where({ column: 'users.id', op: EQ, raw: raw('verifications.user_id') })
  .where({ column: 'verifications.confirmed', op: IS_TRUE })
// → UPDATE users SET role = $1
//   FROM verifications
//   WHERE users.id = verifications.user_id AND verifications.confirmed IS TRUE

9.4 UPDATE with subquery in SET

users.update({
  tier: users.select({ tier: true })
             .from(orders, { alias: 'o' })
             .where({ column: 'o.user_id', op: EQ, raw: raw('users.id') })
             .asSubquery()
})

10. DELETE

10.1 Basic delete

A .where() is required. A bare .delete() with no .where() is a compile-time error. Use .deleteAll() to explicitly opt into a full-table delete.

users.delete().where({ column: 'id', op: EQ, value: userId })
// → DELETE FROM users WHERE id = $1

10.2 DELETE ... USING (join-based delete)

users.delete()
  .using(sessions)
  .where({ column: 'users.id',    op: EQ,     raw: raw('sessions.user_id') })
  .where({ column: 'sessions.expired_at', op: LT, value: cutoff })
// → DELETE FROM users
//   USING sessions
//   WHERE users.id = sessions.user_id AND sessions.expired_at < $1

10.3 DELETE all rows (explicit opt-in)

sessions.deleteAll()
// → DELETE FROM sessions

11. RETURNING

Appends RETURNING to any INSERT, UPDATE, or DELETE. Accepts the same shape as .select().

users.insert({ name: 'Alice', age: 30 }).returning({ id: true, name: true })
// → INSERT INTO users (name, age) VALUES ($1, $2) RETURNING id, name

users.update({ role: 'admin' })
  .where({ column: 'id', op: EQ, value: userId })
  .returning('*')
// → UPDATE users SET role = $1 WHERE id = $2 RETURNING *

users.delete()
  .where({ column: 'id', op: EQ, value: userId })
  .returning({ id: true })
// → DELETE FROM users WHERE id = $1 RETURNING id

12. CTEs (WITH)

12.1 Basic CTE

const recent = users
  .select({ id: true, name: true })
  .where({ column: 'created_at', op: GT, value: cutoff })
  .asCTE('recent_users')

recent.select('*').limit(10)
// → WITH recent_users AS (
//     SELECT id, name FROM users WHERE created_at > $1
//   )
//   SELECT * FROM recent_users LIMIT $2

12.2 Multiple CTEs

const activeUsers = users
  .select({ id: true, name: true })
  .where({ column: 'active', op: IS_TRUE })
  .asCTE('active_users')

const topOrders = orders
  .select({ user_id: true, total: { fn: 'SUM', as: 'spend' } })
  .groupBy('user_id')
  .having({ column: 'spend', op: GT, value: 500 })
  .asCTE('top_orders')

withCTEs([activeUsers, topOrders],
  activeUsers
    .join(topOrders, { on: { left: 'active_users.id', right: 'top_orders.user_id' } })
    .select({ 'active_users.name': true, 'top_orders.spend': true })
)
// → WITH active_users AS (...), top_orders AS (...)
//   SELECT active_users.name, top_orders.spend
//   FROM active_users
//   INNER JOIN top_orders ON active_users.id = top_orders.user_id

12.3 Recursive CTE

withCTEs(
  [
    table('org_tree').asCTERecursive('org', (base, recursive) => ({
      base:      departments.select({ id: true, name: true, parent_id: true })
                             .where({ column: 'parent_id', op: IS_NULL }),
      recursive: departments
                   .join(table('org'), { on: { left: 'departments.parent_id', right: 'org.id' } })
                   .select({ 'departments.id': true, 'departments.name': true, 'departments.parent_id': true }),
    })),
  ],
  table('org').select('*')
)
// → WITH RECURSIVE org AS (
//     SELECT id, name, parent_id FROM departments WHERE parent_id IS NULL
//     UNION ALL
//     SELECT departments.id, departments.name, departments.parent_id
//     FROM departments INNER JOIN org ON departments.parent_id = org.id
//   )
//   SELECT * FROM org

12.4 Writable CTE

const inserted = users
  .insert({ name: 'Alice' })
  .returning({ id: true })
  .asCTE('new_user')

withCTEs([inserted],
  audit_log.insert({ user_id: raw('(SELECT id FROM new_user)'), action: 'signup' })
)
// → WITH new_user AS (
//     INSERT INTO users (name) VALUES ($1) RETURNING id
//   )
//   INSERT INTO audit_log (user_id, action) VALUES ((SELECT id FROM new_user), $2)

13. Subqueries

13.1 Subquery in WHERE

users.select('*').where({
  column: 'id',
  op:     IN,
  values: orders.select({ user_id: true })
                .where({ column: 'total', op: GT, value: 1000 })
                .asSubquery(),
})
// → SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > $1)

13.2 EXISTS / NOT EXISTS

users.select('*').whereExists(
  orders.select({ user_id: true })
        .where({ column: 'orders.user_id', op: EQ, raw: raw('users.id') })
        .where({ column: 'total',           op: GT, value: 500 })
)
// → SELECT * FROM users WHERE EXISTS (
//     SELECT user_id FROM orders WHERE orders.user_id = users.id AND total > $1
//   )

users.select('*').whereNotExists(/* same shape */)
// → WHERE NOT EXISTS (...)

13.3 Scalar subquery in SELECT

users.select({
  id:          true,
  name:        true,
  order_count: orders.select({ id: { fn: 'COUNT', as: 'c' } })
                     .where({ column: 'orders.user_id', op: EQ, raw: raw('users.id') })
                     .asSubquery(),
})
// → SELECT id, name, (SELECT COUNT(id) AS c FROM orders WHERE orders.user_id = users.id)
//   FROM users

13.4 Subquery in FROM

const sub = orders
  .select({ user_id: true, total: { fn: 'SUM', as: 'spend' } })
  .groupBy('user_id')
  .asSubquery('user_totals')

table('user_totals', { fromSubquery: sub })
  .select('*')
  .where({ column: 'spend', op: GT, value: 200 })
// → SELECT * FROM (
//     SELECT user_id, SUM(total) AS spend FROM orders GROUP BY user_id
//   ) AS user_totals
//   WHERE spend > $1

14. Window Functions

14.1 Basic OVER

orders.select({
  user_id: true,
  total:   true,
  running: { fn: 'SUM', col: 'total', over: { orderBy: { column: 'created_at', direction: 'ASC' } }, as: 'running_total' },
})
// → SELECT user_id, total,
//          SUM(total) OVER (ORDER BY created_at ASC) AS running_total
//   FROM orders

14.2 PARTITION BY

orders.select({
  user_id: true,
  total:   true,
  rank:    { fn: 'RANK', over: { partitionBy: ['user_id'], orderBy: { column: 'total', direction: 'DESC' } }, as: 'rank' },
})
// → SELECT user_id, total,
//          RANK() OVER (PARTITION BY user_id ORDER BY total DESC) AS rank
//   FROM orders

14.3 Named windows

When the same OVER clause appears on multiple columns, define it once with .window().

orders.select({
  total:      { fn: 'SUM',  col: 'total', over: 'w', as: 'running' },
  avg_total:  { fn: 'AVG',  col: 'total', over: 'w', as: 'moving_avg' },
  row_number: { fn: 'ROW_NUMBER', over: 'w', as: 'rn' },
})
.window('w', { partitionBy: ['user_id'], orderBy: { column: 'created_at', direction: 'ASC' } })
// → SELECT SUM(total) OVER w AS running, AVG(total) OVER w AS moving_avg, ROW_NUMBER() OVER w AS rn
//   FROM orders
//   WINDOW w AS (PARTITION BY user_id ORDER BY created_at ASC)

14.4 Frame clauses

{ fn: 'AVG', col: 'price', over: {
    orderBy: { column: 'date', direction: 'ASC' },
    frame:   { mode: 'ROWS', start: 'UNBOUNDED PRECEDING', end: 'CURRENT ROW' },
  }, as: 'moving_avg'
}
// → AVG(price) OVER (ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

frame.mode options: ROWS, RANGE, GROUPS.

Supported frame boundaries: UNBOUNDED PRECEDING, N PRECEDING, CURRENT ROW, N FOLLOWING, UNBOUNDED FOLLOWING.

Supported window functions: ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE, plus any aggregate function called with over.

15. Set Operations (UNION, INTERSECT, EXCEPT)

const activeUsers  = users.select({ id: true, name: true }).where({ column: 'active', op: IS_TRUE })
const premiumUsers = users.select({ id: true, name: true }).where({ column: 'tier',   op: EQ, value: 'premium' })

activeUsers.union(premiumUsers)
// → SELECT id, name FROM users WHERE active IS TRUE
//   UNION
//   SELECT id, name FROM users WHERE tier = $1

activeUsers.unionAll(premiumUsers)
// → ... UNION ALL ...

activeUsers.intersect(premiumUsers)
// → ... INTERSECT ...

activeUsers.intersectAll(premiumUsers)
// → ... INTERSECT ALL ...

activeUsers.except(premiumUsers)
// → ... EXCEPT ...

activeUsers.exceptAll(premiumUsers)
// → ... EXCEPT ALL ...

Set operations can be chained and ordered:

activeUsers
  .union(premiumUsers)
  .orderBy({ column: 'name', direction: 'ASC' })
  .limit(50)
// → (SELECT ...) UNION (SELECT ...) ORDER BY name ASC LIMIT $1

16. LOCK

For SELECT ... FOR UPDATE and related row-level locking clauses.

users.select('*')
  .where({ column: 'id', op: EQ, value: userId })
  .lock('UPDATE')
// → SELECT * FROM users WHERE id = $1 FOR UPDATE

.lock('SHARE')
// → FOR SHARE

.lock('NO KEY UPDATE')
// → FOR NO KEY UPDATE

.lock('KEY SHARE')
// → FOR KEY SHARE

Lock with options:

.lock('UPDATE', { of: 'users',  skipLocked: true })
// → FOR UPDATE OF users SKIP LOCKED

.lock('UPDATE', { of: 'users',  noWait: true })
// → FOR UPDATE OF users NOWAIT

17. Escape hatch

For any expression, function, or PostgreSQL syntax not covered by the builder, use raw(). A raw() value is interpolated directly into the SQL string and is never parameterized — use it only for structural SQL, not user input.

import { raw } from 'axiomdb'

users.select({
  id:          true,
  full_name:   raw("first_name || ' ' || last_name"),
  is_adult:    raw("age >= 18"),
  bucket:      raw("NTILE(4) OVER (ORDER BY score DESC)"),
})

users.select('*').where({
  column: 'metadata',
  op:     CONTAINS,
  value:  raw("'{\"role\": \"admin\"}'::jsonb"),
})

users.update({ tsv: raw("to_tsvector('english', name || ' ' || email)") })
  .where({ column: 'id', op: EQ, value: userId })

18. Composability

The builder is immutable. Every method returns a new builder instance. This means partial queries are safe to share, reuse, and extend without mutation.

// Base query shared across routes
const activeUsers = users
  .select({ id: true, name: true, email: true })
  .where({ column: 'active', op: IS_TRUE })
  .where({ column: 'deleted_at', op: IS_NULL })

// Each derived query is a separate object — activeUsers is unchanged
const admins = activeUsers
  .where({ column: 'role', op: EQ, value: 'admin' })
  .orderBy({ column: 'created_at', direction: 'DESC' })

const page2 = activeUsers
  .orderBy({ column: 'name', direction: 'ASC' })
  .limit(25)
  .offset(25)

Executing a query

Every chain terminates by calling .build(), which returns { text, params }:

const { text, params } = activeUsers.build()
await db.query(text, params)

Or call .execute(db) directly if the builder has been configured with a database client:

const rows = await activeUsers.execute(db)

License

MIT