LiquiBase - the way to handle a database

I am in a middle of rework of an old script. Whole structure of a solution was cumbersome a lot so I had to step in to correct the stuff. I knew that the script has some data in a MySQL database. It uses few tables in a large db with tens and tens of other tables.


I decided not to do changes in original solution and I decided to start rework from scratch. First thing was to create a separate database for the script. So I made a backup:

mysqldump [obvious switches here] dbname tbname1 tbname2 > database.sql

Then I created a new database on my workstation and populated it:

mysql [obvious switches here] -e "CREATE DATABASE IF NOT EXISTS [dbname]"
mysql [obvious switches here] [dbname] < database.sql

Now I had a structure and a data I needed for the rework. The point is how to actually get the database (structure and data) to the dev, test, prod environments and how to maintain all the changes in structure and data from now on. The dump would not work as there may be other data and changes done to the database in different environments.

LiquiBase to  the rescue. It allows to keep track of database changes. Changes are stored in an xml changelog file and are divided into changesets. If there is a need to do a change in a database new chageset is appended to the changelog file. One of the great things about LiquiBase is that it can create changelog file from existing database. The first thing I wanted was a structure of the database:

java -jar ../liquibase/liquibase-2.0.3-bin/liquibase.jar \
--classpath=../drivers/mysql-connector-java-5.1.18/mysql-connector-java-5.1.18-bin.jar \
--driver=com.mysql.jdbc.Driver \
--changeLogFile=structure_v1.0.xml \
--url="jdbc:mysql://localhost/database" \
--username=my_db_user \
--password=my_db_password \
generateChangeLog

Then I wanted data stored in the structure:

java -jar ../liquibase/liquibase-2.0.3-bin/liquibase.jar \
--classpath=../drivers/mysql-connector-java-5.1.18/mysql-connector-java-5.1.18-bin.jar \
--driver=com.mysql.jdbc.Driver \
--changeLogFile=data_v1.0.xml \
--url="jdbc:mysql://localhost/database" \
--username=my_db_user \
--password=my_db_password \
--diffTypes="data" \
generateChangeLog

For the sake of my personal sanity I opted to created separate files for structure and for data. It is time to combine the two. In my case I created third changelog file called database.xml and I used "include" elements to join the changelogs. First include is for the structure, second for the data. This way the structure is created before it is populated with data.

It is possible to create more sophisticated directory structure. Namely separate directories for structure, views, stored procedures, triggers and for data. And then separate data for each table into individual files. But for this particular case it was not necessary.

It is also wise to check generated files after creation and fix if necessary. At current version LiquiBase is not able to capture some database artifacts, namely triggers. These have to be created by hand.

I then dropped the database and recreated it again and populated with LiquiBase:

java -jar ../liquibase/liquibase-2.0.3-bin/liquibase.jar \
--classpath=../drivers/mysql-connector-java-5.1.18/mysql-connector-java-5.1.18-bin.jar \
--driver=com.mysql.jdbc.Driver \
--changeLogFile=database.xml \
--url="jdbc:mysql://localhost/database" \
--username=my_db_user \
--password=my_db_password \
update

To make further changes to the database I created separate file for structure changes and named it structure_v1.1.xml and another file for data called data_v1.1.xml. Then I updated database.xml with another two includes for the files. I am keeping making changes to last two files and updating database on my workstation, dev and testing servers via LiquiBase. This way are databases kept in sync and correlate to current work on the programs it is used by.