Skip to main content

CI/CD Database Migrations

Schema changes that live outside version control are schema changes waiting to cause an incident. TalkingSchema generates migration SQL — in Flyway, Liquibase, or raw format — that is designed to be committed alongside application code, reviewed in pull requests, and executed by your CI/CD pipeline automatically. The result is a schema-as-code workflow where every structural change is traceable, auditable, and reversible.

The Schema-as-Code Pipeline​

Developer (TalkingSchema)          Version Control          CI/CD Pipeline
─────────────────────────          ───────────────          ──────────────
Design schema change           →   PR with migration SQL   →   Validate (dry-run)
Review diff + checklist        →   Code review             →   Apply (staging)
Export migration file          →   Merge to main           →   Apply (production)
Document rollback plan         →   Tagged release          →   Verify (health check)

TalkingSchema owns the leftmost column. Every other stage uses standard tooling your team already has.

Generating Flyway Migrations​

Flyway reads versioned SQL files from a db/migration/ directory. File names follow: V{version}__{description}.sql

Generate this schema change as a Flyway migration.

File: V012__add_sustainability_scoring.sql
Changes: Add carbon_offset_credits table, add emissions_unit column
to shipments. Include rollback instructions in a comment block.

Generated output:

-- ============================================================
-- Flyway Migration: V012__add_sustainability_scoring.sql
-- Description : Add carbon offset tracking and emissions unit
-- Author      : TalkingSchema
-- Date        : 2026-03-13
-- Rollback    : See rollback section at end of file
-- ============================================================

BEGIN;

-- 1. Add carbon_offset_credits table
CREATE TABLE carbon_offset_credits (
    credit_id       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    supplier_id     UUID NOT NULL REFERENCES suppliers(supplier_id),
    credit_date     DATE NOT NULL,
    volume_tco2e    DECIMAL(12, 4) NOT NULL CHECK (volume_tco2e > 0),
    registry        VARCHAR(100) NOT NULL,
    certificate_ref VARCHAR(100) UNIQUE,
    created_at      TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_carbon_credits_supplier ON carbon_offset_credits(supplier_id);

-- 2. Add emissions unit column to shipments (nullable for safety)
ALTER TABLE shipments
  ADD COLUMN emissions_unit VARCHAR(20) DEFAULT 'kg_co2';

COMMIT;

-- ============================================================
-- ROLLBACK (run manually if needed — Flyway Community does not
-- auto-rollback; use Flyway Teams for undo scripts)
-- ============================================================
-- BEGIN;
-- ALTER TABLE shipments DROP COLUMN IF EXISTS emissions_unit;
-- DROP TABLE IF EXISTS carbon_offset_credits;
-- COMMIT;

Generating Liquibase ChangeSets​

Liquibase uses a changelog file containing changeSet entries. Each changeSet has an id, author, and a set of change operations, plus an optional rollback element.

Generate this migration as a Liquibase XML changeSet.
Use sequential changeset IDs starting from 012.
Include <rollback> elements. Author: talkingschema.

Generated output:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.x.xsd">

    <changeSet id="012" author="talkingschema"
               comment="Add carbon_offset_credits table">
        <createTable tableName="carbon_offset_credits">
            <column name="credit_id" type="UUID">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="supplier_id" type="UUID">
                <constraints nullable="false"
                    foreignKeyName="fk_credits_supplier"
                    references="suppliers(supplier_id)"/>
            </column>
            <column name="credit_date" type="DATE">
                <constraints nullable="false"/>
            </column>
            <column name="volume_tco2e" type="DECIMAL(12,4)">
                <constraints nullable="false"/>
            </column>
            <column name="registry" type="VARCHAR(100)">
                <constraints nullable="false"/>
            </column>
            <column name="certificate_ref" type="VARCHAR(100)">
                <constraints unique="true"/>
            </column>
            <column name="created_at" type="TIMESTAMPTZ"
                    defaultValueComputed="now()"/>
        </createTable>

        <createIndex tableName="carbon_offset_credits"
                     indexName="idx_carbon_credits_supplier">
            <column name="supplier_id"/>
        </createIndex>

        <rollback>
            <dropTable tableName="carbon_offset_credits"/>
        </rollback>
    </changeSet>

    <changeSet id="013" author="talkingschema"
               comment="Add emissions_unit column to shipments">
        <addColumn tableName="shipments">
            <column name="emissions_unit" type="VARCHAR(20)"
                    defaultValue="kg_co2"/>
        </addColumn>

        <rollback>
            <dropColumn tableName="shipments" columnName="emissions_unit"/>
        </rollback>
    </changeSet>

</databaseChangeLog>

GitHub Actions Integration Example​

A complete CI/CD workflow that applies TalkingSchema-generated Flyway migrations:

# .github/workflows/db-migrate.yml
name: Database Migration

on:
  push:
    branches: [main]
    paths:
      - "db/migration/**"

jobs:
  migrate:
    runs-on: ubuntu-latest
    environment: production

    steps:
      - uses: actions/checkout@v4

      - name: Run Flyway migrations
        uses: flyway/flyway-github-action@v10
        with:
          args: migrate
        env:
          FLYWAY_URL: ${{ secrets.DATABASE_URL }}
          FLYWAY_USER: ${{ secrets.DATABASE_USER }}
          FLYWAY_PASSWORD: ${{ secrets.DATABASE_PASSWORD }}
          FLYWAY_LOCATIONS: filesystem:./db/migration
          FLYWAY_BASELINE_ON_MIGRATE: true
          FLYWAY_VALIDATE_ON_MIGRATE: true

      - name: Verify schema health
        run: |
          psql ${{ secrets.DATABASE_URL }} -c "
            SELECT COUNT(*) FROM information_schema.tables
            WHERE table_schema = 'public';
          "

Frequently Asked Questions​

How should I name migration files for version control?​

Use a monotonically increasing version prefix: V001, V002, ... or a timestamp-based prefix: V20260313120000. Flyway uses the version to determine execution order. Never reuse a version number — Flyway checksums applied migrations and will error if a previously applied file is modified.

Can I preview what Flyway will apply before running it?​

Yes. Run flyway info to see which migrations are pending. Use flyway validate to confirm all applied migrations match their checksums. Generate a dry-run with flyway migrate -dryRunOutput=./dry-run.sql to preview the SQL without executing it.

What happens if a migration fails mid-execution?​

For transactional DDL databases (PostgreSQL), wrap migrations in BEGIN/COMMIT. If the transaction fails, the entire migration is rolled back automatically. For non-transactional DDL (MySQL, some ALTER operations), use Flyway's error handling callbacks to detect partial application and alert before the next deployment.

Should schema migrations and data migrations be in separate files?​

Yes — always. Schema migrations (DDL) and data migrations (DML) have different rollback strategies, different performance characteristics, and different failure modes. Keeping them in separate versioned files gives you the ability to replay, test, or roll back each independently.