Manage SQL Review Rules with Terraform

• Part 1: Manage Environments with Terraform - Set up environments with policies
• Part 2: Manage Databases with Terraform - Register database instances
• Part 3: Manage Projects with Terraform - Organize databases into projects
• Part 4: Manage Bytebase Settings with Terraform - Configure workspace profile and approval policies
• Part 5: Manage SQL Review Rules with Terraform 👈
• Part 6: Manage Users and Groups with Terraform - Configure users and groups
• Part 7: Manage Database Access Control with Terraform - Grant database permissions
• Part 8: Manage Data Masking with Terraform - Protect sensitive data

​

What You’ll Learn

• Configure SQL review rules for schema standards
• Enforce naming conventions and structural requirements
• Apply different severity levels (ERROR, WARNING)
• Target specific environments with review policies

​

Prerequisites

• Completed Part 4: Manage Bytebase Settings with Terraform
• Bytebase running with service account configured
• Your Terraform files from the previous tutorials

​

Setup

• Bytebase workspaces and projects configured
• Environments (Test and Prod) set up
• Workspace settings and approval flows configured

​

Configure SQL Review Rules

​

Step 1 - Create Review Configuration

# 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 to production environment only
  resources = toset([
    bytebase_setting.environments.environment_setting[0].environment[1].name
  ])

  # Rule 1: Warn about nullable columns
  rules {
    type   = "column.no-null"
    engine = "POSTGRES"
    level  = "WARNING"
  }

  # Rule 2: Require specific columns
  rules {
    type    = "column.required"
    engine  = "POSTGRES"
    level   = "ERROR"
    payload = "{\"list\":[\"id\",\"created_ts\",\"updated_ts\",\"creator_id\",\"updater_id\"]}"
  }

  # Rule 3: Tables must have primary key
  rules {
    type   = "table.require-pk"
    engine = "POSTGRES"
    level  = "ERROR"
  }

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

  # Rule 5: Limit maximum rows in SELECT
  rules {
    type    = "statement.maximum-limit-value"
    engine  = "POSTGRES"
    level   = "ERROR"
    payload = "{\"number\":1000}"
  }
}

​

Step 2 - Apply Configuration

terraform plan
terraform apply

​

Step 3 - Verify in Bytebase

1. Go to CI/CD > SQL Review on the left sidebar, you should see Sample SQL Review Config listed. Note that it’s applied to the Prod environment.
2. Click on it to view the configured rules.

​

Understanding SQL Review Rules

​

1. Column Rules

• column.no-null: Warns about nullable columns to encourage data integrity
• column.required: Enforces required columns (id, timestamps, audit fields) for consistency

​

2. Table Rules

• table.require-pk: Ensures every table has a primary key for data integrity and performance

​

3. Naming Rules

• naming.column: Enforces lowercase snake_case column names for consistency across your schema

​

4. Statement Rules

• statement.maximum-limit-value: Prevents SELECT queries without proper limits to avoid performance issues

​

Step 4 - Test SQL Review

1. Go to Project Two, and click Database > Databases on the left sidebar.
2. Check hr_prod and click Edit Schema.
3. Try creating a table that violates the rules:
   Copy

   Ask AI

   -- This will trigger multiple violations
   CREATE TABLE BadExample (
       FirstName VARCHAR(50),  -- Violates naming convention
       LastName VARCHAR(50)    -- Missing required columns, no primary key
   );
   

   Expected violations:
   • ❌ Column naming convention (should be first_name)
   • ❌ Missing required columns (id, created_ts, etc.)
   • ❌ No primary key defined
   
4. Even if you continue to create an issue, it will still show red SQL Review.
5. Try a compliant table:
   Copy

   Ask AI

   -- This follows all rules
   CREATE TABLE good_example (
       id SERIAL PRIMARY KEY,
       first_name VARCHAR(50) NOT NULL,
       last_name VARCHAR(50) NOT NULL,
       created_ts TIMESTAMP NOT NULL DEFAULT NOW(),
       updated_ts TIMESTAMP NOT NULL DEFAULT NOW(),
       creator_id INTEGER NOT NULL,
       updater_id INTEGER NOT NULL
   );
   

   Expected result:
   • ✅ All required rules pass
   

​

Advanced Configuration

​

Targeting 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

# 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 the SQL statement from being executed
  • WARNING: Allows execution but notifies users of potential issues
• Engine Specific: Rules can target specific database engines (PostgreSQL, MySQL, etc.)
• Environment Scoped: Apply different rules to test vs. production environments
• Payload Format: Complex rules use JSON payloads for configuration (e.g., regex patterns, lists)
• Best Practice: Start with WARNING level rules and gradually move to ERROR as teams adapt