Using CommandBox to Run Liquibase Migrations
Liquibase can get complicated but at it’s core you are really just running a ’liquibase update’ at the end of the day. This is fine for one or two database but one of the challenges I quickly ran into when getting started was our unique SQL environment. We have a single application but each ‘client’ has their own database. I won’t argue on the pros or cons of that architecture, but I needed to make Liquibase work in that environment.
While each client has their own database, we also have a parent ‘database of databases’ which has some meta information about each client and their setup.
I just needed a way to query for that data and run a script.
CommandBox to the rescue!
Since CommandBox can leverage CFML I can easily query the parent database and get a list of databases. And CommandBox provides ’task runners’ to make this scriptable:
CommandBox allows you to automate common jobs that you want to run via Task Runners. Tasks are for when you want something more flexible than a recipe or just a simple .cfm execution. Tasks are analogous to other build tools like Ant, except you get to use CFML to build them instead of XML!
So I cobbled together a simple set of steps:
- Query parent database and get a list of database which need to be updated
- Loop over this list and run the appropriate Liquibase migration command (in our case ‘update’ or ‘rollback’)
- Dump out useful info / errors to the screen (the end goal of this will be to run in a CI environment)
First thing I did was to create a new file called ’task.cfc’ in the root of my Liquibase migrations directory. Note a CommandBox task is simply a component.
component extends="commandbox.system.BaseCommand" {
// liquibase stuff will go here
}
Next I defined a ‘pretask()’ method which will run before anything else. This sets up some default variables, defines some environment variables so I can run this both locally and in Bitbucket, and defines datasource information so we can connect to the appropriate databases.
function preTask() {
// set some defaults
param name="variables.bitbucket_env" default="QA" type="string";
param name="variables.dblist" default="" type="string";
param name="variables.parent_datasource" default="#StructNew()#" type="struct";
param name="variables.main_datasource" default="#StructNew()#" type="struct";
param name="variables.errorlog" default="" type="string";
param name="variables.jdbcstring" default="MultiSubnetFailover=True;ApplicationIntent=ReadWrite;" type="string";
param name="variables.jdbcstringlocal" default="integratedSecurity=false;" type="string";
param name="variables.lb_dblist" default="" type="string";
param name="variables.lb_password" default="" type="string";
param name="variables.lb_username" default="" type="string";
param name="variables.migrationerror" default="false" type="string";
param name="variables.mssqlhost" default="localhost" type="string";
param name="variables.mssqlhost_parent_dbname" default="" type="string";
param name="variables.mssqlhost_parent" default="localhost" type="string";
param name="variables.preerror" default="false" type="string";
param name="variables.properties" default="#StructNew()#" type="struct";
param name="variables.parent_qry" default="#QueryNew("")#" type="query";
param name="variables.release" default="" type="string";
// setup datasource
print.line()
.boldBlueLine( 'Getting databases from parent...' )
.toConsole();
try {
bitbucket_env = getSystemSetting('BITBUCKET_DEPLOYMENT_ENVIRONMENT');
lb_password = getSystemSetting('LB_PASSWORD');
lb_username = getSystemSetting('LB_USERNAME');
properties = propertyFile('liquibase.properties');
// setup connection information depending on environment
switch(bitbucket_env) {
case "PRODUCTION":
mssqlhost = "mydatabase.com";
mssqlhost_parent_active = 1;
mssqlhost_parent_dbname = "";
mssqlhost_parent = "parent.mydatabase.com";
jdbc_parent = "jdbc:sqlserver://#mssqlhost_parent#:1433;#jdbcstring#";
break;
default:
mssqlhost = "localhost";
mssqlhost_parent = "localhost";
mssqlhost_parent_active = 0;
mssqlhost_parent_dbname = getparentSetting('LB_DBLIST');
jdbc_parent = "jdbc:sqlserver://#mssqlhost_parent#:1433;#jdbcstringlocal#";
}
parent_datasource = {
class: properties.driver,
connectionString: jdbc_parent & 'databaseName=parent;',
username: lb_username,
password: lb_password
};
// connect to parent table to get list of database names
// getData() is a simple function which returns a query of databases
parent_qry = getData(datasource = parent_datasource
, mssqlhost_parent_active = mssqlhost_parent_active
, mssqlhost_parent_dbname = mssqlhost_parent_dbname);
}
catch ( e ) {
migrationerror = true;
preerror = true;
print.redIndentedLine('Error connecting to Parent Database!' & chr(10) & e.message);
print.line();
}
print.blueBoldLine('Running Liquibase:')
.toConsole();
}
This will connect to the database of databases (parent) and return a query of databases which we can then loop over and run migrations against.
To do this I create a ‘main()’ method which will do the actual heavy lifting and run the migration.
By default this will run the Liquibase action of ‘update-testing-rollback’ which will run the update, and then roll back those changes leaving your database in it’s original state. This is ideal for testing things locally while you tweak things. You can also pass a ’tag’ which is similar to a tag in git - it can allow you to rollback to a certain point.
function main(string liquibase_action="update-testing-rollback", string tag="") {
local.tag = getTagValue(tag=arguments.tag, release=properties.release);
// if there are no errors - loop over, connect to appropriate database and run the migration
if( parent_qry.recordcount && !migrationerror){
cfloop(query=parent_qry, group="parent_dsn"){
if(bitbucket_env IS "LOCAL"){
jdbc_connection = "jdbc:sqlserver://#variables["mssqlhost_" & parent_dsn]#:1433;#jdbcstringlocal#";
} else {
jdbc_connection = "jdbc:sqlserver://#variables["mssqlhost_" & parent_dsn]#:1433;#jdbcstring#";
}
print.yellowLine('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~')
.yellowLine('Running migrations on: #chr(10)#' & parent_dsn & " (#jdbc_connection#)");
cfloop(){
try {
currentdb = parent_dbname;
main_datasource = {
class: properties.driver,
connectionString: jdbc_system & 'databaseName=#currentdb#;',
username: lb_username,
password: lb_password
};
// this is where we actually run the liquibase command
// passing in the appropriate action - update, rollback, etc.
// if we are doing a rollback we also need a tag so we pass that in
// note we also dump out some info to the console in red (rollback)
// or green (update) and all of it is wrapped in a try catch
// so we know if there is an error
// if we're rolling back - pass along the tag
if( liquibase_action EQ 'rollback'){
print.redLine('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~')
.redBoldLine('#liquibase_action# database (#parent_dsn#) to tag: #local.tag#: #chr(10)# - #currentdb# ')
.line()
.toConsole();
command('run')
.params('liquibase', '#liquibase_action#', '#local.tag#')
.flags( 'changeLogFile=#properties.changeLogMain#'
,'driver=#properties.driver#'
,'url="#jdbc_connection#databaseName=#currentdb#"'
,'username=#lb_username#'
,'password=#lb_password#'
)
.run();
} else {
print.greenLine('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~')
.greenBoldLine('#liquibase_action# (#parent_dsn#): #chr(10)# - #currentdb#')
.line()
.toConsole();
command('run')
.params('liquibase', '#liquibase_action#')
.flags( 'changeLogFile=#properties.changeLogMain#'
,'driver=#properties.driver#'
,'url="#jdbc_connection#databaseName=#currentdb#"'
,'username=#lb_username#'
,'password=#lb_password#'
)
.run();
}
}
catch ( any e ) {
migrationerror = true;
// capture which db had the issue so we can go back and do something
if (len(errorlog)){
errorlog &= ',' & currentdb;
} else {
errorlog &= currentdb;
}
}
print.line();
}
}
} else {
print.redIndentedLine('Error occurred (pretask) - main migrations not run...');
return 1;
}
}
Finally we have a ‘postTask()’ which will let us know if there are any errors. When we first started using Liquibase there were a few databases that would throw an error due to them being out of sync with the others and we had to fix things manually. This script is still a work in progress and one thing I’d like to do is improve the error handling.
function postTask() {
if((migrationerror and !len(errorlog)) and !preerror){
print.line()
.redLine('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~')
.redBoldLine( 'There was a migration error. Please check console output.')
.redLine('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
}
if((migrationerror and len(errorlog)) and !preerror){
savecontent variable="dblist" {
for (db in errorlog) {
writeOutput('- ' & db & chr(10));
}
}
print.line()
.redLine('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~')
.redBoldLine( 'There was a migration error with the following db(s):')
.redIndentedLine(dblist)
.redLine('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
}
}
Now we can run:
box task run task main update
And we should see some output:
Getting databases from parent...
Running Liquibase:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Running migrations on:
database1 (jdbc:sqlserver://localhost:1433;integratedSecurity=false;)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Updating Database (database1):
- local
####################################################
## _ _ _ _ ##
## | | (_) (_) | ##
## | | _ __ _ _ _ _| |__ __ _ ___ ___ ##
## | | | |/ _` | | | | | '_ \ / _` / __|/ _ \ ##
## | |___| | (_| | |_| | | |_) | (_| \__ \ __/ ##
## \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___| ##
## | | ##
## |_| ##
## ##
## Get documentation at docs.liquibase.com ##
## Get certified courses at learn.liquibase.com ##
## Free schema change activity reports at ##
## https://hub.liquibase.com ##
## ##
####################################################
Starting Liquibase at 13:32:24 (version 4.9.1 #1978 built at 2022-03-28 19:39+0000)
Liquibase Version: 4.9.1
Liquibase Community 4.9.1 by Liquibase
Running Changeset: main/releases/release-4/1-tag.yaml::tag-release-4::Script
Running Changeset: main/releases/release-4/PROJECT-123.sql::PROJECT-123-1::jimpriest
Running Changeset: main/releases/release-4/PROJECT-123.sql::PROJECT-123-2::jimpriest
Running Changeset: main/releases/release-4/PROJECT-123.sql::PROJECT-123-3::jimpriest
Liquibase command 'update' was executed successfully.
Note: We are running an older version of Liquibase. In newer versions (4.12+) you can add a “liquibase.showBanner” setting to your Liquibase.properties file which will disable the Liquibase startup banner from being shown.
We have over 20 databases we currently run this against and depending on what sort of changes we are making it usually takes 5-10 minutes in our CI environment. Much easier than doing each one manually! :)