Manage database migrations with Liquibase
Not so long ago, we began to implement Liquibase as a data schema migration tool in most of our projects, new and existing ones. The Liquibase database schema migration system is good because it allows you to use version control systems, VCS (for example, Git) to manage the revisions of the application database. More specifically, the VCS describes the changes needed to migrate the database schema from one revision to another.
Although migrating a database schema seems like a pretty straightforward task initially, the task becomes more difficult after you want to roll back schema changes without creating it again.
In addition to the scheme and DDL operations, Liquibase allows you to migrate application data, with the support of roll forward data changes and rollback.
Let's start with the simple. For the example discussed in this article, I will use the command-line Liquibase, as well as a simple CLI client for MySQL.
Liquibase also integrates well with Maven (like goal) or Spring (like bin, launched during context initialization).
')
Let's start with a very simple PERSON table, consisting only of the ID (primary key) and the name:
Liquibase uses so-called “changesets” (changeset), an XML code for describing DDL statements. They make changelog files. The next changer will create a table (“createTable” tag) and two columns (“column” tag).
Using this XML code, Liquibase will add the “Person” table. The command that does this from the command line interface is “update”:
Liquibase has built-in support for rolling back certain types of changesets, for example, “createTable”. If we call Liquibase via the command line with the argument “rollbackCount 1” instead of “update”, the last changeset will be rolled back: the PERSON table will be deleted.
Other types of changesets cannot be deleted automatically. For example, consider the following changesets that add data to PERSON (“insert” tag):
I manually added a rollback tag containing SQL statements that roll back changes in this changeset. This tag can contain both SQL statements and ordinary Liquibase tags.
Since we now have two XML changer files, I created a “master” file that imports the other files in the order necessary to get the correct database revision:
When calling the “update” command for each of the changesets, it is checked whether it was applied to the scheme. If the changeset has not yet been applied, it is executed. For this, Liquibase saves the data in the auxiliary table DATABASECHANGELOGS, which contains the changesets already applied, as well as their hash values. Hashes are used so that it is impossible to change the already completed changesets.
Now that the simple example is working, let's try something more complicated: changing the schema that requires migrating the schema and updating the data. The PERSON table currently only has a column named NAME, and I want to split NAME into two columns - FIRSTNAME and LASTNAME. Before the start of database migration, I am going to put a Liquibase "tag" so that you can roll back all the changes to this tag in the future:
I created a new changeset that adds two new columns:
And this time, Liquibase knows how to roll back this changeset, so that we can not add the rollback tag.
Now the PERSON table has two additional columns and we need to take care of the migration of existing data to the new scheme before we remove the obsolete NAME column. Since data manipulation is not supported by Liquibase out of the box, we need to use the “sql” tag to include native SQL in the changeset.
Note that the contents of the rollback tag seem redundant, but the tag itself is necessary due to the fact that Liquibase allows you to roll back only changesets:
After launching Liquibase with the “update” option, the new changeset is applied to the schema: the created columns FIRSTNAME and LASTNAME already contain data.
Next, I want to get rid of the old NAME column.
The changeset itself is quite simple, since Liquibase supports deleting columns, but the rollback tag has become more complex:
Result of transformations:
Due to the fact that we initially marked the circuit with a tag, and also added instructions for rolling back the changes in all our changesets, we can roll back the modifications to the database schema without losing data! Calling ...
... we are back to the opidinal state of the database schema!
The example of splitting / merging lines of the PERSON name is somewhat contrived, but the same principle can be applied to more serious data changes.
The idea for this post, I stumbled upon working on the division of the existing domain class (corresponding to one table) into three parts: an abstract base class and two subclasses, taking into account the need to preserve the integrity of the data.
Although migrating a database schema seems like a pretty straightforward task initially, the task becomes more difficult after you want to roll back schema changes without creating it again.
In addition to the scheme and DDL operations, Liquibase allows you to migrate application data, with the support of roll forward data changes and rollback.
Let's start with the simple. For the example discussed in this article, I will use the command-line Liquibase, as well as a simple CLI client for MySQL.
Liquibase also integrates well with Maven (like goal) or Spring (like bin, launched during context initialization).
')
Let's start with a very simple PERSON table, consisting only of the ID (primary key) and the name:
mysql> describe Person; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | bigint(20) | NO | PRI | NULL | auto_increment | | name | varchar(255) | NO | UNI | NULL | | +-------+--------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) Liquibase uses so-called “changesets” (changeset), an XML code for describing DDL statements. They make changelog files. The next changer will create a table (“createTable” tag) and two columns (“column” tag).
<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-2.0.xsd"> <changeset id="1" author="mueller@synyx.de" runonchange="true"> <createtable tablename="Person"> <column autoincrement="true" name="id" type="BIGINT"> <constraints nullable="false" primarykey="true"> </constraints> </column> <column name="name" type="VARCHAR(255)"> <constraints nullable="false"> </constraints> </column> </createtable> </changeset> </databasechangelog> Using this XML code, Liquibase will add the “Person” table. The command that does this from the command line interface is “update”:
./liquibase --url=jdbc:mysql://localhost:3306/liquiblog --driver=com.mysql.jdbc.Driver --username=root --password="" --changeLogFile=db.changelog-0.1.0.xml update Liquibase has built-in support for rolling back certain types of changesets, for example, “createTable”. If we call Liquibase via the command line with the argument “rollbackCount 1” instead of “update”, the last changeset will be rolled back: the PERSON table will be deleted.
Other types of changesets cannot be deleted automatically. For example, consider the following changesets that add data to PERSON (“insert” tag):
<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-2.0.xsd"> <changeset id="init-1" author="mueller@synyx.de"> <insert tablename="Person"> <column name="name" value="John Doe"> </column> </insert> <rollback> DELETE FROM Person WHERE name LIKE 'John Doe'; </rollback> </changeset> </databasechangelog> I manually added a rollback tag containing SQL statements that roll back changes in this changeset. This tag can contain both SQL statements and ordinary Liquibase tags.
Since we now have two XML changer files, I created a “master” file that imports the other files in the order necessary to get the correct database revision:
<databasechangelog xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.9" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemalocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9 http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd"> <include file="db.changelog-0.1.0.xml"></include> <include file="db.changelog-0.1.0.init.xml"></include> </databasechangelog> When calling the “update” command for each of the changesets, it is checked whether it was applied to the scheme. If the changeset has not yet been applied, it is executed. For this, Liquibase saves the data in the auxiliary table DATABASECHANGELOGS, which contains the changesets already applied, as well as their hash values. Hashes are used so that it is impossible to change the already completed changesets.
mysql> select id, md5sum, description from DATABASECHANGELOG; +--------+------------------------------------+--------------+ | id | md5sum | description | +--------+------------------------------------+--------------+ | 1 | 3:5a36f447e90b35c3802cb6fe16cb12a7 | Create Table | | init-1 | 3:43c29e0011ebfcfd9cfbbb8450179a41 | Insert Row | +--------+------------------------------------+--------------+ 2 rows in set (0.00 sec) Now that the simple example is working, let's try something more complicated: changing the schema that requires migrating the schema and updating the data. The PERSON table currently only has a column named NAME, and I want to split NAME into two columns - FIRSTNAME and LASTNAME. Before the start of database migration, I am going to put a Liquibase "tag" so that you can roll back all the changes to this tag in the future:
./liquibase --url=jdbc:mysql://localhost:3306/liquiblog --driver=com.mysql.jdbc.Driver --username=root --password="" --changeLogFile=changelog-master.xml tag liquiblog_0_1_0 I created a new changeset that adds two new columns:
<changeset id="1" author="mueller@synyx.de" runonchange="true"> <addcolumn tablename="Person"> <column name="firstname" type="VARCHAR(255)"> <constraints nullable="false"> </constraints> </column> <column name="lastname" type="VARCHAR(255)"> <constraints nullable="false"> </constraints> </column> </addcolumn> </changeset> And this time, Liquibase knows how to roll back this changeset, so that we can not add the rollback tag.
Now the PERSON table has two additional columns and we need to take care of the migration of existing data to the new scheme before we remove the obsolete NAME column. Since data manipulation is not supported by Liquibase out of the box, we need to use the “sql” tag to include native SQL in the changeset.
<changeset author="mueller@synyx.de" id="2"> <sql> UPDATE Person SET firstname = SUBSTRING_INDEX(name, ' ', 1); UPDATE Person SET lastname = SUBSTRING_INDEX(name, ' ', -1); </sql> <rollback> UPDATE Person SET firstname = ''; UPDATE Person SET lastname = ''; </rollback> </changeset> Note that the contents of the rollback tag seem redundant, but the tag itself is necessary due to the fact that Liquibase allows you to roll back only changesets:
- which implicitly have a “rollback” tag, for example “createTable”
- tag "rollback" was explicitly added
After launching Liquibase with the “update” option, the new changeset is applied to the schema: the created columns FIRSTNAME and LASTNAME already contain data.
Next, I want to get rid of the old NAME column.
<changeset id="3" author="mueller@synyx.de" runonchange="true"> <dropcolumn tablename="Person" columnname="name"> </dropcolumn> <rollback> <addcolumn tablename="Person"> <column name="name" type="VARCHAR(255)"> <constraints nullable="false"> </constraints> </column> </addcolumn> <sql> UPDATE Person SET name = CONCAT(firstname, CONCAT(' ', lastname)); </sql> </rollback> </changeset> The changeset itself is quite simple, since Liquibase supports deleting columns, but the rollback tag has become more complex:
- I re-add the old column NAME, using the standard tag "addColumn"
- I am using a SQL statement to recover data in this column.
Result of transformations:
mysql> select * from Person; +----+-----------+------------+ | id | firstname | lastname | +----+-----------+------------+ | 1 | John | Doe | +----+-----------+------------+ 1 rows in set (0.00 sec) Due to the fact that we initially marked the circuit with a tag, and also added instructions for rolling back the changes in all our changesets, we can roll back the modifications to the database schema without losing data! Calling ...
./liquibase --url=jdbc:mysql://localhost:3306/liquiblog --driver=com.mysql.jdbc.Driver --username=root --password="" --changeLogFile=changelog-master.xml rollback liquiblog_0_1_0 ... we are back to the opidinal state of the database schema!
The example of splitting / merging lines of the PERSON name is somewhat contrived, but the same principle can be applied to more serious data changes.
The idea for this post, I stumbled upon working on the division of the existing domain class (corresponding to one table) into three parts: an abstract base class and two subclasses, taking into account the need to preserve the integrity of the data.
Source: https://habr.com/ru/post/178665/
All Articles