Liquibase

Introduction

Liquibase is an open-source database-independent library for tracking, managing and applying database schema changes. It is intended to allow easier tracking of database changes.

As of Liquibase 3.8.x, it does not support H2 compatibility mode.

Therefore, do not use 'MODE' in the JDBC datasource URL. E.g: The following will not work as expected:

jdbc:h2:mem:test_db;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE

Subpages highlighted

• Examples (liquibase)

https://sites.google.com/site/pawneecity/liquibase/samples-liquibase

• Spring Data (liquibase)

https://sites.google.com/site/pawneecity/liquibase/spring-data-liquibase

Reference

H2 Keywords / Reserved Words

http://www.h2database.com/html/advanced.html#keywords

Data types

Liquibase types > https://docs.liquibase.com/change-types/nested-tags/column.html

Liquibase type classes > https://github.com/liquibase/liquibase/tree/master/liquibase-core/src/main/java/liquibase/datatype/core

JDK 11 types > https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/Types.html

To help make scripts database-independent, the following generic data types will be converted to the correct database implementation:

• bigint

• blob

• boolean

• clob [h2: CHARACTER LARGE OBJECT, postgres: text]

• currency

• date

• datetime(scale)

• time

• uuid

Also, specifying a java.sql.Types.* type will be converted to the correct type as well. Examples:

• java.sql.Types.ARRAY

• java.sql.Types.BIGINT [Java: long]

• java.sql.Types.BINARY [Java: byte[]]

• java.sql.Types.BIT  [Java: boolean]

• java.sql.Types.BLOB

• java.sql.Types.BOOLEAN

• java.sql.Types.CHAR(64) [Java: String]

• java.sql.Types.CLOB

• java.sql.Types.DATALINK

• java.sql.Types.DATE [Java: java.sql.Date]

• java.sql.Types.DECIMAL(p, s)  [Java: java.math.BigDecimal]

• java.sql.Types.DISTINCT

• java.sql.Types.DOUBLE [Java: double, oracle(since 4.21.0): DOUBLE PRECISION]

• java.sql.Types.FLOAT [Java double]

• java.sql.Types.INTEGER [Java int]

• java.sql.Types.JAVA_OBJECT

• java.sql.Types.LONGNVARCHAR

• java.sql.Types.LONGVARBINARY [Java: byte[]]

• java.sql.Types.LONGVARCHAR [Java: String]

• java.sql.Types.NCHAR

• java.sql.Types.NCLOB

• java.sql.Types.NUMERIC(p, s)  [Java: java.math.BigDecimal]

• java.sql.Types.NVARCHAR

• java.sql.Types.REAL [Java float]

• java.sql.Types.REF

• java.sql.Types.REF_CURSOR

• java.sql.Types.ROWID

• jata.sql.Types.SMALLINT [Java: short]

• java.sql.Types.SQLXML

• java.sql.Types.STRUCT

• java.sql.Types.TIME [Java: java.sql.Time]

• java.sql.Types.TIME_WITH_TIMEZONE

• java.sql.Types.TIMESTAMP(fractional precision) [Java: java.sql.Timestamp]

• java.sql.Types.TIMESTAMP_WITH_TIMEZONE

• java.sql.Types.TINYINT [Java byte]

• java.sql.Types.VARBINARY [Java byte[]]

• java.sql.Types.VARCHAR(1024) [Java: String, h2: CHARACTER VARYING, postgres: varchar]

Notes:

• Postgres types varchar and text, under the hood, have no difference (both are variable length array)

See also: https://stackoverflow.com/a/4849030/1323562

Sample configuration

In this sample, we'll create the configuration needed in an Spring Boot project with Maven and a PostgreSQL database.

The following sections assume the application is already using the database connection via Sprint Boot configuration [src/main/resources/application.properties].

Maven pom.xml

In project.properties, declare the Liquibase version to be used

<liquibase.version>3.5.5</liquibase.version>

In section project.dependencies, add the Liquibase dependency

        <dependency>

            <groupId>org.liquibase</groupId>

            <artifactId>liquibase-core</artifactId>

            <version>${liquibase.version}</version>

        </dependency>

        <!-- Liquibase SessionLock for 4.x and 3.7.x (remove once it's supported by core)-->

        <dependency>

          <groupId>com.github.blagerweij</groupId>

          <artifactId>liquibase-sessionlock</artifactId>

          <version>1.6.2</version>

        </dependency>

(optional) In section project.build.plugins, the Liquibase plugin makes easy to generate database changes files

        <plugin>

                <groupId>org.liquibase</groupId>

                <artifactId>liquibase-maven-plugin</artifactId>

                <version>${liquibase.version}</version>

                <configuration>

                    <!-- By default, keep skip=true unless you need to run it manually in your development box with your own database -->

                    <skip>true</skip>

                    <outputChangeLogFile>src/main/resources/db/changelog/changes/db.change-GENERATED.yaml</outputChangeLogFile>

                    <!-- if needed, update connection values to your local development DB (this is not TEST, PRE nor PRO) -->

                    <url>jdbc:postgresql://127.0.0.1:5432/my_db</url>

                    <username>postgres</username>

                    <password>postgres</password>

                </configuration>      

        </plugin>

Generate the initial change

The Liquibase dependency looks for a master yaml file named "db.changelog-master.yaml" (at least in Spring Boot) and located in directory:

src/main/resources/db/changelog/

In this master file, let's only indicate that Liquibase must process all changes in files located in subdirectory 'changes".

db.changelog-master.xml

Note: Verified working fine with docker since Liquibase 4.0.0

<?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.8.xsd">  

    <includeAll  path="db/changes/"/>  

</databaseChangeLog>

db.changelog-master.yaml

Remark: It stopped working with docker since Liquibase 4.0.0 (use the .xml alternative instead)

databaseChangeLog:

- includeAll:

    path: changes

    relativeToChangelogFile: true

And a sample file inside the 'changes' subdirectory.

db.change-001.yaml

databaseChangeLog:

- changeSet:

    id: 1

    author: baromojm (generated)

    changes:

    - createTable:

        columns:

        - column:

            autoIncrement: true

            constraints:

              primaryKey: true

              primaryKeyName: applicant_pkey

            name: id

            type: BIGSERIAL

        - column:

            name: email

            type: VARCHAR(255)

        - column:

            name: name

            type: VARCHAR(255)

        - column:

            name: phone_number

            type: VARCHAR(255)

        tableName: applicant

For generating the initial file for the 'changes' subdirectory, the easiest way seems to be executing the Liquibase plugin we've previously configured in pom.xml

mvn liquibase:generateChangeLog

Note: You'll need to rename this file and edit it to suit your specific needs.

         The idea is to create a new file in 'changes' with the differences from previous version every new release.

Keywords / Reserved Words

There is a list of keywords that can't be used as identifiers (table names, column names and so on), unless they are quoted (surrounded with double quotes).

Sample at an JPA entity:

  /** varchar(128) */

  @Column(name = "\"key\"") //'key' and 'value' are H2 keywords

  @Id

  private String key;

  /** varchar */

  @Column(name = "\"value\"") //'key' and 'value' are H2 keywords

  @Nullable

  private String value;

Troubleshooting

liquibase.lockservice : Waiting for changelog lock....

Reference > https://docs.liquibase.com/concepts/tracking-tables/databasechangeloglock-table.html

Unlock and restart the instance:

UPDATE DATABASECHANGELOGLOCK SET LOCKED=0