GuideAdvanced
Database Migrations And Cicd
Learn to change a production database's schema and data without breaking it: the migration as a versioned, ordered, repeatable, and reversible change. It covers `ALTER TABLE` and the table-rebuild pattern, data migrations with backfills, the expand/contract pattern for zero downtime, and database CI/CD — applying migrations, running quality checks and tests in a pipeline, with quality gates and rollback. You evolve Reservo's database through a real series of migrations and come out knowing how to build and operate a production migration system.
- 64
- lessons
- 8
- modules
- English · Spanish
- available in
- Yes
- certificate
- Free
- access
Outcomes
What you'll be able to do
- Understand why migrations exist: schema drift between environments, and why a hand-run ALTER is neither versioned, reversible, nor auditable
- Design the anatomy of a migration: up and down, the versioned file, the `schema_migrations` table, and an idempotent runner that applies only what's pending
- Write schema migrations with ALTER TABLE: adding columns, tables, and indexes, and the table-rebuild pattern that sqlite's limitations require
- Migrate existing data with idempotent, batched backfills, and decide when to separate a schema migration from a data migration
- Apply the expand/contract pattern for zero-downtime migrations, test on a copy before production, and mitigate what's irreversible
- Run migrations in CI: applying them from scratch and incrementally, and detecting schema drift before merging
- Build the database's CI/CD pipeline: quality gates, rollback on failure, and per-environment configuration (dev, staging, prod)
- Deliver Reservo's complete migration system: ordered migrations, a Python runner, and a CI pipeline in YAML
Before you start
What you need to bring
It's for you if...
- Backend devs who change the schema of a live database and need to do it without breaking production
- Teams suffering schema drift across environments from unversioned manual ALTERs
- Anyone setting up or maintaining a CI/CD pipeline that includes the database
- Devs who want to understand what tools like Alembic, Flyway, or Liquibase are doing under the hood
Requirements and materials
- Basic SQL: CREATE TABLE, ALTER TABLE, INSERT
- Python 3 installed (the `sqlite3` module ships with the standard library)
- A general sense of what a CI/CD pipeline is; no prior experience setting one up required
Content
The syllabus, module by module
Open any of them to see its lessons.
- Module 1 Introduction: Why Migrations and Schema Change
- A live database is not recreated from scratch
- Schema drift between environments
- The four failures of manual `ALTER`
- What a migration is and "schema as code"
- The minimal runner that applies a `.sql`
- Versioning migrations in the repo: `0001_init.sql`
- Mini-project: your first migration
- Module 2 — Anatomy of a migration: up/down and versioning
- The anatomy of a migration file and its name-version
- The up/down pair and rollback
- Why order and versioning must be deterministic
- The `schema_migrations` control table
- The idempotent runner that applies only what's pending
- `PRAGMA user_version`: sqlite's native alternative
- Mini-project: build the migration runner
- Module 4 — Data migrations and backfills
- Schema migration vs. data migration
- Backfilling a new column: `duration_min` with `julianday`
- Splitting a field: `name` → `first_name` / `last_name`
- Fixing legacy data in a migration
- The idempotent backfill
- Batched backfills for large tables
- Mini-project: backfilling a new column in Reservo
- Module 5 Introduction: Safe Migrations and Expand/Contract
- Why a destructive migration breaks in-flight code
- The expand/contract pattern and its four phases
- The case: splitting `name` without downtime, in phases
- Reversibility and the `down` of each phase (and why contract is the hard one)
- Testing the migration on a copy of the backup before prod
- The irreversible migration and how to mitigate it
- Mini-project: a zero-downtime column split
- Module 6 — Migrations in CI
- Why CI for migrations: the PR with a migration
- The "migrate clean from zero" test
- The incremental test: applying only the new migration
- The drift check: "up from empty == expected schema"
- Catching a broken migration: the failing test and the exit code
- The GitHub Actions YAML, step by step
- Mini-project: a CI check for Reservo's migrations
- Module 7 — The CI/CD pipeline and quality gates
- What a quality gate is and why the database needs one
- Reusing the quality checks as a gate: violations > 0 ⇒ fail
- The gate in green and in red: a buggy migration caught
- The pipeline as phases in a GitHub Actions YAML
- Rollback vs. fix-forward: what to do when the gate shuts the door
- The dev/staging/prod environments, config, and secrets
- Mini-project: a quality-gated pipeline for Reservo
- Module 8 — Capstone: Reservo's migration system
- The ordered migration set, from empty to the current schema
- The feature migrations: `notes` and `waitlist`
- The `duration_min` backfill: a schema + data migration
- The constraint rebuild, applying expand/contract
- The runner and the quality gate, integrated
- The `deploy.yml` pipeline that orchestrates everything
- Final project: Reservo's migration system
Common questions
What people usually ask
No limit. It's a free guide: come in whenever you like, as often as you like.
No. Modules run from easier to harder, but you can jump to the one you need. Progress is saved per lesson.
Whatever is needed is listed under “What you need to bring”, above. If nothing is listed there, you can start from zero.
In the Club's WhatsApp group, and every two weeks there's a live with an instructor where questions get worked through.
Yes. It's issued automatically once you finish every lesson, with a verifiable code you can share on LinkedIn.
Start whenever you like
What students say
These reviews are from enrolled students who completed at least 50% of the course. We moderate reviews only on content grounds (spam, offensive language, personal data), never for being critical or negative.
No approved reviews yet.
Be the first to share your experience!