This tutorial is part of the Bytebase Terraform Provider series:

What You’ll Learn

In this tutorial, you’ll learn how to:
  • Set up automated SQL review rules to maintain schema standards
  • Implement naming conventions and structural requirements across your databases
  • Configure severity levels (ERROR, WARNING) to control validation strictness
  • Apply review policies to specific environments for targeted governance
  • Test your SQL review rules to ensure they work as intended

Prerequisites

Before starting this tutorial, ensure you have:

Setup

From the previous tutorials, you should have:
  • Bytebase workspaces and projects configured
  • Environments (Test and Prod) set up
  • Workspace settings and approval flows configured

Configure SQL Review Rules

SQL Review rules automatically validate SQL statements against predefined standards, ensuring code quality and schema consistency before changes enter the approval workflow.
Terraform resourcebytebase_review_config
Sample file5-sql-review.tf

Step 1 - Create Review Configuration

The examples below demonstrate some SQL Review rules. For a comprehensive list of available rules and their configuration options, refer to the Terraform provider resource documentation linked above.
Create 5-sql-review.tf with the SQL review configuration:
5-sql-review.tf
# SQL review configuration for production environment
resource "bytebase_review_config" "sample" {
  depends_on = [
    bytebase_setting.environments
  ]

  resource_id = "review-config-sample"
  title       = "Sample SQL Review Config"
  enabled     = true

  # Apply rules to production environment only
  resources = toset([
    bytebase_setting.environments.environment_setting[0].environment[1].name
  ])

  # Rule 1: Column nullability - warn about nullable columns to improve data integrity
  rules {
    type   = "column.no-null"
    engine = "POSTGRES"
    level  = "WARNING"
  }

  # Rule 2: Required audit columns - enforce standard tracking fields
  rules {
    type    = "column.required"
    engine  = "POSTGRES"
    level   = "ERROR"
    payload = "{\"list\":[\"id\",\"created_ts\",\"updated_ts\",\"creator_id\",\"updater_id\"]}"
  }

  # Rule 3: Primary key requirement - ensure all tables have primary keys
  rules {
    type   = "table.require-pk"
    engine = "POSTGRES"
    level  = "ERROR"
  }

  # Rule 4: Column naming standards - enforce snake_case naming convention
  rules {
    type    = "naming.column"
    engine  = "POSTGRES"
    level   = "ERROR"
    payload = "{\"format\":\"^[a-z]+(_[a-z]+)*$\",\"maxLength\":64}"
  }

  # Rule 5: Query performance protection - limit maximum rows in SELECT statements
  rules {
    type    = "statement.maximum-limit-value"
    engine  = "POSTGRES"
    level   = "ERROR"
    payload = "{\"number\":1000}"
  }
}
The configuration above applies SQL Review rules specifically to the Prod environment. Alternatively, you can scope rules to a specific project by using bytebase_project.sample_project.name in the resources attribute.

Step 2 - Apply Configuration

terraform plan
terraform apply

Step 3 - Verify in Bytebase

  1. Navigate to CI/CD > SQL Review in the left sidebar. You should see Sample SQL Review Config listed, showing that it’s applied to the Prod environment. sql-review-list
  2. Click on the configuration to examine the rules you’ve defined. sql-review-pg

Understanding SQL Review Rules

SQL Review rules are organized into categories based on what they validate:

1. Column Rules

  • column.no-null: Issues warnings about nullable columns to promote data integrity and prevent null-related errors
  • column.required: Mandates the presence of essential columns (ID, timestamps, audit fields) to ensure consistent table structure

2. Table Rules

  • table.require-pk: Guarantees that every table includes a primary key, which is crucial for data integrity, replication, and query performance

3. Naming Rules

  • naming.column: Enforces consistent lowercase snake_case naming conventions across your database schema, improving readability and maintainability

4. Statement Rules

  • statement.maximum-limit-value: Prevents potentially harmful SELECT queries by requiring appropriate LIMIT clauses, protecting against performance degradation

Step 4 - Test SQL Review

Let’s validate that your SQL Review rules are working by testing them with some sample SQL:
  1. Navigate to Project Two, then click Database > Databases in the left sidebar.
  2. Select the hr_prod database and click Edit Schema.
  3. Create a table that intentionally violates multiple rules:
    -- This table intentionally violates multiple SQL review rules
    CREATE TABLE BadExample (
        FirstName VARCHAR(50),  -- Violates snake_case naming convention
        LastName VARCHAR(50)    -- Missing required audit columns and primary key
    );
    
    Expected violations:
    • ❌ Column naming convention violation (should be first_name, last_name)
    • ❌ Missing required audit columns (id, created_ts, updated_ts, creator_id, updater_id)
    • ❌ No primary key defined
    sql-review-violations
  4. Even if you proceed to create an issue, the SQL Review violations will be prominently displayed with error indicators. issue-sql-review-errors
  5. Now try creating a compliant table that follows all your rules:
    -- This table complies with all SQL review rules
    CREATE TABLE good_example (
        id SERIAL PRIMARY KEY,                        -- Required primary key
        first_name VARCHAR(50) NOT NULL,              -- Snake_case naming, not null
        last_name VARCHAR(50) NOT NULL,               -- Snake_case naming, not null
        created_ts TIMESTAMP NOT NULL DEFAULT NOW(),  -- Required audit column
        updated_ts TIMESTAMP NOT NULL DEFAULT NOW(),  -- Required audit column
        creator_id INTEGER NOT NULL,                  -- Required audit column
        updater_id INTEGER NOT NULL                   -- Required audit column
    );
    
    Expected result:
    • ✅ All SQL review rules pass successfully
    issue-sql-review-pass

Advanced Configuration

Targeting Multiple Environments

To apply rules to multiple environments:
resources = toset([
  bytebase_setting.environments.environment_setting[0].environment[0].name,
  bytebase_setting.environments.environment_setting[0].environment[1].name
])

Engine-Specific Rules

Different rules for different database engines:
# MySQL-specific rule
rules {
  type   = "table.require-pk"
  engine = "MYSQL"
  level  = "ERROR"
}

# PostgreSQL-specific rule
rules {
  type   = "table.require-pk"
  engine = "POSTGRES"
  level  = "ERROR"
}

Key Points

  • Rule Levels:
    • ERROR: Blocks SQL statement execution, preventing non-compliant changes from proceeding
    • WARNING: Permits execution while alerting users to potential issues, useful for gradual policy adoption
  • Engine Specificity: Rules can be tailored to specific database engines (PostgreSQL, MySQL, Oracle, etc.) to accommodate platform differences
  • Environment Scoping: Apply stricter rules to production while allowing more flexibility in development and testing environments
  • Payload Configuration: Complex rules utilize JSON payloads for detailed configuration (regex patterns, value lists, thresholds)
  • Implementation Strategy: Begin with WARNING level rules to familiarize teams with standards, then gradually transition to ERROR level for enforcement

Part 6: Manage Users and Groups with Terraform