Database release using Liquibase

When releasing the build we have to provide our client with a database used by our application. I would like to present one possible solution with the use of Liquibase, which was successfully implemented in several projects.

Necessary tools

The whole process will be presented on Windows with the use of popular batch files, which will connect all pieces of our puzzle. Of course, it is only an example and proposed tools can be easily changed to e.g. PowerShell.

First of all, we need some automated way to release builds; despite the fact that scripts presented further in this document can be fired manually, it is way better to connect it with continuous integration mechanism (CI).

We will use Liquibase for database versioning. It is an open source Java tool (we have to remember about JRE and sql server driver) helpful with change management in a database.

Ultimately, we need 2 databases for the purpose of releases. One will serve to generate full scripts, whereas another one will be used to generate incremental scripts since the last release.

Overview of the process

The whole process is quite simple, however it requires a few steps:

1. We generate the full script based on an empty database. Due to that we can later create new database and quickly create all necessary tables, procedures etc.
2. We check whether it is necessary to generate the incremental script – due to that we will be able to easily and quickly update database to the newest version if we have changed something there since the last release.

• If it is necessary, we generate this kind of script based on our incremental database and save it in “repository” (for our purposes, it is enough to have some folder on a disk).
• If it is necessary, we update an incremental database to the latest version – due to that we will have to consider only the latest changes in the next release.

3. We copy full and incremental scripts to the build.

• It is important to copy all incremental scripts, so that we can update database from several versions back.

Besides that, for the convenience we can add empty copy of a database to the package.

In the end, the structure of folders looks in the following way:

Process in details

If someone would like to implement a similar solution in their project, below you can find a description of ready-to-use scripts, which just need to be added to CI or simply fired (first adjust them to your own needs if necessary).

To manage the whole process we’ll use two additional files:
UpdateDatabase.bat and GenerateReleaseScripts.bat

UpdateDatabase.bat

It is a script used to either update databases or generate sql scripts. It directly uses Liquibase.bat (available with Liquibase).

Actually what it does is just setting up proper command line parameters and executing command:

:: ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: EXECUTE LIQUIBASE
:: ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
set fullCommand=liquibase-3.2.0liquibase.bat %AuthParams% --classpath=sqljdbc4.jar --driver=com.microsoft.sqlserver.jdbc.SQLServerDriver --url="jdbc:sqlserver://%serverPath%;databaseName=%dbname%;%SecurityString%" --changeLogFile=%masterLogFile% %command%

if %toFile%==1 (
	%fullCommand% > %outputFile% 	
)	else (
	%fullCommand%
)

GenerateReleaseScripts.bat

Actually, this script is just a wrapper for setting and passing parameters to UpdateDatabase.bat that reflects previously described process.

Setting initial values

At the beginning, we have to set appropriate values for the script. We can uncomment following links for tests, however CI should set them for us.

:: these values have to be provided:
:: ------------------------------------------------------------------------------
:: set emptyDbName=CMFULL
:: set lastReleaseDbName=CMINCR
:: set fullScriptOutputFile=output-full.sql
:: set incrementalScriptOutputFile=output-incr2.sql
:: set destinationDirectoryPath=d:\Temp\cm-repo
:: set incrementalScriptsRepository=d:\Temp\cm-incr
:: ------------------------------------------------------------------------------

Values:

• emptyDbName – name of the database that will always be empty, and will serve to create full scripts.
• lastReleaseDbName – name of the database that will be used to generate incremental scripts.
• fullSciptOutputFile – a path to full-script file; CI will usually insert build version somewhere in the filename.
• incrementalScriptOutputFile – a path to incremental-script file; CI will usually insert build version somewhere in the filename.
• destinationDirectoryPath – a path to the folder, which will keep all scripts, backups etc.
• incrementalScriptsRepository – a path to folder with all previous incremental scripts.

In CI it looks more or less like this (an example from CruiseControl.NET):

<exec>
 <baseDirectory>db</baseDirectory>
 <executable>GenerateReleaseScripts.bat</executable>
 <environment>
 <variable name="emptyDbName" value="$(EmptyDbName)" />
 <variable name="lastReleaseDbName" value="$(IncrementalDbName)" />
 <variable name="incrementalScriptOutputFile" value="$(ProjectFileName).Increment.$[$CCNetLabel].sql" />
 <variable name="fullScriptOutputFile" value="$(ProjectFileName).Full.$[$CCNetLabel].sql" />
 <variable name="destinationDirectoryPath" value="$(TemporaryPath)database" />
 <variable name="incrementalScriptsRepository" value="$(IncrementalScriptsRepositoryPath)" />
 <variable name="serverPath" value="localhostSQLEXPRESS" /> 
 <variable name="securityMode" value="SQL" />
 <variable name="username" value="BuildUser" />
 <variable name="password" value="BuildUserPassword" />
 </environment> 
</exec>

As you can see we set only paths, taking care that the version of a build would be included in the name of the output files.

Next, in majority of cases, we only call UpdateDatabase.bat with appropriate parameters.

Creating the structure of directories

We delete the existing folder with scripts and backups and create the new one:

if  exist "%destinationDirectoryPath%" del /F /S /Q "%destinationDirectoryPath%";
mkdir "%destinationDirectoryPath%";
mkdir "%destinationDirectoryPath%\incremental";
mkdir "%destinationDirectoryPath%\full";
mkdir "%destinationDirectoryPath%\backup";

Creating a full script

Creating the full script comes down to starting Liquibase with UpdateSql command on an empty database and redirecting the results to the file:

set outputFile=%destinationDirectoryPath%\full\%fullScriptOutputFile%
set dbName=%emptyDbName%
set command=UpdateSql
call UpdateDatabase.bat 

Checking, whether it is necessary to create an incremental script

In Liquibase there’s a “status” command, which prints the number of change sets that haven’t been executed in the database. Therefore we execute the following command…

set toFile=0
set dbName=%lastReleaseDbName%
set command=status
call UpdateDatabase.bat | findstr /I "change sets have not been applied";

… and search it in terms of text indicating that not all scripts have been executed. If database is up-to-date, we skip creation of the incremental script and an update of the database (goto:previousScriptsCopy):

if %errorlevel% == 1 (
	@echo Database is up to date, no incremental script needed.
	goto :previousScriptsCopy
)

Creating an incremental script and update of an incremental database

However, if the database is not up-to-date (meaning that during development we have implemented new change sets) we have to create new script with changes:

set outputFile=%incrementalScriptsRepository%%incrementalScriptOutputFile%
set dbName=%lastReleaseDbName%
set command=UpdateSql
call UpdateDatabase.bat 

and update the incremental database, so that it would be ready for the next release:

set toFile=0
set dbName=%lastReleaseDbName%
set command=Update
call UpdateDatabase.bat 

Copying incremental scripts to build

At the end, we copy all previously generated incremental scripts to build, so that the package would be complete and it would be possible to recreate database e.g. from version 6 to 9.

xcopy /D /Y "%incrementalScriptsRepository%*.*" "%destinationDirectoryPath%incremental"

Summary

It is worth reminding that tools used are –indeed – only tools. Instead of Liquibase we can as well use Fluent Migrator and for executing the process we can use Ant instead of a command line.

It’s important that the process serves well to our needs. In this case it gives us possibility of easily generating SQL scripts that a client can run herself on her database (as it happens quite often that someone doesn’t know Liquibase, but executing SQL scripts does not cause any issues).

What is more we, as developers, keep database versioning and, when there’s a need, we can easily examine whether all necessary changes to database have been applied.

One more thing to note – the more builds we have the more incremental scripts we generate. We should make sure they don’t get lost somewhere along the way.

Files

Download sample.zip