Database Schema Diff

A schema diff (schema comparison) identifies the structural differences between two database schemas — added tables, dropped columns, changed data types, new indexes, modified constraints, etc. It's used to generate migration scripts when evolving a database schema from one version to another.

A database migration is a script that transforms a database schema from one state to another. Migrations are version-controlled and run in order. Tools like Flyway, Liquibase, Prisma Migrate, and Alembic manage migration files and track which have been applied to each environment.

Destructive migrations permanently remove data (DROP TABLE, DROP COLUMN, TRUNCATE). Non-destructive migrations only add or modify structure without removing existing data (ADD COLUMN, CREATE INDEX, ADD CONSTRAINT). In production, always prefer non-destructive migrations and phase out deprecated columns after all code is updated.

Zero-downtime migrations allow schema changes while the application is running. The pattern: 1) Add new column (nullable), 2) Deploy code that writes to both old and new columns, 3) Backfill existing rows, 4) Add NOT NULL constraint, 5) Deploy code that only uses the new column, 6) Drop old column. Never add NOT NULL without a default on large tables — it will lock the table.