Introduction
This quick start guide explains how to use Liquibase with PostgreSQL (or any other JDBC compliant DBMS). Liquibase is an open source tool for database change tracking. A known caveat in most shared database development scenarios comes when application source code doesn’t match database schema. Liquibase permits to express your database layer in a declarative and incremental fashion with more benefits:
- Better control and management of DDL changes with multiple developers
- Apply DDL changes defined in change sets according to application source code commits
- Execute preconditions for required database changes i.e: Check consistency in table values before an integrity constraint creation
- Integration with other development tools: Maven, Gradle, CI/CD
- Macro executions (Liquibase Flows)
- Vendor neutral database schema generation
In this tutorial we will show the basic Liquibase usage and explore how to manage database changes in PostgreSQL for tables and how you can leverage Liquibase for cross database vendor migrations, CI/CD DevOps tools and colaborative software development.
Liquibase CLI Installation
First, we have assume you have a running PostgreSQL server and a database user with DDL permissions. Liquibase CLI installers can be downloaded from Liquibase website. In my case I downloaded the compressed file for Windows and added the extraction directory to the path.
Lets start with a clean project. Notice that this instructions would work with any other JDBC compliant database (even a free Oracle Cloud database). You can start a new Liquibase project locating in the project folder and type liquibase init project. There are some options related to the name of the changelog file, the format and the JDBC URL of your database.
C:\Users\snishi\projects\lqbase>liquibase.bat init project
####################################################
## _ _ _ _ ##
## | | (_) (_) | ##
## | | _ __ _ _ _ _| |__ __ _ ___ ___ ##
## | | | |/ _` | | | | | '_ \ / _` / __|/ _ \ ##
## | |___| | (_| | |_| | | |_) | (_| \__ \ __/ ##
## \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___| ##
## | | ##
## |_| ##
## ##
## Get documentation at docs.liquibase.com ##
## Get certified courses at learn.liquibase.com ##
## Free schema change activity reports at ##
## https://hub.liquibase.com ##
## ##
####################################################
Starting Liquibase at 21:02:36 (version 4.20.0 #7837 built at 2023-03-07 16:25+0000)
Liquibase Version: 4.20.0
Liquibase Open Source 4.20.0 by Liquibase
Setup new liquibase.properties, flowfile, and sample changelog? Enter (Y)es with defaults, yes with (C)ustomization, or (N)o. [Y]:
C
Enter a relative path to desired project directory [./]:
Enter name for sample changelog file to be created or (s)kip [example-changelog]:
lqbase-changelog
Enter your preferred changelog format (options: sql, xml, json, yml, yaml) [sql]:
xml
Enter name for defaults file to be created or (s)kip [liquibase.properties]:
Enter the JDBC url without username or password to be used (What is a JDBC url? <url>) [jdbc:h2:tcp://localhost:9090/mem:dev]:
jdbc:postgresql://localhost:5432/lqbasedb
Enter username to connect to JDBC url [dbuser]:
lqbase
Enter password to connect to JDBC url [letmein]:
lqbase
Setting up new Liquibase project in 'C:\Users\snishi\projects\lqbase\.'...
Created example changelog file 'C:\Users\snishi\projects\lqbase\lqbase-changelog.xml'
Created example defaults file 'C:\Users\snishi\projects\lqbase\liquibase.properties'
Created example flow file 'C:\Users\snishi\projects\lqbase\liquibase.advanced.flowfile.yaml'
Created example flow file 'C:\Users\snishi\projects\lqbase\liquibase.flowvariables.yaml'
Created example flow file 'C:\Users\snishi\projects\lqbase\liquibase.endstage.flow'
Created example flow file 'C:\Users\snishi\projects\lqbase\liquibase.flowfile.yaml'
To use the new project files make sure your database is active and accessible and run "liquibase update".
For more details, visit the Getting Started Guide at https://docs.liquibase.com/start/home.html
Liquibase command 'init project' was executed successfully.
C:\Users\snishi\projects\lqbase>The previous command generates several files. The most important are:
liquibase.properties: This file contains the configuration for the database, the name of the changelog file among other global properties.lqbase-changelog.xml: Defines the different units of modifications to the database (changesets). We selected XML as the preferred language over SQL because it allows to define DDL in a vendor neutral declarative notation as well as JSON, YML and YAML formats.
The other files are used to build macro commands known as Liquibase Flows that are out of tthe scope of this quick start guide.
Changelog file and Changesets
Changelogs refer to different sets of changes that together comprise a new version of the database (changesets). Liquibase doesn’t enforce this semantic definition but is in the hands of the developer to keep consistency between logical and data layers. Changesets are composed from different database modifications (DDL operations) such as adding/removing a column, column type change and adding/removing objects or constraints.
Creating DDL changes
Once your project is ready you must proceed editing your changelog file. Lets create the typical USER and ROLE tables:
<?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:pro="http://www.liquibase.org/xml/ns/pro"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd ">
<changeSet id="1" author="dev1" labels="example-label" context="example-context">
<comment>An example of a Liquibase changeset</comment>
<createTable tableName="users">
<column name="id" type="int" autoIncrement="true">
<constraints primaryKey="true" nullable="false"/>
</column>
<column name="name" type="varchar(50)">
<constraints nullable="false"/>
</column>
<column name="email" type="varchar(100)"/>
</createTable>
</changeSet>
<changeSet id="2" author="dev2" labels="example-label" context="example-context">
<comment>Create roles and users_roles</comment>
<createTable tableName="roles">
<column name="id" type="int" autoIncrement="true">
<constraints primaryKey="true" nullable="false"/>
</column>
<column name="name" type="varchar(50)">
<constraints nullable="false"/>
</column>
</createTable>
<createTable tableName="users_roles">
<column name="user_id" type="int" />
<column name="role_id" type="int" />
</createTable>
</changeSet>
<changeSet id="3" author="dev3" labels="example-label" context="example-context">
<comment>example-comment</comment>
<addColumn tableName="users">
<column name="phone" type="varchar(20)"/>
</addColumn>
</changeSet>
</databaseChangeLog>To apply the changes, we use liquibase update command:
C:\Users\snishi\projects\lqbase>liquibase.bat update
####################################################
## _ _ _ _ ##
## | | (_) (_) | ##
## | | _ __ _ _ _ _| |__ __ _ ___ ___ ##
## | | | |/ _` | | | | | '_ \ / _` / __|/ _ \ ##
## | |___| | (_| | |_| | | |_) | (_| \__ \ __/ ##
## \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___| ##
## | | ##
## |_| ##
## ##
## Get documentation at docs.liquibase.com ##
## Get certified courses at learn.liquibase.com ##
## Free schema change activity reports at ##
## https://hub.liquibase.com ##
## ##
####################################################
Starting Liquibase at 22:19:15 (version 4.20.0 #7837 built at 2023-03-07 16:25+0000)
Liquibase Version: 4.20.0
Liquibase Open Source 4.20.0 by Liquibase
Running Changeset: lqbase-changelog.xml::1::dev1
Running Changeset: lqbase-changelog.xml::2::dev2
Running Changeset: lqbase-changelog.xml::3::dev3
Liquibase command 'update' was executed successfully.
C:\Users\snishi\projects\lqbase>As you can see, in the postgreSQL database there are 5 new tables: USERS, ROLES and USERS_ROLES according to the changelog configuration and DATABASECHANGELOG and DATABASECHANGELOGLOCK used by Liquibase to control the changeset application. the update command takes the pending changesets and apply them to the database.
Rollback changes
Liquibase provides several commands for rollback. The rollback command reverses changes until it reaches a specified tag. The tag should have been set previously with the tag command. The rollback-count command reveerses a specific number of changesets. In the example, we reverse the last 2 changesets:
C:\Users\snishi\projects\lqbase>liquibase.bat rollback-count --count=2
####################################################
## _ _ _ _ ##
## | | (_) (_) | ##
## | | _ __ _ _ _ _| |__ __ _ ___ ___ ##
## | | | |/ _` | | | | | '_ \ / _` / __|/ _ \ ##
## | |___| | (_| | |_| | | |_) | (_| \__ \ __/ ##
## \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___| ##
## | | ##
## |_| ##
## ##
## Get documentation at docs.liquibase.com ##
## Get certified courses at learn.liquibase.com ##
## Free schema change activity reports at ##
## https://hub.liquibase.com ##
## ##
####################################################
Starting Liquibase at 22:45:46 (version 4.20.0 #7837 built at 2023-03-07 16:25+0000)
Liquibase Version: 4.20.0
Liquibase Open Source 4.20.0 by Liquibase
Rolling Back Changeset: lqbase-changelog.xml::3::dev3
Rolling Back Changeset: lqbase-changelog.xml::2::dev2
Liquibase command 'rollback-count' was executed successfully.You can see that after applying the rollback, the records in the DATABASECHANGELOG table as well as the ROLES and USERS_ROLES tables are gone.
The liquibase status command shows how many changesets from the changelog file are pending to be applyed in the database:
C:\Users\snishi\projects\lqbase>liquibase.bat status
####################################################
## _ _ _ _ ##
## | | (_) (_) | ##
## | | _ __ _ _ _ _| |__ __ _ ___ ___ ##
## | | | |/ _` | | | | | '_ \ / _` / __|/ _ \ ##
## | |___| | (_| | |_| | | |_) | (_| \__ \ __/ ##
## \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___| ##
## | | ##
## |_| ##
## ##
## Get documentation at docs.liquibase.com ##
## Get certified courses at learn.liquibase.com ##
## Free schema change activity reports at ##
## https://hub.liquibase.com ##
## ##
####################################################
Starting Liquibase at 22:53:34 (version 4.20.0 #7837 built at 2023-03-07 16:25+0000)
Liquibase Version: 4.20.0
Liquibase Open Source 4.20.0 by Liquibase
2 changesets have not been applied to lqbase@jdbc:postgresql://localhost:5432/lqbasedb
Liquibase command 'status' was executed successfully.Export Changelogs from existing databases
Now, let’s export our current state in another changelog file for use in other projects:
C:\Users\snishi\projects\lqbase>liquibase generate-changelog --changelog-file=export-changelog.xml
####################################################
## _ _ _ _ ##
## | | (_) (_) | ##
## | | _ __ _ _ _ _| |__ __ _ ___ ___ ##
## | | | |/ _` | | | | | '_ \ / _` / __|/ _ \ ##
## | |___| | (_| | |_| | | |_) | (_| \__ \ __/ ##
## \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___| ##
## | | ##
## |_| ##
## ##
## Get documentation at docs.liquibase.com ##
## Get certified courses at learn.liquibase.com ##
## Free schema change activity reports at ##
## https://hub.liquibase.com ##
## ##
####################################################
Starting Liquibase at 23:05:15 (version 4.20.0 #7837 built at 2023-03-07 16:25+0000)
Liquibase Version: 4.20.0
Liquibase Open Source 4.20.0 by Liquibase
Error parsing command line: Output ChangeLogFile 'export-changelog.xml' already exists!
For detailed help, try 'liquibase --help' or 'liquibase <command-name> --help'The export-changelog.xml only contains the USERS table as it matches the current state:
<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:pro="http://www.liquibase.org/xml/ns/pro" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
<changeSet author="snishi (generated)" id="1678852971701-1">
<createTable tableName="roles">
<column autoIncrement="true" name="id" type="INTEGER">
<constraints nullable="false" primaryKey="true" primaryKeyName="roles_pkey"/>
</column>
<column name="name" type="VARCHAR(50)">
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
<changeSet author="snishi (generated)" id="1678852971701-2">
<createTable tableName="users">
<column autoIncrement="true" name="id" type="INTEGER">
<constraints nullable="false" primaryKey="true" primaryKeyName="users_pkey"/>
</column>
<column name="name" type="VARCHAR(50)">
<constraints nullable="false"/>
</column>
<column name="email" type="VARCHAR(100)"/>
<column name="phone" type="VARCHAR(20)"/>
</createTable>
</changeSet>
<changeSet author="snishi (generated)" id="1678852971701-3">
<createTable tableName="users_roles">
<column name="user_id" type="INTEGER"/>
<column name="role_id" type="INTEGER"/>
</createTable>
</changeSet>
</databaseChangeLog>Development tools integration (Git, Maven, Spring Boot, CI/CD)
- Git: The source code for this quick start guide is located in the Git repository. For software development Git repositories please refer to the Liquibase best practices.
- Maven has a plugin to invoke the Liquibase commands.
- Spring Boot has automatic support for Liquibase: it will apply the changesets for the changelog file located at
src/main/resources/config/liquibase/master.xml. - Other CI/CD tools can use the Java API or the Liquibase CLI to execute commands and keep the database changes up-to-date.