I’m a huge fan of Liquibase for handing database creation and migration over the life of an app. I’ve been using it for a commercial product I work on (with many versions in the wild), and it’s been perfect for auto-upgrading client databases when they install a new version of our war file.

One area I haven’t invested enough energy in is handling the bootstrapping of reference data. Enter the loadData refactoring. This little refactoring handles the loading of reference data into your tables (great for populating your standard lookup tables with seeded values).

If you’ve never used this refactoring before, it goes a little like this…

<changeSet id="20130710-add-classification-reference-data" author="Glen" >
        <loadData 
            file="migrations/reference-data/classification.csv"
            tableName="classification"/>
</changeSet>

You feed the refactoring a .csv file of your sample data, with columns in the first row, and you’re up and running. Here’s the sample classification.csv file I’m pumping into the refactoring above:

version,abbreviation,name,weight,description
0,C,Confidential,60,Confidential
0,TS,Top Secret,80,Top Secret
0,P,Protected,50,Protected
0,S,Secret,70,Secret
0,U,Unclassified (No Security Classification),10,Unclassified
0,G,Government (Unclassified),30,Government

And that’s all grand. I’ve omitted the “id” (PK) field on this table in my csv file (which is an autoincrement Identity field in SQL Server), and Liquibase will gracefully handle all the autoincrement fields on the inserts with a problem.

But I’m late to this game, and my clients will have already populated their own reference data in some cases. What to do about a conditional load of that reference data that only fires if the table is empty? Enter the preConditions constraint.

<changeSet id="20130710-add-classification-reference-data" author="Glen" >
        <preConditions onFail="MARK_RAN">
            <sqlCheck expectedResult="0">SELECT COUNT(*) FROM classification</sqlCheck>
        </preConditions> 
        <loadData 
            file="migrations/reference-data/classification.csv"
            tableName="classification"/>
</changeSet>

And now we’re cooking with gas! If the sqlCheck fails, they must already have their own classification data setup, so I’ll just mark the changeset as run, and move on with my day. Exactly what I was after! Hope it saves you some time…

Loving the Liquibase..

&nbsp;