---
title: Schema & Migrations
description: Define tables, columns, and indexes with ordered migration history.
---

# Schema & Migrations

`defineDatabase` is the schema entrypoint.

```ts
import { defineDatabase } from "@bms/tsql";

export const schema = defineDatabase((db) =>
	db
		.migration("001_create_users", (migration) =>
			migration
				.createTable("users", (t) => ({
					id: t.integer().primaryKey().autoIncrement(),
					email: t.text().notNull(),
					createdAt: t.datetime().notNull(),
				}))
				.createIndex("idx_users_email", "users", ["email"]),
		)
		.migration("002_create_posts", (migration) =>
			migration.createTable("posts", (t) => ({
				id: t.integer().primaryKey().autoIncrement(),
				authorId: t.integer().notNull().references("users.id"),
				title: t.text().notNull(),
				body: t.text(),
			})),
		)
		.migration("003_normalize_user_emails", (migration) =>
			migration
				.update("users")
				.set(({ users }) => ({
					email: users.email.trim().lower(),
				}))
				.execute(),
		),
);
```

## `DatabaseDefinitionBuilder.migration(name, run)`

- `name`: stable migration identifier
- `run`: receives a migration builder with schema methods and query methods (`insert`, `update`, `delete`, `raw`)
- return value is chainable and typed by the updated schema shape
- migration callback is the single place for both schema changes and data transformations

## Schema methods in migration callback

- `createTable(name, columns, options?)`
- `alterTable(name, columns)`
- `createIndex(indexName, tableName, columns, options?)`

### `createTable` options

```ts
createTable("users", buildColumns, { strict: true })
```

`strict` requires a dialect with `supportsStrictTables = true`.

### Column types and modifiers

Column builders:

- `t.integer()`
- `t.text()`
- `t.real()`
- `t.datetime()`
- `t.boolean()`
- `t.json<T>()`
- `t.enum([...])`
- `t.array<T>()`
- `t.set<T>()`

`t.datetime()` maps to `Date` in TypeScript query/mutation types. The active dialect controls runtime conversion at the driver boundary (for example SQLite stores `datetime` as text and parses ISO values back into `Date`).

### `set` type

`t.set<T>()` maps to `Set<T>` in TypeScript.

```ts
const schema = defineDatabase((db) =>
	db.migration("001_create_groups", (migration) =>
		migration.createTable("groups", (t) => ({
			id: t.integer().primaryKey(),
			memberIds: t.set<number>().notNull(),
		})),
	),
);

await db.insert("groups").values({
	id: 1,
	memberIds: new Set([1, 2, 3]),
}).execute();
```

For SQLite, sets are stored as JSON array text and parsed back to `Set` when reading typed select results.

Modifiers:

- `.notNull()`
- `.primaryKey()`
- `.autoIncrement()`
- `.references("table.column")`

## What this creates

`defineDatabase(...)` returns a `Schema<TShape>` object with:

- `schema.runtime` (tables + columns + indexes metadata)
- `schema.migrations` (ordered migration operations)

No SQL runs during definition. Execution happens only through `db.migrate()`.

<!--
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.
-->