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="1" author="admin">
<createTable tableName="department">
<column name="id" type="int">
<constraints primaryKey="true" nullable="false"/>
</column>
<column name="name" type="varchar(50)">
<constraints nullable="false"/>
</column>
<column name="active" type="boolean" defaultValueBoolean="true"/>
</createTable>
</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, table department has been added
Test 2: Rollback update
mvn liquibase:rollback -Dliquibase.rollbackCount=1
table department has been dropped
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: 1
author: admin
changes:
- createTable:
tableName: person
columns:
- column:
name: id
type: int
autoIncrement: true
constraints:
primaryKey: true
nullable: false
- column:
name: firstname
type: varchar(50)
- column:
name: lastname
type: varchar(50)
constraints:
nullable: false
- column:
name: state
type: char(2)
- changeSet:
id: 2
author: admin
changes:
- addColumn:
tableName: person
columns:
- column:
name: username
type: varchar(8)
- changeSet:
id: 3
author: admin
changes:
- addLookupTable:
existingTableName: person
existingColumnName: state
newTableName: state
newColumnName: id
newColumnDataType: char(2)
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
3 new entries in the databasechangelog table
select * from databasechangelog; +----+--------+------------------+---------------------+---------------+----------+------------------------------------+---------------------------------------------------------------------------------------------------------+----------+------+-----------+----------+--------+---------------+ | ID | AUTHOR | FILENAME | DATEEXECUTED | ORDEREXECUTED | EXECTYPE | MD5SUM | DESCRIPTION | COMMENTS | TAG | LIQUIBASE | CONTEXTS | LABELS | DEPLOYMENT_ID | +----+--------+------------------+---------------------+---------------+----------+------------------------------------+---------------------------------------------------------------------------------------------------------+----------+------+-----------+----------+--------+---------------+ | 1 | admin | dbchangelog.yaml | 2021-05-20 16:07:58 | 1 | EXECUTED | 8:f022085620a9dd87ae9ca1f93da51fd0 | createTable tableName=person | | NULL | 3.8.0 | NULL | NULL | 1523278917 | | 2 | admin | dbchangelog.yaml | 2021-05-20 16:07:58 | 2 | EXECUTED | 8:e3f0f50eb245f66c68ec504db93b6717 | addColumn tableName=person | | NULL | 3.8.0 | NULL | NULL | 1523278917 | | 3 | admin | dbchangelog.yaml | 2021-05-20 16:07:59 | 3 | EXECUTED | 8:12a8bb9e438047d9332c8aec4f90a76e | addLookupTable existingColumnName=state, existingTableName=person, newColumnName=id, newTableName=state | | NULL | 3.8.0 | NULL | NULL | 1523278917 | +----+--------+------------------+---------------------+---------------+----------+------------------------------------+---------------------------------------------------------------------------------------------------------+----------+------+-----------+----------+--------+---------------+
Test 6: Rollback last update
mvn liquibase:rollback -Dliquibase.rollbackCount=1
[INFO] Rolling Back Changeset:dbchangelog.yaml::3::admin
[INFO] ALTER TABLE person DROP FOREIGN KEY FK_PERSON_STATE
[INFO] DROP TABLE state
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = '3' AND AUTHOR = 'admin' AND FILENAME = 'dbchangelog.yaml'
Test 7: Rollback last 2 updates
mvn liquibase:rollback -Dliquibase.rollbackCount=2
[INFO] Rolling Back Changeset:dbchangelog.yaml::2::admin
[INFO] ALTER TABLE person DROP COLUMN username
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = '2' AND AUTHOR = 'admin' AND FILENAME = 'dbchangelog.yaml'
[INFO] Rolling Back Changeset:dbchangelog.yaml::1::admin
[INFO] DROP TABLE person
[INFO] DELETE FROM DATABASECHANGELOG WHERE ID = '1' 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