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
Examples (liquibase)
https://sites.google.com/site/pawneecity/liquibase/samples-liquibase
Spring Data (liquibase)
https://sites.google.com/site/pawneecity/liquibase/spring-data-liquibase
https://docs.liquibase.com/change-types/home.html
Liquibase Data Type Handling > https://docs.liquibase.com/concepts/data-type-handling.html
Liquibase Change Types column > 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
char(n) [default 1]
clob [h2: CHARACTER LARGE OBJECT, postgres: text]
currency
date
datetime(scale)
decimal
double
float
int
mediumint
nchar
number
nvarchar
smallint
time
timestamp
tinyint
uuid
varchar [h2: ?, postgres: CHARACTER ?]
xml
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
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].
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>
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.
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;
Reference > https://docs.liquibase.com/concepts/tracking-tables/databasechangeloglock-table.html
Unlock and restart the instance:
UPDATE DATABASECHANGELOGLOCK SET LOCKED=0