1194 words
6 minutes
From Manual to Automated: Complete Guide to Database Change Management with Liquibase and GitLab CI/CD
2025-05-21
IT
Liquibase
/
GitLab-CI-CD
/
Database Schema Migration
/
DevOps
/
Automation

Introduction#

Database schema changes are a necessary part of application development, but they can also be a significant source of errors and downtime if not managed properly. Manually applying schema changes is time-consuming, error-prone, and difficult to track. Automating this process is crucial for maintaining database integrity, ensuring consistent deployments, and accelerating development cycles.

This article will explore how to automate database schema change management using Liquibase and GitLab CI/CD. Liquibase is an open-source database schema change management tool that allows you to define database changes in a platform-agnostic way. GitLab CI/CD is a powerful continuous integration and continuous delivery platform that can be used to automate the application of these changes to your database environments.

Understanding Liquibase#

Liquibase uses changelogs, which are XML, YAML, JSON, or SQL files that define a series of changesets. Each changeset represents a single, atomic database modification. These changesets can include creating tables, adding columns, inserting data, and more.

Here’s a simple example of a Liquibase changelog in XML format:

<?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.1.xsd">

    <changeSet id="1" author="yourname">
        <createTable tableName="users">
            <column name="id" type="INT">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="username" type="VARCHAR(255)">
                <constraints nullable="false" unique="true"/>
            </column>
            <column name="email" type="VARCHAR(255)"/>
        </createTable>
    </changeSet>

    <changeSet id="2" author="yourname">
        <addColumn tableName="users">
            <column name="created_at" type="TIMESTAMP" defaultValueComputed="CURRENT_TIMESTAMP"/>
        </addColumn>
    </changeSet>

    <changeSet id="3" author="yourname" context="prod">
        <preConditions onFail="MARK_RAN">
            <not>
                <indexExists indexName="idx_users_email"/>
            </not>
        </preConditions>
        <createIndex indexName="idx_users_email" tableName="users">
            <column name="email"/>
        </createIndex>
        <rollback>
            <dropIndex indexName="idx_users_email" tableName="users"/>
        </rollback>
    </changeSet>
</databaseChangeLog>

In this example, the changelog defines three changesets:

  • Changeset 1: Creates a table named users with columns id, username, and email.
  • Changeset 2: Adds a column named created_at to the users table.
  • Changeset 3: Creates an index on the email column with preconditions and rollback logic, only applied in production context.

Liquibase tracks which changesets have been applied to a database by storing metadata in special tables (DATABASECHANGELOG and DATABASECHANGELOGLOCK). This allows Liquibase to apply only the changesets that haven’t been applied yet, ensuring that your database schema is always up-to-date.

Setting up GitLab CI/CD#

GitLab CI/CD uses a .gitlab-ci.yml file in the root of your repository to define the pipeline. This file specifies the stages, jobs, and scripts that should be executed as part of your CI/CD process.

Here’s an enhanced example of a .gitlab-ci.yml file for automating Liquibase database migrations across multiple environments:

stages:
  - validate
  - migrate-dev
  - migrate-staging
  - migrate-prod

variables:
  LIQUIBASE_CHANGELOG_FILE: "db/changelog.xml"

# Validate changelog syntax before applying
validate-changelog:
  stage: validate
  image: liquibase/liquibase:latest
  script:
    - liquibase validate --changelog-file=$LIQUIBASE_CHANGELOG_FILE
  except:
    - main

# Development environment migration
migrate-dev:
  stage: migrate-dev
  image: liquibase/liquibase:latest
  variables:
    LIQUIBASE_URL: "jdbc:postgresql://${DEV_DB_HOST}:${DEV_DB_PORT}/${DEV_DB_NAME}"
    LIQUIBASE_USERNAME: "${DEV_DB_USER}"
    LIQUIBASE_PASSWORD: "${DEV_DB_PASS}"
    LIQUIBASE_CONTEXTS: "dev"
  script:
    - echo "Migrating development database..."
    - liquibase status --changelog-file=$LIQUIBASE_CHANGELOG_FILE --url=$LIQUIBASE_URL --username=$LIQUIBASE_USERNAME --password=$LIQUIBASE_PASSWORD
    - liquibase update --changelog-file=$LIQUIBASE_CHANGELOG_FILE --url=$LIQUIBASE_URL --username=$LIQUIBASE_USERNAME --password=$LIQUIBASE_PASSWORD --contexts=$LIQUIBASE_CONTEXTS
  only:
    - develop
  when: on_success

# Staging environment migration
migrate-staging:
  stage: migrate-staging
  image: liquibase/liquibase:latest
  variables:
    LIQUIBASE_URL: "jdbc:postgresql://${STAGING_DB_HOST}:${STAGING_DB_PORT}/${STAGING_DB_NAME}"
    LIQUIBASE_USERNAME: "${STAGING_DB_USER}"
    LIQUIBASE_PASSWORD: "${STAGING_DB_PASS}"
    LIQUIBASE_CONTEXTS: "staging"
  script:
    - echo "Migrating staging database..."
    - liquibase status --changelog-file=$LIQUIBASE_CHANGELOG_FILE --url=$LIQUIBASE_URL --username=$LIQUIBASE_USERNAME --password=$LIQUIBASE_PASSWORD
    - liquibase update --changelog-file=$LIQUIBASE_CHANGELOG_FILE --url=$LIQUIBASE_URL --username=$LIQUIBASE_USERNAME --password=$LIQUIBASE_PASSWORD --contexts=$LIQUIBASE_CONTEXTS
  only:
    - main
  when: manual
  allow_failure: false

# Production environment migration (manual approval required)
migrate-prod:
  stage: migrate-prod
  image: liquibase/liquibase:latest
  variables:
    LIQUIBASE_URL: "jdbc:postgresql://${PROD_DB_HOST}:${PROD_DB_PORT}/${PROD_DB_NAME}"
    LIQUIBASE_USERNAME: "${PROD_DB_USER}"
    LIQUIBASE_PASSWORD: "${PROD_DB_PASS}"
    LIQUIBASE_CONTEXTS: "prod"
  before_script:
    - echo "Starting production database migration..."
    - liquibase status --changelog-file=$LIQUIBASE_CHANGELOG_FILE --url=$LIQUIBASE_URL --username=$LIQUIBASE_USERNAME --password=$LIQUIBASE_PASSWORD
  script:
    - liquibase update --changelog-file=$LIQUIBASE_CHANGELOG_FILE --url=$LIQUIBASE_URL --username=$LIQUIBASE_USERNAME --password=$LIQUIBASE_PASSWORD --contexts=$LIQUIBASE_CONTEXTS
  after_script:
    - echo "Production migration completed. Verifying database state..."
    - liquibase status --changelog-file=$LIQUIBASE_CHANGELOG_FILE --url=$LIQUIBASE_URL --username=$LIQUIBASE_USERNAME --password=$LIQUIBASE_PASSWORD
  only:
    - main
  when: manual
  allow_failure: false
  environment:
    name: production

Let’s break down this enhanced configuration:

  • Multiple stages: Separate stages for validation and different environments (dev, staging, prod).
  • Validation stage: Validates changelog syntax before applying migrations.
  • Environment-specific jobs: Different jobs for each environment with appropriate triggers.
  • Manual approval: Production migrations require manual approval for safety.
  • Context support: Uses Liquibase contexts to apply environment-specific changes.
  • Status checking: Shows pending migrations before applying them.

Integrating Liquibase and GitLab CI/CD#

To integrate Liquibase and GitLab CI/CD, follow these steps:

1. Create a Liquibase changelog structure#

Organize your changelogs in a logical structure:

db/
├── changelog.xml (master changelog)
├── migrations/
│   ├── 001-create-users-table.xml
│   ├── 002-add-user-timestamps.xml
│   └── 003-create-indexes.xml
└── rollbacks/
    ├── 001-rollback-users-table.xml
    └── 002-rollback-user-timestamps.xml

2. Configure GitLab CI/CD variables#

In your GitLab project’s CI/CD settings, define the following masked variables:

Development Environment:

  • DEV_DB_HOST: Development database hostname
  • DEV_DB_PORT: Development database port (default: 5432)
  • DEV_DB_NAME: Development database name
  • DEV_DB_USER: Development database username
  • DEV_DB_PASS: Development database password (masked)

Staging Environment:

  • STAGING_DB_HOST: Staging database hostname
  • STAGING_DB_PORT: Staging database port
  • STAGING_DB_NAME: Staging database name
  • STAGING_DB_USER: Staging database username
  • STAGING_DB_PASS: Staging database password (masked)

Production Environment:

  • PROD_DB_HOST: Production database hostname
  • PROD_DB_PORT: Production database port
  • PROD_DB_NAME: Production database name
  • PROD_DB_USER: Production database username
  • PROD_DB_PASS: Production database password (masked)

3. Create the CI/CD configuration#

Create a .gitlab-ci.yml file with the enhanced configuration shown above.

4. Set up database permissions#

Ensure your database users have appropriate permissions:

-- For migration user
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT ON DATABASE your_database TO migration_user;
GRANT USAGE, CREATE ON SCHEMA public TO migration_user;

Best Practices#

Changelog Management#

  • Use descriptive changeset IDs: Include date and description (e.g., 2025-05-21-create-users-table)
  • One logical change per changeset: Keep changesets atomic and focused
  • Include rollback scripts: Always provide rollback logic for production changes
  • Use preconditions: Prevent changesets from running in inappropriate conditions

Security#

  • Use masked variables: Always mask sensitive information like passwords
  • Limit database permissions: Grant only necessary permissions to migration users
  • Use different credentials per environment: Never share credentials between environments
  • Enable audit logging: Track who performed which migrations and when

Environment Management#

  • Use contexts: Apply environment-specific changes using Liquibase contexts
  • Manual approval for production: Require manual approval for production deployments
  • Test in lower environments first: Always test migrations in dev/staging before production

Monitoring and Logging#

  • Monitor migration duration: Set up alerts for long-running migrations
  • Log migration activities: Keep detailed logs of all migration activities
  • Verify migrations: Always check the status after migration completion

Advanced Configuration#

Using Different Database Types#

Liquibase supports multiple database types. Here are examples for common databases:

MySQL:

variables:
  LIQUIBASE_URL: "jdbc:mysql://${DB_HOST}:3306/${DB_NAME}?useSSL=false"

Oracle:

variables:
  LIQUIBASE_URL: "jdbc:oracle:thin:@${DB_HOST}:1521:${DB_SID}"

SQL Server:

variables:
  LIQUIBASE_URL: "jdbc:sqlserver://${DB_HOST}:1433;databaseName=${DB_NAME}"

Pre- and Post-Migration Scripts#

Add custom scripts to your pipeline:

migrate-prod:
  before_script:
    - echo "Creating database backup..."
    - pg_dump $LIQUIBASE_URL > backup-$(date +%Y%m%d-%H%M%S).sql
  script:
    - liquibase update --changelog-file=$LIQUIBASE_CHANGELOG_FILE --url=$LIQUIBASE_URL --username=$LIQUIBASE_USERNAME --password=$LIQUIBASE_PASSWORD
  after_script:
    - echo "Running post-migration validations..."
    - liquibase validate --changelog-file=$LIQUIBASE_CHANGELOG_FILE --url=$LIQUIBASE_URL --username=$LIQUIBASE_USERNAME --password=$LIQUIBASE_PASSWORD

Using Liquibase Properties Files#

For complex configurations, use properties files:

# liquibase.properties
changeLogFile=db/changelog.xml
driver=org.postgresql.Driver
url=jdbc:postgresql://localhost:5432/mydb
username=dbuser
password=dbpass
contexts=production
logLevel=INFO

script:
  - liquibase --defaults-file=liquibase.properties update

Troubleshooting Common Issues#

Lock Issues#

If you encounter lock issues:

# Release locks manually if needed
liquibase releaseLocks --changelog-file=$LIQUIBASE_CHANGELOG_FILE --url=$LIQUIBASE_URL --username=$LIQUIBASE_USERNAME --password=$LIQUIBASE_PASSWORD

Failed Migrations#

To handle failed migrations:

# Mark a changeset as executed (use carefully)
liquibase changelogSync --changelog-file=$LIQUIBASE_CHANGELOG_FILE --url=$LIQUIBASE_URL --username=$LIQUIBASE_USERNAME --password=$LIQUIBASE_PASSWORD

# Clear checksums if changeset content changed
liquibase clearCheckSums --changelog-file=$LIQUIBASE_CHANGELOG_FILE --url=$LIQUIBASE_URL --username=$LIQUIBASE_USERNAME --password=$LIQUIBASE_PASSWORD

Connection Issues#

Common connection problems and solutions:

  • Timeout issues: Increase connection timeout in JDBC URL
  • SSL issues: Configure SSL parameters properly
  • Network issues: Ensure GitLab runners can access database servers
  • Credential issues: Verify credentials and permissions

Conclusion#

Automating database schema change management with Liquibase and GitLab CI/CD can significantly improve your database reliability, reduce errors, and accelerate your development cycles. By following the practices outlined in this article, you can establish a robust, secure, and scalable database deployment pipeline.

Key benefits of this approach include:

  • Consistency: Ensures all environments have the same schema
  • Traceability: Complete audit trail of all database changes
  • Safety: Prevents manual errors and provides rollback capabilities
  • Speed: Automated deployments reduce time-to-market
  • Collaboration: Version-controlled changelogs enable team collaboration

Remember to start small, test thoroughly in non-production environments, and gradually expand your automation as your team becomes more comfortable with the process. Consider exploring Liquibase Pro for additional enterprise features if your organization requires advanced capabilities like enhanced reporting, change approval workflows, or advanced rollback features.

For more information, refer to the Liquibase documentation and GitLab CI/CD documentation to explore additional features and customization options.

From Manual to Automated: Complete Guide to Database Change Management with Liquibase and GitLab CI/CD
https://en.dymripper.com/posts/2025-05-21-automating-database-schema-change-management-with-liquibase-and-gitlab-cicd/
Author
DYMripper
Published at
2025-05-21
Automating Windows Patch Management with Chocolatey and Ansible
Automating Infrastructure Drift Detection and Remediation with Terraform and CloudWatch Events