Migration Runtime

db.migrate() applies pending migrations from your schema in order.

What db.migrate() does

• Ensures __tsql_migrations exists.
• Reads already-applied migration names.
• Compiles schema SQL from schema.migrations.
• Runs each pending migration inside its own transaction.
• Records migration name and timestamp after success.

If a migration fails, that migration transaction is rolled back and execution stops.

If the connection provides executeTransaction(statements), db.migrate() compiles each pending migration into CompiledQuery[] and sends it through that batch transaction primitive. This supports runtimes such as Cloudflare D1, where transactions are D1Database.batch() calls rather than SQL-level interactive begin / commit sessions.

If executeTransaction is not provided, db.migrate() uses db.begin() and the interactive transaction path.

Operational usage

• Preferred: run in CI/deploy bootstrap step.
• Monolith: run once during startup before serving traffic.
• Safe to call repeatedly; applied migrations are skipped.

Example bootstrap script

import { db } from "../src/db";

await db.migrate();

DML in migrations

Use migration queries directly in the migration callback:

export const schema = defineDatabase((db) =>
	db
		.migration("001_create_users", (schemaBuilder) =>
			schemaBuilder.createTable("users", (t) => ({
				id: t.text().primaryKey(),
				email: t.text().notNull(),
			})),
		)
		.migration("002_uppercase_user_ids", (migration) =>
			migration
				.update("users")
				.set(({ users }) => ({
					id: users.id.upper(),
				}))
				.execute(),
		),
);

The same callback can mix schema and data steps in sequence.

DDL and DML execute in the order they are chained. This permits typed table rebuilds: create a replacement table, copy rows with insert(...).from(select), drop the old table, and rename the replacement without falling back to raw SQL.

For column removal, migration.dropColumn("users", "obsolete") emits ALTER TABLE ... DROP COLUMN and removes the column from the inferred schema. Drop indexes on that column first.

Each migration runs in a transaction, so failures roll back cleanly; rerunning db.migrate() resumes from the first migration that has not been recorded yet.

Compile one migration

Use compileMigrationQueries(schema, dialect, migrationIndex) to compile one migration body into CompiledQuery[] without the migration tracking insert:

const statements = await compileMigrationQueries(schema, sqliteDialect(), 1);

In batch compile mode, query results are unavailable. If migration logic reads query results to build later SQL, tsql throws: connection supports batched transactions only; migration query results unavailable.