Skip to main content

The sql Template Tag

The sql tagged-template helper builds a parameterized statement from a template literal, turning every interpolated value into a bind parameter instead of writing it into the SQL text:

import { sql } from 'postgrejs';

const id = 1;
const request = sql`select * from users where id = ${id}`;
// request.sql    === 'select * from users where id = $1'
// request.params === [1]

The tag never writes a value into the text — the resulting QueryRequest can be passed directly to connection.query():

const result = await connection.query(sql`select * from t where id = any(${[1, 3]})`);

connection.query() recognizes a QueryRequest and forwards .sql and .params for you; passing params yourself alongside a sql\`` value throws, since the statement already carries its own.

Composing fragments​

A QueryRequest interpolated inside another sql\`` template is spliced in as a fragment, with its own placeholders renumbered onto the outer statement:

const filter = city ? sql`where city = ${city}` : sql``;
const request = sql`select * from t ${filter} order by id`;

sql.ident(name)​

Values become parameters, but identifiers (table/column/schema names) cannot be — $1 is always a value. sql.ident() writes a name into the statement instead, quoted like PostgreSQL's own quote_ident():

await connection.query(
  sql`select ${sql.ident(column)} from ${sql.ident(table)}`,
);

sql.values(data, columns?)​

Builds the column list and VALUES clause of an INSERT from an object, or from an array of objects for a multi-row insert:

await connection.query(sql`insert into users ${sql.values({ id: 1, name: 'ada' })}`);
// insert into users ("id","name") values ($1,$2)

await connection.query(sql`insert into users ${sql.values([
  { id: 1, name: 'a' },
  { id: 2, name: 'b' },
])}`);
// insert into users ("id","name") values ($1,$2),($3,$4)

Pass columns to restrict and order which keys are written — always do this when data comes from outside (a request body), so a caller can't smuggle in an extra column:

sql.values(userInput, ['id', 'name']);

sql.set(data, columns?)​

Builds the assignment list of an UPDATE from an object:

await connection.query(
  sql`update users set ${sql.set({ city })} where id = ${id}`,
);
// update users set "city" = $1 where id = $2

As with sql.values(), pass columns to restrict what gets written when data is externally supplied.

QueryRequest.stringify(options?)​

Renders the statement as plain SQL text with every parameter written in as a literal — for cases where you need a literal string rather than a parameterized call, such as passing it to execute(), which has no params option:

sql`select ${42}`.stringify();          // "select '42'::int4"
sql`select ${true}`.stringify();        // "select 't'::bool"
sql`select ${null}`.stringify();        // "select null"
sql`select ${'hi'}`.stringify();        // "select 'hi'"
sql`select ${[1, 2]}`.stringify();      // "select '{"1","2"}'"

await connection.execute(sql`select * from accounts where id = ${accountId}`);

Most values get an explicit cast ('42'::int4, 't'::bool), encoded through the same type map query() uses, so the two paths agree on how a value is represented. A string, a Date, and an array of numbers/bigints/strings/Dates are the exception: like their parameter forms (see Strings, Dates, and numeric arrays go out unspecified), they're written bare, with no cast, so the same statement resolves them from context whichever way it runs. A value with no text encoding at all (e.g. an unregistered type) makes stringify() throw rather than silently guess — use query() instead for values like that.

sql.ident() results contain no parameters, so stringify() on an identifier fragment just returns the quoted name unchanged.

See also​