July 2, 2015

Using Liquibase Without Database Access

Updated January 24, 2022

There are many, many different ways users need to interact with their database using Liquibase. For some users, the target database isn�t even physically accessible to Liquibase. We have some customers in financial services and in the government using Liquibase without any internet access at all and without direct access to the database. In this blog, I'll walk through how you can use Liquibase without direct database access.

When working with a database without access, you can use Liquibase to generate SQL scripts and then have the team with access to the target database run the scripts. The SQL scripts allow you to act as the bridge between Liquibase and the actual target database in whatever way works for you. Most commands have a -sql suffixed version of the command which will generate a SQL script of what it would have run rather than actually executing the logic. For example, liquibase update-sql, liquibase rollback-sql, and liquibase changelogsync-sql.

However, when running these commands, it's important to understand that Liquibase needs to know two things:

Which database type to generate SQL for (vendor and in some cases, the version)
Which changesets have been applied in the past

Use an Equivalent Database

One option for relaying this information to Liquibase is to run your -sql command against a database that is the same kind of database and has the same databasechangelog table content as your inaccessible target database. This can be a backup of the target database, or perhaps a database that is always your last stop in the pipeline before the inaccessible one.

Running liquibase update-sql against your “equivalent” database will not change the database you run Liquibase against, but it will generate the SQL necessary to make it match what the changelog requests both for that database AND for any other databases of the same type and with the same databasechangelog table information. Therefore, you can run the generated SQL first against your equivalent database to ensure it works, and then run it against your inaccessible database. Then, the next time you run liquibase update-sql against your equivalent database you will get a new SQL script with the new changesets.

Use an Offline Database

What if you don’t want to deal with managing an equivalent database simply to generate SQL? After all, Liquibase really just needs to know the kind of database and what changesets were run on it before.

You can provide this information to Liquibase with an “offline” database URL. Instead of specifying a jdbc URL, such as jdbc:mysql://localhost/lbcat, you can use offline:mysql or offline:postgresql which lets Liquibase know what kind of database you are connecting to, without actually needing a connection.

The syntax for the offline URL is offline:<databaseType> where databaseType is one of the values from “Type Name” table on our supported databases page.

For finer dialect control, you can specify parameters like offline:mysql?version=3.4&caseSensitive=false

Available dialect parameters:

version: Standard X.Y.Z version of the database
productName: String description of the database, like the JDBC driver would return
catalog: String containing the name of the default top-level container (database in some databases schema in others)
caseSensitive: Boolean value specifying if the database is case sensitive or not

Offline Database History

The above offline URL parameters let Liquibase know the kind of database you need to generate SQL for, but most -sql operations also need to know which changesets have run before, and without an active database connection you cannot rely on the DATABASECHANGELOG table like normal.

Instead, when using an offline URL Liquibase uses a CSV file that mimics the structure of the DATABASECHANGELOG table.

By default, Liquibase uses a file called databasechangelog.csv in your working directory, but the actual path can be specified with a changeLogFile parameter such as offline:mssql?changeLogFile=path/to/file.csv

It is up to you to ensure that the contents of the CSV file match what is in the database. Running update-sql automatically appends to the CSV file under the assumption that you will apply the SQL to the database. Since the CSV file matches a certain database, it is not 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 have a separate file for each managed 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 update-sql 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 correct content in the databasechangelog.csv file for that particular database.

If you do not want the DATABASECHANGELOG table SQL included in updateSQL output, there is an outputLiquibaseSql parameter that can be passed in your offline URL.

Possible outputLiquibaseSql values:

“none” will output no DATABASECHANGELOG statements
“data_only” will output only INSERT INTO DATABASECHANGELOG statements
“all” will output CREATE TABLE DATABASECHANGELOG if the CSV file does not exist as well as INSERT statements (default value)

Offline Snapshots

But wait – there’s more! Aside from being able to provide support for the -sql commands, you can also perform database comparison operations like diff, diff-changelog and generate-changelog.

In order to do comparison operations like this, the offline URL accepts a “snapshot” parameter which gives the location of a json file containing the snapshot of a live database. You generate the json file by running liquibase snapshot –snapshot-format=json against your target database. Like the SQL files from the commands mentioned previously, the snapshot json file acts as the intermediary between Liquibase and databases it cannot access directly.

Once you have generated a snapshot from your non-accessible database, you can run diff operations between your actual databases and an offline:mysql?snapshot=path/to/snapshot.json reference-url parameter and you can see what is different between the two databases.

You can even use offline:mysql?snapshot= settings for both the URL and reference-url arguments which can even do things like letting you store various versions of the snapshot file to compare your schemas at different points in history.

Summing It Up

There are a lot of different ways development teams use Liquibase. If you have more questions about using Liquibase for your specific use case, our team and our community can help. Here are some resources you can use to get up and running: