Skip to main content
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