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 at 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.

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.

pom.xml

 <?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, you 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 launch update 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). They can include both changes in the structure and changes in the data. 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

Launch contexts

For more convenient management of various configurations, such as development / production, contexts can be used. 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

Roll back 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 not fully corresponding to the original, for example, used domains or inheritance will need to be added separately (see Restrictions).

Preservation of the scheme 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.

Inheritance and 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

Applications

Project structure

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

How to add liquibase to an existing project

How base changes work

More about update

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

More about custom SQL

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

Source: https://habr.com/ru/post/436994/