Database CI/CD and Schema Migration with Snowflake and GitHub

Estimated: 30 mins
Database CI/CD and Schema Migration with Snowflake and GitHub

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


Overview

In the last article Database CI/CD and Schema Migration with Snowflake, 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:

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

ngrok is a reverse proxy tunnel, and in our case, we need it for a public network address in order to receive webhooks from VCS. ngrok we used here is for demonstration purposes. For production use, we recommend using Caddy.

ngrok-reverse-proxy

  1. Login to ngrok Dashboard and follow its Getting Started steps to install and configure.

  2. Run ngrok:

    ngrok http 8080

    and obtain the public URL https://b725-103-197-71-76.ap.ngrok.io: terminal-ngrok

  3. Run Bytebase in Docker with the following command:

    docker run --rm --init \
      --name bytebase \
      --publish 8080:8080 \
      --volume ~/.bytebase/data:/var/opt/bytebase \
      bytebase/bytebase:2.14.1
  4. Bytebase is running successfully in Docker, and you can visit it via localhost:8080. Register an admin account and it will be granted the workspace admin role automatically.

  5. Click the gear icon (Settings) on the top right. Click General under Workspace. Paste https://b725-103-197-71-76.ap.ngrok.io as External URL under Network section and click Update.

    external-url

  6. Bytebase is running successfully in Docker, and you can visit it via https://b725-103-197-71-76.ap.ngrok.io.

Step 2 - Find your Snowflake account in Bytebase

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

  2. If you have followed the last article, you should have a Project TestSnowflake and a database DB_DEMO_BB. home-article1

Step 3 - Connect Bytebase with GitHub.com

  1. Click Settings on the top bar, and then click Workspace > Version Control. sts-vs-step1-github

  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

  4. Go to your GitHub account. Click Settings on the dropdown menu. github-settings

  5. Click Developer Settings at the bottom of the left-side bar. Click OAuth Apps, and click New OAuth App. github-oauth-3png

  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 github-new-oauth

  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 sts-vs-step2-filled

  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 sts-vs-step3

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
  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 prj-vs-step1-github-prj
  3. Keep the default setting, and click Finish. prj-vs-step3 prj-vs-enabled

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_ID}}/{{DB_NAME}}##{{VERSION}}##{{TYPE}}##{{DESCRIPTION}}.sql. It is the default configuration for file path template setting when you configure the project version control previously.
  • test/DB_DEMO_BB##202212152000##ddl##create_hello_world.sql
  • test corresponds to {{ENV_ID}}
  • 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.

CREATE SCHEMA DEMO;
CREATE TABLE HELLO_WORLD
(
FIRST_NAME VARCHAR
,LAST_NAME VARCHAR
);

vscode-create-table

  1. Commit and push this file.
  2. Go to Bytebase, and go into project TestSnowflake. You’ll find there is a new Push Event and a new issue 102 completed. bb-push-notification-only
  3. Click issue/102 and go to 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
  1. 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 github-LATEST-create-table
  2. 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.
USE SCHEMA DEMO;
ALTER TABLE HELLO_WORLD ADD COLUMN AGE NUMBER;

vscode-add-age 7. After pushing the new SQL file, go back to the Bytebase and you should find the generated issue. is-add-age-done 8. Click view migration and compare the diff. db-view-migration 9. Go to GitHub repository and you will find the LATEST SQL has been updated to reflect the latest schema. github-LATEST-add-age

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.

You can check out our GitOps docs 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 to learn the setup. Have a try and look forward to your feedback!

Edit this page on GitHub

Subscribe to Newsletter

By subscribing, you agree with Bytebase's Terms of Service and Privacy Policy.