Database Change Management with ClickHouse and GitHub

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

• Database Change Management with ClickHouse
• Database Change Management with ClickHouse and GitHub (this one)

In the last article Database Change Management with ClickHouse, 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 Change Management with ClickHouse.
• You have a ClickHouse Cloud account or a ClickHouse self-managed instance running.
• You have a GitHub account.
• You have a public GitHub repository, e.g  clickhouse-test-bb-local.
• You have Docker installed locally.
• You have a ngrok 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.

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.

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

2. Run
   

ngrok http 5678

and obtain the public URL:

1. Make sure your Docker daemon is running, if it’s running Bytebase container for the previous tutorial, 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.

2. Start the Bytebase Docker container by typing the following command in the terminal. Pay attention to the last parameter --external-url https://b16b-103-197-71-76.ap.ngrok.io, which is generated by ngrok.
   

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://b16b-103-197-71-76.ap.ngrok.io

`

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

Step 2 - Find your ClickHouse instance in Bytebase

1. Visit https://b16b-103-197-71-76.ap.ngrok.io in your browser, and log in using your admin account created from the previous article.
   

2. If you have followed the last article, you should have a project TestClickHouse and a database db_demo.
   

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.
   

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 ClickHouse

1. Go to project TestClickHouse, 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 clickhouse-test-bb-local.
   
   

3. Keep the default setting, and click Finish.

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

1. In your GitHub repository clickhouse-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 under project GitOps.

db_demo##202303020000##ddl##create_t2.sql

• Test corresponds to {{ENV_NAME}}
• db_demo corresponds to {{DB_NAME}}
• 202303020000 corresponds to {{VERSION}}
• ddl corresponds to {{TYPE}}
• create_t2 corresponds to {{DESCRIPTION}}

Paste the sql script in it.

`
CREATE TABLE
t2 (id UInt64, name String) ENGINE = MergeTree
ORDER BY id;

`

1. Commit and push this file.

2. Go to Bytebase, and go into project TestClickHouse. You’ll find there is a new Push Event and a new issue 106 created.
   

3. Click issue/106 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.

1. Click View change, and you could view the schema diff.
   

2. 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 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 ClickHouse schema in GitHub and trigger the change upon committing the change to the repository, to bring your ClickHouse 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!