> ## Documentation Index
> Fetch the complete documentation index at: https://docs.vertz.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Migrations

> Automatic schema migrations during development, explicit migrations for production

Vertz handles migrations by diffing your TypeScript schema against the database. During development, migrations run automatically when you save a schema file — no commands needed. For production, you generate migration files and apply them explicitly.

## Getting started

<Steps>
  <Step title="Define your schema">
    Create a `.schema.ts` file with your table definitions using the `d` builder. See the [schema
    guide](/guides/db/schema) for details.
  </Step>

  <Step title="Start the dev server">
    Run `vertz dev`. The dev server watches your schema files and applies migrations automatically
    when you save.
  </Step>

  <Step title="Generate migration files before deploying">
    When you're ready to deploy, run `vertz db migrate` to create a migration file, then `vertz db
            deploy` in production to apply it.
  </Step>
</Steps>

## Command comparison

| Command            | Creates migration file? |   Applies to DB?   | Use when                                                        |
| ------------------ | :---------------------: | :----------------: | --------------------------------------------------------------- |
| `vertz dev`        |            No           |     Yes (auto)     | Local development — schema changes apply on save                |
| `vertz db push`    |            No           |         Yes        | Quick one-off schema sync without a migration file              |
| `vertz db migrate` |           Yes           |         Yes        | Generating a reviewable migration file for production           |
| `vertz db deploy`  |            No           | Yes (pending only) | Production deployments — applies committed migration files      |
| `vertz db pull`    |            No           |         No         | Generate a schema file from an existing database                |
| `autoMigrate()`    |            No           |         Yes        | Programmatic dev setup (test suites, scripts). **SQLite only.** |

* **`vertz dev`** — the default for local development. No commands to remember.
* **`vertz db push`** — useful when you want to apply changes outside the dev server without generating a migration file (e.g., CI test databases).
* **`vertz db migrate`** — generates a numbered `.sql` file in your migrations directory and applies it. Commit this file to version control.
* **`vertz db deploy`** — reads migration files from disk and applies any that haven't been applied yet. Never generates new files. Safe for production.
* **`vertz db pull`** — connects to an existing database and generates a TypeScript schema file. See the [introspection guide](/guides/db/introspection).
* **`autoMigrate()`** — a programmatic API for test setup or custom scripts. It diffs the schema snapshot and applies changes directly. Currently supports SQLite only — Postgres users should use `vertz db push` or `migrateDev()` instead.

## File structure

After running `vertz db migrate`, your project will have:

```
my-app/
├── migrations/
│   ├── 0000_initial.sql              # First migration (raw SQL)
│   ├── 0001_add-priority-to-tasks.sql
│   ├── _journal.json                 # Migration metadata & checksums
│   └── _snapshot.json                # Current schema snapshot
├── src/
│   └── tasks.schema.ts               # Your schema definitions
└── vertz.config.ts
```

| File                        | Purpose                                                                               |
| --------------------------- | ------------------------------------------------------------------------------------- |
| `migrations/*.sql`          | Numbered migration files containing DDL statements. Commit these.                     |
| `migrations/_journal.json`  | Tracks which migrations exist, their checksums, and creation timestamps. Commit this. |
| `migrations/_snapshot.json` | The current schema state used for diffing. Commit this.                               |

The migrations directory defaults to `./migrations/` and can be changed via `migrationsDir` in `vertz.config.ts`.

## Development workflow

During `vertz dev`, schema changes are detected and applied automatically:

```
# Start the dev server
vertz dev

# Edit a schema file — migration runs automatically:
# [DB] Schema change detected: tasks.schema.ts
# [DB] Applied: added column "priority" to "tasks"
```

Change your schema, save the file, keep building. The dev server:

1. Detects `.schema.ts` file changes
2. Diffs the current schema against the last snapshot
3. Generates and applies the necessary SQL
4. Warns on destructive changes (table or column drops)
5. Updates the snapshot

No migration files are written during development.

### Manual commands

If you need to run migrations outside the dev server:

```bash theme={null}
# Generate and apply migration from schema diff
vertz db migrate

# Push schema changes directly (no migration file)
vertz db push

# Drop everything and re-apply from scratch
vertz db reset
```

<Warning>`vertz db reset` destroys all data. Use only in development.</Warning>

## Production workflow

For production deployments, use explicit migration files:

```bash theme={null}
# Generate a migration file from schema changes
vertz db migrate

# Apply pending migration files (production-safe)
vertz db deploy
```

`vertz db deploy` only applies existing migration files — it never generates new ones. This ensures production databases only receive reviewed, committed migrations.

### Check migration status

```bash theme={null}
vertz db status
```

Shows which migrations have been applied and which are pending. Also detects schema drift — differences between the expected schema and the actual database state.

### Baseline

For existing databases, create a baseline migration that represents the current state:

```bash theme={null}
vertz db baseline
```

This marks the current database state as the starting point for future migrations — no SQL is applied.

## Programmatic API

For custom tooling or test setup:

```ts theme={null}
import { migrateDev, migrateDeploy, migrateStatus } from '@vertz/db';

// Development: generate and apply migration
await migrateDev({
  queryFn: db.queryFn,
  currentSnapshot: db.snapshot,
  previousSnapshot: loadFromFile(),
  migrationsDir: './migrations',
});

// Production: apply pending migration files
await migrateDeploy({
  queryFn: db.queryFn,
  migrationsDir: './migrations',
});

// Check status
const status = await migrateStatus({
  queryFn: db.queryFn,
  migrationsDir: './migrations',
});
```

## RLS policy migrations

If you use [RLS policy generation](/guides/server/codegen#rls-policy-generation-opt-in) from the codegen system, the migration pipeline can include RLS changes alongside schema changes.

When you pass `rlsPolicies` to `migrateDev()`, it diffs the current RLS state against the previous snapshot and appends the necessary SQL:

```ts theme={null}
import { migrateDev } from '@vertz/db';

await migrateDev({
  queryFn: db.queryFn,
  currentSnapshot: db.snapshot,
  previousSnapshot: loadFromFile(),
  migrationsDir: './migrations',
  rlsPolicies: codegenOutput.rlsPolicies, // from codegen
});
```

The generated migration includes:

1. Schema DDL changes (table/column modifications)
2. `ALTER TABLE ... ENABLE ROW LEVEL SECURITY` for new RLS tables
3. `DROP POLICY` for removed or changed policies
4. `CREATE POLICY` for new or changed policies
5. `ALTER TABLE ... DISABLE ROW LEVEL SECURITY` for tables that no longer have policies

The snapshot tracks RLS state, so subsequent migrations only include incremental changes.

### Setting session variables

RLS policies reference PostgreSQL session variables (`app.user_id`, `app.tenant_id`) via `current_setting()`. These must be set per-request within a transaction using `SET LOCAL`:

```sql theme={null}
BEGIN;
SET LOCAL app.user_id = '<user-uuid>';
SET LOCAL app.tenant_id = '<tenant-uuid>';
-- All queries here are filtered by RLS policies
SELECT * FROM tasks;
COMMIT;
```

`SET LOCAL` scopes the variables to the current transaction — they're automatically cleared on commit or rollback. The framework's entity CRUD pipeline sets these automatically when RLS policies are present.

## Troubleshooting

### Snapshot out of sync

**Symptom:** `vertz db migrate` generates unexpected changes (re-adding columns that already exist, or dropping columns you didn't remove).

**Cause:** The `_snapshot.json` file doesn't match the actual database state. This can happen if you applied schema changes manually or edited the database outside the migration system.

**Fix:**

1. Run `vertz db status` to see what the system thinks the current state is.
2. If the database is correct and the snapshot is wrong, run `vertz db push` to sync the snapshot to match your current schema, then run `vertz db migrate` to generate a clean migration.
3. If the database is wrong, run `vertz db reset` (development only) to rebuild from migration files.

### Migration fails to apply

**Symptom:** `vertz db deploy` or `vertz db migrate` fails with a SQL error.

**Fix:**

1. Read the error — it usually points to a specific SQL statement (e.g., column already exists, constraint violation).
2. If the migration was partially applied, check `vertz db status` to see which migrations succeeded.
3. Fix the failing migration SQL file manually, then re-run `vertz db deploy`.
4. If the database is in an inconsistent state in development, `vertz db reset` will drop everything and re-apply all migrations from scratch.

<Warning>`vertz db reset` drops all tables and data. Never use in production.</Warning>

### Concurrent schema edits

**Symptom:** Two developers edit the schema at the same time and both run `vertz db migrate`. The second migration may conflict with the first.

**Fix:**

1. The journal system detects sequence number collisions. If two migrations share the same number, `vertz db migrate` will warn you.
2. Pull the latest migrations from version control, resolve any conflicts in the SQL files and `_journal.json`, then re-run `vertz db migrate`.

### autoMigrate not available for Postgres

`autoMigrate()` only supports SQLite. If you need programmatic migration for Postgres in test setup or scripts, use `migrateDev()` or `push()` from `@vertz/db` instead:

```ts theme={null}
import { push } from '@vertz/db';

await push({
  queryFn: db.queryFn,
  currentSnapshot: db.snapshot,
  previousSnapshot: loadFromFile(),
  dialect: 'postgres',
});
```