Database GitOps with Bitbucket Pipelines
This is part of our database GitOps series with Bytebase:
- Database GitOps with GitHub Actions
- Database GitOps with Azure DevOps Pipeline
- Database GitOps with GitLab CI
- Database GitOps with Bitbucket Pipelines (this one)
This tutorial shows you how to build an database GitOps workflow using Bitbucket Pipelines and Bytebase API. You’ll learn to create a streamlined database release workflow where you can:
- Submit schema migrations through Bitbucket
- Automatically run SQL reviews on pull requests
- Auto-create and deploy Bytebase releases when merging to
main
While we use Bitbucket Pipelines in this guide, you can apply these concepts to other CI platforms like GitHub Actions, GitLab CI, or Azure DevOps using the Bytebase API.
While we use PostgreSQL with Bitbucket Pipelines in this guide, you can apply these concepts to other SQL or NoSQL databases with any CI platforms like GitHub Actions, GitLab CI, or Azure DevOps using the Bytebase API.
Repository
https://bitbucket.org/p0nyyy/cicd/
Prerequisites
Automatic Rollout across environments
Step 1 - Start Bytebase with ngrok
ngrok is a reverse proxy tunnel that provides a public network address to access Bytebase. We use ngrok here for demonstration purposes.
-
Run Bytebase in Docker with the following command:
-
Once Bytebase is running in Docker, you can access it at
localhost:8080
. -
Log in to the ngrok Dashboard and complete the Getting Started steps to install and configure ngrok. To use a consistent domain, navigate to Universal Gateway > Endpoints to find your assigned domain:
<<YOURS>>.ngrok-free.app
. -
Start ngrok with your domain by running:
You should see output similar to this:
-
You can now access Bytebase at
https://<<YOURS>>.ngrok-free.app
. -
(Optional) To configure SSO (Entra/SCIM), Log in to Bytebase, click Settings > General in the left sidebar. Scroll to the Network section, set
https://<<YOURS>>.ngrok-free.app
as the External URL and click Confirm and update.
Step 2 - Create Service Account
-
Log in as
Workspace Admin
, and go to IAM & Admin > Users & Groups. Click + Add User, fill in withapi-sample
, choose theWorkspace DBA
role sufficient for this tutorial and click Confirm. -
Find the newly created service account and Copy Service Key. We will use this token to authenticate the API calls.
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.
-
Go to CI/CD > SQL Review, click Create SQL Review.
-
Select the
Sample Template
and click Next. -
Select
Prod
environment as the attached resources and click Confirm. Now the SQL review is enabled for theProd
environment.
Note: Usually we enable SQL review for Prod
environment as above. In this demo, we would switch to enable it for Test
to fit the following GitLab CI workflow.
Step 4 - Copy the Example Repository and Configure Variables
-
Create a new repository and copy the configuration file
bitbucket-pipelines.yml
from https://bitbucket.org/p0nyyy/cicd/. It includes sql review and release creation. -
Go into
bitbucket-pipelines.yml
, replace the variable in all 3 steps the 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_SERVICE_ACCOUNT_SECRET: the password of the service account
In bitbucket-pipelines.yml
, pay attention to BYTEBASE_TARGETS
in deploy-to-test
stage. You should put all the databases including both Test
and Prod
environments. NOT ONLY the Test
database.
Step 5 - Configure in Bitbucket
Before creating the migration files, you need to configure the environment in Bitbucket to match the environment in Bytebase.
-
Go to Repository settings > Pipelines > Deployments. Rename the environment name to
Test
->test
andProduction
->prod
. -
Go to Pipelines to enable it for the pull request to the main branch. Without this the pipeline will not be triggered.
Step 6 - 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.
202505141650_create_table_t1.sql
. - A migration file may end with
ddl
ordml
to indicate its change type. If it doesn’t end with any of the two, its change type is DDL by default.
-
Within your repository, create the following migration files under
migration
directory:- 202505141650_create_table_t1.sql
-
Commit to a new branch and create a pull request, the
sql-review
pipeline will be triggered. There will be a warning in the SQL review result. -
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.
-
When the SQL review is passed, you can merge the pull request. The
release
pipeline will be triggered to create a release in Bytebase and then roll out automatically on test but waiting for approval on prod. -
Click into the pipelines and choose the merge pipeline, you can see release is created in test environment but waiting for approval on prod.
-
If you expand the log for
bytebase-action rollout ...
, you can follow the rollout link to Bytebase. -
Click Deploy on deploy-to-prod stage in Bitbucket, the release will be deployed to prod environment.
Summary
Now you have learned how to database GitOps with Bitbucket Pipelines. If you want to trigger a release creation with other git providers (e.g. GitHub, GitLab, Azure DevOps), you may customize the workflow file.