standards-queries_skill

Overview

This skill codifies production-ready database query standards: secure parameterized queries, eager loading to avoid N+1 problems, and strategic indexing for performance. It guides writing SQL and ORM queries, setting timeouts, using transactions, and caching expensive operations to keep data access safe and fast.

How this skill works

I inspect query code and database interaction patterns for injection risks, N+1 access, overly broad SELECTs, missing indexes, absent transactions for multi-step writes, and lack of timeouts or caching. I recommend concrete fixes: parameterize inputs, add eager loads or JOINs, select only needed columns, create targeted indexes, wrap related writes in transactions with row locks, and introduce query timeouts or caches where appropriate.

When to use it

• When writing raw SQL or ORM queries in backend code
• When implementing repository/data access layers or query files
• When optimizing data fetching to prevent N+1 queries
• When adding indexes for WHERE, JOIN, or ORDER BY columns
• When wrapping related writes in transactions or setting query timeouts
• When caching expensive or frequently-run queries

Best practices

• Always use parameterized queries or ORM query builders; never concatenate user input into SQL strings
• Eager-load relations (include/join/selectinload) to avoid N+1, choosing strategy by relation size
• Select only required columns; avoid SELECT * on wide or high-volume tables
• Index columns used in filters, joins, and sorts; prefer composite indexes for multi-column filters but avoid over-indexing
• Wrap multi-step writes in transactions and use row-level locks when modifying read data
• Set sensible statement timeouts and cache expensive queries with TTL or write-based invalidation

Example use cases

• Refactor an API endpoint that triggers N+1 queries to use eager loading with include/join
• Convert string-interpolated SQL to parameterized queries to eliminate SQL injection risk
• Add composite index for queries filtering by user_id and status to speed reports
• Wrap balance transfer logic in a transaction with with_for_update/lock to prevent races
• Introduce Redis caching for a heavy aggregation query with hourly TTL and invalidate on writes