Database Migrations

This guide covers Prisma migrations for CivStart's PostgreSQL database.

Important

Always create migrations for schema changes. This project has automated checks to prevent pushing schema changes without migrations.

Quick Guide

When You Modify schema.prisma:

1. Make your schema changes in packages/database/prisma/schema.prisma

2. Create a migration immediately:

   pnpm db:migrate:dev --name describe_your_changes

   Example:

   pnpm db:migrate:dev --name add_user_role_field

3. Review the generated migration in packages/database/prisma/migrations/

4. Commit BOTH the schema and migration files together:

   git add packages/database/prisma/schema.prisma
   git add packages/database/prisma/migrations/
   git commit -m "feat: Add user role field"

5. Push to remote:

   git push

Automated Protection

Pre-Push Hook

Before every git push, a hook runs to check:

• If schema.prisma changed, migration files must also change
• Blocks push if schema changed without migrations

GitHub Actions

On every PR and push to dev/staging/main:

• Validates schema changes have corresponding migrations
• Fails CI if migrations are missing

Bypass (NOT RECOMMENDED)

git push --no-verify

Warning

This defeats the purpose of the protection!

Common Commands

Command	Description
pnpm db:migrate:dev --name <name>	Create a new migration
pnpm db:migrate:status	Check migration status
pnpm db:migrate:deploy	Apply migrations (production)
pnpm db:generate	Regenerate Prisma Client
pnpm db:studio	Open Prisma Studio GUI
pnpm db:check-migrations	Manually run migration check

Migration Workflow

Development (Local)

# 1. Modify schema.prisma
# 2. Create migration
pnpm db:migrate:dev --name add_new_field

# 3. Test locally
pnpm dev

# 4. Commit and push
git add .
git commit -m "feat: Add new field"
git push

Staging/Production

Migrations are automatically applied by the deployment workflow:

1. Workflow builds Docker image with migration files
2. Runs prisma migrate deploy in ECS task
3. Then deploys the service

Troubleshooting

"Migration check failed" on push

Problem: You modified schema.prisma but didn't create a migration.

Solution:

pnpm db:migrate:dev --name your_change_description
git add packages/database/prisma/migrations/
git commit --amend --no-edit
git push

"Migration already exists" error

Problem: You ran prisma migrate dev twice.

Solution:

• Delete the duplicate migration folder
• Or use prisma migrate resolve if needed

Schema and database out of sync

Problem: Your local DB doesn't match schema.

Solution:

# Check status
pnpm db:migrate:status

# Reset database (DESTROYS DATA)
pnpm db:push --force-reset

# Or apply pending migrations
pnpm db:migrate:dev

Best Practices

1. Always create migrations - Never push schema changes without migrations
2. Descriptive names - Use clear migration names: add_user_email_field not update
3. Test locally first - Run migrations on local DB before pushing
4. One logical change - Keep migrations focused on one change
5. Review migrations - Check generated SQL before committing
6. Never edit migrations - Don't modify migrations after they're pushed
7. Never delete migrations - Use prisma migrate resolve instead

Emergency: Failed Migration in Production

If a migration fails in staging/production:

1. Check the logs in CloudWatch
2. Use prisma migrate resolve to mark it as applied/rolled-back
3. Fix the issue and create a new migration
4. Redeploy

See the scripts/fix-failed-migration.ts for emergency cleanup.

Migration File Structure

packages/database/prisma/migrations/
├── 20251115000000_add_user_role/
│   └── migration.sql          # The actual SQL
├── 20251115000001_add_index/
│   └── migration.sql
└── migration_lock.toml        # Lock file (don't modify)

Resources

• Prisma Migrate Documentation