# Database Migrations

> Manage database schema changes with Prisma Migrate: creating, applying, and troubleshooting migrations.

*Canonical: https://makerkit.dev/docs/tanstack-prisma/database/migrations*

---

Migrations track and apply schema changes to your database in a controlled, version-controlled way. [Prisma](./prisma) Migrate generates SQL migration files by comparing your schema with the last known state, giving you full visibility into what changes before it hits the database.

This guide is part of the [Database Configuration](./overview) documentation.

## Migration Workflow

The typical workflow is:

1. Edit the schema in `packages/database/src/prisma/schema.prisma`
2. Create and apply a migration: `pnpm --filter @kit/database prisma:migrate`
3. Review the generated SQL file in `packages/database/src/prisma/migrations/*/migration.sql`
4. Regenerate the client if needed: `pnpm --filter @kit/database prisma:generate`

{% callout type="default" title="prisma migrate dev creates AND applies" %}
`prisma:migrate` runs `prisma migrate dev`, which generates the SQL migration **and** applies it to your development database in one step. There is no separate generate + migrate two-step. Review the generated SQL afterward, especially for destructive changes like dropping columns or changing types.
{% /callout %}

## Repo Conventions

Keep examples aligned with the current schema style in this repo:

- ids are explicit `String @id`
- ownership fields use camelCase such as `organizationId`
- table names are controlled with `@@map`
- migrations live in `packages/database/src/prisma/migrations`

## Adding a New Table

Add the model to `packages/database/src/prisma/schema.prisma`:

```prisma {% title="packages/database/src/prisma/schema.prisma" %}
model Project {
  id             String       @id
  name           String
  organizationId String
  createdAt      DateTime     @default(now())

  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)

  @@index([organizationId])
  @@map("project")
}
```

Add the reverse relation on `Organization`:

```prisma
model Organization {
  // existing fields...
  projects Project[]
}
```

Create and apply the migration:

```bash
pnpm --filter @kit/database prisma:migrate
```

Prisma writes a SQL file such as:

```sql
-- CreateTable
CREATE TABLE "project" (
    "id" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "organizationId" TEXT NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "project_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE INDEX "project_organizationId_idx" ON "project"("organizationId");

-- AddForeignKey
ALTER TABLE "project" ADD CONSTRAINT "project_organizationId_fkey"
  FOREIGN KEY ("organizationId") REFERENCES "organization"("id")
  ON DELETE CASCADE ON UPDATE CASCADE;
```

## Adding a Column

Add the new field to your existing model:

```prisma
model Project {
  id             String  @id
  name           String
  description    String? // New nullable column
  organizationId String
}
```

Create and apply:

```bash
pnpm --filter @kit/database prisma:migrate
```

The generated SQL:

```sql
ALTER TABLE "project" ADD COLUMN "description" TEXT;
```

{% callout type="warning" title="Adding required columns to populated tables" %}
Adding a required (non-nullable) column to a table that already has rows fails without a default. Either give the field a `@default(...)`, or add it as nullable first, backfill data, then make it required in a follow-up migration.
{% /callout %}

## Changing Column Types

Be careful with type changes. Some are safe, some are not:

**Safe changes:**
- Making a column nullable
- `Int` to `BigInt`

**Unsafe changes (may fail or lose data):**
- `String` to `Int`
- Making a column required when NULLs exist
- Reducing precision

For unsafe changes, edit the generated `migration.sql` to add a backfill step before applying, or write the data migration in a separate step.

## Migration Files

Migrations are stored in `packages/database/src/prisma/migrations`:

```
packages/database/src/prisma/migrations/
├── 20260115235605_init/
│   └── migration.sql
├── 20260305073325/
│   └── migration.sql
├── 20260413023018/
│   └── migration.sql
├── 20260629091122/
│   └── migration.sql
└── migration_lock.toml
```

Each migration is a timestamped directory containing a single `migration.sql`. Prisma records applied migrations in the `_prisma_migrations` table in your database and applies only the ones that haven't run yet.

## Production Deployments

Use `prisma migrate deploy` in CI and production — not `prisma migrate dev`. `migrate deploy` applies pending migrations without prompting or generating new ones:

```bash
# In your deployment script or CI/CD pipeline
DATABASE_URL="$PROD_DATABASE_URL" pnpm --filter @kit/database exec prisma migrate deploy
```

### Deployment Best Practices

1. **Test migrations on staging first**: Always run migrations against a staging database before production
2. **Backup before migrating**: Take a database backup before applying migrations to production
3. **Run migrations before deploying code**: The new code may depend on schema changes
4. **Keep migrations small**: Smaller migrations are easier to debug and roll back

## Resetting in Development

To wipe and rebuild your local database from scratch, re-applying every migration:

```bash
pnpm --filter @kit/database prisma:reset
# or from the root
pnpm run db:reset
```

{% callout type="warning" title="Reset drops all data" %}
`prisma:reset` runs `prisma migrate reset`, which drops the database and re-applies all migrations. Never run it against a database with data you care about.
{% /callout %}

## Rolling Back

Prisma Migrate does not have built-in rollback. For rollbacks:

1. Write a reverse migration manually with `prisma migrate dev --create-only`, then edit the SQL
2. Or restore from a database backup
3. Or use your database provider's point-in-time recovery

For important changes, test migrations on a staging database first.

## Common Mistakes to Avoid

**Editing old migration files**: Once a migration has been applied to any database (including staging), don't edit or delete it. Prisma tracks applied migrations by name and checksum.

**Forgetting to commit migrations**: Migration files in `src/prisma/migrations` must be committed to version control. They are the source of truth for your schema history.

**Using `prisma db push` in production**: `prisma:push` applies schema changes directly without creating migration files. Use it only for rapid prototyping in development. In production, always use proper migrations.

**Adding required columns to populated tables without a backfill plan**: Changing a column to required fails if NULLs exist. Write a data migration first, then change the constraint.

{% faq
   title="Frequently Asked Questions"
   items=[
     {"question": "What does prisma:migrate do?", "answer": "It runs prisma migrate dev, which compares your schema.prisma to the last migration, generates a new SQL migration for the differences, and applies it to your development database in one step. Review the generated SQL afterward."},
     {"question": "How do I apply migrations in production?", "answer": "Use prisma migrate deploy, not prisma migrate dev. migrate deploy applies pending migrations without prompting or generating new ones, which is what you want in CI and production."},
     {"question": "How do I roll back a migration?", "answer": "Prisma Migrate has no built-in rollback. Create a new reverse migration with prisma migrate dev --create-only and edit the SQL, restore from a backup, or use your provider's point-in-time recovery. Always test on staging first."},
     {"question": "Can I edit generated migration SQL files?", "answer": "Yes, you can edit a migration before it has been applied, for example to add a data backfill step. Use prisma migrate dev --create-only to generate the SQL without applying it, edit it, then apply. Never edit migrations that have already run."}
   ]
/%}

---

**Next:** [Client →](./client)