Enforcing Database Quality in GitHub Actions

As of September 9, 2024, Quality Checks have been renamed to Policy Checks. Check out this blog to learn more.

‍

The quality checks feature within Liquibase allows developers to release faster while ensuring compliance by reducing security risks and costly manual errors. Integrating quality checks into your GitHub Actions pipeline moves that compliance check further left, providing faster feedback for individual contributors!

‍

Set up quality checks with GitHub Actions

Quality checks can be integrated with any existing repository that uses GitHub Actions and Liquibase.

‍

Starting with a sample GitHub repository

For this example, I’ve created a simple repository that contains a single changelog called “example-changelog.sql” in my “changelogs” directory and a GitHub Actions pipeline that only checks out the code.

Simple GitHub repository:

build.yml contents:

  name: Build and Test
  
  on: [push, pull_request]
  
  jobs:
  	runchecks:
    	name: "Checkout code"
      runs-on: ubuntu-latest
      steps:
      	- uses: actions/checkout@v2

Currently, my pipeline is succeeding. This is not surprising because the pipeline only checks out the repository.

Add quality checks to the GitHub repository

Adding quality checks to my repository is a two step process. First, I configure the quality checks that I want to run on my changelogs. Second, I will add the Liquibase GitHub Action to my build pipeline.

Initial configuration of quality checks

To configure the quality checks that I want to run, I open up a terminal in my repository, and run `liquibase checks show`. Press “y” when prompted to choose whether to create the liquibase.checks-settings.conf file. This file stores information about the quality checks that we will customize shortly.

‍

$ liquibase checks show
Starting Liquibase at 12:02:54 (version 4.10.0 #2501 built at 2022-05-04 14:27+0000)
Liquibase Version: 4.10.0
Liquibase Community 4.10.0 by Liquibase

Generating report on current configuration of checks using settings in: liquibase.checks-settings.conf

liquibase.checks-settings.conf does not exist.
Would you like to create it? (Y/n) [Y]:
y
liquibase.checks-settings.conf successfully created!

‍

Next, I commit my newly created liquibase.checks-settings.conf file to my repository so that I can keep track of what changes I’ve made to my quality checks configuration.

$ git status
On branch main
Your branch is up to date with 'origin/main'.

Untracked files:
  (use "git add <file>..." to include in what will be committed)
        liquibase.checks-settings.conf

nothing added to commit but untracked files present (use "git add" to track)

$ git add liquibase.checks-settings.conf

$ git commit -m "add Quality Checks configuration file"
[main 35c7c24] add Quality Checks configuration file
 1 file changed, 95 insertions(+)
 create mode 100644 liquibase.checks-settings.conf

If I execute quality checks now, I expect that it will succeed. Note that I must specify the location of my changelog when I execute quality checks.

$ liquibase checks run --changelog-file changelogs/example-changelog.sql
Starting Liquibase at 12:10:34 (version [local build] #0 built at 2022-05-04 17:00+0000)
Liquibase Version: [local build]
Liquibase Community [local build] by Liquibase

Executing Quality Checks against changelogs/example-changelog.sql

No valid Liquibase Pro license key detected!
Unlock unlimited checks with a free Pro license Key from https://liquibase.com/protrial
Execution limited to 5 checks.


Changesets Validated:
  ID: 1; Author: your.name; File path: changelogs/example-changelog.sql
  ID: 2; Author: your.name; File path: changelogs/example-changelog.sql
  ID: 3; Author: other.dev; File path: changelogs/example-changelog.sql

Checks 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

Checks Skipped Due to Community Limit of 5 Checks:
  Check Table Column Count
  Warn when 'TRUNCATE TABLE' detected
  Warn on Detection of grant that contains 'WITH GRANT OPTION'
  Warn on Detection of grant that contains 'WITH ADMIN OPTION'
  Rollback Required for Changeset
  Changesets Must Have a Label Assigned
  Changesets Must Have a Context Assigned
  Changesets Must Have a Comment Assigned

Liquibase command 'checks run' was executed successfully.

‍

All three of my changesets are validated by the five quality checks that I can run as a community user of Liquibase, and I can see that quality checks has executed successfully.

I’ll push my changes to my repository and then we’ll go through step two, adding the GitHub Action to my existing build pipeline.

Add quality checks to my build pipeline

Recall that I have a very simple build.yml in my repository, which currently only checks out the repository.

We’re going to use the liquibase-github-action GitHub Action to run quality checks in our GitHub Actions pipeline.

Let’s add that to our build.yml.

Original build.yml contents

name: Build and Test
on: [push, pull_request]

jobs:
  runchecks:
    name: "Run quality checks"
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v2
      - uses: liquibase/liquibase-github-action@v5
        with:
          operation: 'checks run'
          changeLogFile: 'changelogs/example-changelog.sql'
          checksSettingsFile: 'liquibase.checks-settings.conf'

That’s a bit different than what we had before, so let’s review the changes one-by-one.

• I’ve added the liquibase/liquibase-github-action@v5 GitHub Action.
• With the “operation” parameter, I am indicating that the Liquibase operation I wish to execute is “checks run”. We executed this command earlier when we were configuring quality checks for the first time.
• Below that, I’ve specified the changeLogFile location.
• Finally, I’ve specified the name of my checksSettingsFile.

This looks good to me, so I’ll commit and push my changes to my build.yml file and review the changes in GitHub.

The pipeline has run successfully, and if I open up the logs on GitHub, I can see the output from executing “liquibase checks run”:

Configure failure exit codes in quality checks

When executing quality checks in Liquibase, by default, the exit code is 0 for any failing checks. This, of course, means that if I do violate one of the rules specified in my quality checks, the GitHub Action pipeline will still run successfully because the liquibase executable will exit with an exit code of 0.

This isn’t useful for our use case, so let’s configure one of the checks to return a non-zero exit code.

We’ll modify the ChangeDropTableWarn rule to exit with a non-zero exit code if it detects a problem in my changelog.

$ liquibase checks customize --check-name ChangeDropTableWarn

Starting Liquibase at 14:19:26 (version 4.10.0 #2501 built at 2022-05-04 14:27+0000)

Liquibase Version: 4.10.0

Liquibase Community 4.10.0 by Liquibase

Set the Severity to return a code of 0-4 when triggered. (options: 'INFO'|0, 'MINOR'|1, 'MAJOR'|2, 'CRITICAL'|3, 'BLOCKER'|4)? [INFO]:

CRITICAL

Customization complete. Review the table below to confirm your changes.

...

I can review my changes in the table that was printed to my console, and confirm that my ChangeDropTableWarn rule now has a severity of 3.

When we commit and push our changes to the repository, the build will still succeed because none of the changesets violate that quality check.

‍

A real world failure use case

Consider the use case where a developer makes a new branch in the repository and modifies the changelog and erroneously adds a DROP TABLE statement. Above, we configured the ChangeDropTableWarn rule to exit with an exit code of 3 if it detects a DROP TABLE statement.

Let’s test this scenario out.

First, I’ll need to add a Branch Protection Rule to my repository in GitHub. I’ll add a rule with the following criteria:

• Branch name pattern: *
• Require status checks to pass before merging: checked
• “Run quality checks” added to the list of “Status checks that are required.”

Now, I’ll create a branch in my repository called JIRA-1, and I’ll add a DROP TABLE changeset to my changelog in that branch:

--changeset bad.dev:4 labels:example-label context:example-context
drop table person;

I’ll commit and push this change to my repository, and open up a pull request for this branch. In the pull request overview, I can see that the “Run quality checks” status check has failed and prevents me from merging my pull request:

I can also view the log output for that status check and observe that my ChangeDropTableWarn check is failing:

As a developer, I’m now prevented from merging my branch which contains changes that are not permitted.

‍

Summary

We started from a basic repository containing a simple changelog and effectively empty GitHub Actions pipeline, added our customized quality checks configuration for Liquibase, and added the liquibase-github-action to our build pipeline so that quality checks were executed on every push and pull request.

Shifting this validation of changesets to the pull request status checks enables developers to review their own changes automatically, before merging their own code. This improves the stability of the organization’s CI/CD pipeline, by ensuring that only safe changes are merged.

‍

Try using quality checks with Liquibase

Give quality checks with Liquibase a try! It’s available with Liquibase Pro and free for you to try during a trial of Liquibase Pro. You’ll get a trial key to unlock this advanced feature and many others to help your team easily manage the quality of your database deployments.