Get Started with the checks Command

Note: This is a Liquibase Pro feature, so you need a Liquibase Pro License Key to use it.

To build quality, security, and compliance into your database development workflow, start using the Liquibase checks command. Liquibase allows executing checks against a changelog and customize the checks configuration to catch specific scenarios.

Quality Checks can run at any point in the workflow. You can set changelog checks to run before deploying any changes, and then run them after deployment to ensure your changelog is still following the rules you intended. The same rules apply to database checks. You can run a database check before an update to be sure all is well before deployment, and you can run checks after an update to ensure all DB rules or standards are still intact.

Prerequisites

Tip: If you are new to Liquibase, follow Get Started with Liquibase.

Ensure you use Liquibase 4.5.0 or later.

Step 1: Running checks against your changelog with the default configuration

The quality checks capability supports all changelog formats. This instruction includes a formatted SQL changelog. If you use your own changelog, example output might differ.

  1. Create a new changelog.sql file and add the following:
  2. --liquibase formatted sql
    				
    --changeset your.name:1 labels:v0 context:all
    create table person (
    	id int primary key,
    	name varchar(50) not null,
    	address1 varchar(50),
    	address2 varchar(50),
    	city varchar(30)
    )
    				
    --changeset your.name:2 labels:v0 context:all
    create table company (
    	id int primary key,
    	name varchar(50) not null,
    	address1 varchar(50),
    	address2 varchar(50),
    	city varchar(30)
    )
    				
    --changeset other.dev:3 labels:v0 context:all
    alter table person add column country varchar(2)
    
    --changeset other.dev:4 labels:v0 context:all
    drop table person;
  3. Open your CLI and navigate to the directory with the changelog file that you intend to use. Start by executing checks against a sample changelog file using the default configuration provided by Liquibase:
  4. liquibase checks run

    Note: If a configuration file does not exist, Liquibase prompts you to create a new liquibase.checks-settings.conf file for checks.

If you use a sample changelog provided in the instruction, the last changeset in changelog.sql contains a drop table statement that is flagged by one of the checks in the default configuration.

Executing Quality Checks against changelog.sql
Checks completed validation of the changelog and found the following issues:
Changeset ID:       4
Changeset Filepath: changelog.sql
Check Name:         Warn when 'DROP TABLE' detected (ChangeDropTableWarn)
Message:            Liquibase recommends that changesets which result in tables
					being dropped are reviewed carefully to prevent the
					unintentional loss of data.  Review this changeset to
					confirm that the referenced table is being dropped safely.

Changesets Validated:
  ID: 1; Author: your.name; File path: changelog.sql
  ID: 2; Author: your.name; File path: changelog.sql
  ID: 3; Author: other.dev; File path: changelog.sql
  ID: 4; Author: other.dev; File path: changelog.sql

run against each changeset:
  Warn on Detection of 'GRANT' Statements
  Warn on Detection of 'REVOKE' Statements
  Warn when 'DROP TABLE' detected
  Warn when 'DROP COLUMN' detected
  Warn when 'MODIFY <column>' detected

Liquibase command 'run' was executed successfully.

Step 2: Disabling checks in the default configuration

You can disable any checks by using the disable command.

To disable a check, you need to know the short name, which you can get by running the show command. The show subcommand lists information and customization settings for available checks including the check’s full name, short name, description, enabled status, and customization options. The short name for the DROP TABLE check is ChangeDropTableWarn.

Then, run the name in the --check-name parameter with the disable subcommand:

liquibase checks disable --check-name=ChangeDropTableWarn

Note: Liquibase saves the configuration change to your checks configuration file, the default of which is liquibase.checks-settings.conf, and print the configuration again to show the change.

If you execute the run command one more time, no issues will be detected.

Executing Quality Checks against changelog.sql

Changesets Validated:
  ID: 1; Author: your.name; File path: changelog.sql
  ID: 2; Author: your.name; File path: changelog.sql
  ID: 3; Author: other.dev; File path: changelog.sql
  ID: 4; Author: other.dev; File path: changelog.sql

run against each changeset:
  Warn on Detection of 'GRANT' Statements
  Warn on Detection of 'REVOKE' Statements
  Warn when 'DROP COLUMN' detected
  Warn when 'MODIFY <column>' detected
  Check Table Column Count
				
Liquibase command 'run' was executed successfully.

Note: To enable a check, use the enable command.

Step 3: Creating and using a custom check

Some checks have customization options that allow you to adjust the parameters used to determine whether a changeset meets the criteria of the check. You can adjust these parameters using the copy or customize command.

  1. Create a check that finds table truncation statements in a sample changelog by generating a copy of the SqlUserDefinedPatternCheck check:
  2. liquibase checks copy --check-name=SqlUserDefinedPatternCheck
  1. Provide a short name, search string, and message for your check:
  2. Give your check a short name for easier identification (alpha-numeric characters only) (default "SqlUserDefinedPatternCheck1"):
    SqlTruncateCheck
    New check 'SqlTruncateCheck' created from 'SqlUserDefinedPatternCheck'
    
    Set 'SEARCH_STRING' (current: 'null'):
    (?i)^(\s*)(TRUNCATE)
    Set 'MESSAGE' (current: 'A match for regular expression <SEARCH_STRING> was detected in Changeset <CHANGESET>.') (default "A match for regular expression <SEARCH_STRING> was detected in Changeset <CHANGESET>."):
    Table truncation detected.  Review this changeset to ensure data is not unintentionally deleted.
    Customization complete. Review the table below to confirm your changes.

    After you complete the form, the checks configuration is printed for your review.

  1. Add the following to the changelog.sql file and save your changes:
  2. --changeset other.dev:4 labels:v0 context:all
    truncate person;
  1. Execute the run subcommand again:
  2. liquibase checks run

    The new check indicates that there is an issue with your new changeset:

    Checks completed validation of the changelog and found the following issues:
    Changeset ID:       5
    Changeset Filepath: changelog.sql
    Check Name:         Check for specific patterns in sql (SqlUserDefinedPatternCheck)
    Message:            Table truncation detected.  Review this changeset to ensure
                        data is not unintentionally deleted.

The following pages will help you proceed with all quality checks capabilities: