The Dialect Plugin System

@sqb/builder itself has no built-in knowledge of any specific database. Every dialect-specific behavior — pagination syntax, bind-parameter placeholders, reserved-word quoting, RETURNING support, and so on — comes from a separate dialect plugin package that registers itself with the builder. This page covers the full mechanism: what the shipped dialect packages are, exactly what happens when you import one, what happens if you forget to, and how to write your own.

The shipped dialect packages

Package	dialect name	Notes
@sqb/postgres-dialect	'postgres'
@sqb/mysql-dialect	'mysql'
@sqb/mariadb-dialect	'mariadb'
@sqb/mssql-dialect	'mssql'
@sqb/oracle-dialect	'oracle'
@sqb/sqlite-dialect	'sqlite'	Also used by @sqb/sqljs — sql.js is SQLite-flavored, so it registers under the same 'sqlite' dialect name rather than shipping a separate sqljs-dialect package.

Each one is a side-effect import — you don't call anything or use any export from it, you just import it once before generating SQL for that dialect:

import '@sqb/postgres-dialect';
import { Select } from '@sqb/builder';

Select('id').from('customers').limit(10).generate({ dialect: 'postgres' }).sql;
// select id from customers LIMIT 10

You can import more than one dialect package in the same process — useful for a tool that generates SQL for several databases (a migration generator, a multi-tenant app with per-tenant database engines, etc.). Each registered extension only activates for .generate() calls whose dialect option matches its own.

Do you need to do this yourself?

Not if you're using @sqb/connect with a database adapter. Importing an adapter package already does this for you — @sqb/postgres's own entry point imports @sqb/postgres-dialect internally as part of registering itself:

// inside @sqb/postgres's own index.ts
import '@sqb/postgres-dialect';
import { AdapterRegistry } from '@sqb/connect';
// ...
AdapterRegistry.register(new PgAdapter());

So import '@sqb/postgres' in your own code transitively imports the dialect plugin too — see Creating a Client. The explicit import '@sqb/postgres-dialect' shown on this page matters specifically when you use @sqb/builder standalone — generating SQL text with no adapter, no client, and no database connection at all (a schema tool, a query previewer, a codegen script, ...).

What happens if you forget the import

Nothing throws. .generate() never validates that dialect is a "known" name — it's a plain string. If no registered extension matches it, generation just falls straight through to @sqb/builder's own generic, dialect-neutral rendering for every node:

import { Select } from '@sqb/builder';
// no dialect package imported

Select('id').from('customers').limit(10).generate({ dialect: 'postgres' }).sql;
// select id from customers
//                           ^ no "LIMIT 10" at all — the builder's default
//                             SELECT renderer has no pagination logic;
//                             that's entirely dialect-owned

This is a normal, exercised code path (the builder's own test suite calls .generate() with no dialect at all and asserts against exactly this generic output) — not an error condition. It's worth knowing precisely because it fails silently: a typo'd dialect name ('postgress') or a forgotten import produces valid-looking SQL that's just quietly missing dialect-specific behavior, rather than an exception pointing at the mistake.

How registration works

The mechanism is a small, in-memory, process-wide registry: SerializerRegistry, exported from @sqb/builder itself. A dialect package's entire job, at import time, is to construct one object shaped like a SerializerExtension and register it:

// this is (almost) the entire source of @sqb/postgres-dialect
import { SerializerRegistry } from '@sqb/builder';
import { PostgresSerializer } from './postgres-serializer.js';

SerializerRegistry.register(new PostgresSerializer());

SerializerRegistry's public API:

Method	Description
register(...extensions)	Adds one or more extensions to the registry. Throws TypeError if an extension has no dialect. Does not dedupe — registering two extensions for the same dialect keeps both, layered.
unRegister(...extensions)	Removes extensions by object reference (not by dialect name) — you need to keep a handle to the instance you registered.
getAll(dialect)	Returns every extension currently registered for a given dialect name.
findDialect(dialect)	Returns the first extension registered for a dialect, or undefined.
has(extension)	Whether a specific extension instance is currently registered.
items()	Iterates every registered extension, across all dialects.
forEach(callback)	Same, as a callback.
size / get(index)	Total count / positional access.

Because it's a single static array shared by the whole process, an extension registered once (e.g. via a top-level side-effect import) stays registered for the process's lifetime, or until something explicitly calls unRegister() with that same instance. That's fine for a normal application; it matters for test suites, which typically pair register() in a before() hook with a matching unRegister() in after() to avoid one test's dialect leaking into another.

What a SerializerExtension actually does

interface SerializerExtension {
  dialect: string;
  serialize?(
    ctx: SerializeContext,
    type: SerializationType | string,
    obj: any,
    defaultFn: DefaultSerializeFunction,
  ): string | undefined;
  isReservedWord?(ctx: SerializeContext, word: string): boolean;
}

Every part of a query — not just the statement as a whole, but each column, join, condition, and literal — is rendered through a single dispatch point, ctx.serialize(type, data, defaultFn), where type is a SerializationType member like SELECT_QUERY, SELECT_QUERY_JOIN, COMPARISON_EXPRESSION, EXTERNAL_PARAMETER, or RETURNING_BLOCK. Resolution order:

1. Per-query 'serialize' event hooks, if any were attached with .on('serialize', ...) — see Per-query serialize hooks.
2. Every registered extension whose dialect matches options.dialect, tried in registration order. The first one whose serialize() returns something other than null/undefined wins.
3. defaultFn — the builder's own dialect-neutral rendering for that node, used if nothing above intercepted it.

An extension only needs to implement the node types it actually cares about — returning undefined (or just not matching in a switch) for anything else falls straight through to the default renderer. This is why a dialect package can be small: @sqb/postgres-dialect's PostgresSerializer only intercepts three SerializationTypes.

A real example: @sqb/postgres-dialect

• SELECT_QUERY — appends LIMIT/OFFSET (the builder's own default SELECT renderer has no pagination logic at all):

  serialize(ctx, type, o, defaultFn) {
    if (type === SerializationType.SELECT_QUERY) {
      let out = defaultFn(ctx, o);
      if (o.limit) out += '\nLIMIT ' + o.limit;
      if (o.offset) out += (o.limit ? ' ' : '\n') + 'OFFSET ' + o.offset;
      return out;
    }
  }

• COMPARISON_EXPRESSION — rewrites = NULL/!= NULL into IS NULL/IS NOT NULL, rewrites array IN/NOT IN into Postgres's ANY(...) array operator, and rewrites a full-text match condition into @@ plainto_tsquery(...).
• EXTERNAL_PARAMETER — renders bind parameters as positional $1, $2, ... instead of the builder's default named :name placeholders.
• isReservedWord() — flags Postgres-specific reserved words (limit, returning, window, ...) for auto-quoting, on top of a small ANSI baseline the builder already checks first.

It does not override RETURNING_BLOCK — the builder's generic default (returning col1, col2) is already valid Postgres syntax, so Postgres inherits it for free.

dialectVersion: branching within one dialect

@sqb/oracle-dialect shows why GenerateOptions.dialectVersion exists — Oracle's pagination syntax changed at 12c:

serialize(ctx, type, o, defaultFn) {
  if (type === SerializationType.SELECT_QUERY) {
    const majorVersion = ctx.dialectVersion
      ? parseInt(String(ctx.dialectVersion).split('.')[0], 10)
      : 0;
    if (majorVersion >= 12) {
      // Oracle 12c+: OFFSET n ROWS FETCH NEXT n ROWS ONLY / FETCH FIRST n ROWS ONLY
    }
    // pre-12c (or no dialectVersion given): wrap the query in a ROWNUM subquery instead
  }
}

import '@sqb/oracle-dialect';
import { Select } from '@sqb/builder';

const query = Select('*').from('customers').limit(10);

query.generate({ dialect: 'oracle', dialectVersion: '12' }).sql;
// select * from customers FETCH FIRST 10 ROWS ONLY

query.generate({ dialect: 'oracle', dialectVersion: '11' }).sql;
// select * from (select * from customers) where rownum <= 10

query.generate({ dialect: 'oracle' }).sql;
// select * from (select * from customers) where rownum <= 10
// (no dialectVersion given -> treated as pre-12c, same rownum subquery)

@sqb/oracle-dialect also overrides quite a bit more than Postgres does: SELECT_QUERY_FROM (falls back to from dual for a table-less select, since Oracle requires it), STRING_VALUE/ DATE_VALUE (wrapped in to_date(...)/to_timestamp_tz(...)), and RETURNING_BLOCK (suppressed entirely — Oracle's builder-level RETURNING support isn't rendered by this extension).

Writing your own dialect

Nothing here is specific to the six shipped databases. dialect is typed as a plain string everywhere — there's no enum or union restricting it — and SerializerRegistry is public API, exported from @sqb/builder itself and exercised this way by the builder's own test suite. If SQB doesn't ship an adapter for your database, or you just want to generate SQL text for one, you can register your own extension the exact same way a shipped dialect package does:

// my-custom-dialect.ts
import {
  SerializerRegistry,
  SerializationType,
  type SerializerExtension,
  type SerializeContext,
  type DefaultSerializeFunction,
} from '@sqb/builder';

const RESERVED_WORDS = new Set(['foo', 'bar']);

class MyCustomDialectSerializer implements SerializerExtension {
  dialect = 'my-custom-db';

  isReservedWord(_ctx: SerializeContext, word: string): boolean {
    return RESERVED_WORDS.has(word.toLowerCase());
  }

  serialize(
    ctx: SerializeContext,
    type: SerializationType | string,
    o: any,
    defaultFn: DefaultSerializeFunction,
  ): string | undefined {
    switch (type) {
      case SerializationType.SELECT_QUERY: {
        let out = defaultFn(ctx, o);
        if (o.limit) out += '\nTOP ' + o.limit; // a fictitious pagination syntax
        return out;
      }
      default:
        return undefined; // let everything else fall through to the default renderer
    }
  }
}

// side-effect registration, exactly like a shipped dialect package
SerializerRegistry.register(new MyCustomDialectSerializer());

import './my-custom-dialect.js';
import { Select } from '@sqb/builder';

Select('id').from('customers').limit(10).generate({ dialect: 'my-custom-db' }).sql;

Start from whichever SerializationType your database renders differently from the builder's ANSI-ish defaults — pagination and bind-parameter style are the two almost every dialect needs to override; identifier quoting (isReservedWord) is usually the next one worth adding.

See also

• Generating SQL per Dialect — the .generate() API this system feeds into, and per-query 'serialize' hooks for one-off overrides.
• SerializationType — every node type you can intercept.
• SerializerExtension, GenerateOptions — full interface reference.
• Choosing a database adapter — for running queries against a real database rather than just generating SQL text.