DevOps: Database Change Management with MariaDB and GitHub
Tags: mariadb, devops, github, cicd
Published: 2023-06-17 16:46:33

This is a series of articles about DevOps: Database Change Management with MariaDB

- [DevOps: Database Change Management with MariaDB](https://dev.to/adela_bytebase/devops-database-change-management-with-mariadb-17pa)
- DevOps: Database Change Management with MariaDB and GitHub (this one)

---

In the last article [DevOps: Database Change Management with MariaDB](https://dev.to/adela_bytebase/devops-database-change-management-with-mariadb-17pa), 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 [DevOps: Database Change Management with MariaDB](https://dev.to/adela_bytebase/devops-database-change-management-with-mariadb-17pa).
- You have a GitHub account.
- You have a public GitHub repository, e.g  `test-bb-mariadb-gitops`.
- You have [Docker](https://www.docker.com/) installed locally.
- You have a [ngrok](http://ngrok.com) account. 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](http://GitHub.com). We use ngrok here for demonstration purposes. For production use, we recommend using [Caddy](https://caddyserver.com/).
  ![ngrok](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb-and-github/ngrok.webp)

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

To make local-running Bytebase visible to GitHub, we’ll pass ngrok generated URL to [--external-url](https://www.bytebase.com/docs/get-started/install/external-url).


1. Login to [ngrok Dashboard](https://dashboard.ngrok.com/) and follow its [Getting Started](https://dashboard.ngrok.com/get-started/setup) steps to install and configure.

2. Run

   ```bash
   ngrok http 5678
   ```

   and obtain the public URL:
   ![terminal-ngrok](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb-and-github/terminal-ngrok.webp)

3. Make sure your Docker daemon is running, if it’s running Bytebase container for [the previous tutorial](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb), there're two ways:

- 1a. Go to **Settings** > **Workspace** > **General**, fill in the **External URL** field and click **Update**.
- 1b. Stop and remove it. The data created in the last tutorial is stored under `~/.bytebase/data` by default and will be restored if the system restarts. Start the Bytebase Docker container by typing the following command in the terminal. Pay attention to the last parameter `--external-url https://1681-149-129-123-75.ngrok-free.app`, which is generated by ngrok.

   ```bash
   docker run --init \
   --name bytebase \
   --platform linux/amd64 \
   --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:%%bb_version%% \
   --data /var/opt/bytebase \
   --port 8080 \
   --external-url https://1681-149-129-123-75.ngrok-free.app
   ```

   You can now visit Bytebase via `https://70ca-154-9-204-36.ngrok.io/`.

## Step 2 - Find your MariaDB instance in Bytebase

1. Visit `https://70ca-154-9-204-36.ngrok.io/`in your browser, and log in using your admin account created from the previous article.

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**.
   ![bb-settings-vc-step1](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb-and-github/bb-settings-vc-step1.webp)

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.
   ![bb-settings-vc-step2](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb-and-github/bb-settings-vc-step2.webp)

3. Go to your GitHub account. Click **Settings** on the dropdown menu.
   ![gh-settings-dropdown](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb-and-github/gh-settings-dropdown.webp)

4. Click **Developer Settings** at the bottom of the left side bar. Click **OAuth Apps**, and click **New OAuth App**.
   ![gh-oauth-apps](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb-and-github/gh-oauth-apps.webp)

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.
   ![gh-copy-client-id](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb-and-github/gh-copy-client-id.webp)
   ![bb-vc-client-id](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb-and-github/bb-vc-client-id.webp)

7. Click **Next**. You will be redirected to the confirmation page. Click **Confirm and add**, and the Git provider is successfully added.
   ![bb-settings-vc-step3](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb-and-github/bb-settings-vc-step3.webp)

## Step 4 - Enable GitOps workflow with MariaDB

1. Go to project `Demo Git`, click **GitOps**, and choose **GitOps Workflow**. Click **Configure GitOps**.
   ![bb-demo-git-gitops-workflow](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb-and-github/bb-demo-git-gitops-workflow.webp)

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.
   ![bb-demo-git-gitops-enabled](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb-and-github/bb-demo-git-gitops-enabled.webp)

## 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.

   ```sql
   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.
   ![bb-push-event](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb-and-github/bb-push-event.webp)

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 `Owner`, because the GitHub user you use to commit the change has the same email address found in the Bytebase member list.
     ![bb-issue-gitops-to-rollout](https://www.bytebase.com/content/docs/tutorials/database-change-management-with-mariadb-and-github/bb-issue-gitops-to-rollout.webp)

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](https://www.bytebase.com/blog/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](https://www.bytebase.com/docs/how-to/workflow/gitops-feature-branch) to learn the setup. Have a try and look forward to your feedback!