Automatic creation of Liquibase migrations for PostgreSQL

Working with Liquibase always brings confidence that the database objects are in the state in which you want it. It is this confidence that makes database migration systems so popular.

Today I want to tell you how to make working with liquibase a little more convenient. If you are writing the migration manually, and PostgreSQL is your target database, then read this article, I am sure that the time spent on reading the article will pay off with interest.



I hope that you are already familiar with the work of Liquibase, so I’ll describe in a few words how the system works. In order to make changes to the database, you need to create a migration file (changeset), a link to which will need to be specified in the change log file (changelog), after which the migration can be successfully applied to the target database. The undeniable and obvious advantage of this approach is the ability to roll back the changes made.
')
In my opinion, liquibase has only one major drawback - automatic rollback of changes only works if the migration is described as an XML file. This moment darkens the heads of both the developers who perform changes in the database and the database administrators who perform the inspection of changes. XML markup is far from being so good to understand, unlike old and well-known SQL. Liquibase, fortunately, allows you to write migrations to SQL, with only one annoying drawback - automatic rollback now does not work and the rollback section needs to be written manually.

Today we will correct this misunderstanding and learn how to create migration files for liquibase, firstly in SQL, secondly with the rollback section, thirdly, these files will be generated automatically. Do not believe? Now I will tell you everything in detail.

We will create migration in the following way - we will have two databases, one will be conditional for development, the other will be combat (stage, test - choose to your taste). We will make changes to the developer database using our favorite tool, then we will compare the databases, we will create the migration, which we will deploy in the target database (we will consider it a battle database).

All commands below will be displayed taking into account the work in the Linux shell. To generate the migration file section, we need pgCodeKeeper, and to form the rollback section, we need the sed utility in the system.

Let's agree that we have two databases - dbdev and dbprod, we make changes to dbdev manually, and changes get to dbprod using Liquibase.

I will create a migration directory in / tmp in which I will do all the work, of course you can use whatever directory you want. PostgreSQL is already installed on my local computer, and it is with him that I will carry out the work.

We create databases for work:

$ mkdir /tmp/migration $ cd /tmp/migration/ $ createdb dbdev $ createdb dbprod 

Downloading JDBC drivers for PostgreSQL, Liquibase and pgCodeKeeper distributions

 $ wget https://jdbc.postgresql.org/download/postgresql-42.1.3.jar $ wget https://github.com/liquibase/liquibase/releases/download/liquibase-parent-3.4.2/liquibase-3.4.2-bin.tar.gz $ wget http://pgcodekeeper.ru/cli/release/pgCodeKeeper-cli-3.11.4.201707170702.zip 

Unzip the liquibase and pgcodekeeper into the current directory, of course you unpack them into dedicated directories.

Attention! Since the writing of the article there have been some changes. The CLI version of pgCodeKeeper can be downloaded here: github.com/pgcodekeeper/pgcodekeeper/releases

 $ tar xzvf liquibase-3.4.2-bin.tar.gz $ unzip pgCodeKeeper-cli-3.11.4.201707170702.zip 

In the current directory, create a settings file for liquibase - liquibase.properties with the following contents:

 driver: org.postgresql.Driver classpath: ./postgresql-42.1.3.jar url: jdbc:postgresql:dbprod username: user password: topsecret changeLogFile: db.changelog.xml 

I draw your attention that these are the settings for Liquibase, i.e. describe in it the connection with the dbprod database, to which we will deploy our migrations. Set the username and password to your values, my username is ags, and in future messages, the output of the commands will contain exactly this name. In the current directory, create a file db.changelog.xml with the following contents:

 <?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-3.1.xsd"> </databaseChangeLog> 

Check if liquibase is ready for operation:

 $ ./liquibase status ags@jdbc:postgresql:dbprod is up to date Liquibase 'status' Successful 

Well, half the work is done, just a little is left. Check if pgCodeKeeper works.

 $ ./pgcodekeeper-cli.sh jdbc:postgresql:dbdev jdbc:postgresql:dbprod 

if the team did not give any message, then everything is fine. Please note, if your postgres instance is configured on a different host or port or password access is used, then you must generate the appropriate JDBC URL for your database. How to form it read here .

The preparatory stage is completed, the bases are created, the tools are configured - you can proceed to the main part.

Create a table in the dbdev database:

 [ags@saushkin-ag:/tmp/migration] $ psql dbdev psql (9.6.3,  9.5.7)  "help",   . (ags@[local]:5432) 16:08:43 [dbdev] =# create table users (id serial primary key, name text); CREATE TABLE : 20,708  (ags@[local]:5432) 16:09:16 [dbdev] * =# commit; COMMIT : 6,913  

Check if pgCodeKeeper can find the differences.

 $ ./pgcodekeeper-cli.sh jdbc:postgresql:dbdev jdbc:postgresql:dbprod CREATE SEQUENCE users_id_seq START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; ALTER SEQUENCE users_id_seq OWNER TO ags; CREATE TABLE users ( id integer DEFAULT nextval('users_id_seq'::regclass) NOT NULL, name text ); ALTER TABLE users OWNER TO ags; ALTER TABLE users ADD CONSTRAINT users_pkey PRIMARY KEY (id); ALTER SEQUENCE users_id_seq OWNED BY users.id; 

Yes, everything works as expected. Preparing the migration creation script migrate.sh

 #!/bin/bash FILENAME=${1:-changeset.sql} #    echo "--liquibase formatted sql" > $FILENAME echo "--changeset $USER:$FILENAME" >> $FILENAME echo "" >> $FILENAME #    ./pgcodekeeper-cli.sh jdbc:postgresql:dbdev jdbc:postgresql:dbprod >> $FILENAME echo "" >> $FILENAME #    (     ) ./pgcodekeeper-cli.sh jdbc:postgresql:dbprod jdbc:postgresql:dbdev | sed -e 's/^/--rollback /' >> $FILENAME 

The script takes one parameter - the name of the output file, if the file name is not specified, then the default is changeset.sql. Next, the SQL file header is generated, which is necessary for Liquibase to save information about the changes made on the target database. Next is the formation of the actual migration file.

Team:

 ./pgcodekeeper-cli.sh jdbc:postgresql:dbdev jdbc:postgresql:dbprod >> $FILENAME 

finds the differences between the databases, forms the file converting the structure of database objects from dbdev to dbprod and saves them in the output file.

And the team:

 ./pgcodekeeper-cli.sh jdbc:postgresql:dbprod jdbc:postgresql:dbdev | sed -e 's/^/--rollback /' >> $FILENAME 

looking for differences between the same bases only in a different direction, the target database now becomes developer, so that you can perform an automatic rollback of changes. The rollback section in Liquibase is flagged with --rollback comments.

We check the formation of migration. For the test, I use / dev / stdout as the output file:

 $ chmod +x ./migrate.sh $ ./migrate.sh /dev/stdout --liquibase formatted sql --changeset ags:/dev/stdout CREATE SEQUENCE users_id_seq START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; ALTER SEQUENCE users_id_seq OWNER TO ags; CREATE TABLE users ( id integer DEFAULT nextval('users_id_seq'::regclass) NOT NULL, name text ); ALTER TABLE users OWNER TO ags; ALTER TABLE users ADD CONSTRAINT users_pkey PRIMARY KEY (id); ALTER SEQUENCE users_id_seq OWNED BY users.id; --rollback ALTER TABLE users --rollback DROP CONSTRAINT users_pkey; --rollback --rollback DROP TABLE users; --rollback 

Excellent, both the run-up section and the rollback section are formed. And for this we did not have to write a single line of SQL manually! All work on creating a migration file was done automatically.

We form the real file of migration

 $ ./migrate.sh 001_users.sql 

We connect it to the migration log by editing the db.changelog.xml file, adding the include directive with the 001_users.sql file to it:

 <?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-3.1.xsd"> <include file="001_users.sql" /> </databaseChangeLog> 

And roll changes to dbprod, before rolling changes, let's set a tag in the target database so that we can roll back further.

 $ ./liquibase tag 001_before_users Successfully tagged ags@jdbc:postgresql:dbprod Liquibase 'tag' Successful $ ./liquibase migrate Liquibase Update Successful 

Checking status

 $ ./liquibase status ags@jdbc:postgresql:dbprod is up to date Liquibase 'status' Successful 

Roll back to the state before rolling the table

 $ ./liquibase rollback 001_before_users Liquibase Rollback Successful 

We check the status again

 $ ./liquibase status 1 change sets have not been applied to ags@jdbc:postgresql:dbprod Liquibase 'status' Successful 

We return completed pre-roll

 $ ./liquibase migrate Liquibase Update Successful 

Let's check if the database structures are different now? Now, after we have “played” with the rollbacks and rollbacks, the state of the dbdev and dbprod databases should be the same. Is it so?

 $ ./pgcodekeeper-cli.sh jdbc:postgresql:dbdev jdbc:postgresql:dbprod ALTER TABLE databasechangeloglock DROP CONSTRAINT pk_databasechangeloglock; DROP TABLE databasechangeloglock; DROP TABLE databasechangelog; 

pgCodeKeeper says that there are no two tables in dbdev - databasechangeloglock and databasechangelog. These tables were created automatically by liquibase and in them it stores information about the performed reels. Without these tables, liquibase operation will be impossible. So that these tables do not interfere with us further in the formation of migrations, you can either copy the structure of these tables into the developer database or use the ignore list feature for pgCodeKeeper.

To enable the ability to ignore objects, create a .pgcodekeeperignore file in the current directory with the following contents:

 $ cat .pgcodekeeperignore SHOW ALL HIDE REGEX "databasechangelog.*" 

Now, the team

 $ ./pgcodekeeper-cli.sh -I .pgcodekeeperignore jdbc:postgresql:dbdev jdbc:postgresql:dbprod 

should not display any changes.

So, what should be done to make changes according to the proposed scheme:

1. We make changes to the developer database
2. Using our script, we create a migration
3. We inspect the migration and connect it to the migration log
4. Deploy changes to combat (test, stage) database
5. If necessary, we do rollback

Creating Liquibase migration files is now possible to do automatically. Migrations are described in SQL and, importantly, the rollback section is also created without human intervention. The hardest thing to do is to come up with the name of the migration file.

PS: Liquibase has a built-in mechanism for forming a changeset between the two databases, unfortunately, I can say from my own experience that it does not always work well.

PPS: Since the writing of the article, the placement of the CLI releases has changed, now they are here: github.com/pgcodekeeper/pgcodekeeper/releases

The Eclipse update site is here: pgcodekeeper.org/update