Skip to main content

Liquibase Database Migrations

Overview​

Liquibase is a database version control tool that manages database schema changes in a reliable, version-controlled manner. All database migrations for the application are defined as YAML files in infra/db/changelogs/.

What is Liquibase?​

Liquibase tracks and applies database changes (called changesets) in a structured format, solving the database versioning problem:

  • Version Control: Track every database change in git
  • Reproducibility: Same migrations run identically across environments
  • Rollback Support: Undo changes if needed
  • Team Coordination: Prevent conflicting schema changes

App Migration Structure​

Directory Layout​

infra/db/
├── Dockerfile                    # Liquibase + PostgreSQL
├── changeset-master.yaml         # Main changelog reference
├── changeset-test.yaml          # Test data changelog
└── changelogs/
    ├── changelog-tb_m_employee.yaml
    ├── changelog-tb_m_system.yaml
    ├── changelog-tb_m_user.yaml
    ├── changelog-tb_r_user_session.yaml
    ├── changelog-tb_s_employee.yaml
    ├── changelog-ext_pg_stat_statements.yaml
    ├── structure/               # Schema structure changelogs
    └── rollback/               # Rollback changesets

Changelog Format​

Basic Structure​

databaseChangeLog:
  - changeSet:
      id: '1'
      author: 'dev-team'
      changes:
        - createTable:
            tableName: 'tb_m_employee'
            columns:
              - column:
                  name: 'id'
                  type: 'UUID'
                  constraints:
                    primaryKey: true
              - column:
                  name: 'name'
                  type: 'VARCHAR(255)'
                  constraints:
                    nullable: false
              - column:
                  name: 'email'
                  type: 'VARCHAR(255)'
                  constraints:
                    unique: true
                    nullable: false

Key Components​

ElementPurpose
idUnique identifier for changeset
authorWho made this change
changesList of database operations
rollbackUndo operations (optional)

Common Change Types​

Create Table​

- changeSet:
    id: 'create-users-table'
    author: 'dev-team'
    changes:
      - createTable:
          tableName: 'users'
          columns:
            - column:
                name: 'id'
                type: 'UUID'
                defaultValueComputed: 'gen_random_uuid()'
                constraints:
                  primaryKey: true
            - column:
                name: 'email'
                type: 'VARCHAR(255)'
                constraints:
                  unique: true
            - column:
                name: 'created_at'
                type: 'TIMESTAMP'
                defaultValueComputed: 'CURRENT_TIMESTAMP'

Add Column​

- changeSet:
    id: 'add-phone-to-users'
    author: 'dev-team'
    changes:
      - addColumn:
          tableName: 'users'
          columns:
            - column:
                name: 'phone'
                type: 'VARCHAR(20)'

Create Index​

- changeSet:
    id: 'create-email-index'
    author: 'dev-team'
    changes:
      - createIndex:
          indexName: 'idx_users_email'
          tableName: 'users'
          columns:
            - column:
                name: 'email'

Add Constraint​

- changeSet:
    id: 'add-fk-user-session'
    author: 'dev-team'
    changes:
      - addForeignKeyConstraint:
          constraintName: 'fk_user_session_user'
          baseTableName: 'user_session'
          baseColumnNames: 'user_id'
          referencedTableName: 'users'
          referencedColumnNames: 'id'

Data Migration (Insert)​

- changeSet:
    id: 'seed-systems'
    author: 'dev-team'
    changes:
      - insert:
          tableName: 'systems'
          columns:
            - column:
                name: 'id'
                value: 'sys-001'
            - column:
                name: 'name'
                value: 'Inventory Management'
            - column:
                name: 'status'
                value: 'active'

Rollback Features​

Include Rollback Logic​

- changeSet:
    id: 'add-status-column'
    author: 'dev-team'
    changes:
      - addColumn:
          tableName: 'users'
          columns:
            - column:
                name: 'status'
                type: 'VARCHAR(50)'
                defaultValue: 'active'
    rollback:
      - dropColumn:
          tableName: 'users'
          columnName: 'status'

Undo Last Changeset​

docker-compose exec postgres liquibase \
  --changeLogFile=changeset-master.yaml \
  rollback-one-update

Rollback to Specific Date​

docker-compose exec postgres liquibase \
  --changeLogFile=changeset-master.yaml \
  rollback-to-date \
  --date="2024-01-15"

Running Migrations​

Automatic (Docker Startup)​

Migrations run automatically when the PostgreSQL container starts:

docker-compose up postgres  # Runs all changesets automatically

Manual Migration​

# Apply pending changesets
docker-compose exec postgres liquibase \
  --changeLogFile=changeset-master.yaml \
  update

# Check migration status
docker-compose exec postgres liquibase \
  --changeLogFile=changeset-master.yaml \
  status

# List all applied changesets
docker-compose exec postgres liquibase \
  --changeLogFile=changeset-master.yaml \
  history

Changeset Naming Conventions​

Follow the application naming conventions for consistency:

changelog-tb_m_employee.yaml     # Master data tables
changelog-tb_r_user_session.yaml # Relationship/Junction tables
changelog-tb_s_employee.yaml     # Status/Snapshot tables
changelog-ext_pg_stat_statements.yaml  # Extensions

Prefixes:

  • tb_m_ - Master/Reference tables
  • tb_r_ - Relationship/Junction tables
  • tb_s_ - Status/Snapshot tables
  • ext_ - PostgreSQL extensions

Best Practices​

# ✅ GOOD: Logical, single change
- changeSet:
    id: 'create-employees-table'
    changes:
      - createTable:
          tableName: 'tb_m_employee'
          # ...

- changeSet:
    id: 'create-employees-index'
    changes:
      - createIndex:
          indexName: 'idx_employee_email'
          # ...

2. Use Descriptive IDs​

# ✅ GOOD: Clear intent
id: "2024-01-15-create-audit-log-table"

# ❌ AVOID: Vague
id: "1"

3. Always Include Rollback​

- changeSet:
    id: 'add-department-column'
    changes:
      - addColumn: # ...
    rollback:
      - dropColumn: # ...

4. Test Before Committing​

# Test on staging
docker-compose -f docker-compose.staging.yaml up postgres

# Verify changes
docker-compose exec postgres psql -U postgres -d qms

5. Document Why, Not What​

- changeSet:
    id: 'add-soft-delete-column'
    comment: 'Support soft deletes for audit trail compliance (GDPR)'
    # ...

Troubleshooting​

"Changeset already run" Error​

If you modify an already-applied changeset:

# Option 1: Update changeset ID to make it unique
id: "add-column-v2"

# Option 2: Force undo and reapply
docker-compose exec postgres liquibase \
  rollback-to-tag --tag=before-change

Migration Fails, Database Inconsistent​

# Check applied changesets
docker-compose exec postgres liquibase \
  --changeLogFile=changeset-master.yaml \
  status

# Mark changeset as resolved
docker-compose exec postgres liquibase \
  markChangeSetRan

Validate Changeset File​

docker-compose exec postgres liquibase \
  --changeLogFile=changeset-master.yaml \
  validate

Integration with Prisma​

The application uses Prisma ORM alongside Liquibase:

  1. Liquibase: Database schema version control
  2. Prisma: Type-safe database access in Node.js

Workflow​

# 1. Create Liquibase changeset
# infra/db/changelogs/changelog-new-feature.yaml

# 2. Update Prisma schema
# apps/api/prisma/schema.prisma

# 3. Generate Prisma Client
pnpm prisma generate

# 4. Apply migrations
docker-compose up postgres

Environment-Specific Migrations​

Development​

docker-compose -f docker-compose.yaml up postgres

Testing​

docker-compose -f docker-compose.test.yaml up postgres

Production​

# Use separate changeset file with production safeguards
docker-compose -f docker-compose.prod.yaml up postgres

Migration History​

Available in the PostgreSQL DATABASECHANGELOG table:

SELECT * FROM DATABASECHANGELOG ORDER BY DATEEXECUTED DESC;

Next Steps​