Database GitOps with GitHub Actions

This tutorial shows you how to build an database GitOps workflow using GitHub Actions and Bytebase API. You'll learn to:

1. Create a streamlined database release workflow where you can:

   • Submit SQL migrations through GitHub
   • Automatically run SQL reviews on pull requests
   • Auto-create and deploy Bytebase releases when merging to main

2. Manually control rollouts by stage

While we use GitHub Actions in this guide, you can apply these concepts to other CI platforms like GitLab CI, Bitbucket Pipelines, or Azure DevOps using the Bytebase API.

This tutorial code repository is at https://github.com/bytebase/example-gitops-github-flow

Prerequisites#

• Docker installed
• An ngrok account

Automatic Rollout across environments#

Step 1 - Start Bytebase with ngrok#

Step 2 - Create Service Account#

If you have Enterprise Plan, you can create a Custom Role for the service account which require fewer permissions, and assign this role instead of DBA:

• plans.create
• plans.get
• plans.preview
• releases.check
• releases.create
• releases.get
• rollouts.create
• rollouts.get
• rollouts.list
• sheets.create
• sheets.get
• taskRuns.create
• planCheckRuns.list
• planCheckRuns.run

Step 3 - Configure SQL Review in Bytebase#

Since you will need to run SQL review on your PRs, you need to configure the SQL review in Bytebase.

1. Go to CI/CD > SQL Review, click Create SQL Review.

2. Select the Sample Template and click Next.

3. Select Prod environment as the attached resources and click Confirm. Now the SQL review is enabled for the Prod environment.

Step 4 - Fork the Example Repository and Configure Variables#

1. Fork https://github.com/bytebase/example-gitops-github-flow. There are two workflows in this repository:

   • .github/workflows/sql-review.yml: Lint the SQL migration files after the PR is created.
   • .github/workflows/release.yml: Create a release in Bytebase after the PR is merged to the main branch.

2. Go into .github/workflows/release.yml and .github/workflows/sql-review.yml. In the env section, replace the variable values with your own and commit the changes.

   • BYTEBASE_URL: your ngrok url
   • BYTEBASE_SERVICE_ACCOUNT: api-example@service.bytebase.com (the service account you created in the previous step)
   • BYTEBASE_PROJECT: projects/project-sample (the sample project in the Bytebase)
   • BYTEBASE_TARGETS: instances/test-sample-instance/databases/hr_test,instances/prod-sample-instance/databases/hr_prod (the two default databases in the sample project)
   • FILE_PATTERN: migrations/*.sql (the pattern of the migration files)

3. You may paste the password of the service account you created in the previous step directly after service-secret or keep the service-secret value as ${{secrets.BYTEBASE_SERVICE_ACCOUNT_SECRET}}. Go to Settings > Secrets and Variables > Actions, click New repository secret, and add BYTEBASE_SERVICE_ACCOUNT_SECRET.

4. Go to Actions tab, enable actions workflow run.

Step 5 - Create the migration files#

To create migration files to trigger release creation, the files have to match the following pattern:

• A migration file should start with digits, which is also its version. e.g. 202503131500_create_table_t1_ddl.sql.
• A migration file may end with 'ddl' or 'dml' to indicate its change type. If it doesn't end with any of the two, its change type is DDL by default.

1. Within your forked repository, create the following migration files under migrations directory:

   • 202503131500_create_table_t1_ddl.sql

   CREATE TABLE t1 (
    id SERIAL PRIMARY KEY,
    name TEXT
   );

2. Commit to a new branch and create a pull request, the sql-review workflow will be triggered. There will be a warning in the SQL review result. Go into the File changes tab, you can see the warning.

   

   

3. According to the SQL review result, you can do some changes to the SQL files and push to the branch. Then you should see the SQL review has passed. There are no warnings in the SQL review result.

   CREATE TABLE t1 (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
   );

   

4. When the SQL review is passed, you can merge the pull request. The release workflow will be triggered to create a release in Bytebase and then roll out automatically. Go to Actions tab, you can see the workflow run and pass.

   

5. Click into the workflow run, you can see the release is created in Bytebase and the rollout is applied to the databases automatically.

   

6. If you click the test stage and expand the different sections, you can follow the links to Bytebase.

   

   

Breakdown of the GitHub Actions Workflow#

1. Check out your repo and log in to Bytebase to gain the access token.

   - name: Checkout
     uses: actions/checkout@v4
   - name: Login to Bytebase
     id: login
     uses: bytebase/login-action@v1
     with:
       bytebase-url: ${{ env.BYTEBASE_URL }}
       service-key: ${{ env.BYTEBASE_SERVICE_ACCOUNT }}
       service-secret: ${{ secrets.BYTEBASE_SERVICE_ACCOUNT_SECRET} }}

2. The create-release step scans the files matching the pattern and collects them into a bundle. Note that these files should also obey the naming scheme mentioned above.

   The bundle is first sent for check. Because we set FAIL_ON_ERROR, the release will be created in Bytebase only when the check passes.

   - name: Create release
     id: create-release
     uses: bytebase/create-release-action@v1
     with:
       bytebase-url: ${{ env.BYTEBASE_URL }}
       service-key: ${{ env.BYTEBASE_SERVICE_ACCOUNT }}
       service-secret: ${{ secrets.BYTEBASE_SERVICE_ACCOUNT_SECRET }}
       file-pattern: ${{ env.FILE_PATTERN }}
       check-release: 'FAIL_ON_ERROR'
       project: ${{ env.BYTEBASE_PROJECT }}
       targets: ${{ env.BYTEBASE_TARGETS }}
       validate-only: 'false'

3. Create a rollout and wait for completion.

   - name: Create plan
        id: create-plan
        uses: bytebase/create-plan-from-release-action@v1
        with:
          url: ${{ env.BYTEBASE_URL }}
          token: ${{ steps.login.outputs.token }}
          project: ${{ env.BYTEBASE_PROJECT }}
          release: ${{ steps.create-release.outputs.release }}
          targets: ${{ env.BYTEBASE_TARGETS }}
          check-plan: "SKIP"
   
    - name: Rollout
        id: rollout
        uses: bytebase/rollout-action@v2
        if: ${{ steps.create-plan.outputs.deployment-required == 'true' }}
        with:
          url: ${{ env.BYTEBASE_URL }}
          token: ${{ steps.login.outputs.token }}
          plan: ${{ steps.create-plan.outputs.plan }}
          target-stage: environments/test # the stage environment.
   
   - name: Deploy app
        run: |
          echo "Deploying app to test environment..."
          echo "Deploy app to test environment done!"

   These are the steps:

   • Create the plan from the release
   • Create the rollout
   • Wait for the rollout to complete

Manual Rollout by Environment#

In the previous section, once the PR is merged, we create a release and roll out it to both test and prod environments automatically. You can also manually control the rollout by stage.

1. In the repo, click Settings > Environments, choose Prod. Here you can add required reviewers for the stage and also set wait timer.

2. Create a new branch with this file, and create a pull request. Merge it to the main branch.

   • 202503131530_create_table_t2_ddl.sql

   CREATE TABLE t2 (
         id SERIAL PRIMARY KEY,
         name TEXT NOT NULL
      );

3. Go to the Actions tab, you can see the workflow run and stop at the Prod stage.

   

4. Wait for 5 minutes, the workflow will wait for the required reviewers to approve.

   

5. Click Approve and deploy button, the workflow will continue to rollout to the Prod stage.

   

   

   

Summary#

Now you have learned how to database GitOps with GitHub Action. If you want to trigger a release creation with other git providers (e.g. GitLab, Bitbucket, Azure DevOps), you may customize the workflow file.