Schema Versioning
Tracking and managing the evolution of database schema over time through versioned migration files and metadata tables.
Description
Schema versioning is the practice of treating your database schema as a versioned artifact, just like application code. Every change to the schema -- adding a column, creating an index, modifying a constraint -- is captured in a numbered migration file that can be applied forward or rolled back. A metadata table in the database (often called schema_migrations or flyway_schema_history) records which versions have been applied, ensuring each migration runs exactly once and in the correct order.
Good schema versioning enables several critical workflows: deterministic environment creation (spin up a new database and apply all migrations to get the current schema), safe collaboration (multiple developers can create migrations concurrently, and conflicts are detected at merge time by duplicate version numbers), rollback capability (revert the last migration if a deployment goes wrong), and audit trail (the migration history tells you when and why every schema change was made).
Best practices include: use timestamp-based version prefixes (20240115143000) rather than sequential numbers to reduce merge conflicts, make every migration idempotent when possible (IF NOT EXISTS, IF EXISTS), keep migrations small and focused (one logical change per migration), never modify a migration after it has been applied to a shared environment, and test migrations in CI against a database loaded with production-representative data to catch issues like lock contention on large tables.
Prompt Snippet
Use timestamp-prefixed migration files (YYYYMMDDHHMMSS_description.sql) to prevent merge conflicts. Store both up and down migrations in separate files or sections. Track applied versions in a schema_migrations table with columns: version, applied_at, checksum. Compute a SHA-256 checksum of each migration file and validate it on startup to detect unauthorized modifications. Run migrations in CI against a production-schema clone (restored from the latest backup) to verify compatibility. Block deployments if any migration's down path has not been tested -- enforce this via a pre-deploy CI check.
Tags
Related Terms
Database Migrations
Version-controlled scripts that incrementally modify the database schema, enabling reproducible and reversible schema changes across environments.
Database Backup Strategies
Systematic approaches to creating, storing, and verifying database backups to protect against data loss from hardware failure, human error, or security incidents.
Multi-Tenant Database Design
Architectural patterns for storing multiple customers' data in shared database infrastructure while ensuring isolation, security, and performance fairness.