ORM vs Query Builder vs Raw SQL
Choosing the appropriate database abstraction level based on query complexity, type safety, and performance requirements.
Description
ORMs (Prisma, TypeORM, SQLAlchemy, Hibernate) map database tables to application objects and generate SQL automatically. They accelerate development for CRUD operations, enforce type safety, and handle schema migrations. However, they can generate inefficient SQL for complex queries, obscure performance characteristics, and introduce N+1 problems through lazy loading. ORMs work best for straightforward domain models with well-defined relationships.
Query builders (Knex.js, Kysely, Drizzle ORM, JOOQ) provide a programmatic API for constructing SQL without full object mapping. They offer more control over generated SQL while still providing composability, parameter binding, and some degree of type safety. Kysely and Drizzle provide end-to-end type safety from schema to query result. Query builders are ideal when you need dynamic query construction (variable filters, optional joins) without the overhead of a full ORM.
Raw SQL (using pg, mysql2, or database drivers directly) gives complete control and transparency. It is the right choice for complex analytical queries, database-specific features (window functions, CTEs, recursive queries), and performance-critical paths where you need to hand-optimize the query plan. The trade-off is losing type safety, composability, and migration tooling. Many mature codebases use a hybrid approach: an ORM for standard CRUD, a query builder for dynamic queries, and raw SQL for complex reports and bulk operations.
Prompt Snippet
Adopt a layered data access strategy: use Drizzle ORM for standard CRUD operations with full TypeScript type inference from the schema, Kysely for dynamic query composition where filters and joins vary at runtime, and parameterized raw SQL via the pg driver for complex analytical queries involving CTEs, window functions, or database-specific syntax. Wrap all raw SQL in a repository pattern with explicit return type annotations. Never concatenate user input into raw SQL -- always use parameterized queries ($1, $2) or tagged template literals.
Tags
Related Terms
N+1 Query Problem
A performance anti-pattern where fetching a list of N records triggers N additional queries to load related data, instead of using a single JOIN or batch query.
Database Migrations
Version-controlled scripts that incrementally modify the database schema, enabling reproducible and reversible schema changes across environments.
SQL vs NoSQL Selection
Choosing between relational and non-relational databases based on data shape, query patterns, and consistency requirements.