Schema & Migrations

defineDatabase is the schema entrypoint.

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?)
• dropIndex(indexName)
• dropTable(tableName)
• renameTable(fromTableName, toTableName)

The builder updates its schema type after dropTable and renameTable, so later migration steps can only refer to the current table names. Index and table names are checked against the accumulated runtime schema while the definition is built.

createTable options

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.

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().