---
title: Quickstart
description: Define migrations, run them safely, and write typed SQL in application code.
---

# Quickstart

This guide takes you through the full `tsql` workflow:

- Define schema migrations in TypeScript.
- Apply migrations in CI or application startup.
- Treat each migration as SQL (DDL + DML) in one callback.
- Use the same schema for typed queries in server code.

## Define your schema with migrations

`tsql` treats your ordered migration history as the source of truth.

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

export const schema = defineDatabase((db) =>
	db
		.migration("001_create_users", (schemaBuilder) =>
			schemaBuilder.createTable("users", (t) => ({
				id: t.integer().primaryKey().autoIncrement(),
				email: t.text().notNull(),
				createdAt: t.datetime().notNull(),
			})),
		)
		.migration("002_add_profile_fields", (schemaBuilder) =>
			schemaBuilder.alterTable("users", (t) => ({
				displayName: t.text(),
				avatarUrl: t.text(),
			})),
		),
);
```

Use stable, ordered migration names (for example `001_`, `002_`, ...). `db.migrate()` uses those names to track applied migrations in `__tsql_migrations`.

## Connect and run migrations

At runtime you provide three things:

- `schema`: your `defineDatabase(...)` result
- `dialect`: currently `sqliteDialect()`
- `connection`: an adapter that implements `SqlConnection.execute(sql, params)`

```ts
import { DatabaseSync } from "node:sqlite";
import type { SQLInputValue } from "node:sqlite";
import { connect, sqliteDialect, type SqlConnection } from "@bms/tsql";
import { schema } from "./schema";

const sqlite = new DatabaseSync(process.env.DB_PATH ?? "./app.sqlite");

const connection: SqlConnection = {
	execute: async (sql, params) => {
		const statement = sqlite.prepare(sql);
		const sqliteParams = params as readonly SQLInputValue[];

		if (/^\s*select\s/i.test(sql)) {
			return { rows: statement.all(...sqliteParams) };
		}

		statement.run(...sqliteParams);
		return { rows: [] };
	},
};

export const db = connect({
	schema,
	dialect: sqliteDialect(),
	connection,
});
```

Then run migrations once in a bootstrap path:

```ts
await db.migrate();
```

## SQL rewrites in migrations

Migrations are SQL-first scripts authored in TypeScript. Chain DDL steps (`alterTable`, `createIndex`) and DML steps (`update`, `insert`, `delete`, `raw`) in order.

Example: add `normalizedEmail` and backfill it in one migration.

```ts
// schema.ts
export const schema = defineDatabase((db) =>
	db
		.migration("001_create_users", (schemaBuilder) =>
			schemaBuilder.createTable("users", (t) => ({
				id: t.integer().primaryKey().autoIncrement(),
				email: t.text().notNull(),
			})),
		)
		.migration("002_add_normalized_email", (migration) =>
			migration
				.alterTable("users", (t) => ({
					normalizedEmail: t.text(),
				}))
				.createIndex("idx_users_normalized_email", "users", ["normalizedEmail"])
				.update("users")
				.where(({ users }) => users.normalizedEmail.isNull())
				.set(({ users }) => ({
					normalizedEmail: users.email.trim().lower(),
				}))
				.execute(),
		),
);
```

```ts
// migrate.ts
import { db } from "./db";

await db.migrate();
```

If your transform is multi-step, keep chaining in the same callback (schema ops + `insert`/`update`/`delete`/`raw`). Each migration runs in a transaction, and `db.migrate()` skips migrations that were already recorded.

## Run this in CI or monolith startup

### CI / deployment step (recommended)

Create a dedicated script:

```ts
// scripts/migrate.ts
import { db } from "../src/db";

await db.migrate();
```

Run it before starting the new version:

```bash
pnpm tsx scripts/migrate.ts
```

### Monolith startup

For single-process monoliths, running migrations during startup is fine:

```ts
import { db } from "./db";
import { startServer } from "./server";

await db.migrate();
await startServer();
```

## Write typed queries in server code

The same `schema` used for migrations drives query typing.

```ts
import { db } from "./db";

const users = await db
	.from("users")
	.where(({ users }) => users.email.like("%@example.com", db.dialect))
	.orderBy(({ users }) => users.createdAt.desc())
	.select(({ users }) => ({
		id: users.id,
		email: users.email,
		displayName: users.displayName,
	}))
	.execute();
```

For compile-only flows (for logging or external execution), use `.compile()` instead of `.execute()`.

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