Database CI/CD and Schema Migration with MariaDB and GitHub

A series of articles about Database CI/CD and Schema Migration with MariaDB

• Database CI/CD and Schema Migration with MariaDB
• Database CI/CD and Schema Migration with MariaDB and GitHub (this one)

---

In the last article Database CI/CD and Schema Migration with MariaDB, you have tried UI workflow in Bytebase.

This tutorial will bring you to the next level by introducing the GitOps workflow, where you commit the schema change script to the GitHub repository, which will in turn trigger the schema deployment pipeline in Bytebase.

You can use Bytebase free version to finish the tutorial.

Features included#

• GitOps Workflow
• Change History

Prerequisites#

Before you start this tutorial, make sure:

• You have followed our previous UI-based change tutorial Database CI/CD and Schema Migration with MariaDB.
• You have a GitHub account.
• You have a public GitHub repository, e.g  test-bb-mariadb-gitops.
• You have Docker installed locally.
• You have a ngrok account.

Step 1 - Run Bytebase in Docker and set the External URL generated by ngrok#

Step 2 - Find your MariaDB instance in Bytebase#

1. Visit Bytebase Console through the browser via your ngrok URL. Log in using your account created from the previous tutorial.

2. Click Projects on the top bar, and then click Create Project. Fill in Demo Git as Project Name, DGT as Key, Standard as Mode and click Create.

3. Go to the Demo Git project, and click New DB.

4. Fill the form with the following information and click Create.

   • Name: demo_db_git
   • Environment: Test
   • Instance: MariaDB test It will create an issue "CREATE DATABASE …" automatically. Because it’s for Test environment, the issue will automatically run then becomes Done. The database is created.

5. Click New DB again, fill the form with the following information and click Create.

   • Name: demo_db_git
   • Environment: Prod
   • Instance: MariaDB prod

   It will create an issue "CREATE DATABASE …" automatically. Because it’s for Prod environment, the issue will require manual rollout. Click Rollout to run then becomes Done. The database is created.

Step 3 - Connect Bytebase with GitHub.com#

1. Click Settings on the top bar, and then click Workspace > Version Control. Choose GitHub.com and click Next.

2. Follow the instructions within STEP 2, and in this tutorial, we will use a personal account instead of an organization account. The configuration is similar.

3. Go to your GitHub account. Click Settings on the dropdown menu.

4. Click Developer Settings at the bottom of the left side bar. Click OAuth Apps, and click New OAuth App.

5. Fill Application name and then copy the Homepage and Authorization callback URL in Bytebase and fill them. Click Register application.

6. After the OAuth application is created successfully. Click Generate a new client secret. Copy Client ID and this newly generated client secret and paste them back in Bytebase.

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

Step 4 - Enable GitOps workflow with MariaDB#

1. Go to project Demo Git, click GitOps, and choose GitOps Workflow. Click Configure GitOps.

2. Choose GitHub.com - the provider you just added. It will display all the repositories you can manipulate. Choose test-bb-mariadb-gitops.

3. Keep the default setting, and click Finish. The GitOps workflow is successfully enabled.

Step 5 - Change schema for MariaDB by pushing SQL schema change files to GitHub#

1. In your GitHub repository test-bb-mariadb-gitops, create a folder bytebase, then create a subfolder prod, and create an sql file following the pattern {{ENV_ID}}/{{DB_NAME}}##{{VERSION}}##{{TYPE}}##{{DESCRIPTION}}.sql. It is the default configuration for file path template setting under project version control.

   prod/demo_git_db##2023061614300000##ddl##create_t2.sql

   • prod corresponds to {{ENV_ID}}
   • demo_git_db corresponds to {{DB_NAME}}
   • 2023061614300000 corresponds to {{VERSION}}
   • ddl corresponds to {{TYPE}}
   • create_t2 corresponds to {{DESCRIPTION}}

   Paste the sql script in it.

   CREATE TABLE t2(
   id INT NOT NULL,
   PRIMARY KEY(id)
   )

2. Commit and push this file.

3. Go to Bytebase, and go into project Demo Git. You’ll find there is a new Push Event and a new issue created.

4. Click the issue link, and you’ll see:

   • The issue is created via GitHub.com.
   • The issue is waiting for your rollout because it’s on Prod environment where manual rollout is required by default.
   • The SQL is exactly the one we have committed to the GitHub repository.
   • The Creator is Workspace Admin, because the GitHub user you use to commit the change has the same email address found in the Bytebase member list.

5. Click Rollout, and the SQL will execute. The issue will be Done.

6. Click View change, you could view the schema diff.

7. You may try with your Test environment, the only difference is that you don’t need to rollout the issue manually.

If you have Enterprise Plan. Go to GitHub repository, you will see besides your committed sql, there is a .demo_git_db##LATEST.sql file. Because you have configured Schema path template before, Bytebase will write back the latest schema to that specified path after completing the schema change. Thus you have access to an update-to-date full schema at any time.

Summary and Next#

Now you have tried out GitOps workflow, which will store your MariaDB schema in GitHub and trigger the change upon committing the change to the repository, to bring your MariaDB change workflow to the next level of Database DevOps - Database as Code.

In real world scenario, you might have separate features and main branches corresponding to your dev and production environment, you can check out GitOps with Feature Branch Workflow to learn the setup. Have a try and look forward to your feedback!