Database CI/CD

🏗️ Database CI/CD: Keeping Your Data Safe While Moving Fast

The Moving House Analogy 🏠

Imagine you’re moving to a new house. You can’t just throw everything in boxes randomly! You need to:

• Pack carefully (so nothing breaks)
• Label boxes (so you know what’s inside)
• Move things in order (furniture before decorations)

Database CI/CD works the same way! When your app grows and changes, your database needs to change too. But we must do it carefully, so we don’t lose any precious data.

🎯 What We’ll Learn

1. Database Migrations - Moving your database from one version to another
2. Schema Versioning - Keeping track of all changes
3. Backward Compatible Changes - Making changes without breaking things

📦 Part 1: Database Migrations

What is a Migration?

Think of a migration like instructions for packing and unpacking a box.

When you write a migration, you’re saying:

“Here’s how to change the database, and here’s how to undo it if needed.”

Real Example: Adding a New Column

Let’s say your app stores users with just name and email. Now you want to add phone_number.

-- Migration: Add phone_number
-- UP (go forward)
ALTER TABLE users
ADD COLUMN phone_number
VARCHAR(20);

-- DOWN (go backward)
ALTER TABLE users
DROP COLUMN phone_number;

UP = Move forward (add the change) DOWN = Move backward (undo the change)

Why Do We Need Migrations?

graph TD
    A["App Version 1"] --> B["Database v1"]
    C["App Version 2"] --> D["Database v2"]
    B --> |Migration| D
    D --> |Rollback| B

• ✅ Track changes - Know exactly what changed and when
• ✅ Team work - Everyone applies the same changes
• ✅ Safety net - Can undo mistakes
• ✅ Automation - CI/CD can run migrations automatically

Migration Best Practices

🏷️ Part 2: Schema Versioning

The Recipe Book 📚

Imagine you have a recipe book. Every time you improve a recipe, you:

1. Write down what changed
2. Number the new version
3. Keep the old version (just in case!)

Schema versioning is your database’s recipe book.

How It Works

Each migration gets a unique version number or timestamp:

migrations/
├── 001_create_users.sql
├── 002_add_email_to_users.sql
├── 003_create_orders.sql
└── 004_add_phone_to_users.sql

The database remembers which migrations it has run:

-- Schema versions table
CREATE TABLE schema_versions (
  version VARCHAR(50),
  applied_at TIMESTAMP
);

graph TD
    A["New Migration Created"] --> B["Version Number Assigned"]
    B --> C["Migration Runs"]
    C --> D["Version Recorded"]
    D --> E["Database Updated ✅"]

Version Tracking in Action

When you deploy:

1. Check - What version is the database at?
2. Compare - What migrations haven’t run yet?
3. Run - Apply missing migrations in order
4. Record - Mark migrations as complete

Example Flow:

Database: Has v001, v002, v003
Code: Has v001, v002, v003, v004
CI/CD: "Aha! Need to run v004!"

🔄 Part 3: Backward Compatible Changes

The LEGO Principle 🧱

Imagine you’re building with LEGO. You want to add new pieces without breaking what’s already built.

Backward compatible means:

Old code still works with new database New code works with new database too!

Safe vs. Dangerous Changes

✅ SAFE Changes (Backward Compatible)

⚠️ DANGEROUS Changes (Not Backward Compatible)

The Expand-Contract Pattern 🎭

For risky changes, use two phases:

graph TD
    A["Phase 1: EXPAND"] --> B["Add new column"]
    B --> C["Update code to use both"]
    C --> D["Deploy new code"]
    D --> E["Phase 2: CONTRACT"]
    E --> F["Remove old column"]

Example: Renaming a Column

Wrong way (breaks things):

-- DON'T DO THIS
ALTER TABLE users
RENAME COLUMN name TO full_name;
-- 💥 Old code crashes!

Right way (expand-contract):

Step 1: Expand

-- Add new column
ALTER TABLE users
ADD COLUMN full_name VARCHAR(100);

-- Copy data
UPDATE users
SET full_name = name;

Step 2: Update Code

• Deploy code that reads/writes BOTH columns
• Wait until all old code is gone

Step 3: Contract

-- Now safe to remove old column
ALTER TABLE users
DROP COLUMN name;

The Golden Rules 📜

graph TD
    A["Making a Change?"] --> B{Will old code break?}
    B --> |Yes| C["Use Expand-Contract"]
    B --> |No| D["Safe to Deploy! ✅"]
    C --> E["Add New Thing First"]
    E --> F["Update All Code"]
    F --> G["Remove Old Thing"]

1. Add before remove - New things first, delete later
2. Make nullable - New columns shouldn’t require values immediately
3. Deploy code first - Code should handle new schema before migration runs
4. Test rollback - Make sure you can undo!

🚀 Putting It All Together: Database CI/CD Pipeline

Here’s how it works in a real deployment:

graph TD
    A["Developer Creates Migration"] --> B["Commit to Git"]
    B --> C["CI Pipeline Starts"]
    C --> D["Run Tests with Migration"]
    D --> E{Tests Pass?}
    E --> |No| F["Fix & Retry"]
    E --> |Yes| G["Deploy to Staging"]
    G --> H["Run Migration on Staging"]
    H --> I{Staging OK?}
    I --> |No| J["Rollback & Fix"]
    I --> |Yes| K["Deploy to Production"]
    K --> L["Run Migration on Production"]
    L --> M["Monitor & Verify ✅"]

Quick Summary

🎉 You Did It!

You now understand:

• ✅ How to write and run database migrations
• ✅ Why schema versioning keeps you safe
• ✅ How to make changes without breaking things

Remember: Treat your database like a friend you’re moving houses with. Be careful, communicate clearly, and always have a plan B!

💡 Pro Tip: Always test migrations on a copy of production data before the real thing. Better to break a copy than lose real user data!