Migrations

This document describes the database migration strategy for the Poker Engine project using Prisma.

Overview

The project uses Prisma Migrate for database schema management. Migrations are stored in prisma/migrations/ and tracked in version control.

Local Development

Running Migrations

# Apply pending migrations and create a new one if the schema changed
npm run db:migrate

# In production environments
npm run db:migrate:prod

Creating New Migrations

Modify the schema in prisma/schema.prisma
Run migration to generate SQL and apply:
npm run db:migrate
Prisma will prompt for a migration name
Review the generated SQL in prisma/migrations/<timestamp>_<name>/migration.sql
Commit both the schema changes and migration file

Testing Migrations Locally

Before committing migrations, test that they can be applied and reset:

npm run test:migrations

This script:

Applies all migrations (prisma migrate deploy)
Resets the database (prisma migrate reset --force)
Re-applies all migrations

Requires: DATABASE_URL pointing to a disposable PostgreSQL database. The reset step wipes all data, so never point it at a database you want to keep.

npm run ci:migrations runs the same apply/validate/reset/re-apply cycle against a disposable Docker Postgres container and is the safer local option.

CI/CD Pipeline

Automated Testing

The .github/workflows/migration-test.yml workflow automatically tests migrations on:

Pull requests targeting main or master that modify prisma/** or the workflow file
Pushes to main or master that modify prisma/** or the workflow file

The CI job:

Spins up a PostgreSQL 15 service container
Applies all migrations
Validates the schema
Resets and re-applies migrations
Verifies the final database state

.github/workflows/test.yml and the release workflow also apply migrations and fail if the migrations and prisma/schema.prisma have drifted apart (see scripts/ci/schema-drift.sh for the local equivalent).

Deployment

In production, migrations are applied using:

npx prisma migrate deploy

This command:

Only applies pending migrations
Does not generate new migrations
Does not prompt for user input
Exits with error if migration fails

Migration Best Practices

Writing Safe Migrations

Make migrations reversible: Design changes that can be undone
Avoid data loss: Use ALTER instead of DROP when possible
Split risky migrations: Break large changes into smaller, safer steps
Test with production data: Use a copy of production data for testing

Handling Breaking Changes

For changes that might affect running applications:

Add before remove: First add new column/table, then remove old
Use feature flags: Deploy code that handles both states
Coordinate with deployments: Apply migration during maintenance window

Migration Naming

Use descriptive names that indicate the change:

add_user_email_verification
create_tournament_tables
remove_deprecated_status_field

Troubleshooting

Migration Failed

If a migration fails:

Check the error message in the output
Review the SQL in the migration file
Fix the issue in the schema or SQL
If the migration was partially applied:
npx prisma migrate resolve --rolled-back <migration-name>

Schema Drift

If the database schema doesn’t match expected state:

# Show what the database is missing compared with the schema
npx prisma migrate diff --from-schema-datasource prisma/schema.prisma --to-schema-datamodel prisma/schema.prisma

Fix drift with a new migration. prisma migrate reset drops all data; only use it on a disposable database.

Baseline an Existing Database

For databases with existing schema not managed by Prisma:

npx prisma migrate diff --from-empty --to-schema-datasource prisma/schema.prisma --script > prisma/migrations/0000_baseline/migration.sql
npx prisma migrate resolve --applied 0000_baseline

Command	Description
`npm run db:migrate`	Create and apply migrations in development (`prisma migrate dev`)
`npm run db:migrate:prod`	Apply migrations in production (`prisma migrate deploy`)
`npm run ci:migrations`	Test the migration cycle against a disposable Docker database
`npm run db:studio`	Open Prisma Studio GUI
`npm run test:migrations`	Test the apply/reset cycle (wipes the `DATABASE_URL` database)

npm run db:push is intentionally disabled and exits with an error.