Choosing a Data Access Layer
ORM, query builder, raw SQL and stored procedures as four points on a spectrum, chosen per query rather than per project.
The requirement, the obvious build, and why it breaks
Every lesson starts where the work starts: someone asked for something, and the first implementation that comes to mind has a problem.
ORM, query builder, raw SQL or stored procedure — which one, and on what evidence?
A new service needs to read and write a relational database. Someone has to pick how application code will express queries, and the choice will outlive everyone in the room.
Pick the ORM the framework ships with and use it for everything. It is the documented path, the team already knows it, and it removes a whole category of boilerplate.
The reporting endpoint needs a window function over a grouped join. The ORM expresses it badly or not at all, and the workaround is worse than the SQL would have been.
- The reporting endpoint needs a window function over a grouped join. The ORM expresses it badly or not at all, and the workaround is worse than the SQL would have been.
- A list endpoint that looks like one line of code issues one query per row, and nothing in the source hints at it (The N+1 Query Problem).
- The team can now describe the ORM's API precisely and cannot read an execution plan, so every slow query becomes a guess.
- The opposite failure is just as real: a codebase of hand-written SQL strings where nobody can safely change a column name, and where one
+in the wrong place is an injection (SQL Injection).
What is actually happening
- All four options end in the same place — a SQL string and a parameter list sent over a socket to a database. They differ only in who writes the string and how much of it you can see.
- ORM: you manipulate objects; a mapping layer decides the SQL, tracks which objects changed, and issues statements at flush or commit (What an ORM Actually Does).
- Query builder: you write SQL structurally, in the host language. The library composes and parameterizes; it does not decide what to query (Query Builders).
- Raw SQL: you write the statement, the driver binds parameters and returns rows. Nothing is mapped for you (Raw SQL in Application Code).
- Stored procedure: the statement lives in the database and is invoked by name. The logic moves across the trust and deployment boundary with it.
- The axes that actually differ are: how much SQL is visible in review, how much of the schema is coupled to application types, how expressible complex queries are, and where the logic is deployed from.
Four options, one socket
It helps to collapse the argument first. Every option produces a SQL string and a parameter array, and the database cannot tell which one produced it. Nothing about performance follows from the choice directly — what follows is how many statements you tend to issue, and how easy it is to see them.
What genuinely differs is authorship and visibility. Read the options for what they do to a code review: can a reviewer tell what will hit the database, and can they tell whether it is one statement or a hundred?
Who should write the SQL for this particular access pattern?
when CRUD-shaped work on entities with relationships: load an aggregate, change a few fields, save it. The majority of endpoints in most products.
cost The SQL is generated, so query count and shape are invisible until you look. Complex reads either escape the abstraction or are expressed badly (What an ORM Buys and What It Costs).
when Queries whose shape depends on runtime conditions — optional filters, dynamic sorts, conditional joins — where string assembly would be both ugly and dangerous.
cost You write SQL semantics in a second syntax. Nothing maps rows to objects for you, and the builder's API becomes another dialect to learn (Query Builders).
when Analytical reads, window functions, CTEs, bulk INSERT ... ON CONFLICT, anything where you want to control the plan and read the statement literally.
cost Schema changes are found by grep. Result mapping and null handling are yours. Discipline about parameterization is not optional (Raw SQL in Application Code).
when Logic that must be enforced regardless of which application connects, or a batch operation whose data volume makes round trips the dominant cost.
cost Business logic now deploys on a different pipeline from the code that calls it, in a language most of the team does not review, with weaker testing and version control.
The axes that actually differ
A comparison table is only useful if the columns are the things that later cause pain. These four are: whether a reviewer can see the statement, whether one call means one round trip, whether the hard query is expressible, and where the code lives when it is time to deploy.
| SQL visible in review? | Round trips predictable? | Complex reads | Deploys with the app? | |
|---|---|---|---|---|
| ORM | No — generated | No — lazy loads are implicit | Awkward; escape hatch common | Yes |
| Query builder | Mostly — structurally | Yes — one call, one statement | Good, up to the builder's coverage | Yes |
| Raw SQL | Yes — literally | Yes | Full power of the dialect | Yes |
| Stored procedure | Only in the database | Yes — one call, many statements inside | Full power, plus procedural control | No — separate change process |
Choosing per query, not per religion
The most common real-world architecture is boring and correct: an ORM or builder for the ninety per cent of access that is "load this, change that, save it", and a small, well-reviewed set of hand-written statements for the reports, the bulk operations and the two queries that decide the product's performance.
That means the interesting design work is not picking a library. It is deciding where the seam sits, so that the raw statements are findable, testable against a real database, and not scattered through request handlers.
app.get('/reports/revenue', async (req, res) => {
const rows = await orm.query(
`SELECT date_trunc('month', created_at) AS m, SUM(total)
FROM orders WHERE tenant_id = ${req.user.tenantId}
GROUP BY 1 ORDER BY 1`,
)
res.json(rows)
})// reports/revenue.sql.ts
export const monthlyRevenue = (db: Db, tenantId: string) =>
db.query<{ m: Date; total: string }>(
`SELECT date_trunc('month', created_at) AS m, SUM(total) AS total
FROM orders WHERE tenant_id = $1
GROUP BY 1 ORDER BY 1`,
[tenantId],
)
// handler
res.json(await monthlyRevenue(db, req.user.tenantId))The first interpolates a tenant id into the statement — injectable, and injectable in the one place that decides tenant isolation. The second binds it as a parameter and gives the query a name, a signature and a place to be tested against a real database. The reason is not tidiness: it is that raw SQL is worth having only if it is reviewable and findable.
How to build it
Most important first.
- Decide per query shape, not per project. Most systems are best served by an ORM or builder for CRUD-shaped access plus raw SQL for the handful of analytical or bulk statements that justify it.
- Make the generated SQL observable from day one — statement logging in development, query counts per request in production. A layer whose output you cannot see cannot be reviewed (What a Backend Should Actually Log).
- Whatever you choose, put it behind a boundary that has domain-shaped methods rather than letting query fragments leak into handlers (The Repository Layer).
- Parameterize everywhere, with no exceptions and no "it is only an internal value". Identifiers that cannot be parameterized get an allow-list, not an escape function.
- Write the pathological query first. If the hardest read in the product is awkward in the layer you picked, you have learned that now rather than in month nine.
What can go wrong
- The ORM becomes load-bearing for something it was never good at — reporting, bulk updates, recursive queries — and the escape hatch is used so often it is the real access layer.
- Raw SQL scattered across handlers, so a schema change requires grepping strings and hoping.
- Stored procedures deployed by a process unrelated to the application's deploy pipeline, so code and schema drift and nobody notices until a call signature changes.
- A repository layer that "abstracts the database" but exposes the ORM's query object in its signatures, which is the coupling it claimed to remove (When the Repository Is Just Indirection).
- Read-modify-write through any of these layers has the same race: two requests read the same row, both compute a new value, the second write wins. The access layer does not fix it; a version column, an atomic update or a lock does (Optimistic Concurrency).
- Parameterization is the property that matters, and every option can have it or lose it. An ORM with a
whereRawfed by user input is exactly as injectable as a concatenated string (SQL Injection). - Table and column names cannot be bound as parameters. Sort fields and filter columns arriving from a query string must be validated against a fixed allow-list before they reach the query.
- Mass assignment is the ORM-specific hazard: binding a request body straight onto an entity lets a caller set columns you never intended to expose, such as a role or a tenant id.
- The database user your application connects as should not own the schema it queries. Grant what the service needs and nothing more.
- "ORMs are slow." The ORM's overhead is real but usually small next to the number of round trips it encouraged. The problem is query *count* and query *shape*, not mapping cost.
- "Raw SQL is faster." The same SQL is the same SQL. Raw SQL is more *expressive*; it is not inherently quicker, and it usually issues fewer queries only because you were forced to think about them.
- "Using an ORM means I do not need to know SQL." It means you need to know SQL *and* what the ORM generates. It adds a layer of knowledge rather than removing one.
- "We picked an ORM, so the decision is made." It is made for CRUD. Every product eventually grows a query the ORM should not be asked to express.
Operating it
- Queries per request, as a metric with the route as a label. A route whose query count scales with result size is an N+1 you have not found yet.
- Statement-level logging with the application's own identifier attached — a comment carrying route and correlation id survives into the database's slow-query log and makes it joinable with your traces (Correlation Ids That Survive Every Hop).
- The slow query log, read regularly rather than during incidents. Database Engineering owns the analysis; you owe it the query text (The Slow Query Workflow).
- At 10x traffic the choice of layer matters far less than the number of round trips per request and the size of the pool (Connection Pools).
- At 100x, read/write splitting, batching and pagination shape the code more than the library does — and an access layer that hides the SQL makes each of those harder to introduce.
- Team scale changes the answer. Ten engineers sharing one hand-rolled SQL layer need conventions the library would have given them; two engineers with a hard query shape do not need the library at all.
- Mixing layers is the recommendation, and it costs consistency: two ways to do the same thing, two sets of conventions, and reviewers who must know both.
- Making the SQL visible costs log volume and some noise in development. It is still cheaper than the first N+1 you find in production.
- A repository boundary costs indirection. In a small service it is genuine ceremony; the argument for it is change isolation, and that only pays when change happens.
Where this applies
Backend advice is context-sensitive. These labels say what each claim is specific to, and where a different stack or scale would differ.
- GENERALThe four options and the axes between them hold for any relational database and any language with a mature driver ecosystem.
- LANGUAGE-SPECIFICThe realistic menu differs: Python and Ruby have unusually strong ORMs (SQLAlchemy, Django ORM, ActiveRecord) that many teams use exclusively; Go's ecosystem leans toward thin mappers and code generation over SQL, so "just use the ORM" is not the default advice there.
- DATABASE-SPECIFICStored procedures are a mainstream option on SQL Server and Oracle, where tooling and deployment support them well; on Postgres and MySQL they are usable but far less common in application teams, so the operational cost of choosing them is higher.
Where the depth lives
This domain teaches the application-side mechanism and hands the rest off.
- — Programming Languages & Runtime Internals — how a mapping layer turns rows into typed objects, and what that costs in allocation.