The Database CI/CD Best Practice with Bitbucket

Wanna other VCS providers instead? 👉

The Database CI/CD Workflow#

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? Here, we present our envisioned Database CI/CD workflow.

database-devops-workflow

The developer creates a Pull Request containing the SQL script;
SQL Review CI is automatically triggered to review SQL and offers suggestions via a comment in the PR;
The team leader or another peer on the dev teams approves the change and merges the SQL script into the watched branch (default is the main branch);
The merge event automatically triggers the rollout pipeline in Bytebase and creates a ticket capturing the intended change;
(Optional) an approval flow will be auto matched based on the change risk and be followed via Bytebase’s built-in UI;
Approved scripts are executed gradually according to the configured rollout stages;
When the rollout is completed, Bitbucket CI may get notified and proceed to deploy the application.

Set Up Database CI/CD with Bitbucket in Bytebase (Free Plan)#

Here's a step-by-step tutorial on how to set up this Database CI/CD with Bitbucket 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.

ngrok-reverse-proxy

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.1.0

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.
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.
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:
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.
Now you can access Bytebase via <<YOURS>>.ngrok-free.app.

Step 2 - Add Bitbucket.org as a Git provider in Bytebase#

Visit Bytebase via your ngrok URL. Click CI/CD > GitOps, choose Bitbucket.org.
Follow the app password link to https://bitbucket.org/account/settings/app-passwords/ and click Create app password. Fill in the Label, then check the following fields and click Create.
- Account (Read)
- Workspace membership (Read)
- Projects (Read)
- Webhooks (Read and Write)
- Repositories (Read and Write)
- Pull requests (Read and Write)
Copy the generated password, go back to Bytebase, and paste the password into the field with your bitbucket username and click Confirm and add. The provider is added successfully.

Step 3 - Configure a GitOps Workflow in Bytebase#

Go to bitbucket.org, under workspace bytebase-demo, create a new project bb-gitops-2024 and a repository bb-test.
Go to Bytebase, go to the Sample Project. Click Integration >GitOps on the left and click Add GitOps connector. Choose Bitbucket.org 2024 (the git provider you just configured) and bytebase-demo/bb-test (the repository you just created).
Keep the default settings for the remaining fields and click Finish. The gitops connector is created successfully.

Step 4 - Configure SQL Review in Prod#

Go to CI/CD > SQL Review in workspace, choose Prod as the environment. Make sure a SQL review policy is attached and enabled on Prod.
Click Edit, click PostgreSQL tab. Make sure Enforce "NOT NULL" constraints on columns is enabled. This is to make sure the SQL Review can work.

Step 5 - Create a Pull Request to trigger issue creation#

Go to bb-test on Bitbucket. Add a new file 20240919_create_table_t2024.sql under bb-test/bytebase/ which is the directory configured in the previous step. Copy the following SQL script into the file and commit the change via a new branch which will create a Pull Request automatically.
```
CREATE TABLE "public"."t2024" (
      "id" integer PRIMARY KEY,
      "name" text
);
```
Wait for a while, there is a SQL Review comment added. As we configured in the previous step, NOT NULL is a warning level SQL Review rule.

Edit our sql file as following and commit it on the same branch, and merge the PR.

CREATE TABLE "public"."t2024" (
      "id" integer NOT NULL PRIMARY KEY,
      "name" text NOT NULL
);

There will be a new comment saying the PR has triggered a Bytebase rollout.
Follow the link to go to Bytebase. There's an issue with two stages, this is because we have two databases in this project, by default, the SQL will be applied to all databases within the project. If you merge the previous version SQL script, the SQL Review task run here will show yellow warning and waiting for rollout. Click Resolve to resolve the issue.
After the rollout completes, click View change to see the diff.
You may also go to a specific database page to view all its change history.

Advanced Features (Enterprise Plan)#

You may upgrade to Enterprise plan to explore more features.

Manual Rollout#

Go to Environments > Prod, Find Rollout policy section, and choose Manual rollout > Require rolling out by dedicated roles.

bb-env-prod-manual-rollout

Custom Approval#

Go to CI/CD > Custom Approval. Set Project Owner -> DBA as Approval flow for DDL > High Risk.
Go to CI/CD > Risk Center. Click Add rule and click Load for the first template. Click Add.

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 (Prod) 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 Prod. 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 Database > Anomalies, and you'll see the Schema drift there too.

Summary#

Now with Bytebase, you have a complete Database CI/CD workflow with Bitbucket. 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.