In this article, you will know what database migrations are, when and on what projects they are configured, and how they work. In addition, we will analyze step by step how to set up migrations using the Liquibase open library.
Database migrations – what is it and what is it for
Database migration is like a version control system for your database schema. It allows developers to change the structure of the database, inform other team members about these changes and be aware of updates themselves, as well as track the history of changes.
Why database migrations are needed and how they make life easier for developers
- As the application develops, the database schema changes. New tables and columns are added. Migrations make it easier to track these changes.
- Modern projects often have several stands – development, testing, production and others. There is a problem of database synchronization – you need to transfer changes to stands consistently and without conflicts. Migrations help solving this problem.
- Modern projects work according to the Agile methodology, and it is difficult to determine the structure of the database at the start. It develops along with the project from sprint to sprint. Therefore, automated database refactoring should be the same mandatory tool as refactoring of any other components.
- Since migrations are part of the project’s source code, changes to the database structure can be approved or rejected during code reviews before they hit the release grid.
Most often, Liquibase or Flyway tools are used to set up database migrations in Java applications. They are both written in Java and easily integrate with maven, gradle and other build tools, allowing for greater customization. They offer a Java API and can be extended. The main difference between them is the formats where the tool records changes. Flyway only uses SQL, while Liquibase also works with XML, YAML or JSON.
How migrations work
- Changes that a developer makes to the database schema are written in text-based configuration files understandable by Liquibase or Flyway. These changes are converted into SQL queries. With their help, the tool accesses the database and makes the necessary changes.
- All changes we make are stored in separate files. They are often referred to as changelogs.
- In changelog files, changes are represented as changesets, so-called savepoints. A changeset can store one or more database changes. Each changeset is uniquely identified.
- When you first run the migration, Liquibase or Flyway creates a table in the database schema to keep track of applied changesets and then works with it automatically. If the change has already been applied, it will not be re-executed.
Liquibase library: what is it and what projects is it suitable for
Liquibase is an open database independent library for tracking, managing and applying database schema changes. It supports the vast majority of databases, including PostgreSQL, MySQL, Oracle, Sybase, HSQL, Apache Derby. Works with XML, YAML, JSON, SQL formats.
Its advantages: the Liquibase library provides more features out of the box, unlike Flyway – undoing changes, auto-generation of migrations, etc. It has dry-run, that is, you can see which SQL queries will be executed.
Unlike Flyway, which only supports migration scripts in SQL and Java formats, Liquibase is a universal tool. It allows you to roll the same migrations to any database and abstract from SQL. This library is more suitable for projects where you need to work with different environments and DBMS.
Liquibase should not be used if your project has a simple database structure as well as if you need the ability to change the database schema using fully custom SQL or Java code. In this case, it is more convenient to engage with Flyway.
Setting up migration with Liquibase
How to set up a migration of the out of the box database
- Connect the Liquibase library, that is, add a dependency in your project
- Write the zero version of the database, that is, use the scripts to describe the database that has already been deployed
- All subsequent changes in the database structure are to be made only through scripts.
How to create a migration for a new database
A link to the Bitbucket project
Step 1. To work with the library, we need to add a dependency to the pom.xml file. Below is an example of how to do this.
‹dependency> ‹groupId>org.liquibase‹/groupId> ‹artifactId>liquibase-core‹/artifactId> ‹/dependency>
Step 2. Now we need to create the Liquibase script itself. As already mentioned, you can use XML, YAML, JSON or SQL formats for this task. We will use XML as the most descriptive one. Its advantages are that it allows you to automatically substitute the names of tags and attributes in the file.
First, we create the main executable changelog.xml file, where we will add the paths to the scripts. In changelog.xml we insert a standard empty template using the path: src/main/resources/db/changelog/changelog.xml. We will supplement it later.
‹?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">
‹!—We will write the script here later -->
‹/databaseChangeLog>
Step 3. Now let’s write scripts to create tables. Let’s take the database schema below as a basis.
Let’s describe the changeset for creating the genre table. To do this, create a file using the path: src/main/resources/db/changelog/create-changeset-genre-table.xml. An example is below:
‹?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">
‹changeSet id="create_table_genre" author="mediaSoft">
‹!--Put the creation of the genre table -->
‹createTable tableName="genre">
‹!—Create fields -->
‹column autoIncrement="true" name="genre_id" type="bigint">
‹constraints primaryKey="true" nullable="false"/>
‹/column>
‹column name="genre_name" type="varchar(64)">
‹constraints nullable="false" unique="true"/>
‹/column>
‹/createTable>
‹/changeSet>
‹/databaseChangeLog>
Let’s recall that a changeset is something like an analogue of a commit in version control systems. It may contain one or more database changes. It is good practice to have one change for one changeset – this can be creating or changing a table, deleting an object, adding an index or data to a table, etc. Each changeset must have two identifiers: unique id and author. Other tags can be viewed on the official website.
Now let’s add data to the genre table using the insert tag. To do this, create a new file with the path src/main/resources/db/changelog/insert-changeset-genre-table.xml and add a new changeset:
‹?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">
‹!-- Adding 4 new genres -->
‹changeSet id="insert-into-genre" author="mediaSoft">
‹insert tableName="genre">
‹column name="genre_name" value="Roman"/>
‹/insert>
‹insert tableName="genre">
‹column name="genre_name" value="Poem"/>
‹/insert>
‹insert tableName="genre">
‹column name="genre_name" value="Tale"/>
‹/insert>
‹insert tableName="genre">
‹column name="genre_name" value="Epos"/>
‹/insert>
‹/changeSet>
‹/databaseChangeLog>
Next, we create a changeset to add the book table with the way: src/main/resources/db/changelog/create-changeset-book-table.xml.
‹?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">
‹changeSet id="create_table_book" author="mediaSoft">
‹createTable tableName="book">
‹column autoIncrement="true" name="book_id" type="bigint">
‹constraints primaryKey="true" nullable="false"/>
‹/column>
‹column name="title" type="varchar(100)">
‹constraints nullable="false"/>
‹/column>
‹column name="genre_id" type="bigint">
‹constraints nullable="false"/>
‹/column>
‹column name="price" type="double">
‹constraints nullable="false"/>
‹/column>
‹column name="amount" type="integer">
‹constraints nullable="false"/>
‹/column>
‹/createTable>
‹/changeSet>
‹/databaseChangeLog>
Step 4. Now we need to link our tables. To do this, we describe the creation of a foreign key in changeset-genre-table.xml:
‹addForeignKeyConstraint baseColumnNames="genre_id"
baseTableName="book"
constraintName="fk_author_id"
referencedColumnNames="genre_id"
referencedTableName="genre"/>
Step 5. Now let’s write a script to fill the book table. Let’s create a new file with the path: src/main/resources/db/changelog/insert-changeset-book-table.xml
Let’s use the capabilities of Liquibase and check the changeset before executing it to see if the genre table contains the genre we need.
To do this, we declare the preConditions tag. In this tag, we indicate the result of the check – it is written in the expectedResult parameter. When checking, we will use SQL, for this we declare the sqlCheck tag.
‹?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">
‹!—Let’s add 1 book -->
‹changeSet id="insert-into-book" author="mediaSoft">
‹preConditions onFail="WARN">
‹sqlCheck expectedResult="4">select count(*) from genre‹/sqlCheck>
‹/preConditions>
‹comment>We check that our table contains all 4 genres before adding a book‹/comment>
‹insert tableName="book">
‹column name="title" value="The Daughter of the Commandant"/>
‹column name="genre_id" value="0"/>
‹column name="price" value="1200"/>
‹column name="amount" value="14"/>
‹/insert>
‹/changeSet>
‹/databaseChangeLog>
Step 6. Now import our migrations in our root changelog.xml file:
‹include file="changeset-genre-table.xml" relativeToChangelogFile="true"/> ‹include file="changeset-book-table.xml" relativeToChangelogFile="true"/> ‹include file="insert-changeset-genre-table.xml" relativeToChangelogFile="true"/> ‹include file="insert-changeset-book-table.xml" relativeToChangelogFile="true"/>
Key aspect: first, create a table which you will bind the elements to, and only then the one you will bind. Otherwise, Liquibase will not understand what needs to be connected to what and will show an error. In our case, we first create genre, and only then book with its foreign key.
Step 7. Now let’s start our project. Liquibase uses our scripts and will create all the tables. As a result, it should all look like this:
Step 8. Now let’s add the author field to the already created book table. To do this, we need to add a new changeset in changeset-book-table.xml and use the addcolumn tag.
It is strictly forbidden to change and add something to an existing changeset that got into the general branch in the version control system. Most likely this changeset has already been executed on one of the stands, and its checksum got into the databaseChangelog table. After changing the changeset, its checksum will change. And Liquibase will give an error when checking it. Therefore, if you need to make changes to objects created in old changesets, create a new one.
‹changeSet id="add_colum_author" author="mediaSoft" runOnChange="true"> ‹addColumn tableName="book"> ‹column name="author" type="VARCHAR(255)"> ‹constraints nullable="false"> ‹/constraints> ‹/column> ‹/addColumn> ‹/changeset>
After starting the project, we will see that a new author field has been added to our table.
Step 9. Now that we have figured out how to start Liquibase, we can move on to rolling back the changes.
Many operations can be rolled back by Liquibase on its own, such as creating a table and adding a column. For some changesets, you need to write rollback scripts. We will use automatic rollback. Other methods are described on the official Liquibase website. It is important to understand that rolling back changes results in data loss.
We will use rollback by tag as it is the most convenient and easiest way. In this case, the tag is a kind of checkpoint by which Liquibase determines that this changeset was the last one in our changes. You can return to this checkpoint at any time if we do something wrong.
To install the tag, run the command in the terminal:
mvn liquibase:tag -Dliquibase.tag=tag_1.
The tag is always set on the last changeset. Now if we look at the databaseChangelog table, we will see that tag_1 is attached to the last changeset. We can roll back to it if necessary.
Let’s check the tag. To do this, we create a new changeset to create the comics table along the path src/main/resources/db/changelog/create-changeset-comics-table.xml. Let’s fill this file:
‹?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">
‹changeSet id="create_table_comics" author="mediaSoft">
‹!—Setting the creation of the comics table-->
‹createTable tableName="comics">
‹!—Creating fields -->
‹column autoIncrement="true" name="comics_id" type="bigint">
‹constraints primaryKey="true" nullable="false"/>
‹/column>
‹column name="comics_name" type="varchar(64)">
‹constraints nullable="false" unique="true"/>
‹/column>
‹/createTable>
‹/changeSet>
‹/databaseChangeLog>
Let’s add our new file to changelog.xml, run it and make sure the table is added to the database.
Now, if we want to delete the comics table, we need to run this command in the terminal, adding our tag at the end of the command:
mvn liquibase:tag –Dliquibase.tag=tag_1.
After executing the command, we will see that the table has returned to its original state.
A few more tips for working with Liquibase
Use XML, not YAML or JSON
XML enables auto-completion in the IDE and automatically checks the formal correctness of the document against the data schema. So it’s better to use XML when writing, and to refer to other formats if you want to quickly test the migration.
Don’t use Liquibase’s automatic generation
Liquibase is able to generate changesets itself, based on an existing database, and show the changes that have occurred in the database compared to the existing changesets. These amenities should be treated with care. Yes, they allow you to get rid of the routine, but even in this case, all changesets must be viewed and checked. After all, you don’t want to fix a logical error generated by the utility and left for production, right?
Better have many small changesets than one but big
One changeset – one atomic operation. If there are a lot of changes, it is better to keep the operations smaller. For each small operation, we can write our own separate check. Such a change will be easier to roll back not affecting the rest. It will also be easier for another developer on the team to understand it.
Work on operations in isolation, don’t unnecessarily add multiple changes to one changeset, think about the content of the rollback tag. It is better to have a lot of simple and even similar changesets than one complex and slurred one.
Scripts
It is good practice to store the scripts for each table separately and then include them in the root file that will run them. As in SQL, it is important to keep up with writing the order of the queries, since they are run one after another.
Check the link to the official guide with the best practices for using Liquibase.
Stay tuned for new posts on your favorite topics
Subscribe to the tags you are interested in to follow new posts and keep abreast of events.
