Liquibase is an open source tool for database schema change management, it helps teams track, version, and deploy database schema and logic changes.
It has 2 flavors; the community edition and the pro one.
In this post, we will do a local MySQL database schema changes management between 2 schema(s)
Source: bankaccounts schema and the Target is: bankaccounts2 schema
In one use case we will move all the schema into the target DB and then we will capture any changes and apply them into the target DB so we can make sure the source and target DB are consistent.
1) Download the Liquibase tool:
From this page: https://download.liquibase.org/download/?frm=n you can download it.
If for example, you are using Windows then just unzip the folder and add it to the PATH variable of your environment variables.
Done? let's move to the next step.
2) Create a separate folder for Source and target name them as you wish
3) Do initial DB configurations:
Open MySQL DB and create the following schema(s):
CREATE SCHEMA `bankaccounts` ;
GRANT ALL PRIVILEGES ON *.* TO 'bankaccounts'@'localhost' IDENTIFIED BY 'bankaccounts';databasechangelog
CREATE SCHEMA `bankaccounts2` ;
GRANT ALL PRIVILEGES ON *.* TO 'bankaccounts2'@'localhost' IDENTIFIED BY 'bankaccounts2';databasechangelog
Now we have 2 identical empty schema(s)
4) Load DB Schema:
Load Schema 1, our source schema with some DB objects by executing this script:
CREATE TABLE `bankaccounts`.`accounts` (
`id` INT NOT NULL AUTO_INCREMENT,
`account_no` VARCHAR(45) NULL,
`balance` DOUBLE NULL,
`currency` VARCHAR(45) NULL,
PRIMARY KEY (`id`));
CREATE TABLE `bankaccounts`.`transactions` (
`transaction_id` INT NOT NULL AUTO_INCREMENT,
`account_no` VARCHAR(45) NULL,
`transaction` DOUBLE NULL,
`date` DATETIME NULL,
`transaction_details` VARCHAR(45) NULL,
PRIMARY KEY (`transaction_id`));
Now our source DB contains 2 tables.
- In a directory named "source"
1- Create a configure a file named: "liquibase.properties"
with the following content:
changeLogFile: ./dbchangelog.xml
driver: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/bankaccounts
username: bankaccounts
password: bankaccounts
classpath: ../../../mysql-connector-java-5.1.23-bin.jar
Note: you need also to fix the MySQL connector jar location as per your environment
2- Run the Liquibase tool:
liquibase generateChangeLog
The output will be directed into the dbchangelog.xml file specified in our properties file
Here is the content I got, you should get similar (except the id of the change set)
<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" 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-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-3.8.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
<changeSet author="ooransa (generated)" id="1578934983284-1">
<createTable tableName="accounts">
<column autoIncrement="true" name="id" type="INT">
<constraints nullable="false" primaryKey="true"/>
</column>
<column name="account_no" type="VARCHAR(45)"/>
<column name="balance" type="DOUBLE"/>
<column name="currency" type="VARCHAR(45)"/>
</createTable>
</changeSet>
<changeSet author="ooransa (generated)" id="1578934983284-2">
<createTable tableName="transactions">
<column autoIncrement="true" name="transaction_id" type="INT">
<constraints nullable="false" primaryKey="true"/>
</column>
<column name="account_no" type="VARCHAR(45)"/>
<column name="transaction" type="DOUBLE"/>
<column name="date" type="VARCHAR(45)"/>
<column name="transaction_details" type="VARCHAR(45)"/>
</createTable>
</changeSet>
</databaseChangeLog>
As you can see the 2 tables are captured in this file and we can now execute this into the target DB to do a schema migration by configuring the DB in a new property file and execute:
liquibase update
Which will apply all the changes in the XML file, the file structure is simple as collection of changeSet every changeSet capture one DB modification.
The changes are identified as unique by combination of author and id fields, for example:
author="ooransa (generated)" id="1578934983284-2"
We will skip this part as it will be demonstrated with the second use case.
In the "target" folder:
1- create a new file named "liquibase.properties"
with the following content:
changeLogFile: ./dbchangelog.xml
driver: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/bankaccounts2
username: bankaccounts2
password: bankaccounts2
referenceUrl: jdbc:mysql://localhost:3306/bankaccounts
referenceUsername: bankaccounts
referencePassword: bankaccounts
referenceDriver: com.mysql.jdbc.Driver
classpath: ../../../mysql-connector-java-5.1.23-bin.jar
Note: you need also to fix the MySQL connector jar location as per your environment
As you can see this time we configured the source (reference DB) and the target DB for this configurations.
2- Execute the command:
liquibase diffChangeLog
This will capture the difference between our empty schema and the source/referenced schema which will produce a file similar to the previous XML file.
Now apply these changes into the DB using the command:
liquibase update
That's it, all we need to do. whenever a new changes happen to the DB execute the diffChangeLog then the update commands, no more :)
Note a change log table will be also created in our target DB named: databasechangelog plus a lock table to make sure only one liquibase command executed at a time.
Now we did the initial DB migration from the source DB to the target DB
Let's try this sample modification:
- Create a new table in your source DB "bankaccounts" for example:
CREATE TABLE `bankaccounts`.`test_change` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NULL,
PRIMARY KEY (`id`));
Now you can see the new table created in the target DB.
Drop the table from the source DB and re-execute both commands:
liquibase diffChangeLog
You can see the change appended to the file:
<changeSet author="ooransa (generated)" id="1578935549140-1">
<dropTable tableName="test_change"/>
</changeSet>
Then apply it using:
liquibase update
So whatever changes you do in the source you capture it using the diffChangeLog and apply it using the update, at the end all the changes you made for your DB will be persisted in this single DB change log file.
You can also do a rollback of the changes by using the rollback option.
For more information: visit: https://www.liquibase.org/index.html
It is better to use maven plugin and generate the DB change log as part of your build and package process. Now in the source folder, create a new folder named "maven" and in this folder create "pom.xml" file as following:
1) Add the plugin as simple to the maven pom file as:
<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>osa.ora</groupId>
<artifactId>BankingServiceDB</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>BankingServiceDB</name>
<dependencies>
<dependency>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-maven-plugin</artifactId>
<version>3.8.5</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.23</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-maven-plugin</artifactId>
<version>3.8.5</version>
<configuration>
<outputChangeLogFile>./dbchangelog.xml</outputChangeLogFile>
<driver>com.mysql.jdbc.Driver</driver>
<url>jdbc:mysql://localhost:3306/bankaccounts</url>
<username>BankAccounts</username>
<password>BankAccounts</password>
</configuration>
</plugin>
</plugins>
</build>
</project>
This also included the MySQL DB Driver dependency for example for MySQL
2. The configurations is now part of our maven file, but in case you need to have them outside it in the property file: liquibase.properties You need to modify the configuration section in the POM file as following:
<configuration>
<propertyFile>liquibase.properties</propertyFile>
</configuration>
outputChangeLogFile: ./dbchangelog.xml
driver: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/bankaccounts
username: BankAccounts
password: BankAccounts
Note that you don't need to specify the jar location for MySQL connector jar, it will be added to the classpath by maven.
3) Execute it using maven:
mvn liquibase:generateChangeLog
Or execute any of other liquibase command options!
Let's now try the other way around, modify the POM file to use the properties file:
<configuration>
<propertyFile>liquibase.properties</propertyFile>
diffChangeLogFile: ./dbchangelog_diff.xml
changeLogFile: ./dbchangelog.xml
driver: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/bankaccounts2
username: bankaccounts2
password: bankaccounts2
referenceUrl: jdbc:mysql://localhost:3306/bankaccounts
referenceUsername: BankAccounts
referencePassword: BankAccounts
referenceDriver: com.mysql.jdbc.Driver
Now run the diff command to get the delta between the 2 databases:
mvn liquibase:diff
Do some changes to the source schema and then re-run the command and check the output file.
It has 2 flavors; the community edition and the pro one.
In this post, we will do a local MySQL database schema changes management between 2 schema(s)
Source: bankaccounts schema and the Target is: bankaccounts2 schema
In one use case we will move all the schema into the target DB and then we will capture any changes and apply them into the target DB so we can make sure the source and target DB are consistent.
1) Download the Liquibase tool:
From this page: https://download.liquibase.org/download/?frm=n you can download it.
If for example, you are using Windows then just unzip the folder and add it to the PATH variable of your environment variables.
Done? let's move to the next step.
2) Create a separate folder for Source and target name them as you wish
3) Do initial DB configurations:
Open MySQL DB and create the following schema(s):
CREATE SCHEMA `bankaccounts` ;
GRANT ALL PRIVILEGES ON *.* TO 'bankaccounts'@'localhost' IDENTIFIED BY 'bankaccounts';databasechangelog
CREATE SCHEMA `bankaccounts2` ;
GRANT ALL PRIVILEGES ON *.* TO 'bankaccounts2'@'localhost' IDENTIFIED BY 'bankaccounts2';databasechangelog
Now we have 2 identical empty schema(s)
4) Load DB Schema:
Load Schema 1, our source schema with some DB objects by executing this script:
CREATE TABLE `bankaccounts`.`accounts` (
`id` INT NOT NULL AUTO_INCREMENT,
`account_no` VARCHAR(45) NULL,
`balance` DOUBLE NULL,
`currency` VARCHAR(45) NULL,
PRIMARY KEY (`id`));
CREATE TABLE `bankaccounts`.`transactions` (
`transaction_id` INT NOT NULL AUTO_INCREMENT,
`account_no` VARCHAR(45) NULL,
`transaction` DOUBLE NULL,
`date` DATETIME NULL,
`transaction_details` VARCHAR(45) NULL,
PRIMARY KEY (`transaction_id`));
Now our source DB contains 2 tables.
FIRST USE CASE: INITIAL DB MIGRATION:
Now Let's prepare our Liquibase tool to capture and do initial DB migration- In a directory named "source"
1- Create a configure a file named: "liquibase.properties"
with the following content:
changeLogFile: ./dbchangelog.xml
driver: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/bankaccounts
username: bankaccounts
password: bankaccounts
classpath: ../../../mysql-connector-java-5.1.23-bin.jar
Note: you need also to fix the MySQL connector jar location as per your environment
2- Run the Liquibase tool:
liquibase generateChangeLog
The output will be directed into the dbchangelog.xml file specified in our properties file
Here is the content I got, you should get similar (except the id of the change set)
<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" 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-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-3.8.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
<changeSet author="ooransa (generated)" id="1578934983284-1">
<createTable tableName="accounts">
<column autoIncrement="true" name="id" type="INT">
<constraints nullable="false" primaryKey="true"/>
</column>
<column name="account_no" type="VARCHAR(45)"/>
<column name="balance" type="DOUBLE"/>
<column name="currency" type="VARCHAR(45)"/>
</createTable>
</changeSet>
<changeSet author="ooransa (generated)" id="1578934983284-2">
<createTable tableName="transactions">
<column autoIncrement="true" name="transaction_id" type="INT">
<constraints nullable="false" primaryKey="true"/>
</column>
<column name="account_no" type="VARCHAR(45)"/>
<column name="transaction" type="DOUBLE"/>
<column name="date" type="VARCHAR(45)"/>
<column name="transaction_details" type="VARCHAR(45)"/>
</createTable>
</changeSet>
</databaseChangeLog>
As you can see the 2 tables are captured in this file and we can now execute this into the target DB to do a schema migration by configuring the DB in a new property file and execute:
liquibase update
Which will apply all the changes in the XML file, the file structure is simple as collection of changeSet every changeSet capture one DB modification.
The changes are identified as unique by combination of author and id fields, for example:
author="ooransa (generated)" id="1578934983284-2"
We will skip this part as it will be demonstrated with the second use case.
SECOND USE CASE: DB SCHEMA CHANGE MANAGEMENT:
Let's now prepare our target DB to receive the initial and any updates to the schemaIn the "target" folder:
1- create a new file named "liquibase.properties"
with the following content:
changeLogFile: ./dbchangelog.xml
driver: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/bankaccounts2
username: bankaccounts2
password: bankaccounts2
referenceUrl: jdbc:mysql://localhost:3306/bankaccounts
referenceUsername: bankaccounts
referencePassword: bankaccounts
referenceDriver: com.mysql.jdbc.Driver
classpath: ../../../mysql-connector-java-5.1.23-bin.jar
Note: you need also to fix the MySQL connector jar location as per your environment
As you can see this time we configured the source (reference DB) and the target DB for this configurations.
2- Execute the command:
liquibase diffChangeLog
This will capture the difference between our empty schema and the source/referenced schema which will produce a file similar to the previous XML file.
Now apply these changes into the DB using the command:
liquibase update
That's it, all we need to do. whenever a new changes happen to the DB execute the diffChangeLog then the update commands, no more :)
Note a change log table will be also created in our target DB named: databasechangelog plus a lock table to make sure only one liquibase command executed at a time.
Now we did the initial DB migration from the source DB to the target DB
Let's try this sample modification:
- Create a new table in your source DB "bankaccounts" for example:
CREATE TABLE `bankaccounts`.`test_change` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NULL,
PRIMARY KEY (`id`));
- Capture the new changes using the command: (while still in the target folder)
liquibase diffChangeLog
The new modification will be appended to our initial dbchangelog.xml so you can see a new entry:
<changeSet author="ooransa (generated)" id="1578935272217-1">
<createTable tableName="test_change">
<column autoIncrement="true" name="id" type="INT">
<constraints nullable="false" primaryKey="true"/>
</column>
<column name="name" type="VARCHAR(45)"/>
</createTable>
</changeSet>
- Apply the changes to our target DB by executing the command: (while still in the target folder)
liquibase update
Now you can see the new table created in the target DB.
Drop the table from the source DB and re-execute both commands:
liquibase diffChangeLog
You can see the change appended to the file:
<changeSet author="ooransa (generated)" id="1578935549140-1">
<dropTable tableName="test_change"/>
</changeSet>
Then apply it using:
liquibase update
So whatever changes you do in the source you capture it using the diffChangeLog and apply it using the update, at the end all the changes you made for your DB will be persisted in this single DB change log file.
You can also do a rollback of the changes by using the rollback option.
For more information: visit: https://www.liquibase.org/index.html
Using Maven Plugin
It is better to use maven plugin and generate the DB change log as part of your build and package process. Now in the source folder, create a new folder named "maven" and in this folder create "pom.xml" file as following:
1) Add the plugin as simple to the maven pom file as:
<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>osa.ora</groupId>
<artifactId>BankingServiceDB</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>BankingServiceDB</name>
<dependencies>
<dependency>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-maven-plugin</artifactId>
<version>3.8.5</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.23</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-maven-plugin</artifactId>
<version>3.8.5</version>
<configuration>
<outputChangeLogFile>./dbchangelog.xml</outputChangeLogFile>
<driver>com.mysql.jdbc.Driver</driver>
<url>jdbc:mysql://localhost:3306/bankaccounts</url>
<username>BankAccounts</username>
<password>BankAccounts</password>
</configuration>
</plugin>
</plugins>
</build>
</project>
2. The configurations is now part of our maven file, but in case you need to have them outside it in the property file: liquibase.properties You need to modify the configuration section in the POM file as following:
<configuration>
<propertyFile>liquibase.properties</propertyFile>
</configuration>
Which point to configuration file "liquibase.properties" contains the following items:
driver: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/bankaccounts
username: BankAccounts
password: BankAccounts
3) Execute it using maven:
mvn liquibase:generateChangeLog
Or execute any of other liquibase command options!
Let's now try the other way around, modify the POM file to use the properties file:
<configuration>
<propertyFile>liquibase.properties</propertyFile>
</configuration>
And configure it as following:
changeLogFile: ./dbchangelog.xml
driver: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/bankaccounts2
username: bankaccounts2
password: bankaccounts2
referenceUrl: jdbc:mysql://localhost:3306/bankaccounts
referenceUsername: BankAccounts
referencePassword: BankAccounts
referenceDriver: com.mysql.jdbc.Driver
Now run the diff command to get the delta between the 2 databases:
mvn liquibase:diff
Do some changes to the source schema and then re-run the command and check the output file.
For the rollback details, I would suggest you read about this from this nice article:
https://www.baeldung.com/liquibase-rollback
No comments:
Post a Comment