Automatically create liquibase migrations for PostgreSQL
- Tutorial
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 write migrations manually, and your target database is PostgreSQL, then read this article, I'm sure the time spent reading the article will pay off with interest.
I hope that you are already familiar with the work of Liquibase, so in just a few words I will describe 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 changelog file, 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 significant drawback - automatic rollback of changes only works if the migration is described as an XML file. This moment overshadows the heads of both developers who make changes to the database and database administrators who inspect changes. XML markup is far from being readable, 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 a rollback section, thirdly, these files will be generated automatically. Do not believe? Now I’ll tell you everything in detail.
We will create migrations as follows - we will have two databases, one will be conditional for development, the other combat (stage, test - choose to your liking). We will make changes to the development database using our favorite tool, then we will compare the databases, create a migration, which we will deploy in the target database (we will consider it a battle one).
All the 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 create the rollback section, we need sed in the system.
Let's agree that we have two databases - dbdev and dbprod, in dbdev we make changes manually, and in dbprod the changes get through Liquibase.
I will create a migration directory in / tmp in which I will do all the work, of course you can use the directory you want. I already have PostgreSQL installed on the local computer, and I will work with it.
We create databases for work:
Downloading JDBC drivers for PostgreSQL, Liquibase and pgCodeKeeper distributions
Unzip liquibase and pgcodekeeper into the current directory, of course you unzip them into selected directories.
In the current directory, create the settings file for liquibase - liquibase.properties with the following contents:
Please note that these are settings for Liquibase, i.e. describe in it the connection to the dbprod database on which we will deploy our migrations. Set the username and password to your values, I have the username ags and in the subsequent messages of the output of the commands this name will be encountered. In the current directory, create the db.changelog.xml file with the following contents:
Check if liquibase is ready for use:
Well done, half the work is done, just a little remains. Check if pgCodeKeeper works.
if the team did not give any message - then everything is fine. Please note that if your postgres instance is configured on a different host or port or using password access, you must create the appropriate JDBC URL for your database. How to form it read here .
The preparatory phase is completed, the bases are created, the tools are set up - you can proceed to the main part.
Create a table in the dbdev database:
Check if pgCodeKeeper can find the differences.
Yes, everything works as expected. Preparing a script to create migrations migrate.sh
The script takes one parameter - the name of the output file, if the file name is not specified, then changeset.sql is accepted by default. Next, the SQL file header is generated, which Liquibase needs to save information about the changes made to the target database. Next is the formation of the actual migration file.
Command:
finds differences between the databases, forms a file for converting the structure of database objects from dbdev to dbprod and saves it in the output file.
A team:
it searches for differences between the same databases only in a different direction, the target database is now becoming a developer, so that it is possible to automatically roll back the changes. The rollback section in Liquibase is marked with --rollback comments.
Checking the formation of migration. For the test, as the output file, I use / dev / stdout:
Well, both the roll-up section and the roll-back section are formed. And for this we did not have to write a single SQL line manually! All work on creating the migration file was done automatically.
We form a real migration file
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:
And we roll the changes to dbprod, before rolling the changes, let's put the tag in the target database so that we can roll back in the future.
Check status
We roll back to the state before the table rolls
Check status again
Return pre-roll
Let's check if the database structures are different now? Now, after we have “played around” with rollbacks and rollbacks, the state of the dbdev and dbprod databases should be the same. Is it so?
pgCodeKeeper says 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 rolls. Without these tables, liquibase will not be possible. So that these tables do not interfere with us during the formation of migrations, you can either copy the structure of these tables to the development database or use the ignore list feature for pgCodeKeeper.
In order to use the ability to ignore objects, create a .pgcodekeeperignore file in the current directory with the following contents:
Now, the team
should not display any changes.
So, what needs to be done to make changes according to the proposed scheme:
Liquibase migration file creation is now possible to perform automatically. Migrations are described in SQL and, importantly, the rollback section is also created without human intervention. The most difficult thing to do is to come up with a migration file name.
PS: Liquibase has a built-in mechanism for generating a difference set between two databases, unfortunately, I can say from my own experience that it doesn’t always work well, replacing this mechanism with the code generated by pgCodeKeeper can generate migrations for more complex changes to the database.
PPS: Since the writing of the article, the placement of CLI releases has changed, now they are here: github.com/pgcodekeeper/pgcodekeeper/releases
The update site for Eclipse is here: pgcodekeeper.org/update
Today I want to tell you how to make working with liquibase a little more convenient. If you write migrations manually, and your target database is PostgreSQL, then read this article, I'm sure the time spent reading the article will pay off with interest.
I hope that you are already familiar with the work of Liquibase, so in just a few words I will describe 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 changelog file, 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 significant drawback - automatic rollback of changes only works if the migration is described as an XML file. This moment overshadows the heads of both developers who make changes to the database and database administrators who inspect changes. XML markup is far from being readable, 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 a rollback section, thirdly, these files will be generated automatically. Do not believe? Now I’ll tell you everything in detail.
We will create migrations as follows - we will have two databases, one will be conditional for development, the other combat (stage, test - choose to your liking). We will make changes to the development database using our favorite tool, then we will compare the databases, create a migration, which we will deploy in the target database (we will consider it a battle one).
All the 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 create the rollback section, we need sed in the system.
Let's agree that we have two databases - dbdev and dbprod, in dbdev we make changes manually, and in dbprod the changes get through Liquibase.
I will create a migration directory in / tmp in which I will do all the work, of course you can use the directory you want. I already have PostgreSQL installed on the local computer, and I will work with it.
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 liquibase and pgcodekeeper into the current directory, of course you unzip them into selected directories.
Attention! There have been some changes since the writing of this article. 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 the 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
Please note that these are settings for Liquibase, i.e. describe in it the connection to the dbprod database on which we will deploy our migrations. Set the username and password to your values, I have the username ags and in the subsequent messages of the output of the commands this name will be encountered. In the current directory, create the db.changelog.xml file with the following contents:
Check if liquibase is ready for use:
$ ./liquibase status
ags@jdbc:postgresql:dbprod is up to date
Liquibase 'status' Successful
Well done, half the work is done, just a little remains. Check if pgCodeKeeper works.
$ ./pgcodekeeper-cli.sh jdbc:postgresql:dbdev jdbc:postgresql:dbprodif the team did not give any message - then everything is fine. Please note that if your postgres instance is configured on a different host or port or using password access, you must create the appropriate JDBC URL for your database. How to form it read here .
The preparatory phase is completed, the bases are created, the tools are set up - 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 a script to create migrations 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 changeset.sql is accepted by default. Next, the SQL file header is generated, which Liquibase needs to save information about the changes made to the target database. Next is the formation of the actual migration file.
Command:
./pgcodekeeper-cli.sh jdbc:postgresql:dbdev jdbc:postgresql:dbprod >> $FILENAMEfinds differences between the databases, forms a file for converting the structure of database objects from dbdev to dbprod and saves it in the output file.
A team:
./pgcodekeeper-cli.sh jdbc:postgresql:dbprod jdbc:postgresql:dbdev | sed -e 's/^/--rollback /' >> $FILENAME
it searches for differences between the same databases only in a different direction, the target database is now becoming a developer, so that it is possible to automatically roll back the changes. The rollback section in Liquibase is marked with --rollback comments.
Checking the formation of migration. For the test, as the output file, I use / dev / stdout:
$ 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
Well, both the roll-up section and the roll-back section are formed. And for this we did not have to write a single SQL line manually! All work on creating the migration file was done automatically.
We form a real migration file
$ ./migrate.sh 001_users.sqlWe 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:
And we roll the changes to dbprod, before rolling the changes, let's put the tag in the target database so that we can roll back in the future.
$ ./liquibase tag 001_before_users
Successfully tagged ags@jdbc:postgresql:dbprod
Liquibase 'tag' Successful
$ ./liquibase migrate
Liquibase Update Successful
Check status
$ ./liquibase status
ags@jdbc:postgresql:dbprod is up to date
Liquibase 'status' Successful
We roll back to the state before the table rolls
$ ./liquibase rollback 001_before_users
Liquibase Rollback Successful
Check status again
$ ./liquibase status
1 change sets have not been applied to ags@jdbc:postgresql:dbprod
Liquibase 'status' Successful
Return pre-roll
$ ./liquibase migrate
Liquibase Update Successful
Let's check if the database structures are different now? Now, after we have “played around” with 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 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 rolls. Without these tables, liquibase will not be possible. So that these tables do not interfere with us during the formation of migrations, you can either copy the structure of these tables to the development database or use the ignore list feature for pgCodeKeeper.
In order to use 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:dbprodshould not display any changes.
So, what needs to be done to make changes according to the proposed scheme:
- We make changes to the development database
- Using our script, we form the migration
- Inspect migration and plug it into the migration log
- Deploying changes to the combat (test, stage) database
- If necessary, do a rollback
Liquibase migration file creation is now possible to perform automatically. Migrations are described in SQL and, importantly, the rollback section is also created without human intervention. The most difficult thing to do is to come up with a migration file name.
PS: Liquibase has a built-in mechanism for generating a difference set between two databases, unfortunately, I can say from my own experience that it doesn’t always work well, replacing this mechanism with the code generated by pgCodeKeeper can generate migrations for more complex changes to the database.
PPS: Since the writing of the article, the placement of CLI releases has changed, now they are here: github.com/pgcodekeeper/pgcodekeeper/releases
The update site for Eclipse is here: pgcodekeeper.org/update