SQL Review with Bytebase GUI

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 invoked from the Bytebase GUI, CI or API.

This is the 3rd part of tutorial series of SQL Review:

1. SQL Review with Bytebase GUI (this one)
2. SQL Review with CI (TBD)
3. SQL Review with Bytebase API
4. Codify SQL Review Policies with Bytebase API

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

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

Configure SQL Review Policies#

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

2. Click CI/CD > SQL Review on the left side menu. You can find there is already one default SQL Review policy named SQL Review Sample Policy.

   

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.

   

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.

   

Trigger SQL Review#

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.

   

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.

   

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

   

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.

   

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

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

   If you find that you can't edit the SQL, you can click Setting on the left side menu, and then check Allow modify statement or otherwise the SQL is not editable.

   

Summary#

Now you have learned how to trigger SQL Review in Bytebase GUI, you may also refer to the SQL Review document for more details. Next, let's learn how to trigger SQL Review from your CI API.