How to Improve SQL Queries Generated by Cursor

1-- docs/schema.sql
2-- Auto-generated schema reference for Cursor context
3
4CREATE TABLE users (
5  id SERIAL PRIMARY KEY,
6  email VARCHAR(255) UNIQUE NOT NULL,
7  name VARCHAR(100) NOT NULL,
8  role VARCHAR(20) DEFAULT 'user',
9  created_at TIMESTAMP DEFAULT NOW(),
10  updated_at TIMESTAMP DEFAULT NOW()
11);
12
13CREATE TABLE orders (
14  id SERIAL PRIMARY KEY,
15  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
16  status VARCHAR(20) DEFAULT 'pending',
17  total_cents INTEGER NOT NULL,
18  created_at TIMESTAMP DEFAULT NOW()
19);
20
21CREATE INDEX idx_orders_user_id ON orders(user_id);
22CREATE INDEX idx_orders_status ON orders(status);
23CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

1# .cursorrules (add to existing file)
2
3## SQL and Database Rules
4- NEVER use SELECT * — always list specific columns
5- ALWAYS use parameterized queries ($1, $2) — never string interpolation
6- Use LEFT JOIN only when null results are expected, prefer INNER JOIN
7- Include LIMIT on all list queries, default 50, max 1000
8- Use cursor-based pagination (WHERE id > $last_id) for large datasets
9- Reference docs/schema.sql for table structure
10- Add EXPLAIN comments when generating complex queries
11- Prefer EXISTS over IN for subqueries with large result sets
12- Always include ORDER BY with LIMIT to ensure deterministic results

1---
2description: SQL query optimization rules
3globs: "**/*.sql, **/queries/**, **/repositories/**, **/dal/**"
4alwaysApply: false
5---
6
7- Reference @docs/schema.sql for table structure before writing any query
8- Include index hints as comments: -- Uses idx_orders_user_id
9- Prefer CTEs (WITH clauses) over nested subqueries for readability
10- Every query must have a comment explaining what it returns
11- Batch INSERT/UPDATE operations when handling multiple rows
12- Use RETURNING clause in PostgreSQL for INSERT/UPDATE to avoid extra SELECT

1// Prompt to type in Cursor Chat (Cmd+L):
2// @docs/schema.sql
3// Generate a repository function that fetches a user's recent orders.
4// Requirements:
5// - Accept userId and optional status filter
6// - Return order id, status, total_cents, created_at
7// - Paginate with cursor-based pagination (after_id parameter)
8// - Default limit 20, max 100
9// - Sort by created_at DESC
10// - The orders table has ~10M rows, optimize accordingly
11// - Use parameterized queries, not string interpolation

1// Select your existing query, then press Cmd+K and type:
2// @docs/schema.sql Optimize this query for a table with 10M rows.
3// Use existing indexes, replace SELECT * with specific columns,
4// add cursor-based pagination, and use parameterized queries.

1// Prompt to type in Cursor Composer (Cmd+I):
2// @docs/schema.sql @src/repositories/orderRepository.ts
3// Generate a query-benchmark.ts utility that:
4// 1. Imports all queries from the repository
5// 2. Runs EXPLAIN ANALYZE on each query with sample params
6// 3. Checks that no query uses Seq Scan on tables > 10k rows
7// 4. Reports index usage and estimated cost
8// 5. Exits with code 1 if any query misses an index