Skip to main content

Migration Guide

How to change the Prisma schema safely, both locally and in production.

Convention​

All migrations under prisma/migrations/<timestamp>_<name>/migration.sql are idempotent (IF NOT EXISTS, ADD COLUMN IF NOT EXISTS) so re-runs are safe. We do NOT use prisma migrate deploy against production — too risky for our scale + the Prisma Cloud is overkill. We write SQL by hand.

Workflow​

1. Edit prisma/schema.prisma​

Add the field, model, or relation.

2. Regenerate the client​

npx prisma generate

This updates the TypeScript types so the rest of the codebase compiles.

3. Write the migration SQL by hand​

Create a directory + file:

mkdir -p prisma/migrations/$(date +%Y%m%d)_<name>
$EDITOR prisma/migrations/$(date +%Y%m%d)_<name>/migration.sql

Use idempotent statements:

-- Good
ALTER TABLE "StoreOrder" ADD COLUMN IF NOT EXISTS "cancellationReason" TEXT;
CREATE INDEX IF NOT EXISTS "StoreOrder_status_idx" ON "StoreOrder"("status");
CREATE TABLE IF NOT EXISTS "EmailLog" ( ... );

-- Bad (non-idempotent)
ALTER TABLE "StoreOrder" ADD COLUMN "cancellationReason" TEXT;
CREATE INDEX "StoreOrder_status_idx" ON "StoreOrder"("status");

Idempotent migrations let you re-run safely if a deploy gets interrupted, and let you apply the same SQL across local + staging + prod.

4. Apply locally​

PGPASSWORD=<local-password> psql -h localhost -U ka26user -d ka26 -f prisma/migrations/<dir>/migration.sql

5. Test the change​

  • Run unit tests
  • Spot-check via Prisma Studio
  • Run any tests that touch the changed tables

6. Apply to production​

PGPASSWORD=Ka26Mkt2026 psql -h 34.123.40.64 -U ka26user -d ka26 -f prisma/migrations/<dir>/migration.sql

Verify:

PGPASSWORD=Ka26Mkt2026 psql -h 34.123.40.64 -U ka26user -d ka26 -c '\d "StoreOrder"'

7. Deploy code​

Push the schema + code changes via normal CI/CD. The deploy contains the new types; the DB already has the new columns from step 6.

Order matters: DB before code​

Always apply the migration BEFORE deploying code that reads from new columns. The reverse causes a brief window where the code expects fields that don't exist yet.

For column ADDITIONS this is forgiving — the old code just ignores the new column. For RENAMES or REMOVALS, you need a 2-deploy dance:

  1. Deploy code that reads BOTH old + new column (writes new)
  2. Backfill old → new in DB
  3. Deploy code that reads new only
  4. Drop old column

Examples (today's migrations)​

MigrationWhatPattern
20260417_add_email_log/migration.sqlNew EmailLog tableCREATE TABLE + indexes
20260417_doctor_password_reset/migration.sqlAdd resetToken + resetTokenExp to DoctorALTER TABLE ... ADD COLUMN IF NOT EXISTS
20260417_store_order_cancellation_reason/migration.sqlAdd cancellationReason to StoreOrderALTER TABLE ... ADD COLUMN IF NOT EXISTS

Rollback strategy​

Postgres doesn't have transactional DDL across statements + Cloud SQL doesn't expose a "previous state" snapshot beyond backups.

For ADDITIONS — there's nothing to rollback (the new column is just unused).

For DROPS or RENAMES — rollback means restoring from backup OR running an inverse migration (which means having written one upfront — recommended for any destructive migration).

Tests for migrations​

Add a test that asserts the migration SQL exists + has the expected statements:

it("StoreOrder has cancellationReason field", () => {
  const schema = read("prisma/schema.prisma");
  const block = schema.match(/model\s+StoreOrder\s*\{[\s\S]*?^\}/m)?.[0] || "";
  expect(block).toMatch(/cancellationReason\s+String\?/);
});

it("migration SQL exists", () => {
  const rel = "prisma/migrations/20260417_store_order_cancellation_reason/migration.sql";
  expect(fs.existsSync(path.join(ROOT, rel))).toBe(true);
  expect(read(rel)).toMatch(/ADD COLUMN\s+IF NOT EXISTS\s+"cancellationReason"/);
});

This way if someone reverts the migration accidentally, the test catches it.