Liquidbase can deploy database changes from the command line, as well as part of an automated build and deployment process. The tool can also detect differences between databases.
There are Open Source and Pro editions available.
Install and Setup:
The free edition is available for download here. I’m using the CLI installer for Windows.
Next, I’ll create a new project for a test setup. In the command line, I’ll navigate to the directory where I want to create the project and run:
liquibase init project
Answer Y to the prompt to set up project with defaults. This install will include database drivers for the most common systems. I’ll be using a Postgres database for my test, so the driver for that would be included.
In the liquibase.properties file, enter the database connection info. There is a source database, which will have the most recent changes applied (such as a dev DB). There’s also the target database, where the changes will be applied.
For each database server, we’ll use a JDBC URL. For my test, I’m using a Postgres database, so it would look like:
jdbc:postgresql://localhost:5432/dbname
I’ve also added default schemas (the default is public for Postgres), using the liquibase.command.defaultSchemaName and liquibase.command.referenceDefaultSchemaName
properties.
This page has a listed of supported database systems, as well as the syntax needed for connecting.
Once everything is setup, we can run the status command to make sure that we can connect:
liquibase status
If we’ve specified a default schema, it must already exist in the target database.
Changelog:
A changelog is a list of all of the changes for a system.
A changeset is the unit of deployment. If there is an error in deploying any item in the changeset, then the changes for the entire set are rolled back, and no later sets are run.
The changeset can be made up of SQL statements, or by changetypes. Each individual changetype is a database change (Like creating a table, or adding a column). They can be defined in JSON, XML, or YAML.
The changesets are applied in the order that they appear in the changelog.
Liquibase recommends that each changetype only contain one change. I would be inclined to have everything needed for a specific change in one changeset, so that everything gets rolled back if there is an error.
We can define a default changelog in the Properties file with the changeLogFile property. A default changelog file (example-changelog.sql) is created when a project is initialized, so that can be used as a template.
We can also specify a changeset other than the default with the –changelog-file option in a command.
Liquibase will create DATABASECHANGELOGLOCK and DATABASECHANGELOG tables to track what has been deployed.
For my tests, I’m going to use SQL to define my changelog. I’ve posted my test changelog here.
We can include data changes as part of the changesets as well, to include any reference data that is needed.
Execution:
Running the status command will also display the changesets that have not yet been deployed.
Once we’re ready to deploy, we run:
liquibase update
This will deploy all pending changesets (There are options to only deploy certain ones). If everything is successful, we’ll see a summary of the changesets deployed.
In my tests, I’m deploying to an empty target database. If I wanted to start using Liquibase for an existing database, I could use the generate-changelog command to generate a changelog for the current state.
Running the diff command will give a list of any differences between the source and target databases:
liquibase diff
Running the diff-changelog command can add any diffs to the change log for you. However, when I tried this for adding a column, the table name didn’t include the schema, so that would fail. It’s a simple fix to correct that, but always a good idea to closely review any generated code.
The changelog also allows a rollback tag, where we can add statements to reverse the changes for a changeset with the rollback command.
Wrap-Up
I was very impressed with Liquibase and everything it can do. This is a great tool to set up a repeatable process to deploy database changes and to check on drift between deployments.
Links:
SQL Rob 