Automatically create Liquibase migrations for PostgreSQL

Working with Liquibase always brings confidence in the fact that the database objects are in the same condition in which you want it. It is this confidence and makes system migrations so popular databases.

Today I want to tell you how to make using liquibase a bit easier. If you write a migration by hand, and your target database is PostgreSQL, then read this article, I am sure, the time spent on reading the article will be repaid with interest.

I hope you already know how to work with Liquibase, so only two words describe how the system works. In order to make changes in the database, you need to create a migration file (changeset) that you need to specify in the file change log (changelog), after which the migration can be successfully applied to the target database. Undeniable and obvious benefit of this approach is the ability to rollback the changes.

In my opinion, liquibase, there is only one drawback — the automatic rollback of changes only works only if the migration is described in the XML file. This point overshadows your developers performing changes to database, and database administrators performing the inspection changes. XML markup is not so easy to perceive, in contrast to the old and well known SQL. Liquibase, fortunately, allows you to write migration SQL, only with one annoying drawback — the automatic rollback does not work and a section of a rollback you need to write it yourself.

Today we will correct this misunderstanding and will learn how to create migration files for liquibase in the first SQL, second section of rollback, thirdly, these files will be generated automatically. Don't believe? Now I will tell everything in detail.

To create a migration we will be following — we have two databases, one is conditional for the development of other combat (stage, test, select to taste). We will make changes to a development database using your favorite tool, then compare the database, we generate the migration, which will deploy in the target database (we will consider combat).

All commands below will be displayed with the work in the Linux shell. To generate sections of the migration file we need pgCodeKeeper, and for the formation of the section of rollback, the system utility sed.

Let's agree that we have two databases dbdev and dbprod, dbdev we make changes manually, and in the fall dbprod changes using Liquibase.

I will create in /tmp directory migration which will do all the work, of course you can use whatever directory you wish. I have PostgreSQL already installed on the local computer, and with it I'm going to work.

Create database:

the

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

Downloaded the JDBC driver for PostgreSQL, Liquibase distributions and pgCodeKeeper

the

$ 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

Rotaryforum liquibase and pgcodekeeper in the current directory, you can certainly unpack them in a dedicated directory.

the

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

In the current directory, create a configuration file for liquibase liquibase.properties with the following content:

the

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

Note that it is the setting for Liquibase, i.e. describe it the connection to the database dbprod, which we will deploy our migration. The user name and password, set their values, I have a user name and ags in the future, the message command output will meet just the name. In the current directory create a file db.changelog.xml with the following content:
the

<?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 to work:

the

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

Well, half the way, remains very little. Check whether pgCodeKeeper.

the

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

if the team was not given any messages then all is well. Please note, if your instance of Postgres configured on another host or port, or use a password, you must create the corresponding JDBC URL for your database. How to create a read here.

The preparatory phase is completed, the database is created, the instruments are set up — you can proceed to the main part.

We will create a table in the database dbdev:

the

[ags@saushkin-ag:/tmp/migration] $ psql dbdev
psql (9.6.3, the server 9.5.7)
Enter "help" to get help.

(ags@[local]:5432) 16:08:43 [dbdev] =# create table users (id serial primary key, name text); 
CREATE TABLE
Time: 20,708 MS
(ags@[local]:5432) 16:09:16 [dbdev] * =# commit;
COMMIT
Time: 6,913 MS

Check whether pgCodeKeeper differences.

the

$ ./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. Prepare a script to create migrations migrate.sh

the

#!/bin/bash

FILENAME=${1:-changeset.sql}

# Header of the file migration
echo "--liquibase formatted sql" > $FILENAME
echo "changeset --$USER:$FILENAME" >> $FILENAME
echo "" >> $FILENAME

# Create section of the roll-forward
./pgcodekeeper-cli.sh jdbc:postgresql:dbdev jdbc:postgresql:dbprod >> $FILENAME

echo "" >> $FILENAME

# Create partition rollback (database go in reverse order)
./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, the default changeset.sql. Afterwards, a header of the SQL file that Liquibase is necessary to save information about the performed changes on the target database. Next is the formation of a proper migration file.

Team:

the

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

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

And the team:

the

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

looking for differences between the bases only in the other direction, the target database now becomes developer, in order to be able to automatically roll back changes. Section rollback Liquibase marked comments-rollback.

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

the

$ 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 

Perfectly formed as a section of the reel and the section of rollback. And we didn't have to write any SQL strings by hand! All the work on the creation of the migration file was automatically done.

Formed a real file migration

the

$ ./migrate.sh 001_users.sql

Connect it to the journal of the migrations, editing the file db.changelog.xml by adding the include Directive with the file 001_users.sql:

the

<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"

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 rolled the changes to dbprod, before coasting changes let's set the tag in the target database, so we can revert in the future.

the

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

$ ./liquibase migrate
Liquibase Update Successful

Check the status

the

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

Rolled back to the state before coasting of the table

the

$ ./liquibase rollback 001_before_users
Liquibase Rollback Successful

Check the status again

the

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

Return completed pre-nakat

the

$ ./liquibase migrate
Liquibase Update Successful

Let's check whether the different structures of the databases now? Now, after we "play" with the logs and rollbacks the state of database dbdev and dbprod must be the same. So?

the

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

DROP TABLE databasechangeloglock;

DROP TABLE databasechangelog;

pgCodeKeeper says dbdev no two tables databasechangelog and databasechangeloglock. These tables were created automatically liquibase and it stores information about the setups. Without these tables liquibase will fail. To these tables in the future not hurt us in the formation of migration you can either copy the structure of these tables in a development database or to use the ignore list for pgCodeKeeper.

In order to keep the ability to ignore objects create a file .pgcodekeeperignore in the current directory with the following content:

the

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

Now,

the

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

should not show any change.

So what should you do to make changes on the proposed scheme:

the
1. Make changes to development DB
the
2. With the help of our script the generated migration
the
3. Inspect the migration and plug it into the migrations history
the
4. Deploy changes on combat (test, stage) of the database
the
5. if necessary, do a rollback

Create a file migrations Liquibase is now possible to perform automatically. Migration is described in the SQL language and most importantly, a section of the pullback 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 the formation of changeset differences between the two database, unfortunately, in my experience I can say that it does not always work well, replacing this mechanism at the code generated pgCodeKeeper you can generate the migration for more complex changes in the database.

P. P. S.: pgCodeKeeper is distributed with a trial license for 100 objects in the database, if your database contains more objects, contact (email mail and Telegram channel is on product website) and you will be sent a license for an unlimited number of database objects absolutely for free.

Article based on information from habrahabr.ru