DEV Community

Cover image for ⚙ī¸đŸ”Ĩ Automating Database Schema Change workflow Using GitHub Actions 🐙🚀
Adela for Bytebase

Posted on â€ĸ Edited on â€ĸ Originally published at bytebase.com

10 2 2 2 3

⚙ī¸đŸ”Ĩ 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.

workflow

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 ci@service.bytebase.com with the Workspace DBA role, which will be used to authenticate the API calls.

bb-workspace-members-ci

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.

bb-project-database

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.

gh-workflows

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.

bb-environment-sql-review

bb-sql-review-policy-not-null

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: "ci@service.bytebase.com"
         DATABASE: "instances/prod-instance/databases/example"
   ...
Enter fullscreen mode Exit fullscreen mode

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 }}
      ...
Enter fullscreen mode Exit fullscreen mode

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.

gh-pr1-blocked

Click on the Details for SQL Review.

gh-sql-review-warning

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

gh-sql-review-warning-file-annotation

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.

gh-waiting-for-approval

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

gh-add-reviewers

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.

gh-waiting-for-check

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: "ci@service.bytebase.com"
      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:
    ...
Enter fullscreen mode Exit fullscreen mode

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.

bb-issue-user-post

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.

bb-custom-approval

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.

bb-issue-done

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

gh-pr2-blocked

gh-re-run

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.

gh-re-run-pass

gh-all-pass-ready-merge

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.

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

Top comments (1)

Collapse
 
kehoecj profile image
Clayton Kehoe â€ĸ

Nice guide - thanks for putting this together

Billboard image

Create up to 10 Postgres Databases on Neon's free plan.

If you're starting a new project, Neon has got your databases covered. No credit cards. No trials. No getting in your way.

Try Neon for Free →

👋 Kindness is contagious

Engage with a sea of insights in this enlightening article, highly esteemed within the encouraging DEV Community. Programmers of every skill level are invited to participate and enrich our shared knowledge.

A simple "thank you" can uplift someone's spirits. Express your appreciation in the comments section!

On DEV, sharing knowledge smooths our journey and strengthens our community bonds. Found this useful? A brief thank you to the author can mean a lot.

Okay