The Database CI/CD Best Practice with Bitbucket
Wanna other VCS providers instead? 👉
- The Database CI/CD Best Practice with GitLab
- The Database CI/CD Best Practice with GitHub
- The Database CI/CD Best Practice with Azure DevOps
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 Bitbucket. It's similar with GitLab, GitHub or Azure DevOps.
- The developer creates a Merge Request containing the SQL migration script;
- The team leader or another peer on the dev teams approves the change and merges the SQL script into a branch;
- The merge event automatically triggers the release pipeline in Bytebase and creates a release 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;
- 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;
- Confirm the migration and proceed to the corresponding application rollout.
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.
Login to ngrok Dashboard and follow its Getting Started steps to install and configure.
and obtain the public URL
Run Bytebase in Docker with the following command:
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 adminrole automatically.
Click the gear icon (Settings) on the top right. Click General under Workspace. Paste
https://b725-103-197-71-76.ap.ngrok.ioas External URL under Network section and click Update.
Bytebase is running successfully in Docker, and you can visit it via
Step 2 - Add Bitbucket.org as a Git provider in Bytebase
Visit Bytebase via your ngrok URL. Click gear icon (Settings) > Integration > GitOps, choose
Bitbucket.org, and click Next. You will see STEP 2. Copy the Redirect URI.
https://bitbucket.org/, your account must be an workspace admin of the Bitbucket workspace (able to access the workspace Settings page). Go to the Settings page, then navigate to APPS AND FEATURES > OAuth consumers section and click Add a consumer. Fill in the following fields:
- Redirect URI: Copied from Bytebase GitOps config STEP 2
Account > Read,
Webhooks > Read and write,
Repositories > Write
Copy the Key and Secret from the Bitbucket 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
bitbucket.organd create a new project
Go to Bytebase, go to the
Sample Project. Click GitOps tab and choose
GitOps workflow. Click Configure GitOps. Choose
Bitbucket.org(the git provider you just configured) and the repository you just created.
You'll be redirected to STEP 3. Change Branch to
master, keep everything else as default, and click Finish.
Step 4 - Create a Pull Request to trigger issue creation
bb-gitops-ngrokon Bitbucket. Create a new branch
add-nickname-table-employee. On the new branch, create a subdirectory
bytebase, and create a sub-subdirectory
prod. Within the
proddirectory, create a file
employee##202311012500##ddl##add_nickname_table_employee.sql. Copy the following SQL script into the file and commit the change.
Create a pull request including the above commits and merge it. Go back to Bytebase, you'll see there is a new issue created by the pull request.
Click the issue to see the details. SQL review automatic checks failed. Click the warning, you'll see the details.
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.
Update the SQL script and commit in a new branch and click Merge.
Go back to Bytebase, and refresh the issue. You'll see the SQL review passed. 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.
Go to Environments > 2.Prod, Find Rollout policy section, and choose Manual rollout > Require rolling out by dedicated roles.
Go to Settings > Security & Policy > Custom Approval. Set
Project Owner -> DBAas 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 Bitbucket, and create a new branch
add-country-table-employee. Create a file
bytebase/proddirectory. Copy the following SQL script into the file and commit the change.
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 Bitbucket, you'll notice there's a new file
bytebase/prod/with the latest schema written back by Bytebase.
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.
(Prod) employeeon the left, and paste and run the following script:
Go back to Bytebase Console, and click Databases >
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 Anomaly Center, and you'll see the Schema drift there too.
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.