Dialect & SQL Compilation

Built-in dialects

tsql exports three built-in dialect helpers:

• sqliteDialect()
• postgresDialect()
• mysqlDialect()

They provide dialect-specific placeholder syntax, identifier quoting, DDL compilation, and mapped-type conversion hooks.

sqliteDialect() provides:

• name = "sqlite"
• placeholder(index) => "?"
• double-quoted identifiers
• supportsStrictTables = true
• SQLite column types such as integer, real, and text
• mapped-type conversion for datetime, boolean, json, enum, array, and set

postgresDialect() provides:

• name = "postgres"
• placeholder(index) => "$1", $2, ...
• rebasePlaceholders(sql, startIndex, paramCount) for composed expressions
• double-quoted identifiers
• supportsStrictTables = false
• PostgreSQL column types such as integer generated by default as identity, timestamp with time zone, and jsonb
• enum columns compiled as text with a check (...) constraint
• mapped-type conversion for json, enum, array, and set

mysqlDialect() provides:

• name = "mysql"
• placeholder(index) => "?"
• backtick-quoted identifiers
• supportsStrictTables = false
• MySQL column types such as int, double, datetime(3), json, and native enum(...)
• mapped-type conversion for datetime, boolean, json, enum, array, and set

Custom dialects

Implement SqlDialect when integrating another backend:

type ColumnMeta = {
	type: string;
	config?: Record<string, unknown>;
	notNull: boolean;
	primaryKey: boolean;
	autoIncrement: boolean;
	references?: string;
};

type SqlDialect = {
	name: string;
	placeholder(index: number): string;
	rebasePlaceholders?(sql: string, startIndex: number, paramCount: number): string;
	quoteIdentifier(identifier: string): string;
	supportsStrictTables: boolean;
	columnSqlType(column: ColumnMeta): string;
	compileColumnSql?(columnName: string, column: ColumnMeta): string;
	toDatabaseValue?(column: ColumnMeta, value: unknown): unknown;
	fromDatabaseValue?(column: ColumnMeta, value: unknown): unknown;
};

Use these conversion hooks for complex types such as datetime, boolean, json, enum, array, and set. This keeps type-safe application values independent from storage format differences across backends.

Implement rebasePlaceholders when a dialect uses numbered placeholders and SQL fragments can be composed from separately compiled expressions, as in PostgreSQL.

Implement compileColumnSql when a backend needs custom column DDL beyond columnSqlType, such as PostgreSQL check constraints for enums or MySQL auto_increment handling.

Query compilation

All query builders expose .compile() and return:

type CompiledQuery = {
	sql: string;
	params: readonly unknown[];
};

Use compile mode for logging, snapshot tests, or custom execution pipelines.

Schema SQL compilation

tsql also exports schema compilers:

• compileCreateTableSql(schema, dialect)
• compileMigrationSql(schema, dialect)

import { compileCreateTableSql, compileMigrationSql, sqliteDialect } from "@bms/tsql";

const ddl = compileCreateTableSql(schema, sqliteDialect());
const migrations = compileMigrationSql(schema, sqliteDialect());

compileMigrationSql returns SQL grouped by migration name, matching db.migrate() execution order.