How to use Liquibase with PostgreSQL

What is Liquibase ?

Liquibase is a database schema change management solution that enables you to revise and release database changes faster and safer from development to production.

How liquibase works ?

Liquibase uses SQL, XML, JSON, and YAML changelog files to list database changes in sequential order. Database changes have the format of changesets. Changesets contain Change Type, which are types of operations to apply to the database, such as adding a column or primary key. Contextlabel, and precondition changelog tags help precisely control when a database change is made and to which database environment it is deployed.

When you deploy your changes, Liquibase creates two tables in your database: DATABASECHANGELOG  and DATABASECHANGELOGLOCK. The DATABASECHANGELOG table tracks deployed changes so that you have a record. Liquibase compares the changesets in the changelog file with the DATABASECHANGELOG tracking table and deploys only new changesets. The DATABASECHANGELOGLOCK prevents multiple instances of Liquibase from updating the database at the same time. The table manages access to the DATABASECHANGELOG table during deployment and ensures only one instance of Liquibase is updating the database.

Best practice with PostgreSQL

liquibase known as very handy tracking and revert changes in database, so in this practice we will test that.

we will make a several change in database using liquibase. first we are going to tag the current database state, run the update command, and then rollback.

before we move on, make sure that you already meet the requirement for using postgesql with liquibase.

1. Specify properties in connection profile

connection profile contains the information that Liquibase needs to connect to a particular database. It resides in the text-based Liquibase properties file,, along with other properties that rarely change. A connection profile eliminates the need to enter properties through the command prompt as parameters, saving you time and potential typographical errors.

our will look like this:

changeLogFile is a sql file contain changesets that we want to apply for our database.

liquibase.command.url is a connection profile, specifiying which url, port, and database we want to connect with.

2. Creating changelog file

changelog file contain changeset, changeset can be one or more SQL statement.

below here is the example of a changelog file (changelog2.sql):

-- liquibase formatted sql 

--changeset foo:1 
--comment: no comment
create table public.update_test1(
    id serial primary key,
    name varchar(50) not null,
    address1 varchar(50),
    address2 varchar(50),
    city varchar(30)
--rollback DROP TABLE public.update_test1;

--changeset foo:2 
--comment: no comment
create function public.f_return1() returns text
    language plpgsql
    as $$
   return '1';
--rollback DROP FUNCTION public.f_return1();

3. Tag the database (before update)

before we doing some change in database, let’s tag it first, so if we want to revert the condition we can easily use rollback command. To tag the current state of database we can use liquibase tag version1 command, like this:

liquibase tag version1

now if we check databasechangelog table, the tag will be appear

tag version1

move on to an update

4. Running update

liquibase update command will apply changelog2.sql into the target database. but first let’s check the status of our own database using liquibase status.

it said there are 2 changeset that has not been applied.

to check if there is any issue before running update command, you can use liquibase update-sql. if there is no errors occur then we can safely execute update command.

liquibase update

the ‘update’ was succesfully executed. now let’s check the databasechangelog table

id 1 and 2 with author foo is the change that we recently made.

on the next one we will revert the database with rollback command.

5. what if we want to ‘go back’

let’s say that we don’t want those change made to our database, this where liquibase came in handy. Liquibase provide rollback command, The rollback command is typically used to revert all changes that were made to the database after the tag you specify.

remember that we already tag the database on step 3, now we can undo the change using this command:

liquibase rollback version1

keep that in mind that liquibase start rollback the changeset from the end of the file, we can see from above that the order is 2::foo then 1::foo.

Using the rollback command comes with risks to your database, so it’s important to look for potential unintended consequences before executing this command. You can do this with the rollback-sql command.

and that is the end of this tutorial, thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>