How to Setup Database CI/CD with GitHub, Part 2: GitHub.com Database GitOps

A series of articles about Database CI/CD with GitHub

• The Database CI/CD Best Practice with GitHub

• How to Setup Database CI/CD with GitHub Part 1-3

  • Part 1: Enable SQL Review with GitHub Actions
  • Part 2: GitHub.com Database GitOps (this one)
  • Part 3: Put Them Together

In the last article How to Setup Database CI/CD with GitHub, Part 1: Enable SQL Review with GitHub Actions, you learned how to enable SQL review GitHub actions and trigger it automatically when there is a pull request in your repo.

This second part will guide you through configuring GitHub.com database GitOps with Bytebase. After following these steps, whenever SQL files carrying the database change are merged into your GitHub repository, the database change pipeline will be automatically triggered. Thus your Git repository always keeps the source of truth of your database schema, and your live database schema always evolves whenever SQL files change in the Git repository.

Step 1 - Run Bytebase in Docker with the URL generated by ngrok

In this tutorial, you’ll run Bytebase locally using Docker.

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

1. Login to ngrok Dashboard and follow its Getting Started steps to install and configure.
2. Run ngrok http 5678 and obtain the public URL:

3. Run Bytebase in Docker with the following command.

docker run --init \
  --name bytebase-github \
  --restart always \
  --publish 5678:8080 \
  --health-cmd "curl --fail http://localhost:5678/healthz || exit 1" \
  --health-interval 5m \
  --health-timeout 60s \
  --volume ~/.bytebase/data:/var/opt/bytebase \
  bytebase/bytebase:2.8.0 \
  --data /var/opt/bytebase \
  --port 8080 \
  --external-url https://03f1-103-102-7-52.ngrok.io \

4. Bytebase is running successfully in Docker, and you can visit it via https://03f1-103-102-7-52.ngrok.io

Step 2 - Add GitHub.com as a Git provider in Bytebase

1. Open Bytebase, go to Settings > Version Control, choose GitHub.com, and click Next.

2. Copy the Authorization callback URL.

3. Open GitHub, and go to Settings > Developer Settings > OAuth Apps. Click New OAuth App.

4. Scroll down on the new OAuth App page, paste the Authorization callback URL, then click Update Application.

5. On the same page, you can also find Client ID and Client secrets.

6. Switch back to the Bytebase console, fill Client ID and Client secrets in the form as Application ID and Secret.

6. Click Next. You will be redirected to the confirmation page. Click Confirm and add, and the Git provider is successfully added.

Step 3 - Use GitOps workflow to apply Schema Change

1. Go to Instances to add two instances for Test and Prod environments respectively. In our case, we use two AWS RDS MySQL instances with the same employee data set.

• If you don’t have any database to use, check our docs to run MySQL in docker.
• We also open sourced the sample employee data set for MySQL, which you can import: https://github.com/bytebase/employee-sample-database-mysql. If you don't import this, you can also create databases and tables manually for your needs.

2. Create a Project, click Transfer in DB and choose two identical databases belonging to Test and Prod environments respectively.

3. Go to Version Control under the project, and choose GitOps workflow.

4. Create a repository named database-gitops-sample in GitHub. Select this repository in Bytebase. Keep the default setting unless you know what you are doing. Pay attention to Branch field to make sure it’s the one you want to watch.

5. Click Finish, and you can see that Version Control is enabled.

6. Create a bytebase folder and prod, test folders under it in your GitHub repository.

7. Create a sql script following the name convention {{ENV_ID}}/{{DB_NAME}}##{{VERSION}}##{{TYPE}}##{{DESCRIPTION}}.sql, and here we create employeeGitHub##202208171630##ddl##add_nickname.sql under the test directory.

• test corresponds to {{ENV_ID}}
• employeeGitHub corresponds to {{DB_NAME}}
• 202208171630 corresponds to {{VERSION}}
• ddl corresponds to {{TYPE}}
• add_nickname corresponds to {{DESCRIPTION}}

8. Commit and push this script.
9. Go back to the Bytebase Console, and you can see there is a new issue created.

10. Visit this issue, and click Approve. The SQL will execute against the employeeGitHub database in the Test environment, and it shows Done. You may also configure the environment to skip this manual approval step.

11. Go to database employeeGitHub > table employee, and the nickname column is added there.

12. Switch to your code editor, and you will find there is an auto-generated file .employeeGitHub##LATEST.sql, which is the latest schema written back by Bytebase.

13. Copy the migration script file employeeGitHub##202208171630##ddl##add_nickname.sql and paste it into prod directory, and repeat the process. The schema change will execute on Prod environment.

Congratulations! Now you have 1) Enabled SQL Review GitHub Actions for your GitHub repo, 2) Tried out database GitOps with GitHub. In the upcoming final part, we’ll put these two together, and thus, implementing a complete database CI/CD workflow.

Ready to try out this workflow yourself? Join our Discord channel and share your experience.