Liquibase is a source control management tool for database development. It supports code branching, merging, database change documentation, DB diffs, multiple developer support. It can be used through build process, from application or on-demand. It is an open source software (Apache 2.0 license) software. Integrates well with Maven, Ant, With Liquibase database development cycle & version control becomes vey simple.
The basic lifecycle of database development with Liquibase is as follows:
1.Download & configure Liquibase
2.Create new changelog file in XML, YAML, JSON or SQLformat
3.Add changeset to changelog file
4.Run liquibase update
5.Commit changelog file to source control
6.GOTO 3
Core concepts:
Change: Any database change (metadata or data) e.g. create schema. Liquibase supports both descriptive changes in XML file that generate SQL for supported databases and raw SQL.
Example:
<createTable tableName="person">
<column name="id" type="int">
<constraints primaryKey="true" nullable="false"/>
</column>
<column name="name" type="varchar(50)">
<constraints nullable="false"/>
</column>
</createTable>
Changeset: A collection of changes to be applied to the database. Generally there should be just one change per changeset to avoid failed autocommit statements that can leave the database in an unexpected state. Change Sets are uniquely identified by the "author" and "id" attribute along with with the location of the changelog file and are the units Liquibase tracks execution of. When Liquibase runs, it queries the DATABASECHANGELOG table for the changesets that are marked as executed and then executes all changesets in the changelog file that have not yet been executed.
Changelog: Developers store database changes in text-based files on their local development machines and apply them to their local databases. Changelog files can be be arbitrarily nested for better management.
Getting started with Liquibase:
Step 0: Install Liquibase
Download Liquibase 3.2.2 (released on July 23, 2014) from http://www.liquibase.org/download/index.html
Extract the liquibase-VERSION.zip or liquibase-VERSION.tar.gz file to a local directory. The extracted files contain a liquibase.bat and liquibase shell script for Windows and Mac/UNIX systems.
Step 1: Create a Changelog File
The database changelog file is where all database changes are listed. It is XML based, so start with an empty XML file.
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
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.1.xsd">
</databaseChangeLog>
Step 2: Add change set
Each change set is uniquely identified by an “id” attribute and an “author” attribute. These two tags, along with the name and package of the changelog file uniquely identify the change. If only an “id” needed to be specified, it would be too easy to accidentally duplicate them, especially when dealing with multiple developers and code branches. Including an “author” attribute minimizes the chances of duplications.
Think of each change set as an atomic change that you want to apply to your database. It’s usually best to include just one change in your change set, but more are allowed and can make sense if you are inserting multiple rows that should be added as a single transaction. Liquibase will attempt to run each change set as a single transaction, but many databases will silently commit and resume transactions for certain commands (create table, drop table, etc.)
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
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.1.xsd">
<changeSet id="1" author="JK">
<createTable tableName="person">
<column name="id" type="int">
<constraints primaryKey="true" nullable="false"/>
</column>
<column name="name" type="varchar(50)">
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
</databaseChangeLog>
Step 3: Run the change set
There are many ways to execute your change log including via command line, Ant, Maven, and Spring. We should use command line for development purpose (typically Dev) and Maven for deployment purpose (typically QA).
Create a liquibase.properties file inside the liquibase install directory as follows and run command liquibase update. This will run the changelog file and will execute the change sets.
-------------------------------------------------
#liquibase.properties
driver:com.mysql.jdbc.Driver
classpath:lib/mysql-connector-java-5.1.28.jar
url:jdbc:mysql://localhost:3306/mydb?allowMultiQueries=true&createDatabaseIfNotExist=true
username:root
password:$s%6a@w
changeLogFile:database/db.changelog-master.xml
logLevel:debug
----------------------------------------------
Most of the tags are self describing.
url tag is DB dependent and should represent the computer name / ip address, port number, and database name. allowMultipleQueries and createDatabaseIfNotExist are liquibase specific tags.
changeLogFile tag represents the name of the changelog file. Liquibase supports file chaining (i.e. a master file referring to child files). And using that is a best practice. We are following that.
logLevel tag represents the logging behaviour. Right now we should set it to debug to get a feel of what's happening under the hood.
Step 4: Check the behaviour of Liquibase
After running the update command successfully, check whether the appropriate DB objects are created. Additionally you can check the two tables created by Liquibase in your database. All databases managed by Liquibase have these tables (viz: databasechangelog and databasechangeloglock).
