SQL Cursor Rules: Database Query Optimization Guide

---
description: Enforces best practices for SQL development, focusing on query optimization, indexing, CTEs, and database-agnostic patterns. Provides comprehensive guidelines for writing performant, maintainable database queries with proper context.
globs: **/*.sql
---
# SQL Best Practices

You are an expert in SQL development and database technologies.
You understand modern SQL development practices, query optimization, and the importance of providing complete context in code generation.

### Context-Aware Code Generation
- Ask about the database dialect (PostgreSQL, MySQL, SQLite, etc.) before generating dialect-specific syntax
- Include relevant schema context (table structures, relationships, indexes) when generating queries
- Specify whether the query is for OLTP, analytics, or migration workload
- Document the expected data volume and query frequency for optimization guidance

### Code Style
- Uppercase SQL keywords (SELECT, FROM, WHERE, JOIN)
- Place each major clause on a new line for readability
- Indent subqueries and CTE definitions
- Use snake_case for table and column names
- Use meaningful table aliases (o for orders, not t1)

### Query Optimization
- Never use SELECT *; always list required columns explicitly
- Use EXPLAIN ANALYZE to understand query execution plans
- Prefer specific JOIN types (INNER, LEFT) over implicit joins
- Use EXISTS over IN for subquery checks when filtering on indexed columns
- Avoid functions in WHERE clauses that prevent index usage

### Indexing Strategy
- Add indexes on columns used in WHERE, JOIN, and ORDER BY clauses
- Use composite indexes for queries filtering on multiple columns
- Use partial indexes for filtered queries on large tables
- Monitor index usage and remove unused indexes
- Consider covering indexes for frequent query patterns

### CTEs & Window Functions
- Use CTEs (WITH clauses) to break complex queries into readable steps
- Prefer CTEs over nested subqueries for multi-step transformations
- Use ROW_NUMBER(), RANK(), and LEAD()/LAG() for analytic queries
- Use window functions over self-joins for running totals and comparisons

### Schema Design & Migrations
- Normalize data to reduce redundancy, denormalize only for read performance
- Use appropriate data types (INTEGER for IDs, TIMESTAMP for time, TEXT for large strings)
- Define foreign key constraints to maintain referential integrity
- Write idempotent migrations that can be rerun safely
- Include up/down migration pairs for reversibility

### Security & Injection Prevention
- Use parameterized queries or prepared statements for all dynamic values
- Never concatenate user input directly into SQL strings
- Apply row-level security for multi-tenant databases
- Grant minimal privileges (SELECT/INSERT/UPDATE/DELETE) per role

-- CTE with window function for analytics query
WITH user_orders AS (
    SELECT
        u.id,
        u.email,
        o.total,
        ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY o.created_at DESC) as rn
    FROM users u
    JOIN orders o ON o.user_id = u.id
    WHERE o.created_at >= '2025-01-01'
)
SELECT id, email, total
FROM user_orders
WHERE rn = 1  -- most recent order per user
ORDER BY total DESC
LIMIT 10;

-- Parameterized query with prepared statement
-- Safe: uses $1, $2 placeholders, not concatenation
PREPARE find_users_by_role (text) AS
    SELECT id, email, role
    FROM users
    WHERE role = $1
    AND deleted_at IS NULL
    ORDER BY created_at DESC;

EXECUTE find_users_by_role('admin');