Managing database schema changes with liquibase on an existing database tutorial

If you have had a project with several developers working at a high velocity on different branches you are probably aware of the amount of trouble that a theoretically simple task such as managing the db schema changes can provoke. The main issues occur on a daily-basis at development, less DRY, less agile; also the production deployments and merges can be severly affected

Database migration tools

Luckily most frameworks come with migration tools, rails, django, sequelize or yii as it is an important tool to ensure some agility. Today I will be taking a look at liquibase which you may find interesting

A small tutorial with examples for liquibase

Using node?

Don’t miss this article
Database migrations with node db-migrate

I have prepared a simple example to demonstrate managing database schema changes with liquibase on an existing database. To make it run you will need to download and install liquibase, and a db driver, in my case for mysql the popular java mysql connector. The simple schema is shown here, I also added a view which is not shown

sample_db_schema

I recommend you to use a properties file describing your database connection on your codebase. I will be using a database named liquitest. To use it, please provide the username/pass for your server


Reverse engineering

Once downloaded and setup, let’s see some usage samples. Let’s say you already have a database. Liquibase allows you to reverse engineering the schema (most of it, yet functions/triggers are not supported) with a command like this

liquibase --changeLogFile="./.xml" generateChangeLog

I have created three changesets as an example, one for the schema, one with default data and the other one with some fixture data for this example


# Dummy make all
cat drop.sql create.sql insert.sql test_data.sql | mysql -u[your user] -p[your pass] liquitest

# Reverse engineering basic schema
cat drop.sql create.sql | mysql -u[your user] -p[your pass] liquitest
liquibase --changeLogFile="./boot.xml" generateChangeLog

# Reverse engineering categories table
cat drop.sql create.sql insert.sql | mysql -u[your user] -p[your pass] liquitest
liquibase --changeLogFile="./init_categories.xml" --diffTypes="data" generateChangeLog

# Reverse engineering sample data (Delete by hand the categories insert)
cat drop.sql create.sql insert.sql test_data.sql | mysql -u[your user] -p[your pass] liquitest
liquibase --changeLogFile="./init_sample_data.xml" --diffTypes="data" generateChangeLog

Synchronize your metadata

Once the schema has been reversed engineered the followings commands will create and populate the tables that liquibase uses for handling the versions. This should be run in production, to prepare the database for the upcoming changes


liquibase --changeLogFile=./boot.xml changeLogSync
liquibase --changeLogFile=./init_categories.xml changeLogSync
liquibase --changeLogFile=./init_sample_data.xml changeLogSync

These are the tables that liquibase uses

liquibase_metadata

Keep track of the modifications with liquibase schemas

Now any developer could start modifying the schema by providing liquibase changesets. Let’s add some more changes, for instance let’s refactor the schema to make the category available on the project rather than as a foreign key. This would be a SCM’ed file that all developers should maintain and merge. You could even agree to use per branch files or per functionality. In my case I call it artist.xml as it is on the artist branch

It contains the following operations

  • add a field to store the category on the table
  • update the field with the content of the category table
  • drop the previous foreign key constraint and field
  • recreate the view







UPDATE project
JOIN
category ON project.category_id = category.id
SET project.category_name = category.name







SELECT liquitest.project.id AS id,
liquitest.project.name AS name,
liquitest.project.category_name AS category,
liquitest.project_data.description AS description
FROM liquitest.project join
liquitest.project_data ON liquitest.project_data.project_id = liquitest.project.id


As you may see, the structure is quite straightforward, and also allows raw SQL. Most of the commands are auto-rollbackable while for complex operations you should provide your own Rollbacks

Rebuilding from scratch from the changesets

Now any developer can recreate the schema from scratch executing the whole migration. Liquibase will execute all of the operations inside the files also checkin the schema history on the metadata table


cat drop.sql | mysql -u[your user] -p[your pass] liquitest
liquibase --changeLogFile=./boot.xml migrate
liquibase --changeLogFile=./init_categories.xml migrate
liquibase --changeLogFile=./init_sample_data.xml migrate
liquibase --changeLogFile=./artist.xml migrate

Know your database schema status

You can always check the status of your schema reviewing the table DATABASECHANGELOG. It contains useful information, such as the person who proposed the change or the kind of operations run

Find here (and play with) the whole code of this tutorial

 

Bytefilia