sql-workflow

The key words "MUST", "MUST NOT", "REQUIRED", "SHALL", "SHALL NOT", "SHOULD", "SHOULD NOT", "RECOMMENDED", "MAY", and "OPTIONAL" in this document are to be interpreted as described in RFC 2119. # SQL Workflow ## Tool Grid | Task | Tool | Command | |------|------|---------| | Lint | SQLFluff | `sqlfluff lint` | | Format | SQLFluff | `sqlfluff fix` | | Analyze | Database | `EXPLAIN ANALYZE` | ## Naming Conventions ### Tables and Columns - All identifiers MUST use `snake_case` - Table names SHOULD be plural nouns (e.g., `users`, `order_items`) - Column names MUST be descriptive and unambiguous - Primary keys SHOULD be named `id` or `{table_singular}_id` - Foreign keys MUST follow `{referenced_table_singular}_id` pattern - Boolean columns SHOULD use `is_`, `has_`, or `can_` prefix ### Examples ```sql -- Good CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, is_completed BOOLEAN DEFAULT FALSE, created_at TIMESTAMP WITH TIME ZONE ); -- Bad CREATE TABLE Order ( OrderID INT, userId INT, completed BOOLEAN ); ``` ## Data Types ### Preferred Types - Text: MUST use `TEXT` over `VARCHAR` unless length constraint is business requirement - Timestamps: MUST use `TIMESTAMP WITH TIME ZONE` for all temporal data - Integers: SHOULD use `BIGINT` for IDs to prevent overflow - Decimals: MUST use `NUMERIC(precision, scale)` for money/financial data - UUIDs: SHOULD use native `UUID` type when available - JSON: SHOULD use `JSONB` over `JSON` for PostgreSQL ### Avoid - `CHAR(n)` - wastes space with padding - `FLOAT`/`DOUBLE` for financial calculations - `TIMESTAMP` without time zone ## Migrations ### File Naming Migrations MUST follow: `YYYYMMDD_HHMMSS_description.sql` ``` 20241215_143022_create_users_table.sql 20241215_150000_add_email_index_to_users.sql 20241216_090000_create_orders_table.sql ``` ### Migration Structure ```sql -- Migration: 20241215_143022_create_users_table.sql -- Description: Creates the users table with core fields BEGIN; CREATE TABLE users ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email TEXT NOT NULL, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() ); COMMIT; ``` ### Rules - Each migration MUST be wrapped in a transaction (BEGIN/COMMIT) - Migrations MUST be idempotent when possible (use IF NOT EXISTS) - Down migrations SHOULD be provided for reversible changes - Data migrations MUST be separate from schema migrations ## Query Style ### Formatting - Keywords MUST be uppercase: `SELECT`, `FROM`, `WHERE`, `JOIN` - Identifiers MUST be lowercase: `users`, `created_at` - Each major clause SHOULD start on a new line - Indentation SHOULD be 4 spaces - Commas SHOULD be at the end of lines (trailing commas) ### Example ```sql SELECT u.id, u.email, COUNT(o.id) AS order_count FROM users AS u LEFT JOIN orders AS o ON u.id = o.user_id WHERE u.is_active = TRUE AND u.created_at >= '2024-01-01' GROUP BY u.id, u.email HAVING COUNT(o.id) > 0 ORDER BY order_count DESC LIMIT 100; ``` ### Aliases - Table aliases MUST be meaningful (not single letters for complex queries) - Column aliases MUST use `AS` keyword explicitly - Aliases SHOULD be lowercase ## Indexes ### When to Create - Primary keys automatically create indexes - Foreign key columns SHOULD have indexes - Columns frequently used in WHERE clauses SHOULD be indexed - Columns used in ORDER BY with LIMIT SHOULD be considered - Composite indexes for multi-column queries ### Naming Convention ``` idx_{table}_{column(s)} idx_{table}_{column1}_{column2} ``` ### Examples ```sql -- Single column index CREATE INDEX idx_users_email ON users (email); -- Composite index CREATE INDEX idx_orders_user_id_created_at ON orders (user_id, created_at); -- Partial index CREATE INDEX idx_orders_pending ON orders (id) WHERE status = 'pending'; -- Unique index CREATE UNIQUE INDEX idx_users_email_unique ON users (email); ``` ### Anti-patterns - Indexes on low-cardinality columns (boolean, status with few values) - Too many indexes on write-heavy tables - Unused indexes consuming storage ## Constraints ### Foreign Keys ```sql ALTER TABLE orders ADD CONSTRAINT fk_orders_user_id FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE; ``` ### Check Constraints ```sql ALTER TABLE products ADD CONSTRAINT chk_products_price_positive CHECK (price > 0); ALTER TABLE users ADD CONSTRAINT chk_users_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'); ``` ### Naming - Foreign keys: `fk_{table}_{column}` - Check constraints: `chk_{table}_{description}` - Unique constraints: `uq_{table}_{column(s)}` ## NULL Handling ### Rules - MUST use `IS NULL` / `IS NOT NULL` for NULL comparisons - MUST NOT use `= NULL` or `!= NULL` - SHOULD use `COALESCE()` for default values - SHOULD use `NULLIF()` to convert values to NULL ### Examples ```sql -- Correct NULL handling SELECT * FROM users WHERE deleted_at IS NULL; -- Using COALESCE for defaults SELECT COALESCE(nickname, email) AS display_name FROM users; -- Using NULLIF to handle empty strings SELECT NULLIF(phone, '') AS phone FROM users; ``` ## Performance ### Query Analysis - MUST use `EXPLAIN ANALYZE` before optimizing - SHOULD check for sequential scans on large tables - SHOULD monitor query execution time ```sql EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123; ``` ### Best Practices - MUST NOT use `SELECT *` in production code - SHOULD specify only required columns - SHOULD use `EXISTS` instead of `COUNT(*)` for existence checks - SHOULD use `LIMIT` with `ORDER BY` for pagination - MUST avoid N+1 queries - use JOINs or batch fetching ### Examples ```sql -- Bad: SELECT * SELECT * FROM users WHERE id = 1; -- Good: Explicit columns SELECT id, email, created_at FROM users WHERE id = 1; -- Bad: COUNT for existence SELECT COUNT(*) > 0 FROM orders WHERE user_id = 1; -- Good: EXISTS SELECT EXISTS (SELECT 1 FROM orders WHERE user_id = 1); ``` ## Transactions ### Rules - Write operations SHOULD use explicit transactions - Transactions MUST be as short as possible - SHOULD set appropriate isolation level when needed ### Example ```sql BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; ``` ### Isolation Levels ```sql -- For critical financial operations BEGIN ISOLATION LEVEL SERIALIZABLE; -- ... operations ... COMMIT; ``` ## SQLFluff Configuration ### Recommended .sqlfluff ```ini [sqlfluff] dialect = postgres templater = raw max_line_length = 120 [sqlfluff:rules:capitalisation.keywords] capitalisation_policy = upper [sqlfluff:rules:capitalisation.identifiers] capitalisation_policy = lower [sqlfluff:rules:layout.long_lines] ignore_comment_lines = True ``` ## Common Pitfalls | Pitfall | Solution | |---------|----------| | `= NULL` comparison | Use `IS NULL` | | `SELECT *` in production | Explicit column list | | Missing indexes on FKs | Always index foreign keys | | VARCHAR for text | Use TEXT | | TIMESTAMP without TZ | Use TIMESTAMP WITH TIME ZONE | | No transaction for writes | Wrap in BEGIN/COMMIT | | Guessing query performance | Use EXPLAIN ANALYZE |