Generating Liquibase migrate file in offline mode

Goal

Generate a liquibase migrate file for a database where we don’t have direct access to

Description

I usually use Liquibase to manage and maintain the database versions for most of the projects I have the possibility to work to. And in some (if not all!) of those projects, there are more than one environment, corresponding to more than one database schema, with different access level constraints. So, it is very common to have restrictions to directly access the production database.

The alternative I was used to implement to generate different migrate.sql files for different databases was using a dump/copy of the DATABASECHANGELOG table in my local database (most of the times, it is a simple database such as MySQL that is very easy to setup and use locally) and then, execute the liquibase:updateSQL file with that table data.

Today, I wanted to try something different, that did not depend on a real connection to the database. Fortunately, Liquibase already supports that with a new feature named offline mode. This recipe explains how we can leverage that to be able to generate a migrate.sql file with a file containing the data from that table, instead of a real physical connection to the database.

How to

First, and before going into the steps of the recipe, notice that I usually create a project structure similar to (which makes it very easy to understand which change sets belongs to which release number from the name in the column FILENAME in the liquibase table DATABASECHANGELOG):

  • PROJECT_ROOT
    • db\changelog (folder containing the base changelog files)
      • db.changelog-master.xml (main changelog file, which includes all files db.changelog-.xml files)
      • db.changelog-.xml (file – one for each release number – that includes all changelog files within folder 1.0.0
      • x.y.z (folder containing all changeset files necessary for release x.y.z)

So, here goes the basic steps necessary (I’m using maven, so I’ll be using maven liquibase plugin):

  1. Create the file databasechangelog.csv РThis file will be used as if it was the table DATABASECHANGELOG. Therefore, it should be initialized with the changesets you wish to be or not marked as already belonging to the database to which you want to generate the migrate.sql file. The format of this file should be like:
    "ID","AUTHOR","FILENAME","DATEEXECUTED","ORDEREXECUTED","EXECTYPE","MD5SUM","DESCRIPTION","COMMENTS","TAG","LIQUIBASE","CONTEXTS","LABELS"
    ... add/init lines if necessary
    
  2. Create a profile for offline mode – Add an offline or similar name profile that does not use the database connection URL but uses the offline protocol format instead. Also, in that format (I have also a dev format, active by default, that connects directly to the development database and updates it during normal project build):
    ...
    <build>
    <resources>
    <resource>
    <directory>${project.basedir}/src/main/resources</directory>
    <filtering>true</filtering>
    <includes>
    <include>**/*.properties</include>
    </includes>
    </resource>
    </resources>
    <plugins>
    <!-- pzenida - added to be able to manage database changes and versionning -->
    <plugin>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-maven-plugin</artifactId>
    <version>3.4.0</version>
    <configuration>
    <propertyFileWillOverride>true</propertyFileWillOverride>
    <propertyFile>target/classes/liquibase.properties</propertyFile>
    <promptOnNonLocalDatabase>false</promptOnNonLocalDatabase>
    <migrationSqlOutputFile>target/classes/migrate.sql</migrationSqlOutputFile>
    </configuration>
    <dependencies>
    <dependency>
    <groupId>${jdbc.groupId}</groupId>
    <artifactId>${jdbc.artifactId}</artifactId>
    <version>${jdbc.version}</version>
    </dependency>
    </dependencies>
    </plugin>
    </plugins>
    </build>
    ...
    <profiles>
    <profile>
    <id>dev</id>
    <activation>
    <activeByDefault>true</activeByDefault>
    </activation>
    <build>
    <plugins>
    <plugin>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-maven-plugin</artifactId>
    <executions> <!-- Override the plugin to update the DB by default -->
    <execution>
    <phase>process-resources</phase>
    <goals>
    <goal>update</goal>
    </goals>
    </execution>
    </executions>
    </plugin>
    </plugins>
    </build>
    </profile>
    <profile>
    <id>offline</id>
    <properties> <!-- Override the connection URL to use offline mode -->
    <javax.persistence.jdbc.url>offline:oracle</javax.persistence.jdbc.url>
    </properties>
    <build>
    <plugins>
    <plugin>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-maven-plugin</artifactId>
    <executions>
    <execution>
    <phase>process-resources</phase>
    <goals>
    <goal>updateSQL</goal>
    </goals>
    </execution>
    </executions>
    </plugin>
    </plugins>
    </build>
    </profile>
    </profiles>
    ...
    
  3. Run your new profile – Go to your project’s base folder (I have the liquibase module as a specific database module in a maven multimodule project) and run something like mvn clean install -Poffline. This command should create a JAR of your module in target/ which contains the migrate.sql file

Explanations

The liquibase offline mode makes it possible to use most of liquibase’s power (it’s not possible to update the database, of course, as there is no database connection!) with a very easy and flexible mechanism that enables us to define a file that will be used as if it was the real table that liquibase uses. This trick may really be useful in environments such as QA or PROD, where developers access is hard and system operators and database administrators do not want or accept using liquibase as the tool to manage the database versions but only SQL files with the changes to be applied.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s