SQL Style Guide: Writing Clean and Maintainable Code

12 min read

Table of Contents

The Importance of SQL Style

A consistent SQL style guide is essential for maintaining readable and maintainable database code. Whether you're working solo or in a team, following consistent conventions makes your SQL easier to understand, debug, and modify. This guide covers essential styling practices that will improve your SQL code quality.

1. Naming Conventions

Table and Column Naming:

-- Good table names (plural, descriptive)
CREATE TABLE customers (
    customer_id    INT PRIMARY KEY,
    first_name     VARCHAR(50),
    last_name      VARCHAR(50),
    date_of_birth  DATE
);

-- Good junction table name 
-- (combines both table names)
CREATE TABLE customer_orders (
    customer_id  INT,
    order_id     INT,
    PRIMARY KEY (customer_id, order_id)
);

-- Bad names to avoid
CREATE TABLE customer_data_tbl; -- avoid suffixes
CREATE TABLE cust;              -- avoid abbreviations
CREATE TABLE data;              -- too vague

Naming Best Practices

  • Use snake_case for all identifiers
  • Make names self-descriptive
  • Use plural for table names
  • Be consistent with abbreviations

Naming Anti-patterns

  • Avoid generic names (data, info)
  • Don't use reserved SQL keywords
  • Avoid system-specific prefixes
  • Don't mix naming conventions

2. Query Formatting

Clean Query Formatting Example:

SELECT 
    c.customer_name,
    COUNT(o.order_id) AS total_orders,
    SUM(o.total_amount) AS revenue
FROM 
    customers c
    INNER JOIN orders o 
        ON c.customer_id = o.customer_id
WHERE 
    o.order_date >= '2024-01-01'
    AND o.status = 'completed'
GROUP BY 
    c.customer_name
HAVING 
    COUNT(o.order_id) > 5
ORDER BY 
    revenue DESC
LIMIT 10;

Formatting Guidelines:

  • Major clauses start on new lines
  • Indent subqueries and conditions
  • Align related items vertically
  • Use consistent capitalization for keywords
  • Include line breaks for readability
  • Keep line lengths reasonable (under 80 characters)

3. Comments and Documentation

Well-Documented Query Example:

/* Purpose: Calculate customer lifetime value (CLV)
 * Input: customer_id (required)
 * Output: total_spent, order_count, avg_order_value
 * Author: Jane Smith
 * Created: 2024-03-15
 * Modified: 2024-03-18 - Added order frequency
 */
WITH customer_metrics AS (
    SELECT 
        customer_id,
        COUNT(*) AS order_count,
        SUM(total_amount) AS total_spent,
        -- Calculate average days between orders
        AVG(DATE_DIFF('day', 
            LAG(order_date) OVER (
                PARTITION BY customer_id 
                ORDER BY order_date
            ),
            order_date
        )) AS avg_days_between_orders
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT 
    /* Main metrics for CLV calculation */
    total_spent,
    order_count,
    total_spent / order_count AS avg_order_value,
    avg_days_between_orders
FROM customer_metrics
WHERE customer_id = @customer_id;

4. Common Patterns and Idioms

CTEs vs. Subqueries

-- Prefer CTEs for complex queries
WITH monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', sale_date) 
            AS month,
        SUM(amount) AS revenue
    FROM sales
    GROUP BY 
        DATE_TRUNC('month', sale_date)
),
sales_growth AS (
    SELECT 
        month,
        revenue,
        LAG(revenue) OVER (
            ORDER BY month
        ) AS prev_month_revenue
    FROM monthly_sales
)
SELECT 
    month,
    revenue,
    ((revenue - prev_month_revenue) / 
        prev_month_revenue * 100) 
        AS growth_percentage
FROM sales_growth;

Style Guide Checklist

Formatting

  • Consistent keyword capitalization
  • Proper indentation and alignment
  • Meaningful line breaks

Naming

  • Clear, descriptive names
  • Consistent naming patterns
  • Meaningful abbreviations

Recommended Style Tools

Formatting Tools

  • • SQL formatters
  • • Style checkers
  • • Code review tools
  • • IDE plugins

Documentation Tools

  • • Schema visualizers
  • • Documentation generators
  • • Version control systems
  • • Collaboration platforms

Conclusion

A consistent SQL style guide is more than just making code look pretty – it's about creating maintainable, readable, and reliable database code. By following these guidelines, you'll make your SQL code more professional and easier to work with.

Remember that the best style guide is one that your team consistently follows. Consider adapting these guidelines to match your team's specific needs and preferences.

Ready to Format Your SQL Code?

Try our SQL Formatter tool to automatically format your SQL queries according to these style guidelines.

Try SQL Formatter

Common Style Mistakes to Avoid

Bad Style Examples:

-- Bad: Inconsistent capitalization and formatting
select user_id,User_Name,COUNT(*) as COUNT 
from users u inner join orders o on u.user_id=o.user_id 
WHERE status='ACTIVE' group by user_id,User_Name;

-- Good: Consistent style
SELECT 
    user_id,
    user_name,
    COUNT(*) AS total_orders
FROM 
    users u
    INNER JOIN orders o 
        ON u.user_id = o.user_id
WHERE 
    status = 'ACTIVE'
GROUP BY 
    user_id,
    user_name;

Common Pitfalls

  • Inconsistent indentation
  • Mixed naming conventions
  • Poor line breaks
  • Unclear aliases

Solutions

  • Use an automated formatter
  • Follow team conventions
  • Review code regularly
  • Document style decisions

Advanced Style Guidelines

Complex Query Organization:

/* Report: Sales Performance Analysis
 * Tables: sales, products, categories
 * Purpose: Analyze sales trends by category
 */

-- Step 1: Calculate base metrics
WITH daily_sales AS (
    SELECT 
        DATE_TRUNC('day', sale_date) AS sale_day,
        product_id,
        SUM(quantity) AS units_sold,
        SUM(amount) AS total_revenue
    FROM 
        sales
    WHERE 
        sale_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY 
        DATE_TRUNC('day', sale_date),
        product_id
),

-- Step 2: Add product categories
category_sales AS (
    SELECT 
        ds.sale_day,
        p.category_id,
        c.category_name,
        SUM(ds.units_sold) AS total_units,
        SUM(ds.total_revenue) AS revenue
    FROM 
        daily_sales ds
        INNER JOIN products p 
            ON ds.product_id = p.product_id
        INNER JOIN categories c 
            ON p.category_id = c.category_id
    GROUP BY 
        ds.sale_day,
        p.category_id,
        c.category_name
)

-- Step 3: Calculate final metrics
SELECT 
    category_name,
    SUM(total_units) AS month_units_sold,
    SUM(revenue) AS month_revenue,
    AVG(revenue) AS avg_daily_revenue,
    MAX(revenue) AS best_day_revenue
FROM 
    category_sales
GROUP BY 
    category_name
ORDER BY 
    month_revenue DESC;

Team Style Guide Template

Essential Elements

1. Naming Conventions

  • Table naming pattern: [plural_noun]
  • Column naming pattern: [lowercase_with_underscores]
  • Index naming: idx_[table]_[column(s)]
  • Temp table prefix: tmp_

2. Formatting Rules

  • Indent size: 4 spaces
  • Maximum line length: 80 characters
  • Keyword case: UPPERCASE
  • Comma position: trailing

3. Documentation Requirements

  • Header comments for complex queries
  • Inline comments for business logic
  • Version history for stored procedures
  • Parameter documentation