Database CI/CD and Schema Migration with PostgreSQL and GitHub

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

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


In the last article Database CI/CD and Schema Migration 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 via Pull Request, which will in turn trigger the schema deployment pipeline in Bytebase.

You can use Bytebase Community Plan 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. Run Bytebase in Docker with the following command:

    docker run --rm --init \
      --name bytebase \
      --publish 8080:8080 --pull always \
      --volume ~/.bytebase/data:/var/opt/bytebase \
      bytebase/bytebase:2.15.0
  2. 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.

  3. Login to ngrok Dashboard and complete the Getting Started steps to install and configure. If you want to use the same domain each time you launch ngrok, go to Cloud Edge > Domains, where you'll find the domain <<YOURS>>.ngrok-free.app linked to your account.

  4. Run the ngrok command ngrok http --domain=<<YOURS>>.ngrok-free.app 8080 to start ngrok with your specific domain, and you will see the output displayed below:

    terminal-ngrok

  5. Log in Bytebase and click the gear icon (Settings) on the top right. Click General under Workspace. Paste <<YOURS>>.ngrok-free.app as External URL under Network section and click Update.

    external-url

  6. Now you can access Bytebase via <<YOURS>>.ngrok-free.app.

Step 2 - Find your PostgreSQL databases in Bytebase

  1. Visit Bytebase Console through the browser via your ngrok URL. Log in using your admin account.

  2. Click Select Project on the top bar, and choose the existing Sample Project. You will see there're two databases hr_prod and hr_test in it. bb-sample-project-db

Step 3 - Connect Bytebase with GitHub.com

  1. Go to Bytebase homepage, and click Integration > GitOps on the left sidebar. Choose GitHub.com as Git provider. What we need is a github personal access token. bb-gitops-no-access-token

  2. Go to your GitHub account. Click your avatar and then click Settings on the menu. Click Developer settings on the left sidebar, and then click Personal access tokens > Fine-grained token. gh-fine-grained-tokens

  3. Click Generate new token, fill in the fields and check the scopes according to the description on Bytebase. Click Generate token.

  4. Copy the token and paste it back into Bytebase Integration > GitOps. Click Confirm and add. bb-gitops-access-token

Step 4 - Enable GitOps workflow with PostgreSQL

  1. Go to the project Sample Project, click Integration > GitOps. Click Add Enable GitOps connector. bb-project-gitops-add

  2. Choose GitHub.com - the provider you just added. It will display all the repositories you can manipulate. Choose test-bb-gitops. bb-project-select-repo

  3. Keep the default setting, and click Finish. bb-project-gitops-configure

Step 5 - Change schema for PostgreSQL by pushing SQL schema change files to GitHub via Pull Request

  1. In your GitHub repository test-bb-gitops, create a folder bytebase, then create an sql file 202404121600_create_table_t1.sql.

    Paste the sql script in it.

    CREATE TABLE
       "public"."t1" (
       "id" integer NOT NULL
    );
  2. Create a new branch for this commit and start a pull request. Click Merge pull request to merge the new branch into the main branch. gh-new-branch

  3. Go to Bytebase, and go into project Sample Project. You’ll find there is a new Push Event and a new issue created. bb-push-notification-only

  4. Click and go to the issue page, you’ll see

    • The issue is created via GitHub.com, there's a link to the GitHub commit.
    • The SQL is exactly the one we have committed to the GitHub repository.
    • The SQL has passed the automatic task checks and rollout automatically.
    • Since the project contains two databases, with Community Plan it will automatically apply to all databases within the project by default. With Enterprise Plan, you'll have the option to specify the target database group. bb-project-issue-done
  5. Click View change, you can view the schema diff. bb-project-issue-view-change

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 via Pull Request, to bring your PostgreSQL change workflow to the next level of Database DevOps - Database as Code.

If the built-in workflow is not suitable, you can opt to Bytebase API to fully customize the workflow to integrate with your CI pipeline. Automating Database Schema Change workflow Using GitHub Actions is an example.

Edit this page on GitHub

Subscribe to Newsletter

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