Versioning your database using Liquibase

Delivering database changes through the various environments and into production has always required a lot of overhead. You would have to write scripts, keep track of the order that the scripts need to be run and provide detailed instructions to the DBA’s when running the scripts. Add multiple developers into the mix and this becomes difficult to maintain and control. Bring in Liquibase.

Liquibase is a fantastic tool that allows you to use your version control system to version your database scripts and then to use your CI server to deploy the changes into your environments or to generate the SQL scripts for the DBA’s to review and run.

I had a requirement whereby we wanted to execute our database changes directly into the development and test environments. However once we moved into UAT we were required to generate the SQL scripts for the DBA’s to review and execute on the UAT and finally the production environment. This is where the flexibility and power of Liquibase really comes to the fore.

The progression of database changes would happen as follows:

1. Developer makes changes into the development environment directly using Liquibase from their IDE via maven.
2. Once the changes are committed, the overnight build will run the change into the testing environment along with the deployment of code into the testing environment.
3. When we are ready for UAT the build will generate the SQL file containing all the changes for the DBA to review and run into the UAT environment.
4.  The same file executed on UAT will be promoted to production.

For this example I created a VirtualBox VM with Linux Mint and installed Oracle XE onto the box and created two schemas to replicate a development and production environment. For installing oracle onto the Linux Mint VM I followed these fantastic instructions, I would never have got the install right if it was not for the detailed instructions, and I most likely would have just reverted to MySQL.

So first off I created a separate project to house our database scripts, and arranged the project as per the best practices with a file per release referenced in the master file. Below is the example of the change-log-master.xml.

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd">
  <preConditions>
     <dbms type="oracle" />
  </preConditions>
  <include file="db.changelog-13.8.xml" relativeToChangelogFile="true" />
</databaseChangeLog>

I have created two properties files, one for the development environment and another for the “production” environment. Below is an example of the development properties files.

changeLogFile=db/ddl/db.changelog-master.xml
driver=oracle.jdbc.OracleDriver
url=jdbc:oracle:thin:@x.x.x.x:1521/xe
username=dev
password=dev
verbose=true
dropFirst=false

I then setup Maven to manage the dependencies and the build.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>

    <groupId>com.craigew.database</groupId>

    <artifactId>LiquibaseScripts</artifactId>
    <version>1.0-SNAPSHOT</version>
    <dependencies>
      <dependency>
        <groupId>com.oracle</groupId>
        <artifactId>ojdbc6</artifactId>
        <version>11.2.0</version>
      </dependency>
    </dependencies>
    <build>
    <plugins>
      <plugin>
        <groupId>org.liquibase</groupId>
        <artifactId>liquibase-maven-plugin</artifactId>
        <version>2.0.3</version>
        <configuration>
           <changeLogFile>src/main/liquibase/changelog/db.changelog-master.xml</changeLogFile>
           <propertyFile>src/main/liquibase/properties/liquibase-${env}.properties</propertyFile>
           <promptOnNonLocalDatabase>false</promptOnNonLocalDatabase>
           <verbose>true</verbose>
       </configuration>
       <dependencies>
         <dependency>
           <groupId>org.liquibase.ext</groupId>
           <artifactId>liquibase-oracle</artifactId>
           <version>1.2.0</version>
         </dependency>
       </dependencies>
    </plugin>
  </plugins>
 </build>
</project>

To run the scripts against the different environments I have used Maven profiles to determine what I want Liquibase to do. In the development environment Liquibase must execute the scripts directly onto the database when we do a Maven install, but on the “production” environment I want Liquibase to generate the SQL script. Below is an extract of the profiles from the pom with the two different profiles.

<profiles>
  <profile>
    <id>dev</id>
    <activation>
        <activeByDefault>true</activeByDefault>
    </activation>
    <build>
      <plugins>
        <plugin>
           <groupId>org.liquibase</groupId>
           <artifactId>liquibase-maven-plugin</artifactId>
           <executions>
             <execution>
               <phase>install</phase>
               <goals>
                  <goal>update</goal>
               </goals>
             </execution>
           </executions>
        </plugin>
      </plugins>
     </build>
 </profile>
 <profile>
    <id>release</id>
     <build>
       <plugins>
         <plugin>
           <groupId>org.liquibase</groupId>
           <artifactId>liquibase-maven-plugin</artifactId>
           <configuration>
               <migrationSqlOutputFile>src/main/liquibase/output/migration-release-${version}.sql
               </migrationSqlOutputFile>
           </configuration>
           <executions>
             <execution>
               <phase>install</phase>
               <goals>
                   <goal>updateSQL</goal>
               </goals>
              </execution>
           </executions>
         </plugin>
      </plugins>
   </build>
 </profile>
</profiles>

You will notice in the above extract from the pom that the goals differ between the two profiles. The dev profile executes the “update” goal, whereas the release goal executes the “updateSQL” goal.

The update goal executes the changes directly onto the database, with the updateSQL goal generating the required SQL scripts to affect the changes described in the XML. By default the script is generated into the target directory in a file called migrate.sql. For this example I output it to a different folder with the release version appended to the file name.

So with the empty schema’s, that I setup earlier, available in my local oracle instance I am ready to run my first change.

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd">
<changeSet id="8.0_1" author="cw">
   <createTable tableName="person">
       <column name="address" type="varchar(255)"/>
   </createTable>
   <rollback>
       <dropTable tableName="person"></dropTable>
   </rollback>
 </changeSet>
</databaseChangeLog>

This change simply creates a table with a single column. All our changeSets have a rollback script as a standard.

Running a Maven install from my IDE with the env property set to dev will execute this change directly against the database and create the new table for me.

If you look at your schema the new table is created as expected, but Liquibase has generated two other “helper” tables as well. DATABASECHANGELOG keeps track of all your changes so that they are not run more than once in an environment, and DATABASECHANGELOGLOCK is used by Liquibase to prevent multiple developers updating the database at the same time.

I can now write the necessary code to access this table along with the appropriate tests.

Now when we are ready to move to the UAT environment I can run the build using the following parameters.

mvn install -P release -Denv=prod

This will run Liquibase using the prod properties file (liquibase-dev.properties) with the release profile. And because we have used the updateSQL goal the SQL script is generated. The generated SQL script goes through a final review by the DBA’s and is executed in the UAT.environment.

At this point we would normally create a release branch for the short period we are in UAT. At the same time we create our next change log in trunk so we can start making changes for our next release.

By using Liquibase we have been able to eliminate waste from our development lifecycle allowing us to be leaner in our delivery. We no longer have to create multiple database deployment documents for our DBA’s. The Liquibase scripts in our subversion repository are the only source of the truth and we also have a repeatable process with no human intervention, apart the DBA’s manually running the script into UAT and production. However the ultimate goal is to have one click deployment into all our environments, with no DBA needing to run the scripts.

A complete example project is available at https://github.com/craigew/LiquibaseIntegration.