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?)

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