Backend Queries

Write secure and optimized database queries using parameterized queries, preventing SQL injection, avoiding N+1 queries through eager loading, selecting only needed columns, and using transactions for related operations. Use this skill when writing database queries using ORM query methods, raw SQL, or query builders in repository files, data access layer files, service files, or any backend code that fetches or manipulates database data. Use this when writing ORM queries with methods like findAll, findOne, findMany, where, include, select, joins, or populate to retrieve data from databases, writing raw SQL queries safely using parameterized inputs or prepared statements (never string interpolation or concatenation) to prevent SQL injection attacks, optimizing queries to prevent N+1 problems by using eager loading with include/populate or SQL joins to fetch related data in a single query instead of multiple sequential queries that degrade performance, implementing database transactions to wrap related operations that must succeed or fail together for data consistency such as transferring money between accounts or creating related records, selecting only specific columns needed (SELECT id, name, email) instead of fetching all columns with SELECT * to reduce memory usage and network transfer, indexing database columns that are frequently used in WHERE clauses, JOIN conditions, or ORDER BY statements for query performance improvements, setting up query timeouts to prevent long-running or runaway queries from impacting system performance and blocking other database operations, caching results of complex or frequently-executed queries to improve response times and reduce database load using in-memory caches or query result caches, using query builders like Knex.js, Prisma client, SQLAlchemy query API, or ORM query APIs to construct safe parameterized queries with type safety, debugging slow queries using database query analysis tools, EXPLAIN statements, or logging to identify bottlenecks and optimization opportunities, ensuring all user-provided input is safely handled through parameterized queries or ORM methods to prevent SQL injection attacks and never concatenating user input directly into SQL strings, and wrapping multiple related database operations in transactions with proper error handling and rollback mechanisms to maintain data consistency even when errors occur.