Writing a custom dialect

A dialect adds an engine Turbine does not ship. It is one object, typed Dialect, that builds every piece of SQL that differs between engines. The shipped SQLite, MySQL and SQL Server engines are dialects too.

import {
  postgresDialect,
  type Dialect,
  type DialectName,
  type EngineCapabilities,
  type ResultStrategy,
  type BuiltStatement,
  type InsertStatementInput,
  type BulkInsertStatementInput,
  type UpsertStatementInput,
  type ColumnTypeInput,
  type ColumnDefinitionInput,
  type CreateTableStatementInput,
  type CreateIndexStatementInput,
  type StreamableConnection,
  type DialectIntrospector,
  type DialectIntrospectOptions,
} from 'turbine-orm';

Start from postgresDialect#

Spread the PostgreSQL dialect and override what differs:

import { postgresDialect, TurbineClient, type Dialect } from 'turbine-orm';
 
export const myDialect: Dialect = {
  ...postgresDialect,
  name: 'myengine',
  capabilities: {
    ...postgresDialect.capabilities,
    distinctOn: false,
    positionalJson: false,
    nullsSortHigh: false,
    readOnlyTransactions: false,
    backendCancel: false,
    abortsTransactionOnError: false,
    statementSyntax: null,
    // ...every other key your engine answers differently
  },
  paramPlaceholder: (i) => `@p${i}`,
  supportsVector: false,
  supportsListenNotify: false,
};
 
const db = new TurbineClient({ pool: myPoolShim, dialect: myDialect }, SCHEMA);

The pool shim implements PgCompatPool: query(text, values), connect() and end(). That is the whole driver contract.

Engine capabilities: what the engine is, not what it is called#

capabilities (type EngineCapabilities) says what the engine can do. Turbine reads these keys rather than dialect.name. The object is required: TurbineClient refuses a dialect without one with ValidationError (TURBINE_E003).

A spread copies PostgreSQL's answers under your new name. Go through every key: a wrong true becomes a failing query at runtime, while a correct false becomes a clear TURBINE_E017. The shipped sqliteDialect, mysqlDialect and mssqlDialect (from their subpaths) are worked examples.

Every shipped engine's value, generated from its dialect object and checked against it on every pull request (✓ is true, ✗ is false, and a string value is shown without its quotes):

Two renderings still follow the name. expr.now(), expr.greatest() and expr.least() throw TURBINE_E017 under any name other than postgresql, mysql, sqlite or mssql. schemaToSQL writes column DEFAULTs in PostgreSQL's spelling under any other name.

Feature flags#

Feature flags sit at the top level of the dialect. Most gate a feature: when off, using it throws UnsupportedFeatureError (TURBINE_E017) instead of emitting SQL that cannot work. A few choose a SQL shape instead. jsonPathSupport (required) is 'native', 'function' or 'limited'.

An absent optional flag means off, but a spread is not absence: it carries PostgreSQL's value. Set every flag your engine answers differently as its own property.

resultStrategy#

type ResultStrategy = 'returning' | 'output' | 'reselect';

A reselect is a second statement. It cannot return rows it has no key for, which is why a plain createMany on MySQL returns [].

Required methods#

• Values: paramPlaceholder(index), quoteIdentifier(name), escapeStringLiteral(value). Return a numbered placeholder ($1, :p1, @p1), not ?: parameters are pushed in build order, which is not always text order.
• JSON nesting: buildJsonObject, buildJsonArrayAgg, wrapJsonSubresult, emptyJsonArrayLiteral, nullJsonLiteral, buildJsonContains, buildJsonPathExtract. An empty to-many must come back [] and a missing to-one null.
• Statements: buildReturningClause, buildInsertStatement, buildBulkInsertStatement, buildUpsertStatement, buildInsensitiveLike, buildCorrelation.
• DDL: buildColumnType, buildColumnDefinition, buildPrimaryKeyConstraint, buildCreateTableStatement, buildCreateIndexStatement.
• Migration tracking: buildMigrationTrackingTable, buildMigrationSelectApplied, buildMigrationUpdateChecksum, buildMigrationInsertApplied, buildMigrationDeleteApplied.
• Transactions: beginStatement, commitStatement, rollbackStatement, savepointStatement, releaseSavepointStatement, rollbackToSavepointStatement, buildSetSessionConfig.
• Streaming: openStream(connection, sql, params, batchSize), an async generator of row batches. Only PostgreSQL's runs a server-side cursor; the other shipped dialects load the result, then yield it in batches.

Optional hooks#

An omitted hook falls back to the PostgreSQL behavior.

DialectMigrator is deprecated and unused. Migration locking lives in a DatabaseAdapter.

Checklist#

1. Set every capabilities key and feature flag from measured behavior.
2. Pick the resultStrategy that matches the engine, and document its limits if it is not 'returning'.
3. Test nested with at depth, an ordered to-many with a per-relation limit, a composite-key relation, an empty to-many ([]) and a missing to-one (null).
4. Check that quoteIdentifier survives identifiers containing quote characters.
5. Confirm every value reaches the database as a bound parameter.

See also#

• Database Engines: the engines Turbine ships and their capability matrix.
• Database Compatibility: PostgreSQL-wire databases, which need no dialect.
• The turbine-orm/powdb module: the engine whose queries bypass the dialect.
• Typed Errors: UnsupportedFeatureError and the rest of the code table.