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>
From the command line in the project directory
mvn package
mvn liquibase:update
Check you database, table department has been added
mvn liquibase:rollback -Dliquibase.rollbackCount=1
table department has been dropped
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
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
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 | +----+--------+------------------+---------------------+---------------+----------+------------------------------------+---------------------------------------------------------------------------------------------------------+----------+------+-----------+----------+--------+---------------+
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'
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'
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