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 rollback-sql, and
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.
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: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
databaseType is one of the values from “Type Name” table on our supported databases page.
For finer dialect control, you can specify parameters like
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
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.
- “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)
But wait – there’s more! Aside from being able to provide support for the
-sql commands, you can also perform database comparison operations like
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: