Select Statement

Select() builds a SELECT query as a plain JS object graph and serializes it to dialect-correct SQL text on demand. It has no knowledge of any driver or connection — it only builds and renders SQL.

import { Select, Eq } from '@sqb/builder';

const query = Select('id', 'given_name', 'family_name')
  .from('customers')
  .where(Eq('active', true))
  .orderBy('id')
  .limit(10);

const { sql, params } = query.generate({ dialect: 'postgres' });

Select is dual-callable — call it as a plain function or with new, both return the same kind of instance:

const a = Select('id', 'name');
const b = new Select('id', 'name');

Full API reference: Select class.

Adding columns

Pass columns to the Select(...) constructor, or add them afterwards with .addColumn(). Both accept strings, arrays of strings (flattened automatically), or SQL element instances such as Field, Raw, or a sub-Select.

Select('id', 'given_name family_name'); // "field alias" shorthand
Select(['id', 'given_name'], 'family_name'); // arrays are flattened
Select().addColumn('id', 'given_name');
Select('id, given_name family_name, gender'); // one string, comma-split into 3 columns

A column string may include a table/schema prefix and an optional alias: 'schema1.table1.field1 f1'. If no columns are given at all (or addColumn() is never called), the query serializes to select *.

Select().from('customers').generate().sql;
// 'select * from customers'

To select a sub-query as a column, give it an alias with .as() — an alias is required for any sub-Select used as a column or table:

const sub = Select('id').from('orders').as('order_id');
Select(sub).from('customers');
// select (select id from orders) order_id from customers

from()

.from(...) accepts one or more table names (strings), TableName instances, Raw fragments, or sub-Select/Union queries (the latter two require .as()). Calling .from() again replaces the previous table list rather than appending to it.

Select().from('customers');
Select().from('schema1.customers c');
Select().from('customers', Raw('LATERAL func()'));

join()

See Joins for the full walkthrough of .join(), the JoinType enum, and the join element classes (InnerJoin, LeftJoin, ...).

where()

.where(...) accumulates conditions into an implicit top-level And. Calling .where() more than once keeps adding to the same And rather than replacing it.

Select().from('customers').where(Eq('active', true)).where(Eq('country', 'US'));
// where active = true and country = 'US'

See Operators and Conditions for the full list of condition operators and the object-literal shorthand.

groupBy() and orderBy()

Both accept column name strings or SQL element instances (GroupColumn / OrderColumn are constructed for you from strings). Order columns support a leading +/- or a trailing asc/desc/ascending/descending word:

Select().from('customers').groupBy('country').orderBy('-created_at', 'name asc');
// group by country order by created_at desc, name

as(), distinct(), limit(), offset()

Select('id').from('orders').as('o'); // alias, for use as a sub-select
Select('id', 'name').distinct().from('customers'); // select distinct id, name ...
Select().from('customers').limit(10).offset(20);

.limit() and .offset() coerce their argument to an integer. What SQL each produces depends on the target dialect — see Generating SQL per dialect.

comment()

Attaches an SQL comment block to the generated output, optionally restricted to specific dialects:

Select().from('customers').comment('Only fetch active rows');
// /*Only fetch active rows*/
// select * from customers

Select().from('customers').comment('Postgres only', ['postgres']);

Fetch events

Select (like every query type) is an EventEmitter. @sqb/connect uses the 'fetch' event to notify listeners as rows stream in; @sqb/builder itself only exposes the registration methods:

Select()
  .from('customers')
  .onFetch(row => console.log(row))
  .onceFetch(row => console.log('first row', row));

generate()

.generate(options?) renders the query to SQL text. See Generating SQL per dialect for the full option set (dialect, prettyPrint, params, dialectVersion, strictParams) and the returned { sql, params, paramOptions, returningFields } shape, and The Dialect Plugin System for how pagination, identifier quoting, bind-parameter style, and other dialect-specific behavior are decided by whichever plugin package you've imported.