Database migrations

SnailyCAD uses Prisma Migrate to manage database schema changes. All migrations are version-controlled and applied automatically during deployment.

Migration Files

Migrations are stored in apps/api/prisma/migrations/ with the following structure:

apps/api/prisma/migrations/
├── 20231201000000_initial_schema/
│   └── migration.sql
├── 20231215000000_add_custom_fields/
│   └── migration.sql
├── 20240101000000_update_permissions/
│   └── migration.sql
└── migration_lock.toml

Each migration folder contains:

Timestamp prefix (YYYYMMDDHHMMSS)
Descriptive name
migration.sql file with SQL statements

Development Workflow

Creating a New Migration

When you modify the Prisma schema (apps/api/prisma/schema.prisma), create a migration:

Update the schema

Modify apps/api/prisma/schema.prisma with your changes:

model Citizen {
  id          String   @id @default(cuid())
  name        String
  surname     String
  // Add new field
  middleName  String?
  // ...
}

Create the migration

Run the migration command:

pnpm --filter "@snailycad/api" prisma migrate dev --name add_middle_name

This will:

Generate SQL for the schema changes
Create a new migration file
Apply the migration to your local database
Regenerate the Prisma client

Review the migration

Check the generated SQL in apps/api/prisma/migrations/<timestamp>_add_middle_name/migration.sql:

-- AlterTable
ALTER TABLE "Citizen" ADD COLUMN "middleName" TEXT;

Commit the migration

Add the migration files to version control:

git add apps/api/prisma/migrations/
git add apps/api/prisma/schema.prisma
git commit -m "feat: add middle name field to citizens"

Applying Migrations

In development, migrations are applied automatically when you run:

pnpm --filter "@snailycad/api" prisma migrate dev

In production (Docker or standalone), migrations are applied during startup:

pnpm --filter "@snailycad/api" prisma migrate deploy

The prisma migrate deploy command is automatically run in the Docker container startup (see apps/api/package.json:7).

Production Migrations

Docker Deployment

Migrations are applied automatically when the API container starts:

// apps/api/package.json
{
  "scripts": {
    "start": "pnpm prisma migrate deploy && pnpm prisma generate && node --inspect dist/main.js"
  }
}

The startup sequence:

Apply pending migrations (prisma migrate deploy)
Generate Prisma client (prisma generate)
Start the API server

Standalone Deployment

For standalone installations, migrations are applied during the update process:

# From package.json
pnpm run full-start

This runs:

git pull && pnpm install && pnpm run build && pnpm run start

The start script includes prisma migrate deploy.

Common Migration Tasks

Adding a Field

model Citizen {
  id          String   @id @default(cuid())
  name        String
  // Add optional field
  nickname    String?
}

pnpm --filter "@snailycad/api" prisma migrate dev --name add_citizen_nickname

Making a Field Required

When making an optional field required, ensure existing records have values or provide a default.

model Citizen {
  id          String   @id @default(cuid())
  name        String
  // Change from String? to String with default
  nickname    String   @default("")
}

Adding a Relation

model Citizen {
  id          String   @id @default(cuid())
  name        String
  // Add relation
  notes       Note[]
}

model Note {
  id          String   @id @default(cuid())
  text        String
  citizen     Citizen  @relation(fields: [citizenId], references: [id])
  citizenId   String
}

Creating an Enum

enum RecordType {
  WRITTEN_WARNING
  TICKET
  ARREST_REPORT
  WARRANT
}

model Record {
  id          String      @id @default(cuid())
  type        RecordType
  // ...
}

Migration Best Practices

Use descriptive migration names

Good migration names:

add_citizen_middle_name
create_custom_fields_table
update_officer_callsign_template

Bad migration names:

update
fix
changes

Test migrations locally first

Always test migrations in your local development environment before deploying:

# Reset database and reapply all migrations
pnpm --filter "@snailycad/api" prisma migrate reset

# Verify the schema
pnpm --filter "@snailycad/api" prisma validate

Backup before production migrations

Always backup your database before applying migrations in production:

# Docker backup
docker exec snaily-cad-postgres pg_dump -U postgres snaily-cad-v4 > backup.sql

# Standalone backup
pg_dump -U postgres -d snaily-cad-v4 > backup.sql

See Backup & Restore for more details.

Don't modify existing migrations

Never edit migration files that have been applied to production. Create a new migration instead:

# Don't edit: migrations/20231201_add_field/migration.sql
# Instead create: migrations/20231215_update_field/migration.sql

Handle data migrations carefully

When migrations require data transformation, consider:

Add new field as optional
Migrate data in application code
Make field required in a later migration

Or use Prisma’s $executeRaw for complex data migrations.

Troubleshooting

Migration Failed

If a migration fails during migrate deploy:

# Check migration status
pnpm --filter "@snailycad/api" prisma migrate status

# Resolve the issue in the database manually
# Then mark the migration as applied
pnpm --filter "@snailycad/api" prisma migrate resolve --applied <migration_name>

Schema Out of Sync

If the database schema doesn’t match the Prisma schema:

# Check for drift
pnpm --filter "@snailycad/api" prisma migrate status

# Reset database (development only!)
pnpm --filter "@snailycad/api" prisma migrate reset

Migration Lock

If you see “Another migration is already running”:

Check if another process is running migrations
If stuck, remove the lock manually:

-- Connect to database
psql -U postgres -d snaily-cad-v4

-- Clear migration lock
DELETE FROM _prisma_migrations WHERE migration_name = '<stuck_migration>';

Only clear migration locks if you’re certain no other process is running migrations.

Rolling Back Migrations

Prisma Migrate doesn’t support automatic rollbacks. To rollback:

Restore database from backup
Or manually revert changes:

# Find the migration SQL
cat apps/api/prisma/migrations/<timestamp>_migration/migration.sql

# Write and execute reverse SQL
psql -U postgres -d snaily-cad-v4 -c "ALTER TABLE Citizen DROP COLUMN middleName;"

Migration Commands Reference

Command	Description
`prisma migrate dev`	Create and apply migration in development
`prisma migrate deploy`	Apply pending migrations in production
`prisma migrate status`	Check migration status
`prisma migrate reset`	Reset database and reapply all migrations
`prisma migrate resolve --applied`	Mark migration as applied
`prisma migrate resolve --rolled-back`	Mark migration as rolled back
`prisma generate`	Regenerate Prisma Client
`prisma validate`	Validate schema syntax

Advanced Topics

Custom Migration SQL

You can add custom SQL to generated migrations:

-- AlterTable (generated)
ALTER TABLE "Citizen" ADD COLUMN "nickname" TEXT;

-- Custom data migration
UPDATE "Citizen" SET "nickname" = "name" WHERE "nickname" IS NULL;

Baseline Existing Database

If you’re adopting Prisma Migrate on an existing database:

# Create baseline migration without applying
pnpm --filter "@snailycad/api" prisma migrate diff \
  --from-empty \
  --to-schema-datamodel prisma/schema.prisma \
  --script > prisma/migrations/0_baseline/migration.sql

# Mark as applied
pnpm --filter "@snailycad/api" prisma migrate resolve --applied 0_baseline

Database Schema

Explore the database structure

Backup & Restore

Database backup procedures

Troubleshooting

Common deployment issues

Prisma Docs

Official Prisma Migrate docs

Development

API Reference

Database

Database migrations

Migration Files

Development Workflow

Creating a New Migration

Applying Migrations

Production Migrations

Docker Deployment

Standalone Deployment

Common Migration Tasks

Adding a Field

Making a Field Required

Adding a Relation

Creating an Enum

Migration Best Practices

Troubleshooting

Migration Failed

Schema Out of Sync

Migration Lock

Rolling Back Migrations

Migration Commands Reference

Advanced Topics

Custom Migration SQL

Baseline Existing Database

Database Schema

Backup & Restore

Troubleshooting

Prisma Docs

Build docs developers (and LLMs) love

Development

API Reference

Database

​Migration Files

​Development Workflow

​Creating a New Migration

​Applying Migrations

​Production Migrations

​Docker Deployment

​Standalone Deployment

​Common Migration Tasks

​Adding a Field

​Making a Field Required

​Adding a Relation

​Creating an Enum

​Migration Best Practices

​Troubleshooting

​Migration Failed

​Schema Out of Sync

​Migration Lock

​Rolling Back Migrations

​Migration Commands Reference

​Advanced Topics

​Custom Migration SQL

​Baseline Existing Database

​Related Documentation

Database Schema

Backup & Restore

Troubleshooting

Prisma Docs

Build docs developers (and LLMs) love

Migration Files

Development Workflow

Creating a New Migration

Applying Migrations

Production Migrations

Docker Deployment

Standalone Deployment

Common Migration Tasks

Adding a Field

Making a Field Required

Adding a Relation

Creating an Enum

Migration Best Practices

Troubleshooting

Migration Failed

Schema Out of Sync

Migration Lock

Rolling Back Migrations

Migration Commands Reference

Advanced Topics

Custom Migration SQL

Baseline Existing Database

Related Documentation