Raw SQL and Parameters

Raw()

Raw wraps a literal string that is emitted into the generated SQL verbatim — no escaping, no quoting, no validation. Use it for expressions the builder doesn't model (function calls, dialect-specific syntax, computed columns) anywhere an SQL element is accepted: columns, from(), where()/on() operands, orderBy(), table names, and more.

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

Select(Raw("'John''s Bike' f1")).from('table1');
// select 'John''s Bike' f1 from table1

Select().from('table1', Raw('func1()'));
// select * from table1,func1()

Select().from('table1').orderBy(Raw('LOWER(field1) desc'));
// select * from table1 order by LOWER(field1) desc

That last one needs Raw specifically because .orderBy()'s plain-string form only parses a bare [schema.][table.]field [asc|desc] shape — .orderBy('LOWER(field1)') throws a TypeError ("does not match order column format"). A plain column name doesn't need Raw at all: .orderBy('field1 desc') already works on its own.

Because Raw content is never escaped, never interpolate untrusted input into it directly — build the value with Param instead and let the target driver bind it safely.

Param()

Param represents an external bind parameter. It serializes to :name by default (dialect packages can override the placeholder syntax — ?, $1, @name, etc. — through a SerializerExtension).

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

Select().from('customers').where(Eq('id', Param('id')));

Param is dual-callable and accepts either positional arguments or a single options object; both forms accept an optional dataType (a DataType member) and isArray flag:

Param('id');
Param('id', DataType.INTEGER);
Param('id', DataType.INTEGER, false);
Param({ name: 'id', dataType: DataType.INTEGER, isArray: false });

Supplying values

Bind values are resolved from generate({ params }), or from .values(obj) set directly on the query — both end up in result.params:

const query = Select().from('customers').where(Eq('id', Param('id')));

query.generate({ params: { id: 1 } });
// { sql: 'select * from customers where id = :id', params: { id: 1 }, ... }

Insert('customers', { id: Param('id'), name: Param('name') })
  .values({ id: 1, name: 'Abc' })
  .generate();
// sql: insert into customers (id, name) values (:id, :name)
// params: { id: 1, name: 'Abc' }

result.paramOptions mirrors each parameter's dataType/isArray, which database adapters use to bind values with the correct native type.

strictParams

Passing strictParams: true to .generate() converts literal values into auto-generated Params (named P$_1, P$_2, ...) instead of writing them into the SQL. It applies to the right-hand side of comparisons and, since v6.2.0, to the values of an Insert's VALUES and an Update's SET:

Select().from('table1').where({ id: 1 }).generate({ strictParams: true });
// sql: select * from table1 where id = :P$_1
// params: { P$_1: 1 }

Insert('table1', { id: 1, name: 'x' }).generate({ strictParams: true });
// sql: insert into table1 (id, name) values (:P$_1, :P$_2)
// params: { P$_1: 1, P$_2: 'x' }

In Insert/Update positions only a string, number, boolean, bigint or array value is bound. null, a SqlElement (such as Raw or a sub-query), a Date, a Buffer and a plain object (a JSON literal) keep their literal serialization, because a driver cannot be relied on to bind them the way the literal would have been written. @sqb/connect always generates with strictParams: true, so with the ORM and SqbConnection.execute() those values are now bound rather than inlined too. Before v6.2.0 only the WHERE values were bound.

This is useful for drivers or caches that key on the SQL text and want literal values kept out of it entirely. See Generating SQL per dialect for the rest of the .generate() options.