DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

How does AI transform chaos engineering from an experiment into a critical capability? Learn how to effectively operationalize the chaos.

Data quality isn't just a technical issue: It impacts an organization's compliance, operational efficiency, and customer satisfaction.

Are you a front-end or full-stack developer frustrated by front-end distractions? Learn to move forward with tooling and clear boundaries.

Developer Experience: Demand to support engineering teams has risen, and there is a shift from traditional DevOps to workflow improvements.

Related

  • Master SQL Performance Optimization: Step-by-Step Techniques With Case Studies
  • Resolving Parameter Sensitivity With Parameter Sensitive Plan Optimization in SQL Server 2022
  • Useful System Table Queries in Relational Databases
  • How to Restore a Transaction Log Backup in SQL Server

Trending

  • DZone's Article Submission Guidelines
  • How to Submit a Post to DZone
  • From Monolith to Containers: Real-World Migration Blueprint
  • Enterprise-Grade Distributed JMeter Load Testing on Kubernetes: A Scalable, CI/CD-Driven DevOps Approach
  1. DZone
  2. Data Engineering
  3. Databases
  4. Managing Your Database With Liquibase and Gradle

Managing Your Database With Liquibase and Gradle

Here's a brief overview of Liquibase and how it can be used to manage scripts within your database.

By 
Rafael Salerno user avatar
Rafael Salerno
·
Nov. 11, 16 · Tutorial
Likes (9)
Comment
Save
Tweet
Share
58.2K Views

Join the DZone community and get the full member experience.

Join For Free

One major system development problem has always been how and when we update the database. There are always at least a few questions that crop up:

  • Were scripts for DB changes created?
  • Where do we save the DB changes?
  • When should we apply these changes?
  • Have these changes already been implemented?
  • How do we track and manage database changes?
  • Who made these changes?

Liquibase is a library that can help address these issues. Liquibase is an open source database used to track, manage, and apply changes to the database.

Liquibase works better because it understands what the changes are. For example, a database comparison program would simply see the “person” table on integration has a “firstname” and a “lastname” column, but on live, the “person” table has a “name” column. It would report that you need to drop the “name” column and add a “firstname” and a “lastname” column. While this would leave you with the correct schema, you would lose everyone’s name in the process. With Liquibase, you would have a changeset that says “rename ‘name’ to ‘lastname’ and add a ‘firstname’ column” or, even better, “split the name column on a space and place the values in new ‘firstname’ and ‘lastname’ columns, then drop the ‘name’ column.” Knowing why they are different allows changes to production databases without the fear of losing valuable data.

In this post, I will show how you can use the powerful tool Liquibase together with Gradle to automate these tasks, from there will be easy to put Liquibase to work with your continuos integration tools.

Some important concepts:

Changelog File

The changelog is the file that contains references of all the scripts that should be applied to the database in any environment.

ChangeSet Files

ChangeSet files:

  • Are all the files recorded in a changelog.

  • Can be written primarily in XML, YAML, JSON, and SQL, which I chose for this example.

Some advice:

  • IDs cannot be repeated. Otherwise, they will not run.
  • Scripts should be small.
  • Rollbacks should be added whenever possible.
  • Must be added new scripts on the changelog.xml
  • Everything that was executed is registered on the table DATABASECHANGELOG


Example

Changelog.xml

<?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"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

    <include file="scripts/001.SAMPLE.sql" relativeToChangelogFile="true"/>
    <include file="scripts/002.SAMPLE.sql" relativeToChangelogFile="true"/>

</databaseChangeLog>


001.SAMPLE.sql

--liquibase formatted sql

--changeset salerno:3
create table test1 (
    id int primary key,
    name varchar(255)
);
--rollback drop table test1;


002.SAMPLE.sql

--liquibase formatted sql

--changeset salerno:4
insert into test1 (id, name) values (1, 'name 1');
insert into test1 (id, name) values (2, 'name 2');
insert into test1 (id, name) values (3, 'name 3');
insert into test1 (id, name) values (4, 'name 4');


Build.gradle

apply plugin: 'liquibase'

buildscript {
    repositories {
        mavenCentral()
    }

    dependencies {
    classpath 'org.liquibase:liquibase-core:3.4.1'
    classpath "org.liquibase:liquibase-gradle-plugin:1.1.1"
classpath 'mysql:mysql-connector-java:5.1.13'
    }
}

def changeLog = "$projectDir/src/main/db/changelog.xml"

task('dev') << {
println "executing dev"

liquibase {
    activities {
        main {
            changeLogFile changeLog
            url 'jdbc:mysql://dev-environment/mydb'
            username 'root'
            password '123456'
        }
    }
}    
}

task('qa') << {
println "executing qa"

liquibase {
    activities {
        main {
            changeLogFile changeLog
           url 'jdbc:mysql://qa-environment/mydb'
            username 'root'
            password '123456'
        }
    }
}    
}

task('prod') << {
println "executing prod"

liquibase {
    activities {
        main {
            changeLogFile changeLog
            u url 'jdbc:mysql://prod-environment/mydb'
            username 'root'
            password '123456'
        }
    }
}    
}


I created tasks for every environment I have and where Liquibase should run the script. With Gradle, I need only to choose a task as it is below:

  • To execute:
    • gradle task dev update
    • gradle task qa update
    • gradle task prod update

After that, you can check within your database that scripts 001 and 002 were applied.

On the table DATABASECHANGELOG, it's possible check some records like these:

This sample above complete in my GitHub.

Database Liquibase Gradle sql

Opinions expressed by DZone contributors are their own.

Related

  • Master SQL Performance Optimization: Step-by-Step Techniques With Case Studies
  • Resolving Parameter Sensitivity With Parameter Sensitive Plan Optimization in SQL Server 2022
  • Useful System Table Queries in Relational Databases
  • How to Restore a Transaction Log Backup in SQL Server

Partner Resources

×

Comments

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • [email protected]

Let's be friends: