Queries

The clauses above don't all apply everywhere — .field() makes no sense on a DELETE, .set() only exists on UPDATE. Each query type has it own subset;

Clause SELECT INSERT UPDATE DELETE
.field() / .returning() .field() .returning() .returning() .returning()
.values() / .set() .values() .set()
.join() / .using() .join() .using() (FROM) .using() (USING)
.with() (CTE) ✓ ✓ ✓
.where() / .in() / .notIn() ✓ ✓ ✓
.orderBy() / .limit() ✓

For in-depth documentation and examples for each query type

  • SELECT — field, join, CTE, where, orderBy, limit, prepare options

  • INSERT — values, returning, runtime values

  • UPDATE — set, using, CTE, where, returning, prepare options

  • DELETE — using, CTE, where, returning, prepare options

prepare() — static vs runtime

Every query ends at .prepare(): the entire goal. This method compiles the chained clauses once and returns a reusable function. Values can be baked in at build time (static), left open at call time (runtime), or both — static and runtime parts are merged automatically.

// Everything static — call with no arguments, always returns the same SQL
const getPublished = db.select('post')
  .where({ published: true })
  .prepare();

getPublished()

// Runtime WHERE — enable it in prepare(), supply it at call time
const findPosts = db.select('post')
  .where({ published: true })       // static part, always applied
  .prepare({ where: true });        // AND runtime WHERE enabled

findPosts({ where: { '>=:views': 500 }, limit: 10 })
// WHERE (published = $1 AND views >= $2) LIMIT 10

// Restrict which columns the runtime WHERE may use
const updateUser = db.update('user')
  .prepare({ set: true, where: { user: ['id'] } });

updateUser({ set: { name: 'Dave' }, where: { id: 10 } })

Which of those clauses .prepare() can leave open until call time:

Runtime option SELECT INSERT UPDATE DELETE
where ✓ ✓ ✓
field ✓
orderBy / limit ✓
set / values values set

build() / execute() — shortcuts for prepare()

Two one-shot shortcuts, available on every query type, for when you just want { query, args } (or a result) right away and don't need a reusable function:

// build() — returns { query, args } immediately
const { query, args } = db.select('post').where({ published: true }).build();
// equivalent to:
db.select('post').where({ published: true }).prepare()();

// execute(executor) — builds and calls executor(query, args), returning its result
const rows = await db.select('user').where({ active: true })
  .execute((q, a) => pgClient.query<User[]>(q, a));
// equivalent to:
db.select('user').where({ active: true })
  .prepare(undefined, (q, a) => pgClient.query<User[]>(q, a))();

Both use static clause values only. When you need runtime args, use .prepare() directly.