Skip to main content

Columns and Table References

Select, .groupBy(), .orderBy(), .returning(), and .from() all build their own small element type out of the strings you pass — Field, GroupColumn, OrderColumn, ReturningColumn, and TableName. You rarely construct these directly (a plain string is parsed into the right one automatically), but knowing their shape and parsing rules helps when a string you'd expect to work throws instead, or when you need to pass an explicit instance — e.g. to attach a dataType hint to a Field for a strict comparison.

All four column-reference types (Field, GroupColumn, OrderColumn, ReturningColumn) extend a common abstract base, BaseField, which just defines the shared shape (_field/_schema/_table and a couple of optional hints) — each subclass parses its own string format and serializes independently.

Field​

A column reference, optionally schema/table-qualified and aliased. Select builds one internally for every string column you pass.

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

Select('schema1.table1.field1 f1').from('customers');
// select schema1.table1.field1 as f1 from customers

Construct one directly when you need to attach a dataType/isArray hint (used by comparisons with strictParams):

import { Field, DataType } from '@sqb/builder';

Field('t.field1 f1', DataType.VARCHAR, false);

Full reference: Field.

TableName​

A table reference, optionally schema-qualified and aliased, with optional dialect-specific optimizer hints. .from(), Insert/Update/Delete's tableName argument, and every join constructor build one internally for every plain string you pass.

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

Select().from('sales.customers c');
// select * from sales.customers c

TableName({ schema: 'sales', table: 'customers', alias: 'c', optimizerHint: '/*+ INDEX(c) */' });

A string only parses [schema.]table[ [as] alias] — one optional schema level. Anything that shape can't express (a 3-level qualified name, for instance) needs Raw instead — see Delete Statement for a worked example. Full reference: TableName.

GroupColumn​

One GROUP BY column, optionally schema/table-qualified. Select#groupBy() builds one internally for every string you pass.

Select('country', 'count(*)').from('customers').groupBy('country');
// ... group by country

Full reference: GroupColumn.

OrderColumn​

One ORDER BY column, optionally schema/table-qualified, with an ascending/descending direction baked into the same string — no separate direction argument needed. Select#orderBy() builds one internally for every string you pass.

Select().from('customers').orderBy('-created_at', 'name asc', '+id');
// ... order by created_at desc, name, id

A leading - or a trailing dsc/desc/descending both mean descending; a leading +, a trailing asc/ascending, or nothing at all, means ascending. Full reference: OrderColumn.

ReturningColumn​

One column requested in a RETURNING clause, with an optional alias. ReturningQuery#returning() (used by Insert/Update) builds one internally for every string you pass. Unlike Field, it does not accept a schema/table qualifier.

Insert('customers', { name: 'Jane' }).returning('id', 'name as full_name');
// ... returning id, name as full_name

Full reference: ReturningColumn.

See also​