Lesson 43 • Advanced
Schema Versioning, Migrations & CI/CD
Version your database schema with migration tools and integrate into CI/CD pipelines.
✅ What You'll Learn
- • Writing versioned UP and DOWN migrations
- • Safe migration patterns for zero-downtime deploys
- • Migration tracking and state management
- • CI/CD integration with Flyway and Liquibase
📦 Why Version Your Schema?
🎯 Real-World Analogy: Imagine building a house and making changes without blueprints. "Did we add the bathroom before or after the kitchen?" Migrations are your blueprint history — every change is numbered, documented, and reversible.
Without migrations, teams end up with: different schemas on dev/staging/production, no way to reproduce a database from scratch, and no audit trail of what changed and when.
⚠️ Common Mistake: Making schema changes directly in production with ad-hoc ALTER TABLE statements. Always use versioned migration files checked into Git.
Try It: Writing Migrations
Create versioned migration scripts for tables and indexes
-- Database migrations: versioned SQL scripts
-- Each migration has an UP (apply) and DOWN (rollback)
-- Migration V001: Create users table
-- File: V001__create_users.sql
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Migration V002: Add profile columns
-- File: V002__add_profile_columns.sql
ALTER TABLE users ADD COLUMN display_name VARCHAR(100);
ALTER TABLE users A
...Try It: Safe Rollbacks & Zero-Downtime
Write rollback scripts and use safe ALTER patterns
-- Rollback migrations: undo changes safely
-- Always write a DOWN migration for every UP
-- Rollback V003: Drop posts table
-- File: V003__rollback.sql
DROP INDEX IF EXISTS idx_posts_published;
DROP INDEX IF EXISTS idx_posts_author;
DROP TABLE IF EXISTS posts;
-- Rollback V002: Remove profile columns
-- File: V002__rollback.sql
ALTER TABLE users DROP COLUMN IF EXISTS bio;
ALTER TABLE users DROP COLUMN IF EXISTS avatar_url;
ALTER TABLE users DROP COLUMN IF EXISTS display_name;
-- Safe migrati
...Try It: Migration Tracking
Build a migration tracking table like Flyway uses internally
-- Migration tracking table (what Flyway/Liquibase create)
-- Tracks which migrations have been applied
CREATE TABLE schema_migrations (
version VARCHAR(50) PRIMARY KEY,
description VARCHAR(255),
script VARCHAR(255) NOT NULL,
checksum VARCHAR(64),
applied_at TIMESTAMPTZ DEFAULT NOW(),
applied_by VARCHAR(100) DEFAULT current_user,
execution_time_ms INT
);
-- Example entries after running migrations:
INSERT INTO schema_migrations VALUES
('V001', 'create users', 'V001_
...Try It: CI/CD for Databases
Integrate database migrations into your deployment pipeline
-- CI/CD Pipeline for Database Changes
-- Integrate migrations into your deployment workflow
-- Step 1: Validate SQL syntax (in CI)
-- pg_validate_sql < migration.sql
-- Step 2: Run migrations against test database
-- flyway -url=jdbc:postgresql://test-db/myapp migrate
-- Step 3: Run integration tests
-- pytest tests/integration/
-- Step 4: Deploy to production
-- flyway -url=jdbc:postgresql://prod-db/myapp migrate
-- Example: Flyway configuration (flyway.conf)
-- flyway.url=jdbc:postgresql
...📋 Quick Reference
| Tool | Language | Best For |
|---|---|---|
| Flyway | Java / SQL | Simple SQL-based migrations |
| Liquibase | XML / YAML / SQL | Complex multi-DB environments |
| Alembic | Python | SQLAlchemy projects |
| Knex | JavaScript | Node.js applications |
| dbmate | SQL | Lightweight, any language |
🎉 Lesson Complete!
You can now version and deploy database changes safely! Next, learn about OLTP vs OLAP database design patterns.
Sign up for free to track which lessons you've completed and get learning reminders.