---
title: Dialect & SQL Compilation
description: Configure SQL dialect behavior and compile schema/query SQL.
---

# 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:

```ts
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:

```ts
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)`

```ts
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.

<!--
Sitemap

URL: https://tsql-docs.pages.dev/v0.5.0/guides/quickstart.md
Title: Quickstart
Description: Define migrations, run them safely, and write typed SQL in application code.

URL: https://tsql-docs.pages.dev/v0.5.0/reference/connecting.md
Title: Connecting
Description: Create a database instance from schema, dialect, and runtime connection.

URL: https://tsql-docs.pages.dev/v0.5.0/reference/dialect-and-compilation.md
Title: Dialect & SQL Compilation
Description: Configure SQL dialect behavior and compile schema/query SQL.

URL: https://tsql-docs.pages.dev/v0.5.0/reference/grouping-and-aggregates.md
Title: Grouping & Aggregates
Description: Use groupBy, having, and aggregate helpers for analytical queries.

URL: https://tsql-docs.pages.dev/v0.5.0/reference/joins-and-aliases.md
Title: Joins & Aliases
Description: Join tables, use left joins, and self-join safely with aliases.

URL: https://tsql-docs.pages.dev/v0.5.0/reference/migration-runtime.md
Title: Migration Runtime
Description: Understand db.migrate behavior, tracking, and failure semantics.

URL: https://tsql-docs.pages.dev/v0.5.0/reference/mutations.md
Title: Mutations
Description: Insert, update, and delete data with compile or execute.

URL: https://tsql-docs.pages.dev/v0.5.0/reference/predicates-and-expressions.md
Title: Predicates & Expressions
Description: Use Expr methods, logical operators, null checks, and expression composition.

URL: https://tsql-docs.pages.dev/v0.5.0/reference/raw-sql.md
Title: Raw SQL
Description: Use raw templates and expression templates when fluent APIs are not enough.

URL: https://tsql-docs.pages.dev/v0.5.0/reference/schema-and-migrations.md
Title: Schema & Migrations
Description: Define tables, columns, and indexes with ordered migration history.

URL: https://tsql-docs.pages.dev/v0.5.0/reference/select-basics.md
Title: Select Basics
Description: Build typed select queries with from, where, order, limit, and select.

URL: https://tsql-docs.pages.dev/v0.5.0/reference/transactions.md
Title: Transactions
Description: Use explicit begin, commit, rollback, and await using for safety.

URL: https://tsql-docs.pages.dev/v0.5.0/reference/type-utilities.md
Title: Type Utilities
Description: Derive row, insert, and update types directly from schema.
-->