Treating database changes like application code — with version control, automated testing, and CI/CD pipelines — is the single biggest improvement most teams can make to database reliability.
The Problem with Ad-Hoc Migrations
- No record of what changed or when
- Cannot reproduce schema in a new environment
- No rollback procedure when something goes wrong
- Dev/staging/production schema drift accumulates over time
Flyway: SQL-First Migration Tool
text
migrations/
V1__create_users.sql
V2__add_email_index.sql
V3__add_orders_table.sql
R__create_reporting_views.sql (repeatable)
bash
# Run pending migrations
flyway -url=jdbc:postgresql://localhost/mydb \
-user=postgres -password=secret migrate
# Check migration status
flyway info
# Validate applied migrations match files
flyway validateGitHub Actions CI Pipeline
yaml
name: Database CI
on: [pull_request]
jobs:
migrate-test:
runs-on: ubuntu-latest
services:
postgres:
image: postgres:16
env:
POSTGRES_PASSWORD: test
options: --health-cmd pg_isready
steps:
- uses: actions/checkout@v4
- name: Run migrations
run: |
flyway -url=jdbc:postgresql://localhost/postgres \
-user=postgres -password=test migrate
- name: Run schema tests
run: pytest tests/test_schema.pySchema Tests with pytest
python
import psycopg2, pytest
@pytest.fixture
def conn():
c = psycopg2.connect('postgresql://postgres:test@localhost/postgres')
yield c
c.close()
def test_orders_table_exists(conn):
cur = conn.cursor()
cur.execute("SELECT 1 FROM information_schema.tables WHERE table_name = 'orders'")
assert cur.fetchone() is not None
def test_orders_has_user_id_index(conn):
cur = conn.cursor()
cur.execute("SELECT 1 FROM pg_indexes WHERE tablename='orders' AND indexname='idx_orders_user_id'")
assert cur.fetchone() is not NoneBlue-Green Deployment Pattern
text
1. Deploy new schema version to 'green' database
2. Run migration + tests against green
3. Switch load balancer from blue to green
4. Blue becomes new staging for next deployKey Takeaways
- Every schema change must be a versioned migration file — no ad-hoc SQL in production
- Run migrations in CI against a fresh database on every pull request
- Write schema tests that verify expected tables, indexes, and constraints exist after migration
- Always write a rollback migration alongside every forward migration
JusDB Can Help
Database CI/CD is often the missing piece in engineering teams' deployment pipelines. JusDB can implement a complete database change management process for your team.