Raw SQL & Query Builders · ORM Deep Dive

Quick Facts

At a Glance

Core Ideas

Query builder: a programmatic API that emits SQL — chain .select().from().where() instead of templating strings.
Parameterization: placeholders, not interpolation. The single most important defense against SQL injection.
No identity map, no change tracking, no lazy loading. What you query is what comes back. No surprise queries.
Typed where possible: jOOQ generates types from the schema; sqlx checks queries against the database at compile time.
Output is rows, not objects. You decide how to materialize them — DTOs, plain dicts, tuples.

The Tools

Who Plays Here

Knex (JS / TS)

The original Node query builder. Powers Bookshelf and parts of Objection.js. Maintenance has slowed; new TS code often picks Kysely or Drizzle instead.

Kysely (TS)

Type-safe SQL builder for TypeScript. Schema-aware, no code generation, runs anywhere TS does. Strong fit on edge runtimes.

jOOQ (Java)

Generates Java code from your schema; queries are compile-checked against real columns. Premium licensing for proprietary databases; free for open-source ones.

sqlx (Rust)

Macro-based — sqlx::query!() validates SQL against the live database at compile time. Pure SQL strings, type-safe results.

Dapper (.NET)

Micro-ORM / mapper. You write SQL; Dapper materializes results into objects. The default companion to EF Core.

SQLAlchemy Core (Python)

The expression language without the ORM. Composable SQL, full type support, no session machinery.

Squirrel (Go)

Fluent SQL builder. Go culture leans toward raw SQL with database/sql or sqlx; builders are an opt-in convenience.

Plain SQL files

Goose, dbmate, sqlc (Go), HugSQL (Clojure). Write SQL in .sql files; tooling wires it into typed code. The trend is rising.

When to Reach For It

Good Fits

Reporting and Analytics Queries

Window functions, recursive CTEs, lateral joins, GROUPING SETS. ORMs translate these poorly or not at all. Express them in SQL and stop fighting the abstraction. The query lives in source control like any other code; tested with database fixtures.

Bulk Operations

ORMs generate one INSERT per object; query builders let you write a single multi-row insert or a Postgres COPY. Going from 10k inserts/sec to 100k+ usually means leaving the ORM at the door.

The CQRS Read Side

Pattern: ORM-managed entities for the write model; SQL-shaped queries for read endpoints. The reads are denormalized projections — they don't fit the entity graph anyway, and the ORM's tracking is pure overhead.

Database-Specific Features

Postgres' jsonb operators, full-text search, ON CONFLICT, partial indexes. ORMs cover the lowest common denominator across dialects. If you've committed to one database, use it.

When the ORM Is Demonstrably Wrong

You've optimized the eager loads, added the indexes, profiled the queries — and the ORM still emits something pathological. The repro is a 12-line raw SQL query that runs 50x faster. Use it. Save the ceremony for queries the ORM is good at.

The Tradeoffs

What You Give Up

No automatic mapping. Result rows are tuples or dicts. You write the DTO, or your tool generates it.
No change tracking. "Update what changed" is your job — explicit UPDATE statements, no dirty-checking magic.
Database portability is up to you. If you write Postgres-flavored SQL and need to run on MySQL, that's a rewrite. ORMs hide this; query builders don't.
Refactoring is harder. Renaming a column means grepping SQL strings. Tools like jOOQ and sqlx flag this at compile time; raw strings don't.
You own the security boundary. Always parameterize. String concatenation of user input into SQL is the textbook injection bug — every query builder has a parameter API; use it without exception.

Patterns

How Teams Actually Use Them

ORM + query builder side by side. Hibernate + jOOQ. EF Core + Dapper. Prisma + Kysely. Each does what it's good at.
SQL files reviewed in PRs. Same diffability and reviewability as application code. EXPLAIN output in the PR description for non-trivial queries.
Generated repositories. sqlc and jOOQ generate typed function signatures from .sql files — best of both worlds: SQL you can read, types the compiler enforces.
Test against a real database. No mocks for query logic. Testcontainers or an ephemeral schema per CI run — production parity matters more here than anywhere else.
Index reviews. Without an ORM hiding the SQL, the index strategy becomes visible — and reviewable.

Continue