Database Migrations

Create database migrations by modifying schema.prisma, running pnpm --filter @kit/database prisma:generate to update types, then pnpm --filter @kit/database prisma:migrate to generate and apply SQL. Migration files are version-controlled in packages/database/src/prisma/migrations.

This guide is part of the Database Configuration documentation.

Database migration is a version-controlled change to your database schema, stored as SQL that can be applied forward or rolled back. Migrations track the evolution of your data model over time.

The Prisma Migrate documentation covers advanced scenarios like squashing migrations and handling production databases.

Practical Example: adding a new table

Let's say we want to add a new table to the database called projects. We can do this by adding the following to the packages/database/src/prisma/schema.prisma file:

model Project {
  id             String       @id @default(uuid())
  name           String
  organizationId String       @map("organization_id")
  createdAt      DateTime     @default(now()) @map("created_at")
  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  @@map("projects")
}

Don't forget to add the reverse relation to the Organization model:

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

Now, we can generate a migration file by running the following command:

pnpm --filter "@kit/database" prisma:generate

Then create the migration:

pnpm --filter "@kit/database" prisma:migrate

Prisma will prompt you for a migration name. Enter something like add_projects_table. This creates a migration file in the packages/database/src/prisma/migrations directory.

Expected output:

? Enter a name for the new migration: add_projects_table
Applying migration `20250118_add_projects_table`
The following migration(s) have been applied:
migrations/20250118_add_projects_table/migration.sql

If you open the SQL migration file, you will see something like:

-- migrations/20250118_add_projects_table/migration.sql
CREATE TABLE "projects" (
  "id" TEXT NOT NULL,
  "name" TEXT NOT NULL,
  "organization_id" TEXT NOT NULL,
  "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "projects_pkey" PRIMARY KEY ("id")
);
ALTER TABLE "projects" ADD CONSTRAINT "projects_organization_id_fkey"
  FOREIGN KEY ("organization_id") REFERENCES "organization"("id")
  ON DELETE CASCADE ON UPDATE CASCADE;

The migration is automatically applied when running pnpm --filter "@kit/database" prisma:migrate. We can now use the project model in our codebase:

import { db } from '@kit/database';
// Create a project
const project = await db.project.create({
  data: {
    name: 'My Project',
    organizationId: currentOrgId,
  },
});
// Query projects
const projects = await db.project.findMany({
  where: { organizationId: currentOrgId },
});

Migration Gotchas

We've hit these issues in production migrations across multiple MakerKit deployments:

• Adding NOT NULL columns to existing tables - Fails if the table has rows; add nullable first, backfill data, then alter to NOT NULL in a second migration
• Renaming columns with foreign keys - Requires dropping and recreating constraints; Prisma sometimes generates incorrect SQL for this
• Running prisma migrate on production without --deploy - Enters interactive mode instead of applying pending migrations; always use prisma migrate deploy in CI/production
• Large table migrations without CONCURRENTLY - Adding indexes on tables with 1M+ rows locks the table; use CREATE INDEX CONCURRENTLY in raw SQL migrations
• Forgetting to commit migration files - Team members get schema drift; always commit the migrations/ folder to git
• Editing existing migration files - Causes checksum mismatches on deployed databases; never edit applied migrations

Decision Rules

Use prisma migrate dev when:

• Developing locally and iterating on schema
• You want Prisma to auto-generate the migration SQL
• The database can be reset if needed

Use prisma migrate deploy when:

• Running in CI/CD pipelines
• Deploying to production or staging
• You need non-interactive migration application

Use prisma db push when:

• Prototyping and don't need migration history
• Working with a branch database you'll discard
• Testing schema changes before committing to a migration

If unsure: Use prisma migrate dev locally, prisma migrate deploy in CI. Avoid db push for production databases.

Next: Client →