Database Migrations

How to run and manage Prisma migrations for App CR

Overview

Prisma Migrate is a declarative migration system that helps you keep your database schema in sync with your Prisma schema. It generates SQL migration files and applies them to your database.

Initial Migration

The initial migration (20260218000116_init) creates the foundational database structure for App CR.

Migration Contents

Create User Table

CREATE TABLE "User" (
    "id" SERIAL NOT NULL,
    "email" TEXT NOT NULL,
    "password" TEXT NOT NULL,
    CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "User_email_key" ON "User"("email");

What Gets Created

1. User Table

   • Primary key id with auto-increment (SERIAL)
   • Unique email field with index
   • Password field for authentication
2. Task Table

   • Primary key id with auto-increment (SERIAL)
   • Required title field
   • Optional description field
   • Boolean completed field (defaults to false)
   • Foreign key userId linking to User table
3. Constraints & Indexes

   • Primary key constraints on both tables
   • Unique index on User.email
   • Foreign key constraint from Task to User

---

Running Migrations

Development Workflow

1. Apply Existing Migrations

   Run all pending migrations against your database:

   cd ~/workspace/source/backend
   npx prisma migrate deploy
   

   Use migrate deploy in production environments. It applies migrations without prompting.

2. Development Migration

   For development with schema changes:

   npx prisma migrate dev
   

   This command:

   • Creates new migration files for schema changes
   • Applies migrations to the database
   • Generates Prisma Client
   • Prompts for migration name
3. Verify Migration

   Check migration status:

   npx prisma migrate status
   

   View applied migrations in database:

   npx prisma migrate resolve --applied "migration_name"
   

Common Migration Commands

Apply All Migrations

# Production-safe: applies all pending migrations
npx prisma migrate deploy

---

Creating New Migrations

When you modify the Prisma schema, create a new migration:

1. Modify Schema

   Edit backend/prisma/schema.prisma with your changes:

   model Task {
     id          Int      @id @default(autoincrement())
     title       String
     description String?
     completed   Boolean  @default(false)
     priority    String   @default("medium") // New field
     userId      Int
     author      User     @relation(fields: [userId], references: [id])
   }
   
2. Create Migration

   Generate migration from schema changes:

   npx prisma migrate dev --name add_task_priority
   

   This creates:

   • migrations/20260306XXXXXX_add_task_priority/migration.sql
   • Updates Prisma Client types
3. Review Migration SQL

   Check the generated SQL before applying:

   cat prisma/migrations/20260306XXXXXX_add_task_priority/migration.sql
   
4. Commit Migration

   Add migration files to version control:

   git add prisma/migrations/
   git commit -m "Add priority field to Task model"
   

---

Migration Files

Directory Structure

backend/prisma/
├── schema.prisma
└── migrations/
    ├── 20260218000116_init/
    │   └── migration.sql
    ├── migration_lock.toml
    └── [future_migrations]/

Migration Lock File

# migration_lock.toml
provider = "postgresql"

Never modify migration files directly. Always create new migrations for schema changes.

---

Production Migrations

Deployment Process

1. Test Locally

   Always test migrations in development first:

   npx prisma migrate dev
   
2. Deploy to Production

   Apply migrations in production:

   npx prisma migrate deploy
   

   migrate deploy does not prompt or generate new migrations. It only applies existing ones.

3. Verify Deployment

   Check migration status:

   npx prisma migrate status
   

   Expected output:

   Database schema is up to date!
   

CI/CD Integration

# Example GitHub Actions workflow
- name: Run Migrations
  run: |
    cd backend
    npx prisma migrate deploy
  env:
    DATABASE_URL: ${{ secrets.DATABASE_URL }}

---

Troubleshooting

Reset Development Database

If your development database is in an inconsistent state:

npx prisma migrate reset

This command drops the database, recreates it, and applies all migrations. All data will be lost.

Resolve Failed Migration

If a migration fails partway through:

Mark as Applied

# If migration was applied but not recorded
npx prisma migrate resolve --applied "20260218000116_init"

Schema Drift Detection

Check if database schema differs from migration history:

npx prisma migrate diff \
  --from-schema-datamodel prisma/schema.prisma \
  --to-schema-datasource prisma/schema.prisma

---

Best Practices

Always review generated SQL

Before applying migrations, review the SQL to ensure it matches your intentions:

cat prisma/migrations/[timestamp]_[name]/migration.sql

Use descriptive migration names

Good: npx prisma migrate dev --name add_task_priority

Bad: npx prisma migrate dev --name update

Never edit migration files

Once a migration is committed and shared, never modify it. Create a new migration instead.

Test migrations in development

Always run and test migrations locally before deploying to production.

Backup before major migrations

Create database backups before applying migrations in production:

pg_dump -U user_admin mi_db_crud > backup.sql

---

Next Steps

Database Schema

View complete schema documentation

Database Setup

Configure database connection