White Paper: Just-in-Time Database Access Best Practices

Database GitOps with Azure DevOps Pipeline

Estimated: 40 mins

This is part of our database GitOps series with Bytebase:

This tutorial shows you how to build a database GitOps workflow using Azure DevOps Pipeline and Bytebase API. You'll learn to create a streamlined database release workflow where you can:

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

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

This tutorial code repository is at https://dev.azure.com/bytebase-hq/_git/bytebase-example

Prerequisites

Automatic Rollout across environments

Step 1 - Start Bytebase with ngrok

ngrok is a reverse proxy tunnel, and in our case, we need it for a public network address to allow VCS to call the Bytebase API. ngrok we used here is for demonstration purposes.

ngrok-reverse-proxy

  1. Run Bytebase in Docker with the following command:

    docker run --rm --init \
  --name bytebase \
  --publish 8080:8080 --pull always \
  --volume ~/.bytebase/data:/var/opt/bytebase \
  bytebase/bytebase:3.6.0

  2. Bytebase is running successfully in Docker, and you can visit it via localhost:8080. Register an admin account and it will be granted the workspace admin role automatically.

  3. Login to ngrok Dashboard and complete the Getting Started steps to install and configure. If you want to use the same domain each time you launch ngrok, go to Cloud Edge > Domains, where you'll find the domain <<YOURS>>.ngrok-free.app linked to your account.

  4. Run the ngrok command ngrok http --domain=<<YOURS>>.ngrok-free.app 8080 to start ngrok with your specific domain, and you will see the output displayed below:

    terminal-ngrok

  5. Log in Bytebase and click the gear icon (Settings) on the top right. Click General under Workspace. Paste <<YOURS>>.ngrok-free.app as External URL under Network section and click Update.

    external-url

  6. Now you can access Bytebase via <<YOURS>>.ngrok-free.app.

Step 2 - Create Service Account

  1. Log in as Workspace Admin, and go to IAM & Admin > Users & Groups. Click + Add User, fill in with api-sample, choose the Workspace DBA role sufficient for this tutorial and click Confirm. service-account-create

  2. Find the newly created service account and Copy Service Key. We will use this token to authenticate the API calls. service-account-key

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. bb-sql-review-sample

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

Step 4 - Copy from the Example Repository and Configure Variables

  1. Create a new project. Copy pipelines folder from https://dev.azure.com/bytebase-hq/_git/bytebase-example. There are two workflows in this repository:

    • pipelines/check-release.yml: Lint the SQL migration files after the PR is created.
    • pipelines/rollout-release.yml: Create a release in Bytebase after the PR is merged to the main branch.

  2. Go into pipelines/check-release.yml and pipelines/rollout-release.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. Go to branch policy for main branch, add check-release as a required check. You don't need to add rollout-release as a required check because it will be triggered automatically when the PR is merged.

    ad-branch-policy

    ad-policy-build

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 check-release workflow will be triggered. There will be a warning in the SQL review result.

    ad-check-pass

    ad-check-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
);

    ad-check-no-warning

  4. When the SQL review is passed, you can merge the pull request. The rollout-release workflow will be triggered to create a release in Bytebase and then roll out automatically.

    ad-rollout

  5. You need to permit the release to be deployed to the production environment the first time.

    ad-rollout-permit ad-rollout-prod

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

    bb-rollout-preview

  7. You may customize the workflow file to trigger deployment manually according to your needs.

Summary

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

Edit this page on GitHub