Posted 02 Jul 2015 by Nathan Voxland
There are many, many different processes and requirements companies have for managing their database schemas. Some allow the application to directly manage them on startup, some require SQL scripts be executed by hand. Some have schemas that can differ across customers, some have only one database to deal with.
For people who prefer to execute SQL themselves, Liquibase has always supported an “updateSQL” mode which does not update the database but instead outputs what would be run. This allows developers and DBAs to know exactly what will be ran and even make modifications as needed before actually executing the script.
Before version 3.2, however, Liquibase required an active database connection for updateSQL. It used that connection to determine the SQL dialect to use and to query the DATABASECHANGELOG table to learn what changeSets have already been executed.
With version 3.2, Liquibase added a new “offline” mode. Instead of specifying a jdbc url such as
jdbc:mysql://localhost/lbcat you can use
offline:postgresql which lets Liquibase know what dialect to use. For finer dialect control, you can specify parameters like
Available dialect parameters:
These parameters let Liquibase know what SQL to generate for each changeSet, but without an active database connection you cannot rely on the DATABASECHANGELOG table to track what changeSets have already been ran. Instead, offline mode uses a CSV file which mimics the structure of the DATABASECHANGELOG table.
By default, Liquibase will use a file called “databasechangelog.csv” in the working directory, but it can be specified with a “changeLogFile” parameter such as
It is up to you to ensure that the contents of the csv file match what is in the database. Running updateSQL automatically appends to the CSV file under the assumption that you will apply the SQL to the database. Since the csv file matches a particular database, it isn’t something you normally would store or share under version control because every database can (and probably will) be in a different state. If you do store the files in a central location, you will probably want to at least have a separate file for each database.
By default, the SQL generated by updateSql in offline mode will still contain the standard DATABASECHANGELOG insert statements, so each database that you apply the SQL to will still have a correct DATABASECHANGELOG table. This means that you can switch between a direct-connection update and offline updateSQL as needed. It also means that you can also extract the current contents of the DATABASECHANGELOG table to a CSV file and use that as the file passed to the offline connection to ensure you have the right contents in the file.
If you do not want the DATABASECHANGELOG table SQL included in updateSQL output, there is an “outputLiquibaseSql” parameter which can be passed in your offline url.
Possible outputLiquibaseSql values:
The new 3.4.0 release of Liquibase expands The new 3.4.0 release of Liquibase expands offline support with a new “snapshot” parameter which can be passed to the offline url pointing to a saved database structure. Liquibase will use the snapshot anywhere it would have normally needed to read the current database state. This allows you to use preconditions and perform diff and diffChangeLog operations without an active connection and even between snapshots of the same database from different points in time.
To create a snapshot of your live databases, use the
—snapshotFormat=json parameter on the “snapshot” command.
Command line example:
NOTE: currently only “json” is supported as a snapshotFormat.
You can then use that file with your offline url and any snapshot operations will use it as the database state.
Originally posted to java.dzone.com