Automating Database Schema Change workflow Using GitHub Actions
Tutorial repository https://github.com/bytebase/github-action-example
Developers like to keep their schema migration scripts in Git along with the application code. Thus the migration scripts will be reviewed and version-controlled in the same way as the application code. However, developers still have to manually paste the migration script into their SQL client or ask the DBAs to run it against the target database. This is inefficient and error-prone:
- What if you paste the wrong script / miss the script?
- What if you run the script against the wrong database?
This tutorial teaches how to get rid of this manual process and fully automate database schema change using GitHub Actions and Bytebase API.
Above shows a typical workflow:
-
Developer creates a PR containing the migration script. After PR is created, it triggers a GitHub Action to lint the SQL by calling Bytebase SQL Review API.
-
TL approves the PR.
-
Upon PR approval, it triggers a GitHub Action to create a Bytebase rollout issue. The issue contains the migration script changes.
-
Depending on the configured approval and rollout policy, it may require manual approval and rollout from DBA. Another GitHub Action is configured to block the PR merge until Bytebase rolls out the schema migration. Sometimes, a PR contains both code and schema changes. This setup guarantees the schema migration is applied before the code deployment.
-
Bytebase deploys the schema change and marks the issue as Done.
-
PR re-runs the migration status check and now it turns green.
-
Now PR can be merged.
Prepare Bytebase
Suppose Bytebase is running at https://bytebase.example.com/
. To begin, we'll first set up the necessary data to support our API interactions.
-
Service account: As an admin, we add a service account
[email protected]
with theWorkspace DBA
role, which will be used to authenticate the API calls. -
A database in a project: We have a project called
Example
, and a database:example
.
Prepare GitHubs Actions
Go and check the example on GitHub https://github.com/bytebase/ci-example.
The repository contains several GitHub Action workflows, you may go to .github/workflows
to view.
We will use the following workflows:
bytebase-sql-review.yml
: Triggered on PR change. Thus any SQL review violation will block the PR.bytebase-upsert-migration.yml
Triggered on PR approval. Creates the Bytebase migration issue after approval. And whenever the migration scripts change afterwards, the migration issue will also be updated accordingly.bytebase-check-migration-status.yml
: Triggered on PR change. Thus PR will be blocked until migration completes.
Sample Workflow, Four Phases
To illustrate the workflow, we have divided it into four phases to showcase the database schema change process.
Phase 1: Not passing SQL review on GitHub
Before we delve into the workflow, let's set up the SQL Review policy in Bytebase. The example database is on the Prod
environment, where we will configure SQL review policy. Here we have a policy that checks for NOT NULL
constraints, which we will violate in the PR.
Returning to GitHub Actions and digging into the code, the bytebase-sql-review.yml
workflow is triggered on PR change. It scans the SQL files named following the pattern **.up.sql
in the PR and reports any SQL review policy violations.
Configure the environment.
After authentication, we call the Bytebase API /sql/check
to lint the migration files. We parse the response
and emit GitHub inline annotations for each advice and mark the check as failed if any ERROR
or WARNING
is found.
We create a PR with several SQL files, and it triggers both bytebase-sql-review.yml
and bytebase-check-migration-status.yml
. After these checks are completed, the PR is blocked due to failures.
Click on the Details for SQL Review.
You may also go to Files changed to view the annotations.
Phase 2: Passing SQL review and waiting for TL's approval on GitHub
We then fix the SQL files and pushes. After completing these checks, the PR is still blocked due to failures, but this time SQL review has passed.
In real-life scenarios, the PR also encompasses application code. Because the SQL migration has passed the basic SQL review checks, it is now time for a tech leader to approve this PR.
The developer who creates the PR assigns the tech leader to review on GitHub.
Phase 3: TL approves on GitHub and migration issue is created in Bytebase
The assigned tech leader approves the PR, and another workflow bytebase-upsert-migration.yml
is triggered.
It checks the SQL files named like **.up.sql
within the pull request and creates a rollout issue in Bytebase.
Go to Bytebase and view the created issue, which consists of two tasks corresponding to the presence of two **.up.sql
files in the PR.
You may notice there is an approval flow attached to the created issue, that's because we set up a default custom approval flow for DDL.
Phase 4: Migration completed and PR is mergable on GitHub
After the DBA approves and rolls out the migrations, the issue status will become Done
.
Go back to GitHub, click Details for the failed bytebase-check-migration-status.yml
workflow, and then click Re-run all jobs.
It checks the migration status in Bytebase and return pass
if it's Done
, indicating the database migration has been completed. The PR is now ready to be merged, which means the application code is ready to be deployed.
Summary
Keep in mind that workflows can be tuned according to your organization's needs:
-
You can attach the workflow to different branches depending on your branching strategy (e.g. trunk-based or not).
-
You can use different migration file formats and different migration file structures.
-
You can determine when to create the migration issue, upon PR approval or creation.
Whatever workflow you choose, with the help of GitHub Actions and Bytebase API, you can now keep your migration scripts in the repository, let the migration scripts go through the same code review process and automate the schema migration deployment.