Liquibase and Maven

Introduction

Liquibase is a database version control system, mainly for the structure and, to a lesser extent, the content of the database. At the same time, the description of the database on the one hand is rather abstract and allows using different DBMS on the lower level, and on the other hand, you can always switch to the SQL dialect of a specific DBMS, which is quite flexible. Liquibase is a well-established open source project and is actively used outside of its native Java environment and does not require in-depth Java knowledge to work. The XML format has historically been used as a description of the base structure and base changes, but now YAML and JSON are supported in parallel.

In this article we will summarize the experience of previous generations and focus on working with Liquibase using Maven. As a test operating system, we will use Ubuntu.

Other Liquibase articles

• https://habr.com/ru/post/179425/
• https://habr.com/en/en/post/178665/
• https://habr.com/ru/post/333762/
• https://habr.com/ru/post/251617/
• https://habr.com/ru/post/251617/

Setting up the environment

You can run Liquibase in several ways, but Maven or Gradle is most convenient to use.

sudo apt install maven
mvn -version

The pom.xml here is the Makefile - it already contains all the necessary dependencies, settings and profiles.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.test.db</groupId>
    <artifactId>db</artifactId>
    <version>1.0.0</version>
    <name>db</name>
    <description>Test Database</description>
    <packaging>pom</packaging>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <slf4j.version>1.7.24</slf4j.version>
        <logback.version>1.2.3</logback.version>
        <liquibase.version>3.6.2</liquibase.version>
        <postgresql.version>42.2.5</postgresql.version>
        <snakeyaml.version>1.23</snakeyaml.version>
    </properties>
    <dependencies>
        <!--Logging-->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>${slf4j.version}</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>log4j-over-slf4j</artifactId>
            <version>${slf4j.version}</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>jcl-over-slf4j</artifactId>
            <version>${slf4j.version}</version>
        </dependency>
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>${logback.version}</version>
        </dependency>
        <!--JDBC drivers-->
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>${postgresql.version}</version>
        </dependency>
        <dependency>
            <groupId>org.liquibase</groupId>
            <artifactId>liquibase-core</artifactId>
            <version>${liquibase.version}</version>
        </dependency>
        <dependency>
            <groupId>org.yaml</groupId>
            <artifactId>snakeyaml</artifactId>
            <version>${snakeyaml.version}</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.liquibase</groupId>
                <artifactId>liquibase-maven-plugin</artifactId>
                <version>${liquibase.version}</version>
                <configuration>
                    <propertyFile>${profile.propertyFile}</propertyFile>
                    <changeLogFile>${profile.changeLogFile}</changeLogFile>
                    <dataDir>${profile.dataDir}</dataDir>
                    <!-- log  -->
                    <verbose>${profile.verbose}</verbose>
                    <logging>${profile.logging}</logging>
                    <promptOnNonLocalDatabase>false</promptOnNonLocalDatabase>
                </configuration>
            </plugin>
        </plugins>
    </build>
    <profiles>
      <!-- Development settings, -Denv=dev -->
      <profile>
        <id>dev</id>
        <activation>
          <property>
            <name>env</name>
            <value>dev</value>
          </property>
        </activation>
        <properties>
          <profile.propertyFile>dev/liquibase.properties</profile.propertyFile>
          <profile.changeLogFile>dev/master.xml</profile.changeLogFile>
          <profile.dataDir>dev/data</profile.dataDir>
          <profile.verbose>true</profile.verbose>
          <profile.logging>debug</profile.logging>
        </properties>
      </profile>
      <!-- Production settings, -Denv=prod -->
      <profile>
        <id>prod</id>
        <activation>
          <property>
            <name>env</name>
            <value>prod</value>
          </property>
        </activation>
        <properties>
          <profile.propertyFile>prod/liquibase.properties</profile.propertyFile>
          <profile.changeLogFile>prod/master.xml</profile.changeLogFile>
          <profile.dataDir>prod/data</profile.dataDir>
          <profile.verbose>false</profile.verbose>
          <profile.logging>info</profile.logging>
        </properties>
      </profile>
    </profiles>
</project>

We launch update

After we have done pom.xml, we can run an update of the database - the command liquibase: update.

For this we need:

• liquibase.properties file with database connection settings (login / password and possibly other parameters)
• xml file with database changes
• sh base update startup script

File with database connection settings

liquibase.properties

username=test
password=test
referenceUsername=test
#можно задавать и другие параметры
#url=jdbc:postgresql://dev/test
#referenceUrl=jdbc:postgresql://dev/test_reference

File with database changes

The basic concept of liquibase are the so-called base changes (changesets). These can include both structure changes and data changes. To control the changes applied, liquibase uses the databasechangelog and databasechangeloglock tables .

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
    <changeSet context="legacy" author="author (generated)" id="1">
        <createTable tableName="test">
            <column autoIncrement="true" name="id" type="SERIAL">
                <constraints nullable="false"/>
            </column>
            <column name="user_name" type="VARCHAR(255)"/>
            <column name="preferences" type="TEXT"/>
        </createTable>
    </changeSet>
</databaseChangeLog>

Script run update database

Here liquibase is executed: update for the dev profile and the database from liquibase.url, which is specified in the standard JDBC format. After the update, the table specified in changeSet and two service tables databasechangelog and databasechangeloglock appear in the database .

#!/usr/bin/env bash
mvn liquibase:update\
    -Denv=dev\
    -Dliquibase.url="jdbc:postgresql://dev/test?prepareThreshold=0&stringtype=unspecified"

SQL generation without database update

Sometimes it is required to look at the contents of created queries before launching changes. The liquibase: updateSQL and liquibase: rollbackSQL commands are used for this.

#!/usr/bin/env bash
mvn liquibase:updateSQL\
    -Denv=dev\
    -Dliquibase.url="jdbc:postgresql://dev/test?prepareThreshold=0&stringtype=unspecified" > /tmp/script.sql

Read more about changeSet

Changes can be in different formats, including normal sql or it is in a separate file.

Each change can include a rollback section that allows you to roll back changes with the liquibase: rollback command . In addition, for marking changes, for example, for a more convenient rollback there, you can use tagDatabase .

Plain format

<changeSet context="legacy" author="author (generated)" id="1">
    <createTable tableName="test">
        <column autoIncrement="true" name="id" type="SERIAL">
            <constraints primaryKey="true" primaryKeyName="test_pkey"/>
        </column>
        <column name="c1" type="VARCHAR(255)"/>
        <column name="c2" type="INTEGER"/>
        <column name="c3" type="SMALLINT"/>
        <column name="c4" type="VARCHAR(255)"/>
        <column name="c5" type="TEXT"/>
        <column name="c6" type="VARCHAR(255)"/>
    </createTable>
</changeSet>

Embedded SQL

<changeSet context="legacy" author="author" id="1-domain-some-domain">
    <sql>
        CREATE DOMAIN public.some_domain AS bigint;
        ALTER DOMAIN public.some_domain OWNER TO test;
    </sql>
    <rollback>
        DROP DOMAIN public.some_domain;
    </rollback>
</changeSet>

SQL file

<changeSet context="legacy" author="author" id="1-user">
     <sqlFile dbms="postgresql" path="sql/some.sql" relativeToChangelogFile="true" />
     <rollback> delete from "some"; </rollback>
</changeSet>

Tags

<changeSet context="legacy" author="author" id="1-initial-changeset">
    <tagDatabase tag="initial"/>
</changeSet>

Launch contexts

Contexts can be used to more easily manage various configurations, such as development / production. The context is specified in the changeSet context attribute and then launched by Maven with the -Dcontexts parameter.

Change with context

<changeSet context="legacy" author="author" id="1-initial-changeset">
    <tagDatabase tag="initial"/>
</changeSet>

Run changes by context

#!/usr/bin/env bash
mvn liquibase:update\
    -Denv=dev\
    -Dliquibase.url="jdbc:postgresql://dev/test?prepareThreshold=0&stringtype=unspecified"\
    -Dliquibase.contexts=non-legacy

Rollback changes

The operation is a reverse upgrade, in most cases, is supported automatically. For others, the task is possible through the rollback section. Run by the liquibase: rollback command.

Rollback change

<changeSet context="legacy" author="author" id="1-domain-some-domain">
    <sql>
        CREATE DOMAIN public.some_domain AS bigint;
        ALTER DOMAIN public.some_domain OWNER TO test;
    </sql>
    <rollback>
        DROP DOMAIN public.some_domain;
    </rollback>
</changeSet>

Starting a rollback

#!/usr/bin/env bash
mvn liquibase:update\
    -Denv=dev\
    -Dliquibase.url="jdbc:postgresql://dev/test?prepareThreshold=0&stringtype=unspecified"\
    -Dliquibase.contexts=non-legacy

Comparison

In development, it is convenient to use to compare two existing bases for changes. In the settings (or launch parameters) you will need to add a link to the reference database and data to access it.

liquibase.properties

referenceUsername=test
referenceUrl=jdbc:postgresql://dev/test_reference

Comparison schemes

Comparing schemes url and referenceUrl.

#!/usr/bin/env bash
mvn liquibase:diff\
    -Denv=dev\
    -Dliquibase.referenceUrl="jdbc:postgresql://dev/test?prepareThreshold=0"\
    -Dliquibase.url="jdbc:postgresql://dev/test_reference?prepareThreshold=0"\
    -Dliquibase.diffChangeLogFile=dev/diff.xml

Saving schema

It is also useful to keep the current database schema, with or without data. It must be borne in mind that Liquibase keeps the scheme that does not fully comply with the original, for example, used domains or inheritance will need to be added separately (see Restrictions).

Saving schema without data

Preservation of the existing base scheme.

#!/usr/bin/env bash
mvn liquibase:generateChangeLog\
    -Denv=dev\
    -Dliquibase.url="jdbc:postgresql://dev/test_reference?prepareThreshold=0"\
    -Dliquibase.outputChangeLogFile=dev/changelog.xml

Saving a schema with data

Preservation of the existing database schema.

#!/usr/bin/env bash
mvn liquibase:generateChangeLog\
    -Denv=dev\
    -Dliquibase.url="jdbc:postgresql://dev/test_reference?prepareThreshold=0"\
    -Dliquibase.outputChangeLogFile=dev/changelog.xml

Limitations and problems

Work with binary data in the database

There are certain problems with unloading, comparing and using binary data, in particular the problem with the generation of changes.

• https://liquibase.jira.com/browse/CORE-2650
• https://liquibase.jira.com/browse/CORE-2906

Inheritance and common columns

• http://forum.liquibase.org/topic/postgresql-subtable-via-inherits
• https://stackoverflow.com/questions/25840467/liquibase-common-columns

Source

• https://github.com/liquibase/liquibase

Alternative solutions

Flyway

Along with Liquibase it is popular in the Java community - http://flywaydb.org/documentation

Sqitch

Perl Analog - http://sqitch.org

FluentMigrator

Analog for .Net - https://github.com/schambers/fluentmigrator

DBGeni

Analog for Ruby - http://dbgeni.appsintheopen.com/manual.html

Applications

Project structure

pom.xml - maven makefile
  dev
    liquibase.properties - login/password etc
    master.xml - changesets

How to add liquibase to an existing project

• https://www.liquibase.org/documentation/existing_project.html
• https://www.liquibase.org/documentation/contexts.html

How base changes work

• https://www.liquibase.org/documentation/changeset.html
• https://www.liquibase.org/documentation/databasechangelog_table.html

More on format changes

• http://www.liquibase.org/documentation/json_format.html
• https://www.liquibase.org/documentation/changes/sql.html
• https://www.liquibase.org/documentation/changes/sql_file.html
• https://www.liquibase.org/documentation/column.html

More about update

• https://www.liquibase.org/documentation/maven/generated/update-mojo.html

More on generating changes.

• https://www.liquibase.org/documentation/maven/generated/generateChangeLog-mojo.html

More about custom SQL

• http://www.liquibase.org/documentation/modify_sql.html
• https://stackoverflow.com/questions/28240068/create-column-of-type-double-precision-with-liquibase

Handling database-specific data types

<createTable tableName="t_name">
...
<column name="doubleArray" type="DOUBLE_ARRAY"/>
...
</createTable>
<modifySql dbms="postgresql">
<replace replace="DOUBLE_ARRAY" with="double precision[][]"/>
</modifySql>

Other

• https://news.ycombinator.com/item?id=10145933