Database Change Management with Snowflake and GitHub
Tags: snowflake, github, database, cicd
Published: 2023-01-04 03:15:25

This is a series of articles about Database Change Management with Snowflake

- [Database Change Management with Snowflake](https://dev.to/bytebase/database-change-management-with-snowflake-2mog)
- Database Change Management with Snowflake and GitHub (this one)

---

## Overview

In the last article [Database Change Management with Snowflake](https://dev.to/bytebase/database-change-management-with-snowflake-2mog), you have tried **UI workflow** in Bytebase.

This tutorial will bring your Snowflake 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.

## Prerequisites

Before you start this tutorial, make sure:

- You have followed our previous UI-based change tutorial [Database Change Management with Snowflake](https://dev.to/bytebase/database-change-management-with-snowflake-2mog).
- You have a Snowflake account with the role `ACCOUNTADMIN`.
- You have a GitHub account.
- You have a public GitHub repository.
- 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. We use ngrok here for demonstration purposes. For production use, we recommend using Caddy.

![ngrok](https://bytebase.com/static/blog/database-change-management-with-snowflake-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‘s generated URL  to [--external-url](/docs/get-started/install/external-url). If you’re running Bytebase in Docker without this parameter (as in the previous tutorial), you can stop the running Bytebase first. The data created in the last tutorial is stored under `~/.bytebase/data` by default and will be restored if the Bytebase restarts.

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`ngrok http 5678` and obtain the public URL:
![terminal-ngrok](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/terminal-ngrok.webp)

3. Make sure your docker daemon is running, and then start the Bytebase docker container by typing the following command in the terminal. Pay attention to the last parameter `--external-url` [https://d19c-103-102-7-52.ngrok.io](https://d19c-103-102-7-52.ngrok.io/), which is generated by ngrok.

````bash
docker run --init \
--name bytebase \
--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.9.1 \
--data /var/opt/bytebase \
--port 8080 \
--external-url https://d19c-103-102-7-52.ngrok.io
````
4. Bytebase is running successfully in Docker, and you can visit it via `https://d19c-103-102-7-52.ngrok.io`
![docker](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/docker.webp)
    
## Step 2 - Find your Snowflake account in Bytebase

1. Visit [https://d19c-103-102-7-52.ngrok.io](https://d19c-103-102-7-52.ngrok.io/) in your browser, and log in using your `Workspace Owner` account created from the previous article.
![bytebase-login](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/bytebase-login.webp)

2. If you have followed the last article, you should have a Project `TestSnowflake` and a database `DB_DEMO_BB`.
![home-article1](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/home-article1.webp)

## Step 3 - Connect Bytebase with GitHub.com

1. Click **Settings** on the top bar, and then click **Workspace** > **Version Control**.
![sts-vs-step1-github](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/sts-vs-step1-github.webp)

2. Choose GitHub.com and Click **Next**.

3. 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.
![sts-vs-step2](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/sts-vs-step2.webp)

4. Go to your GitHub account. Click **Settings** on the dropdown menu.
![github-settings](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/github-settings.webp) 

5. Click **Developer Settings** at the bottom of the left-side bar. Click **OAuth Apps**, and click **New OAuth App**.
![github-oauth-3png](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/github-oauth-3png.webp)

6. Fill Application name and then copy the Homepage and Authorization callback URL in Bytebase and fill them in. Click **Register application.**
![sts-vs-step2-unfilled](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/sts-vs-step2-unfilled.webp) 
![github-new-oauth](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/github-new-oauth.webp)

7. After the OAuth application is created successfully. Click **Generate a new client secret**. Copy **Client ID** and this newly generated **Client Secret**, then paste them back into Bytebase.
![github-ds-client-id](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/github-ds-client-id.webp)
![sts-vs-step2-filled](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/sts-vs-step2-filled.webp)

8. Click **Next**. You will be redirected to the confirmation page. Click **Confirm and add**, and the Git provider is successfully added.
![sts-vs-github-auth](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/sts-vs-github-auth.webp)
![sts-vs-step3](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/sts-vs-step3.webp)

## Step 4 - Enable GitOps workflow with Snowflake

1. Go to project `TestSnowflake`, click **Version Control**, and choose GitOps Workflow. Click **Configure version control**.
![prj-vs-gitops](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/prj-vs-gitops.webp)
2. Choose GitHub.com - the provider you just added. It will display all the repositories you can manipulate. Choose `snowflake-test-bb-local`.
![prj-vs-step1](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/prj-vs-step1.webp)
![prj-vs-step1-github-prj](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/prj-vs-step1-github-prj.webp)
3. Keep the default setting, and click **Finish**.
![prj-vs-step3](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/prj-vs-step3.webp)
![prj-vs-enabled](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/prj-vs-enabled.webp)

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

1. In your GitHub repository `snowflake-test-bb-local`, create a folder `bytebase`, then create a subfolder `test`, and create an SQL file following the pattern
```
{{ENV_NAME}}/{{DB_NAME}}##{{VERSION}}##{{TYPE}}##{{DESCRIPTION}}.sql
```
. It is the default configuration for file path template setting when you configure the project version control previously.

- `DB_DEMO_BB##202212152000#ddl#create_hello_world.sql`
- `Test` corresponds to {{ENV_NAME}}
- `DB_DEMO_BB` corresponds to {{DB_NAME}}
- `202212152000` corresponds to {{VERSION}}
- `ddl` corresponds to {{TYPE}}
- `create_hello_world corresponds` to {{DESCRIPTION}}

Paste the sql script in it.

````SQL
CREATE SCHEMA DEMO;
CREATE TABLE HELLO_WORLD
(
FIRST_NAME VARCHAR
,LAST_NAME VARCHAR
);
````
![vscode-create-table](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/vscode-create-table.webp)

2. Commit and push this file.
3. Go to Bytebase, and go into project `TestSnowflake`. You’ll find there is a new Push Event and a new issue 102 completed.
![prj-push-event02](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/prj-push-event02.webp)
4. Click issue/102 and go the issue page, you’ll see:
- The issue is created via github.com.
- The issue is completed without manual approval because it applies the schema change to a database from the Test environment. And our Test environment is configured with no manual approval required.
- The SQL is exactly the one we have committed to the GitHub repository.
- The Assignee is Bytebase, because it’s automatic. If the github user you use to commit the change has the same email address found in the bytebase member list, we will use that member as the assignee.
![is-create-table-done](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/is-create-table-done.webp)
5. Go to GitHub repository, you will see besides your committed SQL, there is a .DB_DEMO_BB##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.
![github-LATEST-list](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/github-LATEST-list.webp)
![github-LATEST-create-table](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/github-LATEST-create-table.webp)
6. Let’s create another SQL file `DB_DEMO_BB##202212152040#ddl#add_age.sql` to see how that latest schema file will be updated after applying a new schema change. Paste the SQL script in it.

````SQL
USE SCHEMA DEMO;
ALTER TABLE HELLO_WORLD ADD COLUMN AGE NUMBER;
````
![vscode-add-age](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/vscode-add-age.webp)
7. After pushing the new SQL file, go back to the Bytebase and you should find the generated issue.
![is-add-age-done](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/is-add-age-done.webp)
8. Click **view migration** and compare the diff.
![db-view-migration](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/db-view-migration.webp)
9. Go to GitHub repository and you will find the LATEST SQL has been updated to reflect the latest schema.
![github-LATEST-add-age](https://bytebase.com/static/blog/database-change-management-with-snowflake-and-github/github-LATEST-add-age.webp)

## Summary and Next
Now you have tried out **GitOps workflow**, which will store your Snowflake schema in GitHub and trigger the change upon committing the change to the repository, to bring your Snowflake change workflow to the next level of Database DevOps - [Database as Code](/blog/database-as-code).

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

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