5 exercises — up/down migrations and rollback, schema version history tables, repeatable migrations, migration conflicts in teams, and baseline migrations for existing databases.
0 / 5 completed
1 / 5
A pull request description says: "This adds an up migration to add the email_verified column. The down migration drops it." What is the purpose of a down migration vs an up migration?
Up and down migrations vocabulary:
Up migration (also called "migrate" or "forward") The forward schema change — the SQL (or DSL) that transforms the database from version N to version N+1. Examples: ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT FALSE
Down migration (also called "rollback" or "revert") The reverse — transforms version N+1 back to version N. Example: ALTER TABLE users DROP COLUMN email_verified
When rollback is not straightforward: • Dropping a column that had data — data is gone; down migration cannot restore it • Renaming a table — the application code may need corresponding rollback too • Data migrations — transforming existing row values is hard to reverse exactly
Vocabulary: • migration file — a versioned script describing one schema change • rollback — the act of running down migrations to revert a deployment • idempotent migration — safe to run multiple times (uses IF NOT EXISTS / IF EXISTS)
2 / 5
A team uses Flyway. After a bad deployment, an engineer runs flyway info and sees the schema version history table. What does this table track, and what is it called in Flyway?
Schema version history table vocabulary:
Flyway: flyway_schema_history Created automatically on first migration. Columns include: • installed_rank — sequential order of execution • version — the version number (from the filename prefix, e.g., V2) • description — human-readable name • type — SQL / JDBC / REPEATABLE • script — filename • checksum — hash of the script content (used to detect tampering) • installed_by — DB user who ran the migration • execution_time — milliseconds • success — boolean; failed migrations are recorded but block further migrations
Liquibase: databasechangelog Similar table tracking each changeset by id, author, filename, dateExecuted, and MD5 hash.
Alembic: alembic_version Simpler table with just one column: the current revision hash.
Why the checksum matters: Flyway recalculates the checksum of each applied migration on every run. If a file is modified after being applied, Flyway throws an error — this prevents silent schema drift from editing historical migrations.
Vocabulary: • schema drift — when the actual DB schema diverges from what migrations describe • pending migration — a migration file that exists but has not been applied yet • applied migration — recorded in the history table; will not be re-run • out-of-order migration — a migration with a lower version number applied after a higher one (Flyway has an outOfOrder flag for this)
3 / 5
A Flyway config includes repeatable migrations — files named R__Create_views.sql. When does a repeatable migration rerun, and how does this differ from a versioned migration?
Repeatable migrations vocabulary:
Versioned migration (V prefix) Filename: V3__Add_index.sql • Runs exactly once when the version is first encountered • Never re-runs, even if the file changes • Changing the file after it has been applied causes a checksum error • Used for: schema changes (CREATE, ALTER, DROP)
Repeatable migration (R prefix) Filename: R__Create_reporting_views.sql • Runs every time its checksum changes • Applied after all pending versioned migrations in the current run • No version number — just a description • Idempotent by design: uses CREATE OR REPLACE VIEW, DROP PROCEDURE IF EXISTS, etc. • Used for: views, stored procedures, functions, triggers, seed/reference data
Liquibase equivalent: Changesets with runOnChange="true" attribute — re-executed when the MD5 hash of the changeset changes.
Alembic equivalent: No built-in concept; developers write custom scripts or use post-migrate hooks for repeatable objects.
Best practices: • Always make repeatable migrations idempotent (CREATE OR REPLACE, not CREATE) • Keep them focused on a single object type • Version the object's source of truth in a separate SQL file; the migration file references or copies it
Vocabulary: • idempotent — safe to run multiple times; same result each time • checksum — hash of the migration file content used to detect changes • migrate lifecycle — Flyway runs versioned (pending) → repeatable (changed) in one flyway migrate call
4 / 5
Two developers open PRs on the same day. Both add a file named V5__Add_column.sql to the migrations folder. The CI pipeline fails with a migration conflict. What is a migration conflict, and when does it occur?
Migration conflict vocabulary:
A migration conflict (version conflict) is one of the most common pain points in team development with Flyway/Liquibase. It occurs when two branches independently create a migration with the same version identifier.
How it happens: 1. Developer A is on branch feature/auth and creates V5__Add_auth_tokens.sql 2. Developer B is on branch feature/payments and creates V5__Add_payments_table.sql 3. Branch A merges first — V5 runs and is recorded in flyway_schema_history 4. Branch B tries to merge — CI runs flyway migrate and sees V5 already applied; the new V5 has a different checksum → error
Prevention strategies: • Timestamp-based versions — use V20240601123045__description.sql (timestamp down to seconds) instead of sequential integers • Centralised version reservation — a shared file or GitHub issue tracking the next available version • Feature branch migration renaming — rename on merge to the next available version
Alembic approach: Alembic uses content-based hashes (revision IDs like a1b2c3d4e5f6) instead of sequential numbers, which naturally avoids version conflicts. Multiple heads must be merged explicitly with alembic merge.
Liquibase approach: Uses author + id + filename as a compound identifier — less prone to conflicts but still possible if the same author reuses IDs.
Vocabulary: • migration head — the latest applied migration version; what the DB is "at" • multiple heads — Alembic term for when two branches both extend from the same revision • merge migration — Alembic's alembic merge creates a new revision that joins two branch heads
5 / 5
A team inherits a production database that was created manually — no migrations exist. They want to start using Flyway. The DBA runs flyway baseline. What does a baseline migration do?
Baseline migration vocabulary:
The problem: If flyway_schema_history does not exist and you run flyway migrate, Flyway will try to run all migration files from V1. But the database already has tables from V1, V2, V3 — so these migrations will fail (tables already exist).
What flyway baseline does: 1. Creates the flyway_schema_history table if it does not exist 2. Inserts a single record marking the current state as the baseline version (e.g., V1 or a specified version) 3. Sets the success flag to true for this baseline record 4. Does NOT execute any SQL against the schema
After baselining, Flyway will only run migrations with a version higher than the baseline. If you baseline at V5, migrations V1–V5 are considered "already applied" and V6+ will run normally.
Companion approach — schema script: For new environments (dev, CI), you typically create a full-schema SQL file (e.g., V1__Baseline_schema.sql) that creates the entire schema from scratch. New environments run this file; existing production environments are baselined to skip it.
Liquibase equivalent: liquibase changelogSync — marks all changesets in the changelog as executed without running them liquibase changelogSyncToTag — marks up to a specific tag
Alembic equivalent: alembic stamp <revision> — marks the database as being at a given revision without running migrations
Vocabulary: • baseline version — the version number used as the starting point • stamp — Alembic term for marking the DB at a revision • changelogSync — Liquibase term for the same concept as Flyway baseline • schema snapshot — a full dump of the current schema, used as a reference or starting migration