DevOps: Database Change Management with PostgreSQL and GitHub
![DevOps: Database Change Management with PostgreSQL and GitHub](/_next/image/?url=%2Fcontent%2Fdocs%2Ftutorials%2Fdatabase-change-management-with-postgresql-and-github%2Ffeature-image.webp&w=2048&q=75)
A series of articles about DevOps: Database Change Management with PostgreSQL
- DevOps: Database Change Management with PostgreSQL
- DevOps: Database Change Management with PostgreSQL and GitHub (this one)
Overview
In the last article DevOps: Database Change Management with PostgreSQL, 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.
Prerequisites
Before you start this tutorial, make sure:
- You have followed our previous UI-based change tutorial DevOps: Database Change Management with PostgreSQL.
- You have a GitHub account.
- You have a public GitHub repository, e.gĀ
pg-test-bb-local
. - You have Docker installed locally.
- You have a ngrok account.
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.
-
Login to ngrok Dashboard and follow its Getting Started steps to install and configure.
-
Run ngrok:
ngrok http 5678
and obtain the public URL
https://b725-103-197-71-76.ap.ngrok.io
: -
Run Bytebase in Docker with the following command:
docker run --init \ --name bytebase \ --restart always \ --publish 5678:8080 \ --volume ~/.bytebase/data:/var/opt/bytebase \ bytebase/bytebase:2.11.1 \ --data /var/opt/bytebase \ --port 8080
-
Bytebase is running successfully in Docker, and you can visit it via
localhost:5678
. Register an admin account and it will be granted theworkspace admin
role automatically. -
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. -
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 PostgreSQL instance in Bytebase
-
Visit Bytebase Console through the browser via your ngrok URL. Log in using your account created from the previous tutorial.
-
If you have followed the last article, you should have a Project
Sample Project
and a databasedemo
.
Step 3 - Connect Bytebase with GitHub.com
-
Click Settings on the top bar, and then click Workspace > Version Control. Choose GitHub.com and click Next.
-
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.
-
Go to your GitHub account. Click Settings on the dropdown menu.
-
Click Developer Settings at the bottom of the left side bar. Click OAuth Apps, and click New OAuth App.
-
Fill Application name and then copy the Homepage and Authorization callback URL in Bytebase and fill them. Click Register application.
-
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.
-
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 PostgreSQL
-
Go to project
Sample Project
, click Version Control, and choose GitOps Workflow. Click Configure GitOps. -
Choose GitHub.com - the provider you just added. It will display all the repositories you can manipulate. Choose
pg-test-bb-local
. -
Keep the default setting, and click Finish.
Step 5 - Change schema for PostgreSQL by pushing SQL schema change files to GitHub
-
In your GitHub repository
pg-test-bb-local
, create a folderbytebase
, then create a subfolderProd
, 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##202316410000##ddl##create_t2.sql
prod
corresponds to{{ENV_ID}}
demo
corresponds to{{DB_NAME}}
202316410000
corresponds to{{VERSION}}
ddl
corresponds to{{TYPE}}
create_t2
corresponds to{{DESCRIPTION}}
Paste the sql script in it.
CREATE TABLE
"public"."t2" (
"id" integer NOT NULL,
"name" character varying(255) NOT NULL,
PRIMARY KEY ("id")
);
COMMENT
ON COLUMN "public"."t2"."id" IS 'ID';
-
Commit and push this file.
-
Go to Bytebase, and go into project
Sample Project
. Youāll find there is a newPush Event
and a newissue 105
created. -
Click
issue/105
and go the issue page, youāll see- The issue is created via GitHub.com
- The issue is waiting for your approval because itās on
Prod
environment where manual approval is required by default. - 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.
-
Click Approve, and the SQL will execute. Click Resolve issue, and the issue will be
Done
. -
Click View change, you could view the schema diff.
-
Go to GitHub repository, you will see besides your committed sql, there is a
.demo##LATEST.sql
file. Because you have configuredSchema 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 PostgreSQL schema in GitHub and trigger the change upon committing the change to the repository, to bring your PostgreSQL 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!