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.

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.