Setup
Create a new java project
...
Code Block | ||||
---|---|---|---|---|
| ||||
<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
...
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
...
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
Code Block | ||||
---|---|---|---|---|
| ||||
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) |
...
Code Block | ||||
---|---|---|---|---|
| ||||
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-Add dbchangelog-sql.yaml
Code Block | ||||
---|---|---|---|---|
| ||||
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 |
...
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
Code Block | ||||
---|---|---|---|---|
| ||||
--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
Code Block | ||||
---|---|---|---|---|
| ||||
--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
Code Block | ||||
---|---|---|---|---|
| ||||
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
Code Block | ||||
---|---|---|---|---|
| ||||
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