backend-queries_skill

Overview

This skill guides writing efficient, secure database queries and data-access logic in TypeScript backends. It emphasizes parameterized queries, eager loading, transaction safety, and query performance optimizations to reduce latency and prevent common pitfalls like N+1 queries and SQL injection. Use it to standardize repository/DAO code and ORM usage across services.

How this skill works

The skill inspects query intent and suggests concrete query patterns: parameterized SQL, ORM query-builder calls, eager-loading or join strategies, and pagination/caching techniques. It recommends specific performance improvements such as indexing hints, selecting only needed columns, using transactions where required, and applying query timeouts or monitoring. Outputs include code snippets, refactor suggestions, and checklist-style validations tailored to TypeScript ORMs like Prisma, TypeORM, and Sequelize.

When to use it

• Writing or refactoring SQL or ORM-based data retrieval logic
• Implementing eager loading or joins to avoid N+1 problems
• Adding filters, sorting, pagination, or search features
• Protecting queries against SQL injection with parameterization
• Optimizing slow queries, adding indexes, or caching results

Best practices

• Always use parameterized queries or ORM parameter binding to prevent SQL injection
• Load only required columns and prefer eager loading or explicit joins to prevent N+1 queries
• Wrap multi-step updates in transactions and handle rollbacks on failure
• Add pagination, limits, and sensible defaults for list endpoints
• Measure query plans and add indexes based on real slow-query evidence, not guesses
• Cache expensive, read-heavy queries and invalidate on writes

Example use cases

• Refactor a REST endpoint from multiple per-record queries into a single eager-loaded ORM query to eliminate N+1
• Implement cursor-based pagination for large result sets with stable sort keys
• Convert string interpolation SQL into parameterized prepared statements to remove injection risk
• Add a transactional update across multiple tables with rollback on any constraint failure
• Instrument and optimize a slow JOIN by analyzing EXPLAIN output and adding an index