> ## Content Index
> Fetch the complete content index at: https://stalin-pereira.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Automating Database Migrations in GitHub Actions
- URL: https://stalin-pereira.com/automating-database-migrations-in-github-actions/
- Published: 2025-12-10T10:36:00.000Z
- Updated: 2026-10-16T19:08:12.000Z
- Description: Automating migrations safely within the pipeline
- Author: Stalin Pereira
- Tags: DevOps

**The Scariest Button in DevOps**

Why database migrations keep engineers awake at night.

## The 'Live Renovation' Analogy

- **The Scenario** — Imagine trying to replace the foundation of a house while the family is eating dinner inside.
- **The Risk** — If you hit the wrong pipe, the house floods. You can't just 'Undo' a flooded basement.
- **The Reality** — This is a production database migration. One bad SQL command can corrupt years of data.

## The Bridge: Remove the Human

- **The Problem** — Humans make typos. Humans forget steps. Humans panic.
- **The Solution** — Codify the changes. Let a robot (GitHub Actions) execute them exactly the same way, every time.

## The Old Way: Cowboy Coding

- **The Method** — SSH into the production server. Paste SQL commands into the terminal.
- **The Feeling** — Sweaty palms. Praying that you didn't miss a semi-colon.
- **The Result** — No audit trail. No rollback plan. Pure chaos.

## The New Way: Migration Files

Database Schema as Code.

- **Versioning** — Every change is a file: `20231027_add_users.sql`. It lives in your git repo.
- **Determinism** — The order is fixed. The computer runs file 1, then file 2, then file 3.
- **Review** — Team members review the SQL in the Pull Request before it ever touches Prod.

## The Pipeline Sequence

The order of operations matters enormously.

- **Step 1: Backup** — Automated snapshot of the DB. If it fails, we need a time machine.
- **Step 2: Migrate** — GitHub Actions runs the schema change (Add Column).
- **Step 3: Deploy App** — Only AFTER the DB is ready, we deploy the new code that uses it.

## The Golden Rule: Non-Breaking Changes

Never break the old code.

- **The Danger** — If you rename a column, the *old* app (still running) crashes instantly.
- **The Fix** — Add the new column first. Copy data. Remove the old column weeks later.
- **Why?** — This allows 'Zero Downtime' deployments.

## Pattern: Expand and Contract

- **Phase 1: Expand** — Add `new_address` column. Update code to write to BOTH `old` and `new` columns.
- **Phase 2: Migrate** — Backfill data from old to new in the background.
- **Phase 3: Contract** — Once everyone uses the new column, delete the old one in a future deploy.

## Safety Net: Transactional DDL

- **The Concept** — Wrap the migration in a Transaction.
- **The Protection** — If step 5 of 10 fails, the database automatically undoes steps 1-4.
- **The Tooling** — Postgres supports this natively. MySQL is trickier (DDL is not transactional).

## Tools of the Trade

- **Prisma / TypeORM** — Great for Node.js. Generates SQL from your TypeScript models.
- **Flyway / Liquibase** — Java/Enterprise standard. Handles versioning and rollbacks robustly.
- **Atlas** — Modern tool for inspecting schema and catching errors in CI.

## The Business Impact

- **Reliability** — Automated migrations reduce deployment failures by over 80%.
- **Speed** — Developers ship features faster when they aren't scared of the database.
- **Compliance** — Auditors love seeing that no human has write-access to Production.

## Summary: Boring is Good

Automation turns terror into routine.

> Make your database deployments so boring you don't even watch them.

---

Do you automate your migrations or run them manually?