Generating SQL per Dialect

Every query object — Select, Insert, Update, Delete, Union — exposes the same .generate(options?) method, inherited from the shared Query base class. It performs no I/O: it just walks the object graph you built and returns SQL text plus bind-parameter metadata.

import '@sqb/postgres-dialect'; // registers 'postgres' — see below

const { sql, params } = Select('id').from('customers').limit(10).generate({
  dialect: 'postgres',
  prettyPrint: true,
});

Options (GenerateOptions)

Option	Type	Description
dialect	string	Name of the target dialect (e.g. 'postgres', 'oracle', 'mssql'). Selects which registered SerializerExtensions apply. Omit it to get the builder's dialect-neutral defaults.
prettyPrint	boolean	Multi-line, indented output instead of a single flattened line.
params	Record<string, any>	Values for any Param placeholders used in the query.
dialectVersion	string	Optional version hint (e.g. '15' for Postgres 15) a dialect extension can use to branch its output.
strictParams	boolean	Converts inline literal values into auto-generated bind parameters instead of embedding them in the SQL text. See Raw SQL and Parameters.

String literals keep their line breaks

The generated SQL is laid out with control characters that generate() flattens to spaces (or re-indents, with prettyPrint). Since v6.2.0 a string literal containing a line break, tab or carriage return is shielded from that pass, so the value written to the database is exactly the one you gave. Before that, \n inside a literal collapsed to a space without prettyPrint, and gained indentation with it.

Result (GenerateResult)

interface GenerateResult {
  sql: string;
  params?: any;
  paramOptions?: Record<string, ParamOptions> | ParamOptions[];
  returningFields?: { field: string; alias?: string }[];
}

• sql — the rendered SQL text.
• params — the prepared bind-parameter values actually referenced by the query (a subset of the params you passed in, keyed by parameter name).
• paramOptions — per-parameter dataType/isArray metadata, for adapters that need to bind values with an explicit native type.
• returningFields — populated when .returning(...) was used (Insert/Update); lists the requested field/alias pairs.

Dialect-aware pagination

.limit()/.offset() are dialect-agnostic on the builder side — the same call renders differently depending on dialect:

Dialect	.limit(10) output
PostgreSQL, MySQL, MariaDB, SQLite	LIMIT 10
Oracle	FETCH FIRST 10 ROWS ONLY
SQL Server	OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

The same applies to identifier quoting, boolean literals, RETURNING support, bind-parameter placeholder syntax (:name vs ? vs $1 vs @name), and more — each is decided by whichever dialect extension is registered for the dialect you pass.

How dialect packages plug in

@sqb/builder ships only dialect-neutral defaults — it has no built-in knowledge of any specific database. Every dialect-specific behavior you saw above comes from a separate dialect plugin package (@sqb/postgres-dialect, @sqb/oracle-dialect, @sqb/mysql-dialect, @sqb/mariadb-dialect, @sqb/mssql-dialect, @sqb/sqlite-dialect), imported once for its side effect — as in the example above — before .generate({ dialect: '...' }) is called with that dialect's name.

This is a plugin system, not a fixed list: dialect names are plain strings with no built-in validation, and you can register your own. The Dialect Plugin System covers the full mechanism in depth — the registry API, what happens if you forget the import (no error, just generic SQL), a real dialect package's internals, and how to write your own.

Per-query serialize hooks

For one-off overrides scoped to a single query (rather than a whole dialect), listen for the 'serialize' event — every query is an EventEmitter, and .generate() runs its 'serialize' listeners before consulting the SerializerRegistry:

Select()
  .from('table1')
  .on('serialize', (ctx, type, obj) => {
    if (type === 'table_name') return 'table2';
  })
  .generate().sql;
// select * from table2

This page covers what you need to know as a consumer of .generate(). Writing a full dialect package — including a real example and the exact registry API — is covered in The Dialect Plugin System.