Database CI/CD Best Practice with Azure DevOps

Hi and welcome to Database DevOps Academy #18!

Each week, we share Database DevOps insights and best practices for modern engineering organizations. If you enjoy this newsletter, we ask you to subscribe and share. ??

In this Issue, we explore Database CI/CD Best Practice for Azure. Enjoy!

Wanna try other VCS providers instead? ??

• The Database CI/CD Best Practice with GitLab
• The Database CI/CD Best Practice with GitHub

Database change is a tricky part of the application development process: it usually involves multiple databases from different environments and cross-team collaboration, to add on top of it, databases are touch and go. It got us thinking: can we treat database the same way we treat application code?

DORA (DevOps Research & Assessment) pointed out that integrating database work into the software delivery process positively contributes to continuous delivery. It’s about time to make databases a part of the CI/CD cycle.

But how does it work, really?

A Complete Database CI/CD Workflow

Here, we present a complete Database CI/CD workflow with Azure DevOps. It's similar with GitHub, Bitbucket or Azure DevOps.

1. The developer creates a Pull Request containing the SQL migration script;
2. SQL Review CI is automatically triggered to review SQL and offers suggestions to assist the code review;
3. After several possible iterations, the team leader or another peer on the dev teams approves the change and merges the SQL script into a branch;
4. The merge event automatically triggers the release pipeline in Bytebase and creates a release ticket capturing the intended change;
5. (Optional) an approval flow will be auto matched based on the change risk and be followed via Bytebase’s built-in UI;
6. Approved scripts are executed gradually according to the configured rollout stages;
7. The latest database schema is automatically written back to the code repository after applying changes. With this, the Dev team always has a copy of the latest schema. Furthermore, they can configure downstream pipelines based on the change of that latest schema;
8. Confirm the migration and proceed to the corresponding application rollout.

Set Up Database CI/CD with Azure DevOps in Bytebase (Free Plan)

Here's a step-by-step tutorial on how to set up this Database CI/CD with Azure DevOps in Bytebase.

Step 1 - Run Bytebase in Docker and set the External URL generated by ngrok

ngrok is a reverse proxy tunnel, and in our case, we need it for a public network address in order to receive webhooks from VCS. ngrok we used here is for demonstration purposes. For production use, we recommend using Caddy .

• Login to ngrok Dashboard and follow its Getting Started steps to install and configure.
• Run ngrok and obtain the public URL

ngrok http 5678        

• Run Bytebase in Docker

docker run --init \
  --name bytebase \
  --restart always \
  --publish 5678:8080 \
  --health-cmd "curl --fail https://localhost:5678/healthz || exit 1" \
  --health-interval 5m \
  --health-timeout 60s \
  --volume ~/.bytebase/data:/var/opt/bytebase \
  bytebase/bytebase:2.9.1 \
  --data /var/opt/bytebase \
  --port 8080        

• Bytebase is running successfully in Docker, and you can visit it via localhost:5678. Register an admin account and it will be granted the workspace owner role automatically.
• Click the gear icon (Settings) on the top right. Click General under Workspace. Paste the public URL from ngrok as External URL under Network section and click Update.
• Bytebase is running successfully in Docker, and you can visit it via the ngrok URL.

Step 2 - Add Azure DevOps as a Git provider in Bytebase

• Visit Bytebase via your ngrok URL. Click gear icon (Settings) > Integration > GitOps, choose Azure DevOps Service, and click Next.

• You will see STEP 2. Copy the Redirect URI. Click Direct Link to your Azure DevOps account.

On the Azure DevOps application registration page, fill the form as follows:

• Company name: can be other names than bb, as long as the organization admin can identify this application is for Bytebase later
• Homepage URL: can be other URLs than https://bytebase.com
• Authorization callback URL: Redirect URI copied from Bytebase STEP 2, begins with the host:port where the Bytebase console is running, and followed by /oauth/callback. This is the URI Azure DevOps uses to callback Bytebase during the OAuth flow
• Authorizied scopes: Find the checkboxes for Code (full), Identity (read), Project and team (read), Build (read and execute)

Click Register application.

Click show. Copy the App ID and Client Secret and paste them into the Bytebase GitOps config page. Click Next. Click Authorize on popup. You will be redirected to the confirmation page. Click Confirm and add, and the Git provider is successfully added.

Step 3 - Configure a GitOps Workflow in Bytebase

• Go to Azure DevOps and create a new project bytebase-gitops. Click Create project.
• Go to Bytebase, go to the Sample Project. Click GitOps tab and choose GitOps workflow. Click Configure GitOps.
• Choose Azure DevOps (the git provider you just configured) and the repository you just created. You'll be redirected to STEP 3. Keep everything as default, scroll down to the bottom and check Enable SQL Review CI via Azure DevOps Pipeline. Click Finish.

• After SQL Review CI is automatically setup, click Review the pull request. You'll be redirected to Azure DevOps. Click Complete and you'll see the CI is automatically configured. It will be triggered later once a new pull request is created.

• Go back to Bytebase, you'll see the GitOps workflow is configured successfully.

Step 4 - Create a Pull Request and Trigger SQL Review CI

• Go to Environments, you'll see there's a SQL Review policy attached with Prod. Click Edit, you'll see three activated SQL Review rules which will be applied via CI. Let's adjust this rule to Error and try to break.

• To test SQL Review CI, we'll create a pull request to change the Prod database schema. However, it will voliate the SQL Review policy first. Go to bytebase-gitlabcom-demo on Azure DevOps. Click New branch, name it add-nickname-table-employee. Click Create branch.
• On the new branch, create a subdirectory bytebase, and create a sub-subdirectory prod. Within the prod directory, create a file employee##202310201700##ddl##add_nickname_table_employee.sql. Copy the following SQL script into the file and commit the change.

ALTER TABLE "public"."employee"
ADD COLUMN "nick_name" text;        

• Create a pull request including the above commits. The SQL Review CI will run automatically and show the fail message. Click Tests to dig deeper.

• Update the SQL script and commit in the current branch. The SQL Review CI will run again and show the pass message. Click Complete and Complete Merge.

ALTER TABLE "public"."employee"
ADD COLUMN "nick_name" text NOT NULL DEFAULT '';        

• Go back to project Sample Project in Bytebase, you'll see the there's an issue created by push event.

• Click to go to the issue. Because there is no approval flow or manual rollout configured. The issue rollouts automatically. You may click View change to see the diff.

Advanced Features (Enterprise Plan)

You may upgrade to Enterprise plan to explore more features.

Click Start free trial on the left bottom and upgrade to Enterprise plan, Go to Instances to Assign License for the existing two instances.

Manual Rollout

Go to Environments > 2.Prod, Find Rollout policy section, and choose Manual rollout by fixed roles with all items checked.

Custom Approval

• Go to Settings > Security & Policy > Custom Approval. Set Project Owner -> DBA as Approval flow for DDL > High Risk.

• Go to Settings > Security & Policy > Risk Center. Click Add rule and click Load for the first template. Click Add.

LATEST Schema Write-back

After schema migration completes, Bytebase will write the latest schema back to the Git repository. So that the team always has a canonical source of truth for the database schema in Git.

• Go back to Azure DevOps, and create a new branch add-country-table-employee. Create a file employee##202310201700##ddl##add_country_table_employee.sql under bytebase/prod directory. Copy the following SQL script into the file and commit the change.

ALTER TABLE "public"."employee"
ADD COLUMN "country" text NOT NULL DEFAULT '';        

• Go back to Bytebase, and go to the newly created issue. Because of the settings we made above, it matches the approval flow Project Owner -> DBA,

• After following the approval flow to click Approve, the banner will show Waiting for Rollout instead. The Assignee then can click Rollout.
• Go back to Azure DevOps, you'll notice there's a new file .employee##LATEST.sql under bytebase/prod/ with the latest schema written back by Bytebase. Ensure the branch policies permit the file to be committed to main.

Schema Drift

Bytebase has built-in schema drift detection to detect unexpected schema changes. Let's use the SQL Editor Admin Mode to simulate this.

• Click terminal icon (SQL Editor) on the top right. You'll be redirected to SQL Editor. Click Admin mode. Everything you do in this mode is the same as connecting directly to the server, which is not recorded by Bytebase.
• Select (Test) employee on the left, and paste and run the following script:

ALTER TABLE "public"."employee"
    ADD COLUMN "city" text NOT NULL DEFAULT '';        

• Go back to Bytebase Console, and click Databases > employee under Test. Click Sync Now. After seeing the success message, refresh the page. You'll see the schema drift. You may configure auto scan on instance detail page to avoid manual sync.

• Go to Anomaly Center, and you'll see the schema drift there too.

Summary

Now with Bytebase, you have a complete Database CI/CD workflow with Azure DevOps. You can apply this workflow to your own project and customize it to fit your needs. If you have any questions, please feel free join and discuss in Discord .