Setup
Create a new java project
Add liquibase.properties
changeLogFile: dbchangelog.xml url: jdbc:mariadb://localhost:3306/policy username: policy password: *****
Add dbchangelog.xml
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:pro="http://www.liquibase.org/xml/ns/pro"
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">
<changeSet id="policy-1" author="policy">
<preConditions onFail="HALT">
<sqlCheck expectedResult="1">SELECT count(1) FROM information_schema.tables
WHERE table_name = 'PDPSTATISTICS'
</sqlCheck>
</preConditions>
<sql dbms="mariadb"
endDelimiter=";"
splitStatements="true"
stripComments="true">
INSERT INTO PDPSTATISTICS
(ID, NAME, VERSION, PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT,
POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT,
POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP)
VALUES (3, 'TestName', '1.0.0', 'TestGroup', 'TestSubGroup', 1, 1, 1, 1, 1, 1, now())
</sql>
<rollback>
DELETE FROM PDPSTATISTICS WHERE ID=3
</rollback>
</changeSet>
</databaseChangeLog>
Add pom.xml
<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.liquibase.mariadb.app</groupId>
<artifactId>LiquibaseMariadbp</artifactId>
<version>1.0-SNAPSHOT</version>
<build>
<pluginManagement>
<plugins>
<plugin>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-maven-plugin</artifactId>
<version>3.8.0</version>
<configuration>
<propertyFile>liquibase.properties</propertyFile>
</configuration>
<dependencies>
<dependency>
<groupId>org.mariadb.jdbc</groupId>
<artifactId>mariadb-java-client</artifactId>
<version>2.7.3</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>5.4.6.Final</version>
</dependency>
</dependencies>
</plugin>
</plugins>
</pluginManagement>
</build>
</project>
Test 1: Update database with changeLog in XML format
From the command line in the project directory
mvn package
mvn liquibase:update
Check you database, a new record has been added to pdpstatistics
[INFO] SELECT * FROM DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
[INFO] SELECT count(1) FROM information_schema.tables
WHERE table_name = 'PDPSTATISTICS'
[INFO] INSERT INTO PDPSTATISTICS
(ID, NAME, VERSION, PDPGROUPNAME, PDPSUBGROUPNAME, POLICYDEPLOYCOUNT, POLICYDEPLOYFAILCOUNT,
POLICYDEPLOYSUCCESSCOUNT, POLICYEXECUTEDCOUNT, POLICYEXECUTEDFAILCOUNT,
POLICYEXECUTEDSUCCESSCOUNT, TIMESTAMP)
VALUES (3, 'TestName', '1.0.0', 'TestGroup', 'TestSubGroup', 1, 1, 1, 1, 1, 1, now())
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog.xml::policy-1::policy ran successfully in 7ms
Test 2: Rollback update
mvn liquibase:rollback -Dliquibase.rollbackCount=1
new record has been removed has been dropped
[INFO] SELECT * FROM DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
[INFO] Rolling Back Changeset:dbchangelog.xml::policy-1::policy
[INFO] DELETE FROM PDPSTATISTICS WHERE ID=3
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = 'policy-1' AND AUTHOR = 'policy' AND FILENAME = 'dbchangelog.xml'
Test 3: Generate database dump chageLog in YAML format
Add the following 2 lines to liquibase.properties
outputChangeLogFile: dbchangelog.mariadb.yaml
diffTypes: catalog,tables,views,columns,indexes,foreignkeys,primarykeys,uniqueconstraints,data,storedprocedure
mvn liquibase:generateChangeLog
The file dbchangelog.mariadb.yaml will contain the DDL for your database in YAML format
Test 4: Generate database dump chageLog in SQL format
change outputChangeLogFile: dbchangelog.mariadb.yaml to outputChangeLogFile: dbchangelog.mariadb.sql
The file dbchangelog.mariadb.sql will contain the DDL for your database in SQL format
Test 5: Update database with changeLog in YAML format
Add dbchangelog.yaml
databaseChangeLog:
- changeSet:
id: policy-2
author: admin
changes:
- createTable:
tableName: JpaPdpPolicyDeploymentAudit
columns:
- column:
name: id
type: int
autoIncrement: true
constraints:
primaryKey: true
nullable: false
- column:
constraints:
primaryKey: true
nullable: false
name: name
type: varchar(120)
- column:
name: version
type: varchar(20)
constraints:
primaryKey: true
nullable: false
- column:
name: pdpGroup
type: varchar(255)
constraints:
nullable: true
- column:
name: pdpType
type: varchar(255)
constraints:
nullable: true
- column:
name: action
type: integer
constraints:
nullable: true
- column:
name: timeStamp
type: datetime
constraints:
nullable: true
- changeSet:
id: policy-3
author: admin
changes:
- addColumn:
tableName: JpaPdpPolicyDeploymentAudit
columns:
- column:
name: changedByUser
type: varchar(255)
constraints:
nullable: true
Add a new plugin dependency to pom.xml
<dependency>
<groupId>org.yaml</groupId>
<artifactId>snakeyaml</artifactId>
<version>1.28</version>
</dependency>
mvn package
mvn liquibase:update
2 new entries in the databasechangelog table
select * from databasechangelog; +----+--------+------------------+---------------------+---------------+----------+------------------------------------+---------------------------------------------------------------------------------------------------------+----------+------+-----------+----------+--------+---------------+ | ID | AUTHOR | FILENAME | DATEEXECUTED | ORDEREXECUTED | EXECTYPE | MD5SUM | DESCRIPTION | COMMENTS | TAG | LIQUIBASE | CONTEXTS | LABELS | DEPLOYMENT_ID | +----+--------+------------------+---------------------+---------------+----------+------------------------------------+---------------------------------------------------------------------------------------------------------+----------+------+-----------+----------+--------+---------------+ | policy-2 | admin | dbchangelog.yaml | 2021-05-24 15:04:49 | 10 | EXECUTED | 8:f5bd9cec4e2374254cc1985e8e9bfaae | createTable tableName=JpaPdpPolicyDeploymentAudit | | NULL | 3.8.0 | NULL | NULL | 1865089400 | | policy-3 | admin | dbchangelog.yaml | 2021-05-24 15:04:49 | 11 | EXECUTED | 8:4c9ec93973102be9b6d966cf6bc724b4 | addColumn tableName=JpaPdpPolicyDeploymentAudit | | NULL | 3.8.0 | NULL | NULL | 1865089400 | +----+--------+------------------+---------------------+---------------+----------+------------------------------------+---------------------------------------------------------------------------------------------------------+----------+------+-----------+----------+--------+---------------+
Test 6: Rollback last update
mvn liquibase:rollback -Dliquibase.rollbackCount=1
[INFO] SELECT * FROM DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
[INFO] Rolling Back Changeset:dbchangelog.yaml::policy-3::admin
[INFO] ALTER TABLE JpaPdpPolicyDeploymentAudit DROP COLUMN changedByUser
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = 'policy-3' AND AUTHOR = 'admin' AND FILENAME = 'dbchangelog.yaml'
Test 7: Rollback last 2 updates
mvn liquibase:rollback -Dliquibase.rollbackCount=1
[INFO] Rolling Back Changeset:dbchangelog.yaml::policy-2::admin
[INFO] DROP TABLE JpaPdpPolicyDeploymentAudit
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = 'policy-2' AND AUTHOR = 'admin' AND FILENAME = 'dbchangelog.yaml'
Test 8: Update database with changeLog in YAML format which includes raw SQL
Add dbchangelog-sql.yaml
databaseChangeLog:
- changeSet:
id: sql-yaml-1
author: admin
changes:
- sql:
comment: create person table
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
sql: create table person (name varchar(30))
stripComments: true
- changeSet:
id: sql-yaml-2
author: admin
changes:
- sql:
comment: insert name
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
sql: insert into person (name) values ('Liquibase Test')
stripComments: true
update liquibase.properties
changeLogFile: dbchangelog-sql.yaml
mvn liquibase:update
A new table called person with one entry has been added to the database
Test 9: Update database with changeLog in YAML format which calls 2 embedded SQL changeLogs
Add customer-table-changelog-1.sql
--liquibase formatted sql
--changeset admin:cust-1
create table customer (
id int PRIMARY KEY,
name varchar(255) NOT NULL,
role varchar(255)
);
-- rollback drop table customer;
Add customer-table-changelog-2.sql
--liquibase formatted sql --changeset admin:cust-2 insert into customer(id, name, role) values (1, 'Robert', 'Software Developer'); insert into customer(id, name, role) values (2, 'John', 'Dev Ops'); -- rollback delete from customer where id in(1,2);
Add dbchangelog-master.yaml
databaseChangeLog:
- include:
file: customer-table-changelog-1.sql
- include:
file: customer-table-changelog-2.sql
Update liquibase.properties → changeLogFile: dbchangelog-master.yaml
Run mvn liquibase:update
[INFO] SELECT * FROM DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
[INFO] create table customer (
id int PRIMARY KEY,
name varchar(255) NOT NULL,
role varchar(255)
)
[INFO] Custom SQL executed
[INFO] ChangeSet customer-table-changelog-1.sql::cust-1::admin ran successfully in 306ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('cust-1', 'admin', 'customer-table-changelog-1.sql', NOW(), 3, '8:6952c30a86e91b5f32a65e313f52b64b', 'sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1599263670')
[INFO] insert into customer(id, name, role) values (1, 'Robert', 'Software Developer')
[INFO] insert into customer(id, name, role) values (2, 'John', 'Dev Ops')
[INFO] Custom SQL executed
[INFO] ChangeSet customer-table-changelog-2.sql::cust-2::admin ran successfully in 11ms
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('cust-2', 'admin', 'customer-table-changelog-2.sql', NOW(), 4, '8:52f9c3c97a509dc22f109be204fc8dfd', 'sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1599263670')
The a new table called customer has been created and 2 records have been inserted.
Note: When using raw SQL you must provide the rollback statements in order to revert the database to it's previous state
Test 10: Update database with changeLog in YAML format which includes raw SQL and tags
Add Add dbchangelog-sql2.yaml
databaseChangeLog:
- changeSet:
id: cust-3
author: admin
changes:
- tagDatabase:
tag: 1.2
- sql:
comment: insert customer
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
sql: insert into customer (id, name, role) values (3, 'Pete', 'Payroll')
stripComments: true
rollback:
- sql:
sql: delete from customer where id = 3;
- changeSet:
id: cust-4
author: admin
changes:
- tagDatabase:
tag: 1.3
- sql:
comment: insert customer
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
sql: insert into customer (id, name, role)
values (4, 'James', 'Payroll')
stripComments: true
rollback:
- sql:
sql: delete from customer where id = 4;
Update liquibase.properties → changeLogFile: dbchangelog-sql2.yaml
Run mvn:liquibase update
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('cust-3', 'admin', 'dbchangelog-sql2.yaml', NOW(), 5, '8:a660ebc91d2fa7b7e4c2287bf1725707', 'tagDatabase; sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1599649760', '1.2')
[INFO] Tag '1.3' applied to database
[INFO] insert into customer (id, name, role) values (4, 'James', 'Payroll')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql2.yaml::cust-4::admin ran successfully in 3ms
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('cust-4', 'admin', 'dbchangelog-sql2.yaml', NOW(), 6, '8:cb8dbdef98c88b2e99d42a6591f88498', 'tagDatabase; sql', '', 'EXECUTED', NULL, NULL, '3.8.0', '1599649760', '1.3')
2 new records have been added to the customer table
Test 11: Rollback update using tag
mvn liquibase:rollback -Dliquibase.rollbackTag=1.2
[INFO] Rolling Back Changeset:dbchangelog-sql2.yaml::cust-4::admin
[INFO] delete from customer where id = 4
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = 'cust-4' AND AUTHOR = 'admin' AND FILENAME = 'dbchangelog-sql2.yaml'
Last insert has been deleted
Test 12: Re-run last update
mvn liquibase:update
[INFO] Tag '1.3' applied to database
[INFO] insert into customer (id, name, role) values (4, 'James', 'Payroll')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql2.yaml::cust-4::admin ran successfully in 3ms
Only the second insert statement ran.
Test 13: Add labels to changeSets
Add dbchangelog-sql3.yaml
databaseChangeLog:
- changeSet:
id: cust-5
author: admin
labels: release1.0
changes:
- tagDatabase:
tag: 1.6
- sql:
comment: insert customer
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
sql: insert into customer (id, name, role) values (5, 'Jane', 'Payroll')
stripComments: true
rollback:
- sql:
sql: delete from customer where id = 5;
- changeSet:
id: cust-6
author: admin
labels: release1.1
changes:
- tagDatabase:
tag: 1.7
- sql:
comment: insert customer
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
sql: insert into customer (id, name, role)
values (6, 'Jean', 'Dev OPS')
stripComments: true
rollback:
- sql:
sql: delete from customer where id = 6;
Add labels to liquibase.properties
labels: release1.0
Run mvn liquibase:update
[INFO] Tag '1.6' applied to database
[INFO] insert into customer (id, name, role) values (5, 'Jane', 'Payroll')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql3.yaml::cust-5::admin ran successfully in 3ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('cust-5', 'admin', 'dbchangelog-sql3.yaml', NOW(), 7, '8:ce6fde026140f9831fa66b60662e842d', 'tagDatabase; sql', '', 'EXECUTED', NULL, 'release1.0', '3.8.0', '1851627465', '1.6')
Only changeSet with label release1.0 has been applied
Test 14: Apply second label
update liquibase.properties
labels: release1.0, release1.1
Run mvn liquibase:update
[INFO] Tag '1.7' applied to database
[INFO] insert into customer (id, name, role) values (6, 'Jean', 'Dev OPS')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql3.yaml::cust-6::admin ran successfully in 5ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('cust-6', 'admin', 'dbchangelog-sql3.yaml', NOW(), 8, '8:96a8060554eef1d1f4cf4f43dd2c376c', 'tagDatabase; sql', '', 'EXECUTED', NULL, 'release1.1', '3.8.0', '1852080035', '1.7')
ChangeSet with label release1.1 has been applied
Test 15: Test contexts
Remove labels: release1.0, release1.1 from liquibase.properties
Add dbchangelog-sql4.yaml
databaseChangeLog:
- changeSet:
id: cust-7
author: admin
context: qa
labels: release1.2
changes:
- tagDatabase:
tag: 1.8
- sql:
comment: insert customer
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
sql: insert into customer (id, name, role) values (7, 'Jo', 'Payroll')
stripComments: true
rollback:
- sql:
sql: delete from customer where id = 7;
- changeSet:
id: cust-8
author: admin
context: test
labels: release1.2
changes:
- tagDatabase:
tag: 1.9
- sql:
comment: insert customer
dbms: '!h2, oracle, mysql, mariadb'
endDelimiter: \nGO
splitStatements: true
sql: insert into customer (id, name, role)
values (8, 'Kate', 'Dev OPS')
stripComments: true
rollback:
- sql:
sql: delete from customer where id = 8;
Run liquibase:update -Dliquibase.contexts=test
[INFO] Tag '1.9' applied to database
[INFO] insert into customer (id, name, role) values (8, 'Kate', 'Dev OPS')
[INFO] Custom SQL executed
[INFO] ChangeSet dbchangelog-sql4.yaml::cust-8::admin ran successfully in 4ms
[INFO] SELECT MAX(ORDEREXECUTED) FROM DATABASECHANGELOG
[INFO] INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID, TAG) VALUES ('cust-8', 'admin', 'dbchangelog-sql4.yaml', NOW(), 9, '8:cea7bb92b5c448e1c217b9ed69fce75e', 'tagDatabase; sql', '', 'EXECUTED', 'test', 'release1.2', '3.8.0', '1856798669', '1.9')
Only the changeSet with context test has run
Note: labels can be applied in a similar fashion: liquibase:update -Dliquibase.labels=release1.2
To see more about contexts and labels please visit: Understanding Contexts vs. Labels