Schema Migration and Versioning Playground
Practice the database changes real teams make on live systems, using PostgreSQL and Flyway, a tool that applies numbered migration scripts in order. Starting from a simple online shop schema with data, you'll write a series of migrations: adding a column and filling it from existing data, splitting a table into two, and renaming a column, each one done so the running application never breaks, using the expand and contract approach (add the new thing, move the data, switch readers over, then remove the old thing). After every migration, integrity checks prove no data was lost. You'll learn versioned migrations, backward-compatible changes, backfilling large tables in batches, avoiding long locks, and planning rollbacks.
- Estimate
- ~13h
- Steps
- 7
- Completed by
- 0
- Proposed by
- codeseed.app
PostgreSQL · pgAdmin · Flyway
Project roadmap
- 01
Create the baseline
~2hSet up Flyway (the command-line tool or Docker image) with PostgreSQL. Write V1__baseline.sql with customers (including a single address text column), products, orders and order_items, and V2__seed.sql that creates realistic data with generate_series (for example 100,000 orders). Write a README section explaining the naming rules and that applied migrations are never edited. Done when: flyway migrate creates and fills the database, and flyway info shows both versions applied.
- 02
Write integrity checks
~1.5hWrite checks.sql with queries that must hold after every migration: row counts per table (recorded before and compared after), the total of all order amounts unchanged, no order items without an order, and no nulls where they're not allowed. Make it raise an error when a check fails. Done when: checks.sql passes on the baseline.
- 03
Add a column and backfill it
~2hWrite a migration adding orders.total_cents as a nullable column (fast, without rewriting the table), then fill it from the order items in batches of 5,000 rows, so no long lock blocks the application. Finally, add a NOT NULL constraint using a CHECK constraint added as NOT VALID and validated separately. Done when: every order has a correct total, checks.sql passes, and each batch runs quickly.
- 04
Split a table
~2.5hMove addresses out of customers into a new addresses table (street, city, postal code and country, parsed from the text), linked by customer. Do it in expand and contract steps: create the table and copy the data, keep both in sync with a trigger while the application moves over, and only in a later migration drop the old column. Done when: every customer has their address in the new table, and checks.sql passes after each step.
- 05
Rename a column compatibly
~2hRename products.name to products.title without breaking old code: add the new column, fill it, keep the two in sync with a trigger, and create a view with the old column name for old readers. Document when it's safe to remove the old name in a final migration. Done when: queries using either name work during the transition.
- 06
Plan rollbacks
~1.5hFlyway's community edition only migrates forward, so for each migration write a matching rollback script in a rollback/ folder (or a forward migration that undoes it) and test each one on a copy of the database, then run checks.sql. Note which changes can't be fully undone (for example after dropping a column) and why. Done when: every rollback is tested and documented.
- 07
Run it like a pipeline
~1.5hWrite a script that starts a fresh PostgreSQL in Docker, runs all migrations one by one, and runs checks.sql after each, stopping at the first failure. Add flyway validate to catch edited migrations. Done when: the script completes on a fresh database, and editing an old migration makes validate fail.
Ready to build this?
Get a GitHub repo and start building. Your AI reviewer checks each step as you go.
Tech stack