Logo

dev-resources.site

for different kinds of informations.

πŸ—‚οΈ Master SQL Review with GUI πŸŽ›οΈ, GitOps πŸ–‡οΈ and API πŸ”Œ

Published at
11/26/2024
Categories
api
gitops
sql
database
Author
yiran_jing_d932aa8bc72a83
Categories
4 categories in total
api
open
gitops
open
sql
open
database
open
Author
25 person written this
yiran_jing_d932aa8bc72a83
open
πŸ—‚οΈ Master SQL Review with GUI πŸŽ›οΈ, GitOps πŸ–‡οΈ and API πŸ”Œ

When modifying data in the database, it's crucial to ensure that the changes are both safe and accurate. Bytebase offers a feature called SQL Review, which allows you to evaluate your SQL changes before they are applied to the database. SQL Review can be initiated through the Bytebase GUI, a GitOps workflow, or via API.

This tutorial will walk you through using SQL Review in Bytebase to improve your database schema change process. The Community Plan is sufficient for completing this tutorial.

Image description

Prerequisites

  1. Make sure you have Docker installed, and if you don’t have important existing Bytebase data locally, you can start over from scratch by rm -rf ~/.bytebase/data.

  2. Copy and paste the commands to start one Bytebase via Docker.

    docker run --rm --init \
      --name bytebase \
      --publish 8080:8080 --pull always \
      --volume ~/.bytebase/data:/var/opt/bytebase \
      bytebase/bytebase:3.1.0
    

Configure SQL Review Policies

UI

  1. Register an admin account and it will be granted the workspace admin role automatically.

  2. Click Security & Policy > SQL Review on the left side menu. You can find there is already one default SQL Review policy named Default SQL Review Policy.

    Image description

  3. Click Edit, you'll be redirected to the SQL Review policy edit page. Here you may specify different rules for different database engines, and here our sample database is PostgreSQL, so we'll focus on the PostgreSQL rules.

    Image description

  4. The default policy is attached to Environment Prod, you may click Change attached resources to attach it to other Environments or Projects. Here we'll keep it as is. You may also click Change the template to change the rules.

  5. Click Change the template, keep the default template Default SQL Review Template and click Next. Add one rule Enforce setting default value on columns, set Error Level to Error and click Confirm and update.

    Image description

GitOps

πŸ“• Tutorial - Codify SQL Review Policies with Bytebase API

Case 1: Trigger SQL Review in Bytebase GUI

  1. Go to Sample Project, since the default SQL Review policy is attached to the Prod environment, we'll create a new table on it. Click Database > Databases on the left side menu, select hr_prod and click Edit Schema.

    Image description

  2. Click Add Table while choosing Prod hr_prod > public > Tables on the left bar. Create a new table t1 with one column id and name, intentionly unclick Not Null for name and click Preview issue.

    Image description

  3. The SQL Review will run automatically before the issue preview, and you can see the violations against our defined rules. Click Continue anyway.

    Image description

  4. After redirecting to the issue page, click Create and Continue anyway, the issue is created with SQL review red marked as there is some Error.

    Image description

  5. You may resolve this by changing the SQL as the following:

    CREATE TABLE "public"."t1" (
       "id" integer NOT NULL DEFAULT 0,
       "name" text NOT NULL DEFAULT ''
    );
    

    Here you need firstly click Settings on the left side menu, and then check Allow modify statement or otherwise the SQL is not editable.

    Image description

Case 2: Trigger SQL Review in GitOps Workflow

Start 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.

Image description

  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:3.1.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=<>.ngrok-free.app 8080 to start ngrok with your specific domain, and you will see the output displayed below:

    Image description

  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.

    Image description

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

Configure GitOps

  1. Use your domain from ngrok to visit Bytebase (if you use localhost, it won't work). Click Integrations > GitOps on the left side menu and then follow the instructions to set up a GitHub.com integration.

    Go to your GitHub repository, click Settings > Developer Settings > Personal access tokens and generate a new token. Choose All repository as Repository access and configure the token permission according to Bytebase's instructions.

    Go back to Bytebase, paste the personal access token and click Confirm and add.

    You may read the GitOps document for more details.

  2. Create a repository bb-gitops-814 in your GitHub account which will be used to submit SQL changes.

  3. Create another project Sample Project GitOps and click New DB on the Database > Databases. Choose Prod Sample Instance, name gitops_prod, environment Prod, database owner name bbsample and click Create.

  4. Within the project, click Integration > GitOps and then Add Repository. Choose the repository bb-gitops-814 and click Add GitOps connector. Follow the 3 steps, keep the default settings and click Finish. Now the workflow is set.

    Image description

  5. Go back to bb-gitops-814 on GitHub, create a file 202408141500_create_t2.sql under bb-gitops-814/bytebase/ folder. Add the following SQL:

    CREATE TABLE "public"."t2" (
       "id" integer NOT NULL PRIMARY KEY,
       "name" text NULL
    );
    
  6. Commit changes, create a new branch and click Propose changes. Then click Create pull request. On the new Pull Request page, you may see the SQL Review comment.

    Image description

  7. Edit the commit as follows and commit directly to the branch, and you may find the SQL Review comment is updated.

    CREATE TABLE "public"."t2" (
       "id" integer NOT NULL DEFAULT 0 PRIMARY KEY,
       "name" text NOT NULL DEFAULT ''
    );
    

    Image description

  8. Merge the pull request in GitHub, and you may find the issue created and executed in Bytebase.

    Image description

    Image description

Case 3: Trigger SQL Review by Bytebase API

You may call Bytebase API in your internal portal or GitHub Actions to trigger SQL Review. We don't go into details here, but you may refer to the following examples:

  • πŸ™ API Example

    Sample portal to call Bytebase API to trigger schema change including SQL Review.

    Image description

  • πŸ™ API in GitHub Actions Example

    Sample github custom actions to call Bytebase API to coordinate the schema migration in Bytebase with the GitHub PR workflow.

    Image description

Summary

Now you have learned how to trigger SQL Review in Bytebase GUI, GitHub, or by API. You may also refer to the SQL Review document for more details.

gitops Article's
30 articles in total
Favicon
How to Solve Common Kubernetes Multi-Cluster Deployment Issues
Favicon
Effortless vCluster Management with Sveltos: An Event-Driven Approach
Favicon
Google's Intent-Based Deployment with Prodspec and Annealing
Favicon
5 Best Practices for Multi-Cluster Kubernetes Add-on Management
Favicon
Emerging Trends in Git: GitOps, Monorepos, Distributed Repositories, and AI Integration
Favicon
Automating PostgreSQL Clusters: A Guide to Deployment with ArgoCD and CloudNativePG
Favicon
Advanced Kubernetes Deployment with GitOps: A Hands-On Guide To Terraform, Ansible, ArgoCD And Observability Tools
Favicon
GitOps and FluxCD: Continuous Deployment for Kubernetes on AWS
Favicon
GitOps vs Traditional DevOps: Understanding the Key Differences and Benefits
Favicon
Consistent Deployment Strategies for Kubernetes
Favicon
Why GitOps is Revolutionizing DevOps: A Guide for Agile Teams
Favicon
GitOps: A Strategic Advantage for Automation, Collaboration, and Cost Savings
Favicon
Setting Up Machine Learning Pipelines with GitOps Principles
Favicon
Automating CI/CD Pipelines for Kubernetes with Argo Rollouts, Argo CD, Argo Workflow & Events
Favicon
How Talking to a College Senior taught me Devops/Gitops
Favicon
Leveraging ArgoCD for Kubernetes Applications: Implementation, Use Cases, and Best Practices
Favicon
πŸ—‚οΈ Master SQL Review with GUI πŸŽ›οΈ, GitOps πŸ–‡οΈ and API πŸ”Œ
Favicon
GitOps Across Clusters β€” How ArgoCD and Kustomize Makes It Simple
Favicon
Portainer + gitops ❀️: A simple way to deploy and manage your self-hosted applications
Favicon
Kargo - promote your application changes in a controlled (GitOps) way!
Favicon
Tekton - A Kubernetes-native CI/CD : Day 46 of 50 days DevOps Tools Series
Favicon
GitOps + ArgoCD: A Perfect Match for Kubernetes Continuous Delivery
Favicon
FluxCD - A lightweight GitOps CD tool: Day 44 of 50 days DevOps Tools Series
Favicon
CI/CD Pipeline for a Next.js Application Using GitHub Actions, ArgoCD, and MicroK8s
Favicon
GitOps Adoption: A Simple Guide to Modern Cloud Management
Favicon
A Production Ready EKS Deployment with GitOps - Part 1 - Introduction
Favicon
Kubernetes Multi-Cluster Management πŸ“¦
Favicon
Stop using "GitOps" to sell your products
Favicon
GitOps: ArgoCD vs FluxCD
Favicon
What is GitOps ?

Featured ones: