ORMs, Query Builders, and Raw SQL
What an ORM actually buys and costs you, where a query builder fits between it and raw SQL, and when to just write the SQL yourself.
ORMs, Query Builders, and Raw SQL
The player loads from youtube-nocookie.com when you press play. Nothing reaches YouTube before that.
Not started
What you'll learn
- An ORM's real job is mapping rows to objects and wiring that mapping into your migration workflow, which removes a huge amount of repetitive boilerplate for straightforward CRUD code.
- That same mapping layer is a leaky abstraction: an ORM can generate a query you didn't expect, and debugging it means reading the SQL it produced, not just the code you wrote.
- A query builder gives up the automatic object mapping in exchange for a thinner, more predictable layer over SQL, which is often the better fit once a query gets genuinely complex.
- Raw SQL is still the right tool for a specific slow query, a bulk operation, or a report — you don't need to pick one approach for an entire codebase.
- Active-record ORMs attach query methods to your model objects themselves, while data-mapper ORMs keep the model and the persistence logic as separate layers — a real design difference, not just a naming one.
The actual problem an ORM solves
Writing a web app against a relational database means constantly translating between two shapes: rows and columns on one side, objects (or structs, or whatever your language calls them) on the other. Without help, that translation is pure boilerplate — hand-written SELECT statements, manual loops turning result sets into objects, manual INSERT/UPDATE statements built from object fields. It’s not hard, it’s just repetitive, and repetitive code is where bugs hide: a column you forgot to update, a type mismatch between what the database returns and what your code expects.
An ORM (Object-Relational Mapper) automates that translation. You define a model — a User class with fields — and the ORM generates the SQL to create, read, update, and delete rows that match it. In most mature ORMs this also plugs into migrations: change a field on the model, generate a migration, and the ORM keeps your schema and your code in sync instead of you tracking that by hand. For straightforward CRUD — the bulk of what a typical backend service does — this genuinely removes a lot of code you’d otherwise write and maintain yourself.
What it costs you
The trade is that the ORM sits between you and the actual query, and that layer is leaky — it doesn’t fully hide what’s happening underneath, it just makes it easy to forget to look. The clearest example is the N+1 query problem: code that looks like one simple loop over a list of objects can silently issue one database query per item, because the ORM happily lets you access a related object without you realizing that access triggers a fresh query. The code reads cleanly. The database logs tell a different story.
This is the core discipline an ORM demands of you: you have to actually look at the SQL it generates, at least often enough to catch the cases where the convenient object-oriented code and the efficient query diverge. Most ORMs expose a query-logging mode for exactly this reason. Treat it as a dev-time habit, not an emergency tool you reach for only once something’s already slow in production.
The embedded video runs through this tradeoff directly — what an ORM genuinely saves you, and the specific situations (complex joins, bulk operations, performance-critical paths) where reaching for it stops paying off.
Query builders: SQL with a programmatic API
A query builder (Knex, SQL query builder libraries, or the “query builder mode” many ORMs also expose) gives up the automatic object mapping but keeps a programmatic, composable interface to SQL. Instead of writing a string and worrying about escaping user input correctly, you write something like db('users').where('active', true).orderBy('created_at'), and the library assembles safe, parameterized SQL from it.
This is a genuinely useful middle point. It’s especially good for queries built up conditionally — a search endpoint where the WHERE clause depends on which filters the user actually provided is awkward to express cleanly in most ORM query APIs, but natural in a query builder. You lose the convenience of getting back a fully-formed domain object with relationships already resolved, but you gain predictability: what you write maps closely to the SQL that actually runs, with none of an ORM’s hidden conventions deciding things on your behalf.
When to just write SQL
Sometimes the right move is neither of the above: write the SQL directly and run it. This makes sense for a handful of recurring situations — a report query joining several tables in a way no amount of query-builder composition makes more readable, a bulk update or data-migration script that only runs once, or a query using a database-specific feature (a particular index type, a window function, a vendor-specific JSON operator) that your ORM or query builder doesn’t model at all. None of this requires abandoning the ORM for the rest of your codebase. A mature backend codebase routinely mixes all three: ORM for everyday CRUD, a query builder for a few complex dynamic queries, and a handful of raw SQL statements for the genuinely special cases.
Active-record vs. data-mapper, briefly
Within “ORM” there’s a real architectural split worth knowing by name. An active-record ORM (Rails’ ActiveRecord, Laravel’s Eloquent) puts persistence methods directly on the model: user.save(), User.find(id). The model knows how to store and load itself. It’s quick to start with and reads naturally for simple cases. A data-mapper ORM (Doctrine, many Java/Hibernate setups, Entity Framework in parts of its API) keeps the model as a plain object with no knowledge of the database, and uses a separate layer — a repository or a session — to load and save it. That separation costs you some directness up front but tends to hold up better once your domain logic gets complicated enough that you don’t want persistence concerns tangled into your core model classes. Neither is “correct” in the abstract; it’s a decision about how much you want your domain objects to know about how they’re stored.
Check yourself
What does an ORM actually do for you beyond saving you from writing SELECT statements by hand?
It maps database rows to and from your application's objects, and in most ecosystems it also ties into your migration tooling, so your schema and your model definitions stay in the same workflow instead of being managed by hand in two different places.
What's the concrete cost of an ORM being a 'leaky abstraction,' in terms of what you actually have to debug?
When a page is slow, the first thing you have to do is turn on query logging and read the actual SQL the ORM generated, because the object-oriented code you wrote doesn't tell you whether it quietly issued one query or fifty — the abstraction hides the query, not the cost of the query.
Where does a query builder sit between an ORM and raw SQL, and why would you reach for one?
A query builder gives you a programmatic, composable way to construct SQL — with autocomplete, string-safety, and conditional query-building — without automatically mapping results back into rich domain objects, which makes it a good fit for complex, dynamic queries where an ORM's conventions start getting in the way.
What's the practical difference between an active-record ORM and a data-mapper ORM?
Active-record puts save/find/update methods directly on your model class, so the model knows how to persist itself, while data-mapper keeps the model as a plain object and hands persistence off to a separate layer — active-record is usually faster to start with, data-mapper usually scales better to complex domain logic.
When does it make sense to drop down to raw SQL even in a codebase that otherwise uses an ORM?
For a specific query that's slow, needs a database-specific feature the ORM doesn't expose, or is a one-off report or bulk operation — writing raw SQL for that one case is normal and doesn't mean abandoning the ORM for everything else.
Go deeper
Notes
Notes are saved to your account.
Everything else on this page works without one — progress and bookmarks keep themselves in this browser and come with you when you sign in. Notes are the one thing worth not trusting to a browser you might clear.
Your notes could not be loaded.