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