Clauses

FIELD

.field() controls which columns appear in SELECT. Three forms are accepted:

String — a single column, with optional alias:

.field('*')                    // all columns
.field('id')                   // one bare column (single-table query)
.field('post.id')              // qualified column (joined query)
.field('id@postId')            // aliased — default SK uses @

Array — multiple columns:

.field(['id', 'title', 'author_id@authorId'])

Object — the most powerful form. Keys are output aliases, values are expressions:

.field({
  postId   : 'post.id',                              // column reference
  count    : 42,                                     // number literal
  active   : true,                                   // boolean literal
  label    : "'Hello'",                              // string literal (single-quoted in SQL)
  comments : {                                       // aggregation → adds GROUP BY
    fn    : 'json_agg',
    group : 'post.id',
    value : { id: 'comment.id', body: 'comment.body' }
  },
  raw      : { fn: 'raw', value: 'COALESCE(title, \'\')' }
})

WHERE

.where() accepts an object where the key encodes both the column and the comparison operator. The default syntax (prefix) places the operator on the left:

DefaultSyntaxKey VerboseSyntaxKey SQL produced
column column column = $n
=:column column = column = $n
<>:column column <> column <> $n
>=:column column >= column >= $n
<=:column column <= column <= $n
>:column column > column > $n
<:column column < column < $n
~~:column column ~~ column ~~ $n (LIKE)
~~*:column column ~~* column ~~* $n (ILIKE)
!~~:column column !~~ column !~~ $n (NOT LIKE)
[=]:column {column} = $n = ANY(column)
[<>]:column {column} <> $n <> ALL(column)
[~~]:column {column} ~~ array_to_string(column,' ') ~~ $n
&&:label ANDlabel OR group — value is an array of AND conditions

Passing null as a value produces IS NULL / IS NOT NULL. Passing an array produces = ANY(...).

OR groups with &&::

.where({
  published : true,
  '&&:any'  : [
    { '~~:title': '%TypeScript%' },
    { '~~:title': '%Postgres%'   },
  ]
})
// WHERE (published = $1 AND (title ~~ $2 OR title ~~ $3))

Column-to-column comparisons with col():

import { col } from 'helice';
.where({ 'post.author_id': col('user.id') })
// post.author_id = user.id  (no parameter)

JOIN

.join() accepts an object where the key encodes the join type and target table, and the value is the ON condition (written as if the joined table is already in scope). The default syntax (prefix) places the join type before the table name:

DefaultSyntaxKey VerboseSyntaxKey SQL produced
table table LEFT JOIN table (default)
i#table INNER JOIN table INNER JOIN table
f#table FULL JOIN table FULL JOIN table
l#table LEFT JOIN table LEFT JOIN table
r#table RIGHT JOIN table RIGHT JOIN table 
db.select('post')
  .join({ user: 'id = post.author_id' })           // LEFT JOIN (default)
  .join({ 'i#user': 'id = post.author_id' })       // INNER JOIN
// ... JOIN user ON user.id = post.author_id

NOTE: After a join, bare column names are no longer valid — use table.column everywhere in .field(), .where(), and .orderBy().

ORDER BY

.orderBy() is available on SELECT. Three forms:

.orderBy('views')                            // single column (default ASC)
.orderBy(['published', 'views'])             // multiple columns (all ASC)
.orderBy({ views: 'DESC', title: 'ASC' })   // with explicit direction
.orderBy({ views: 'DESC', title: '' })      // '' = no direction keyword

After a join, use fully-qualified table.col notation.

RETURNING

.returning() appends a RETURNING clause to INSERT, UPDATE, and DELETE. Accepts the same string, array, and object forms as .field():

.returning('*')
.returning(['id', 'name'])
.returning({ userId: 'id', userName: 'name' })