Lesson 32 / 35
Query Builders, Raw SQL and Hybrid Approaches
Place ORMs on a spectrum with query builders and raw SQL, and decide when to mix them in one application.
By Rahul Agarwal · 15 years in web development
A spectrum of abstraction
Data access tools sit on a spectrum. At one end is raw SQL through a driver such as pg or mysql2: full control, no abstraction, and you map rows to objects yourself. In the middle are query builders such as Knex and Kysely: you compose SQL with functions, which makes dynamic filters easier and, in Kysely's case, type-checked against a schema description, but there are no models or relation loading. Newer SQL-first libraries like Drizzle sit close to SQL while adding schema definitions and types. At the other end are full ORMs like Prisma, TypeORM and Sequelize, which add models, relations, migrations and object mapping. Higher abstraction means faster CRUD and fewer mistakes in routine code, but less control over the exact SQL. Many production apps are hybrid: the ORM handles everyday reads and writes, while reporting queries, bulk imports and database-specific features use raw SQL or a builder, all sharing the same connection pool where possible.
Hybrid in one service
Everyday CRUD through the ORM; a window-function report through parameterised raw SQL on the same client.
// routine write: ORM
await prisma.post.create({ data: { title, authorId } });
// reporting: SQL is clearer here
const topPerAuthor = await prisma.$queryRaw<{ authorId: number; title: string; views: number }[]>`
SELECT "authorId", title, views FROM (
SELECT "authorId", title, views,
ROW_NUMBER() OVER (PARTITION BY "authorId" ORDER BY views DESC) AS rn
FROM "Post"
) ranked
WHERE rn = 1`;The type annotation on raw SQL is a promise you make
The generic <{ authorId: number; ... }[]> on $queryRaw tells TypeScript what to expect, but nothing checks it at runtime. If the SQL changes, the type silently lies. Keep raw queries in one module with tests that run them against a real database.
Quick check: Which statement best describes a query builder such as Knex or Kysely compared with a full ORM?
- It composes SQL with functions but does not manage models, relation loading or object identity like an ORM does
- It is a database server
- It replaces the need for a database driver
- It always generates faster SQL than an ORM
Answer
It composes SQL with functions but does not manage models, relation loading or object identity like an ORM does — Query builders sit between raw SQL and ORMs: they help write SQL safely and dynamically, but stop short of model and relation management.