DATABASECHANGELOGHISTORY table

Liquibase uses the DATABASECHANGELOGHISTORY (DBCLH) table to record a history of all changes it makes to the database. It is a more powerful version of the DATABASECHANGELOG table.

The DATABASECHANGELOGHISTORY table is available in Liquibase 4.27.0 and later. It is only available to Liquibase Pro users, so you need a valid Liquibase Pro license key to use it.

The DATABASECHANGELOG (DBCL) table tracks all deployments you make to your database. However, it only shows changes that have been applied and are currently active. If you roll back a change, that row is removed from the DBCL.

In contrast, the DBCLH table tracks every change you make to your database with Liquibase, including updates, rollbacks, drop-alls, changelog syncs, tags, and runOnChange activity.

A table in the H2 database console displaying a record of all previous Liquibase operations

Uses

The DBCLH table can help developers and DevOps engineers easily see:

Which changes were made to the database and then rolled back
How many times a change has been made, rolled back, and re-deployed
When rollbacks happened in relation to other changes
Any other changes that modified your database at any time

You can use the DBCLH table to understand how the database reached its current state. This reveals your team's deployment and rollback patterns, such as frequent rollbacks after deploying a certain kind of change or number of changes. With this information, you can easily identify whether the source of deployment issues are problems with the changes themselves or with deployment processes.

Note: The DBCLH table is designed to provide Liquibase users with an accessible way to track changes made to the DBCL table. It is not necessarily designed to meet security or regulatory needs.

Supported databases

The DBCLH table is supported for the following databases: DB2 LUW, EnterpriseDB, Google BigQuery, H2, HSQLDB, MariaDB, MSSQL Server, MySQL, Oracle, PostgreSQL, Snowflake, and SQLite.

Use the DATABASECHANGELOGHISTORY table

The following section describes global arguments you can set in your command line, liquibase.properties file (defaults file), in a flow file, or as an environment variable.

Enable or disable the DBCLH

The DATABASECHANGELOGHISTORY table is disabled by default. To create the table for the first time, you must:

Set the --license-key parameter to your Liquibase Pro license key
Set the --dbclhistory-enabled parameter to true
Run a command like update in your command line or in a flow file

If you want to stop Liquibase from recording your database changes in the DBCLH table, set the --dbclhistory-enabled parameter to false. You can re-enable the DBCLH table later by setting --dbclhistory-enabled to true.

Note: The DBCLH cannot retroactively capture database changes that occurred while it was disabled. If you disable and re-enable the DBCLH multiple times while making database changes, your DBCLH table will not be comprehensive.

Display the DBCLH

To display the DBCLH table in STDOUT, run the dbcl-history command. For example, to generate human-readable JSON output in the CLI:

liquibase dbcl-history --format=JSON_PRETTY --verbose

You can use the --output-file global parameter to send command output to a file:

liquibase --output-file=dbclh_2024-03-15.json dbcl-history --format=JSON_PRETTY --verbose

Capture extensions or SQL

By default, whenever the DBCLH table is enabled, Liquibase automatically captures all extension information when you run commands. It stores that data in the DBCLH table under the column EXTENSIONS. You can control this behavior by setting --dbclhistory-capture-extensions.

Also, Liquibase automatically captures all SQL it generates in commands like update and rollback. It stores that SQL in the DBCLH table under the column EXECUTEDSQL. You can control this behavior by setting --dbclhistory-capture-sql.

If you want to disable extension information or SQL capturing, set either parameter mentioned previously to false.

Set a severity level

By default, Liquibase returns an exit code of 1 if it halts while recording an operation to the DBCLH table. If you want to change the severity level of the DBCLH table, set the --dbclhistory-severity parameter to a different value.

Drop the DBCLH

If you want to drop the DBCLH table entirely, you can run the drop-all command with --drop-dbclhistory set to true:

liquibase drop-all --drop-dbclhistory=true

Columns

Column	Standard data type	Description
`ID`	`VARCHAR(255)`	Value from the changeset `id` attribute.
`AUTHOR`	`VARCHAR(255)`	Value from the changeset `author` attribute.
`FILENAME`	`VARCHAR(255)`	Path to the changelog. This may be an absolute path or a relative path depending on how the changelog was passed to Liquibase. For best results, it should be a relative path. The `logicalFilePath` attribute can be used on the changelog or on individual changesets.
`COMMAND`	`VARCHAR(255)`	Name of the command the user specified.
`INTERNAL_COMMAND`	`VARCHAR(255)`	Internal name of the command Liquibase ran.
`ARGUMENTS`	`VARCHAR(2500)`	A list of arguments the user specified, including the command.
`OUTCOME`	`VARCHAR(255)`	Description of how the changeset was executed. Possible values include `EXECUTED`, `FAILED`, `SKIPPED`, `RERAN`, and `MARK_RAN`.
`START`	`TIMESTAMP`	Timestamp in the user's local timezone that the operation started.
`END`	`TIMESTAMP`	Timestamp in the user's local timezone that the operation ended.
`TAG`	`VARCHAR(255)`	Tracks which changesets correspond to tag operations.
`CONTEXTS`	`VARCHAR(255)`	Context(s) used to execute the changeset.
`LABELS`	`VARCHAR(255)`	Label(s) used to execute the changeset.
`MD5SUM`	`VARCHAR(35)`	Checksum of the changeset when it was executed. Used on each run to ensure there have been no unexpected changes to changesets in the changelog file. See Changeset Checksums.
`DESCRIPTION`	`VARCHAR(255)`	Short, auto-generated, human-readable description of the changeset.
`COMMENTS`	`VARCHAR(255)`	Value from the changeset `comment` attribute.
`DEPLOYMENT_ID`	`VARCHAR(10)`	All changesets deployed with the same operation will have the same unique identifier.
`EXECUTEDSQL`	`CLOB`	The SQL Liquibase executed in the operation.
`LIQUIBASEVERSION`	`VARCHAR(20)`	Version of Liquibase used to execute the changeset.
`HOSTNAME`	`VARCHAR(255)`	Name of the machine Liquibase executed the operation on.
`SYSTEMUSER`	`VARCHAR(255)`	Name of the system account who initiated the Liquibase operation.
`INTERFACE`	`VARCHAR(255)`	The way you called Liquibase, such as the CLI or a flow file.
`EXTENSIONS`	`CLOB`	A list of extensions to Liquibase, including pre-installed drivers.

Troubleshooting

DBCLH does not exist

If you try to display the DBCLH table but it does not exist, Liquibase returns exit code 0 and displays the following message:

WARNING: The DATABASECHANGELOGHISTORY does not currently exist. Please set your Pro License key, and set '--dbclhistory-enabled=true' on CLI or via Environment variable, etc and then run an update or rollback command. Then try 'liquibase dbcl-history' command again.

To create the DBCLH table, run the update command with the --dbclhistory-enabled parameter set to true in your command line, liquibase.properties file (defaults file), in a flow file, or as an environment variable. For example, in the CLI:

liquibase --dbclhistory-enabled=true update

When successful, Liquibase produces the following output:

INFO: A new table 'DATABASECHANGELOGHISTORY' was created in 'DEV' to track database changing Liquibase operations. The Pro property 'liquibase.dbclhistory.enabled=true' was configured on the CLI, or as Environment Variable or defaults file property, etc. To pause collecting history, set to 'false'.

DBCLH is disabled

If you try to display the DBCLH table but it is disabled, Liquibase returns exit code 0 and displays the following message:

WARNING: The DATABASECHANGELOGHISTORY is not currently enabled. Please set your Pro License key, and set '--dbclhistory-enabled=true' on CLI or via Environment variable, etc. Then try 'liquibase dbcl-history' command again.

Database is not supported

If you try to run the DBCLH table on an unsupported database, Liquibase returns exit code 0 and displays the following message:

INFO: Unable to enable 'DATABASECHANGELOGHISTORY' functions as support for database <DATABASE> is not yet implemented in Liquibase.