How to Setup Database CI/CD with GitHub, Part 3: Put Them Together

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
  • Part 3: Put Them Together (this one)

---

Now that you have finished Part 1: SQL Review GitHub Actions and Part 2: GitOps workflow, this final part will guide you through putting them together to run the whole process.

As you may ask, during the GitOps process, we can configure SQL review in Bytebase console UI version, why do we need SQL review GitHub Actions? Actually, in real-world scenarios, code review including SQL files is conducted by technical leaders to cover the business domain, while DBAs review the SQL to cover the database domain. Both parties need SQL review, they just look after different aspects.

Let’s dig in and see how to do this! The detailed configuration is based on the previous two tutorials, check out them first if you come across directly to this one.

Here is a demo repo: https://github.com/adela-bytebase/bytebase-gitops-sample with an open PR https://github.com/adela-bytebase/bytebase-gitops-sample/pull/11

Step 1 - Add Configure file for Prod Environment

Repeat the steps in Part 1- Enable SQL Review GitHub Action to configure the prod environment. You now have two GitHub Actions config files for test and prod environments respectively. Pay attention to paths, and file-pattern.

Step 2 - Create a PR, and add your SQL scripts

1. Create a new branch testboth.
2. Add a migration script under bytebase/test folder following the name convention {{ENV_ID}}/{{DB_NAME}}##{{VERSION}}##{{TYPE}}##{{DESCRIPTION}}.sql. Here we name it employeeGitHub##202208211500##ddl##add_nickname.sql.

3. Commit and push this to the testboth branch, and create a PR in GitHub. The SQL review GitHub Actions will run automatically.

4. After the SQL review completes, the PR becomes mergeable.

5. After you merge the PR in GitHub, there will be a new issue generated in Bytebase console.

6. Approve this issue, and the SQL script will run against the test environment. When the issue status turns into Done, a file .employeeGitHub##LATEST.sql will be generated to record the current state of the database.
7. If everything seems OK for you for the test environment, you can then copy your SQL file (except the xx##LATEST.sql) and paste them under the prod folder, to trigger the schema changes on prod environment. Good Luck!

Congratulations! Now you have implemented a complete database CI/CD workflow! Let's go over the steps in the picture again:

1. You add a SQL script on a branch, push it, and create a PR on GitHub.
2. The configured SQL Review GitHub Action run automatically.
3. If the SQL script passes that, you can merge it into the main branch.
4. Since your running Bytebase is configured to watch main branch, it will trigger to create an issue there.
5. The SQL review check will run on the issue again. After its run, you can approve it (You can change the settings to skip manual approval if needed).
6. The SQL script will run on your database, and the migration is done.
7. There will be an auto-generated file .xxx##LATEST.sql, which is the latest schema written back by Bytebase after the migration.
8. After all migrations are done, you can now deploy your application code that is dependent on the new schema.

Customize the structure to fit your own needs! Feel free to Join our Discord channel and share your experience.