⚙️🔥 Automating Database Schema Change workflow Using GitHub Actions 🐙🚀

Tutorial repository https://github.com/bytebase/ci-example

Developers like to keep their schema migration scripts in Git along with the application code. Thus the migration scripts will be reviewed and version-controlled in the same way as the application code.
However, developers still have to manually paste the migration script into their SQL client or ask the DBAs to run it against the target database. This is inefficient and error-prone:

• What if you paste the wrong script / miss the script?
• What if you run the script against the wrong database?

This tutorial teaches how to get rid of this manual process and fully automate database schema change
using GitHub Actions and Bytebase API.

Above shows a typical workflow:

1. Developer creates a PR containing the migration script. After PR is created, it triggers a GitHub
   Action to lint the SQL by calling Bytebase SQL Review API.

2. TL approves the PR.

3. Upon PR approval, it triggers a GitHub Action to create a Bytebase rollout issue.
   The issue contains the migration script changes.

4. Depending on the configured approval and rollout policy, it may require manual approval and rollout from DBA. Another GitHub Action is configured to block the PR merge until Bytebase rolls out the schema migration. Sometimes, a PR contains both code and schema changes. This setup guarantees the schema migration is applied before the code deployment.

5. Bytebase deploys the schema change and marks the issue as Done.

6. PR re-runs the migration status check and now it turns green.

7. Now PR can be merged.

Prepare Bytebase

Suppose Bytebase is running at https://bytebase.example.com/. To begin, we'll first set up the necessary data to support our API interactions.

1. Service account: As an admin, we add a service account [e-mail] with the Workspace DBA role, which will be used to authenticate the API calls.

To limit the service account permissions, you can choose to grant Workspace Member instead of Workspace DBA.
And then in the particular project, grant the account the permission to create an issue.

1. A database in a project: We have a project called Example, and a database: example.

Prepare GitHubs Actions

Go and check the example on GitHub https://github.com/bytebase/ci-example.

The repository contains several GitHub Action workflows, you may go to .github/workflows to view.

We will use the following workflows:

• bytebase-sql-review.yml: Triggered on PR change. Thus any SQL review violation will block the PR.
• bytebase-upsert-migration.yml Triggered on PR approval. Creates the Bytebase migration issue after approval. And whenever the migration scripts change afterwards, the migration issue will also be updated accordingly.
• bytebase-check-migration-status.yml: Triggered on PR change. Thus PR will be blocked until migration completes.

Sample Workflow, Four Phases

To illustrate the workflow, we have divided it into four phases to showcase the database schema change process.

Phase 1: Not passing SQL review on GitHub

Before we delve into the workflow, let's set up the SQL Review policy in Bytebase. The example database is on the Prod environment, where we will configure SQL review policy. Here we have a policy that checks for NOT NULL constraints, which we will violate in the PR.

Returning to GitHub Actions and digging into the code, the bytebase-sql-review.yml workflow is triggered on PR change. It scans the SQL files named following the pattern **.up.sql in the PR and reports any SQL review policy violations.

Configure the environment.

   bytebase-sql-review:
      runs-on: ubuntu-latest
      env:
         BYTEBASE_URL: "https://bytebase-ci.zeabur.app"
         BYTEBASE_SERVICE_ACCOUNT: "[e-mail]"
         DATABASE: "instances/prod-instance/databases/example"
   ...

After authentication, we call the Bytebase API /sql/check to lint the migration files. We parse the response
and emit GitHub inline annotations for each advice and mark the check as failed if any ERROR or WARNING is found.

     name: SQL Review
    steps:
      - name: Checkout
        uses: actions/checkout@v4
      - name: Login to Bytebase
        ...
      - name: Review
        id: review
        uses: ./.github/actions/sql-review
        with:
          github-token: ${{ secrets.GITHUB_TOKEN }}
          pattern: "**/*.up.sql"
          url: ${{ env.BYTEBASE_URL }}
          token: ${{ steps.login.outputs.token }}
          headers: '{"Accept-Encoding": "deflate, gzip"}'
          database: ${{ env.DATABASE }}
      ...

We create a PR with several SQL files, and it triggers both bytebase-sql-review.yml and bytebase-check-migration-status.yml. After these checks are completed, the PR is blocked due to failures.

Click on the Details for SQL Review.

You may also go to Files changed to view the annotations.

Phase 2: Passing SQL review and waiting for TL's approval on GitHub

We then fix the SQL files and pushes. After completing these checks, the PR is still blocked due to failures, but this time SQL review has passed.

In real-life scenarios, the PR also encompasses application code. Because the SQL migration has passed the basic SQL review checks, it is now time for a tech leader to approve this PR.

The developer who creates the PR assigns the tech leader to review on GitHub.

Phase 3: TL approves on GitHub and migration issue is created in Bytebase

The assigned tech leader approves the PR, and another workflow bytebase-upsert-migration.yml is triggered.

It checks the SQL files named like **.up.sql within the pull request and creates a rollout issue in Bytebase.

   bytebase-upsert-migration:
    runs-on: ubuntu-latest
    # Runs only if PR is approved and target branch is main
    if: github.event.review.state == 'approved' && github.event.pull_request.base.ref == 'main'
    env:
      BYTEBASE_URL: "https://bytebase-ci.zeabur.app"
      BYTEBASE_SERVICE_ACCOUNT: "[e-mail]"
      PROJECT: "example"
      DATABASE: "instances/prod-instance/databases/example"
      ISSUE_TITLE: "[${{ github.repository }}#${{ github.event.pull_request.number }}] ${{ github.event.pull_request.title }}"
      DESCRIPTION: "Triggered by ${{ github.event.repository.html_url }}/pull/${{ github.event.pull_request.number }} ${{ github.event.pull_request.title }}"
    name: Upsert Migration
    steps:
    ...

Go to Bytebase and view the created issue, which consists of two tasks corresponding to the presence of two **.up.sql files in the PR.

You may notice there is an approval flow attached to the created issue, that's because we set up a default custom approval flow for DDL.

Phase 4: Migration completed and PR is mergable on GitHub

After the DBA approves and rolls out the migrations, the issue status will become Done.

Go back to GitHub, click Details for the failed bytebase-check-migration-status.yml workflow, and then click Re-run all jobs.

It checks the migration status in Bytebase and return pass if it's Done, indicating the database migration has been completed. The PR is now ready to be merged, which means the application code is ready to be deployed.

Summary

Keep in mind that workflows can be tuned according to your organization's needs:

1. You can attach the workflow to different branches depending on your branching strategy (e.g. trunk-based or not).

2. You can use different migration file formats and different migration file structures.

3. You can determine when to create the migration issue, upon PR approval or creation.

Whatever workflow you choose, with the help of GitHub Actions and Bytebase API, you can now keep your migration scripts in the repository, let the migration scripts go through the same code review process and automate the schema migration deployment.

Comments

[Clayton Kehoe]

Nice guide - thanks for putting this together