Liquibase (Track DB Changes)

Liquibase is a database schema change management tool that helps track, version, and deploy database changes - particularly useful for changing database schemas over time. It works similar to version control systems (like Git), but specifically for database changes (and Schema Evolution).

In the context of the evolution of database management, Liquibase was significant because it introduced the concept of “migrations as code” where database schema changes are:

1. Written as code (in XML, SQL, YAML, or JSON)
2. Version controlled alongside application code
3. Applied in a repeatable, automated way
4. Tracked to maintain database change history

There is an open-source version on GitHub, but also a closed source.

Features from Open Source | Liquibase.

1
2
3
4
5
6
7
8

--liquibase formatted sql
--changeset nvoxland:create-test-table
CREATE TABLE testTable(
columnName1 VARCHAR (355)
);

--rollback DROP TABLE
--rollback testTable

Liquibase makes it easy to define database changes in a format that’s familiar and comfortable to each user and then automatically generates database-specific SQL for you.

Liquibase uses changesets to represent a single change to your database.

Liquibase allows you to specify the database change you want using SQL or several different database-agnostic formats, including XML, YAML, and JSON. Developers can abstract the database code to make it extremely easy to push out changes to different database types. The  abstract format options also offer more flexibility and many more features over a formatted SQL changelog.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

--liquibase formatted sql
--changeset <author name>:<a unique identifier for the SQL changeset>
<SQL statements go here>
<SQL statements go here>
--rollback <rollback SQL statements>
--rollback <rollback SQL statements>

--changeset <author name>:<another unique identifier>
<SQL statements go here>
<SQL statements go here>
--rollback <rollback SQL statements>
--rollback <rollback SQL statements>

Database changes are managed with one ledger that we call the  changelog. Liquibase tracks which changesets have or have not been deployed in a  tracking table.

Using a unique identification scheme (an ID and author, along with the name and path of the file) makes it easy to manage and reorder database changes. Since there are multiple factors that drive the uniqueness of a given change, it also reduces developer conflicts and collisions.

Changelogs and tracking tables allow Liquibase to help teams track and version database changes and deploy changes.

• Track and version database changes
  Developers know which changes have been deployed to the database and which changes have not yet been deployed.
• Deploy changes
  Liquibase compares the changelog against the tracking table and only deploys changes that have not been deployed to the database.

Even the free,  open-source version of Liquibase allows you to undo changes you have made to your database, either automatically or via custom rollback SQL with the  rollback command.

Liquibase Pro adds  Targeted Rollbacks, which allow users to rollback a specific change or set of changes without rolling everything else back, saving a lot of time and headaches.

If your use case requires you to selectively deploy changes, Liquibase is a perfect solution. Since Liquibase uses one ledger (changelog), it’s very straightforward to  add labels and contexts to ensure that your deployments are defined in one place.

1
2
3
4
5
6

-- liquibase formatted sql changeLogID:123

-- changeset nvoxland:example labels:v1 context:all
create table "public"."customer_customer_demo" ("customer_id" CHAR not null, "customer_type_id" CHAR not null, constraint "pk_customer_customer_demo" primary key ("customer_id", "customer_type_"));

-- changeset nvxoland:example2 labels:v1 context:all create table "public"."customers" ("customer_id" CHAR not null, "company_name" VARCHAR(40) not null, "contact_name" VARCHAR(30), "contact_title" VARCHAR(30), "address" VARCHAR(60), "postal_code" VARCHAR(10), constraint "pk_customers" primary key ("customer_id"));

Liquibase Pro Link enables users to snapshot and work with stored logic. Snapshots allow you to get a static view of your database at a particular point in time and is useful for reporting and safeguarding your data by comparing databases (performing diffs) to find differences.

Liquibase Enterprise provides a dashboard with insights into your database changes.

• Liquibase DMC is our on-prem dashboard and reporting tool designed for our Liquibase Enterprise users.

Package your SQL scripts and test the corresponding rollback scripts at the same time.  Liquibase Enterprise validates the SQL scripts as well as the rollback scripts. If the rollbacks fail to revert the database back to its original state, then the build fails, and you’re alerted. The Liquibase command returns an error that you’ll see on the command line. Instant feedback loop!

Automatically enforce best practices set by your DBAs, including naming conventions, grants & revokes, and rollback scripts. Quality checks provide instant feedback on database code. Developers no longer have to wait for hours or days for a DBA to provide feedback on their work.
Quality Checks

Forecast the impact of changes on a target database in any environment. Take the risk out of deployment!  Liquibase Enterprise checks to ensure that nothing has changed in the target database that would cause changes that worked in a lower environment to fail in the target environment.

Never hear “it worked in Test but failed in Stage” ever again.

• automatic Schema Drift detedction

To start using Liquibase quickly and easily, you can write your migration scripts in SQL.

To take advantage of database abstraction abilities that allow you to write changes once and deploy to different database platforms, you can specify database-agnostic changes in XML, JSON, or YAML

Introduction to Liquibase

Liquibase uses SQL, XML, JSON, and YAML  changelog files to list database changes in sequential order. Database changes have the format of  changesets. Changesets contain  Change Types, which are types of operations to apply to the database, such as adding a column or primary key.  Context,  label, and  precondition changelog tags help precisely control when a database change is made and to which database environment it is deployed.

When you deploy your changes, Liquibase creates two tables in your database:  DATABASECHANGELOG and  DATABASECHANGELOGLOCK.

The DATABASECHANGELOG table tracks deployed changes so that you have a record. Liquibase compares the changesets in the changelog file with the DATABASECHANGELOG tracking table and deploys only new changesets.

DATABASECHANGELOGLOCK prevents multiple instances of Liquibase from updating the database at the same time. The table manages access to the DATABASECHANGELOG table during deployment and ensures only one instance of Liquibase is updating the database.

Liquibase offers many ways to manage your database changes:

• Run the  command-line client (CLI).
• Use the  Liquibase Java API and integrate Liquibase into your application to deploy database changes on the application startup.
• Integrate Liquibase into your build processes using  Maven,  Spring Boot,  Ant,  Jenkins,  GitHub Actions, or other CI/CD tools.
• With ephemeral environments in  Docker.

The  update command deploys any changes that are in the changelog file and that have not been deployed to your database yet.

The update command is typically used to apply database changes that are specified in the changelog file to your database.

When you run the update command, Liquibase sequentially reads changesets in the changelog file, then it compares the unique identifiers of id, author, and path to filename to the values stored in the DATABASECHANGELOG table.

• If the unique identifiers do not exist, Liquibase will apply the changeset to the database.
• If the unique identifiers exist, the MD5Sum of the changeset is compared to the one in the database.
• If they are different, Liquibase will produce an error message that someone has changed it unexpectedly. However, if the status of the [runOnChange](https://docs.liquibase.com/concepts/changelogs/attributes/runonchange.html) or runAlways changeset attribute is set to TRUE, Liquibase will re-apply the changeset.

In Liquibase Pro 4.25.1 and later, you can automatically generate a database  Update Report summarizing this command.

To run the update command, specify the driver, classpath, and URL in the Liquibase properties file. For more information, see  Create and Configure a liquibase.properties File. You can also specify these properties in your command line.

Then run the update command:

1

liquibase update --changelog-file=example-changelog.xml

Liquibase handles schema changes like column renames and type changes through specific change types in its changesets. Here’s how it manages these operations:

For ALTER RENAME operations:

• Column renames use the renameColumn change type
• Table renames use the renameTable change type
• These operations preserve the existing data while changing the structure

For ALTER UPDATE TYPES (changing column data types):

• Liquibase uses the modifyDataType change type
• The behavior depends on database compatibility and the specific type conversion

When the database contains data:

• Liquibase attempts to preserve existing data during schema changes
• For simple changes like renaming, data is maintained intact
• For type changes, Liquibase will try to convert the data to the new type
• If the conversion is compatible (like VARCHAR(50) to VARCHAR(100)), data is preserved
• For incompatible conversions (like VARCHAR to INTEGER), behavior depends on:
  • The database system’s conversion rules
  • Whether the data can be validly converted
  • Constraints on the column

If a data conversion might fail, Liquibase provides options to:

1. Use preconditions to check data before attempting changes
2. Write custom SQL for more complex migrations
3. Use multiple changesets to handle the migration (e.g., add new column, copy/transform data, drop old column)

loadUpdateData
this function can even run and load data from source and MERGE if data existis, see example in above link

• no more open-sourced? Liquibase continues to advertise itself as “open source” despite license switch · Issue #7374 · liquibase/liquibase

Origin: Building a Data Engineering Project in 20 Minutes : dataengineering , We use at HelloDATA BE in 2023-09-11.
References:

• Similar to Schema Registry from Apache Kafka but open-source
  Created 2022-04-18