Database Change Management with Amazon Aurora and GitHub
Tags: aws, aurora, github, devops
Published: 2023-03-13 15:37:06

This is a series of articles about Database Change Management with Amazon Aurora

- [Database Change Management with Amazon Aurora](https://dev.to/adela_bytebase/database-change-management-with-amazon-aurora-15d4)
- Database Change Management with Amazon Aurora and GitHub (this one)

---

In the last article [Database Change Management with Amazon Aurora](https://www.bytebase.com/blog/database-change-management-with-amazon-aurora), you have tried **UI workflow** in Bytebase.

This tutorial will bring your Amazon Aurora schema change 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 the following ready:

- Followed our previous UI-based change tutorial [Database Change Management with Amazon Aurora](https://www.bytebase.com/blog/database-change-management-with-amazon-aurora).
- An Amazon Aurora MySQL instance.
- A GitHub account.
- A public GitHub repository, e.g  `aurora-test-bb-local`.
- [Docker](https://www.docker.com/) installed locally.
- An [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. We use ngrok here for demonstration purposes. For production use, we recommend using [Caddy](https://caddyserver.com/).)

![ngrok](https://www.bytebase.com/static/blog/database-change-management-with-amazon-aurora-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 the URL generated by ngrok 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/static/blog/database-change-management-with-amazon-aurora-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/blog/database-change-management-with-amazon-aurora), 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.

4. Start the Bytebase Docker container by typing the following command in the terminal. Pay attention to the last parameter `--external-url https://b725-103-197-71-76.ap.ngrok.io`, 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:1.13.0 \
--data /var/opt/bytebase \
--port 8080 \
--external-url https://b725-103-197-71-76.ap.ngrok.io
```

5. Bytebase is running successfully in Docker, and you can visit it via `https://b725-103-197-71-76.ap.ngrok.io`.
![docker](https://www.bytebase.com/static/blog/database-change-management-with-amazon-aurora-and-github/docker.webp)

## Step 2 - Find your Amazon Aurora in Bytebase

1. Visit Bytebase Console through the browser via your ngrok URL `https://b725-103-197-71-76.ap.ngrok.io`. Log in using your account created from the previous tutorial.
![bb-login](https://www.bytebase.com/static/blog/database-change-management-with-amazon-aurora-and-github/bb-login.webp)

2. If you followed the previous tutorial, you should see that the project and database created are still in your workspace.
![bb-home](https://www.bytebase.com/static/blog/database-change-management-with-amazon-aurora-and-github/bb-home.webp)

## Step 3 - Connect Bytebase with GitHub.com

1. Click **Settings** on the top bar, and then click **Workspace** > **GitOps**. Choose **GitHub.com** and click **Next**.
   
![bb-settings-gitops-1-github](https://www.bytebase.com/static/blog/database-change-management-with-amazon-aurora-and-github/bb-settings-gitops-1-github.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.

3. Go to your GitHub account. Click your avatar on the top right, and then click **Settings** on the dropdown menu.
![github-dropdown](https://www.bytebase.com/static/blog/database-change-management-with-amazon-aurora-and-github/github-dropdown.webp)

4. Click **Developer Settings** at the bottom of the left sidebar. Click **OAuth Apps**, and add a **New OAuth App**.
![github-oauth-apps](https://www.bytebase.com/static/blog/database-change-management-with-amazon-aurora-and-github/github-oauth-apps.webp)

5. Fill **Application name** and then copy the **Homepage** and **Authorization callback URL** in Bytebase and fill them. Click **Register application**.
![github-register-oauth](https://www.bytebase.com/static/blog/database-change-management-with-amazon-aurora-and-github/github-register-oauth.webp)

6. After the OAuth application is created, click **Generate a new client secret**. Copy the **Client ID** and the newly generated **Client Secret**, paste them back into Bytebase's **Application ID** and **Secret**.
![github-clientid-secret](https://www.bytebase.com/static/blog/database-change-management-with-amazon-aurora-and-github/github-clientid-secret.webp)
![bb-settings-gitops-2](https://www.bytebase.com/static/blog/database-change-management-with-amazon-aurora-and-github/bb-settings-gitops-2.webp)

7. Click **Next**. You will be redirected to the confirmation page. Click **Confirm and add**, and the Git provider is successfully added.
![github-auth](https://www.bytebase.com/static/blog/database-change-management-with-amazon-aurora-and-github/github-auth.webp)
![bb-settings-gitops-3](https://www.bytebase.com/static/blog/database-change-management-with-amazon-aurora-and-github/bb-settings-gitops-3.webp)

## Step 4 - Enable GitOps Workflow with Amazon Aurora

1. Go to your project `TestAurora`, 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 `aurora-mysql-test-bb-local`.
![bb-project-gitops-github-list](https://www.bytebase.com/static/blog/database-change-management-with-amazon-aurora-and-github/bb-project-gitops-github-list.webp)

3. Keep the default setting, and click **Finish**.

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

1. In your GitHub repository `aurora-mysql-test-bb-local`, create a folder `bytebase`, then create a subfolder `Test`, and create a SQL file using the naming convention 
```
{{ENV_NAME}}/{{DB_NAME}}##{{VERSION}}##{{TYPE}}##{{DESCRIPTION}}.sql
```
It is the default configuration for the file path template setting under project `TestAurora` > **GitOps**.
   
Paste the sql script in it.

```sql
CREATE TABLE t2
(
   id BIGINT NOT NULL,
   course VARCHAR(255)
);
```

![vscode](https://www.bytebase.com/static/blog/database-change-management-with-amazon-aurora-and-github/vscode.webp)

2. Commit and push this file.

3. Go to Bytebase, and go into project `TestAurora`. You’ll find there is a new `Push Event` and a new `issue 107` created.
![bb-project-push-event](https://www.bytebase.com/static/blog/database-change-management-with-amazon-aurora-and-github/bb-project-push-event.webp)

4. Click `issue/107` and go the issue page. Click **Resolve issue**, and the issue will be `Done`. You’ll see:
   - The issue is created via GitHub.com
   - The issue is executed without approval because it’s on `Test` environment where manual approval is skipped by default. The Assignee is `Bytebase`, because the execution is automatic, and requires no manual approval.
   - The SQL is exactly the one we have committed to the GitHub repository.
   - The Creator is `A`, because the GitHub user you use to commit the change has the same email address found in the Bytebase member list.

![bb-issue-github-create-t2-done](https://www.bytebase.com/static/blog/database-change-management-with-amazon-aurora-and-github/bb-issue-github-create-t2-done.webp)

5. Click **View change**, you can view the schema diff.
![bb-dbdemo-diff](https://www.bytebase.com/static/blog/database-change-management-with-amazon-aurora-and-github/bb-dbdemo-diff.webp)

6. Go to GitHub repository, and you will see besides your committed sql, there is a `.db_demo##LATEST.sql` file. Because you have configured [Schema path template](https://www.bytebase.com/docs/vcs-integration/name-and-organize-schema-files#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.
![github-LATEST](https://www.bytebase.com/static/blog/database-change-management-with-amazon-aurora-and-github/github-LATEST.webp)

## Summary and What's Next

Now that you have tried the **GitOps workflow**, which stores your Amazon Aurora schema in GitHub and triggers the change upon committing change to the repository, to bring your Amazon Aurora change workflow to the next level of Database DevOps - [Database as Code](https://www.bytebase.com/blog/database-as-code).

You can check out [GitOps docs](docs/vcs-integration/overview) to learn more configuration details.

In the real world, you might have separated feature and main branches corresponding to your development 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!