Data AccessGENERALDATABASE-SPECIFICLANGUAGE-SPECIFIC

Raw SQL in Application Code

When to write the statement yourself, how to keep it parameterized and findable, and what you take on when you do.

What actually happensHow to build it

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.

The question

When is hand-written SQL the right call, and how do I keep it from becoming an unmaintainable pile of strings?

The requirement

The finance page needs revenue by month by segment, with a running total and last year's comparison. The ORM can express roughly half of it.

The obvious build

Write the query as a string in the handler, interpolate the tenant id and the date range, and move on. It is one endpoint.

Why it breaks

The interpolated tenant id is an injection in the exact place that decides who sees whose money (SQL Injection).

How it breaks in production
  • The interpolated tenant id is an injection in the exact place that decides who sees whose money (SQL Injection).
  • A year later the column is renamed. The ORM models are updated by tooling; the string is not, and nothing fails until the endpoint is called.
  • The statement has no test, because testing it requires a real database and it lives inside a request handler.
  • Three more endpoints copy the pattern, each with its own slightly different tenant predicate, and one of them forgets it.
Requirement→API Contract→Application Logic→Data Access→External Deps→Concurrency→Failure→Security→Observability→Deployment→Scale

What is actually happening

  • Raw SQL means you hand a statement and a parameter array to the driver. The driver sends them, the database plans and executes, rows come back as arrays or dictionaries.
  • Parameter binding is a protocol feature, not string escaping: the statement text and the values travel separately, so a value can never change the statement's structure. This is why parameterization is a *structural* defence rather than a filter.
  • Nothing maps the result. Column names, null handling, numeric types and date types are yours — and drivers differ, notably in returning fixed-precision numerics as strings to avoid float rounding.
  • You gain access to everything the dialect can do: CTEs, window functions, INSERT ... ON CONFLICT, RETURNING, lateral joins, set operations, FOR UPDATE SKIP LOCKED.
  • You also gain the ability to influence the plan, because you control the statement the planner sees (Cost-Based Optimization).

Binding is structural, escaping is a filter

The reason to insist on parameters is not that they are tidier. It is that the statement text and the values travel to the database as separate things, so no value — however crafted — can become part of the statement's structure. There is no edge case to get wrong because there is no parsing of the value at all.

Escaping, by contrast, is a function that must be correct for every encoding, quoting mode and character set the database supports. It can be right. It is a filter, and filters have gaps.

The same query, three ways
1// Injectable: the value becomes part of the statement
2await db.query(`SELECT * FROM invoices WHERE tenant_id = '${tenantId}'`)
3 
4// Safe from injection, still wrong: any user can name any tenant
5await db.query('SELECT * FROM invoices WHERE tenant_id = $1', [req.body.tenantId])
6 
7// Safe and correct: bound value, tenant from the verified principal
8await db.query(
9  'SELECT id, total, status FROM invoices WHERE tenant_id = $1 AND id = $2',
10  [req.user.tenantId, req.params.id],
11)

The middle case is the one worth staring at: parameterization fixed injection and did nothing at all for authorization. They are different problems with different controls (Object-Level Authorization).

Give statements a name, a type and a test

Raw SQL becomes unmaintainable when it is anonymous. A statement inside a handler has no signature, no test and no discoverable relationship to the schema. The same statement as an exported function with typed parameters and a typed result is an ordinary unit of code that happens to be written in SQL.

This is also what makes the "mix ORM and raw SQL" recommendation workable rather than chaotic: the raw statements live in one place, are reviewed as a set, and are tested against a real database.

A hand-written statement, from decision to production
  1. 1
    Justify it

    Name the dialect feature or round-trip saving that the ORM cannot give.

    fails by Written by preference; now there are two data layers and no reason.

  2. 2
    Name and type it

    One exported function, typed parameters, typed row.

    fails by String inline in a handler — untestable, ungreppable.

  3. 3
    Parameterize

    Every value bound; identifiers from a fixed map.

    fails by One interpolation, usually the sort column or the tenant id.

  4. 4
    Bound the result

    LIMIT, or a cursor, or a streaming read.

    fails by Whole table into process memory once the table grows.

  5. 5
    Test on a real engine

    Same database and major version as production.

    fails by SQLite in tests, Postgres in production — dialect differences pass silently (Test Against the Real Database).

  6. 6
    Tag for observability

    Embed a route comment; export duration and row-count metrics.

    fails by Slow query log full of anonymous statements nobody can attribute.

  7. 7
    Own the plan

    Read EXPLAIN when it matters; recheck after data growth.

    fails by Plan flips to a scan at volume and nobody is watching (Reading EXPLAIN ANALYZE).

The statements worth writing by hand

DATABASE-SPECIFICThe list is written in Postgres dialect. SKIP LOCKED exists in MySQL 8.0+ and not in older versions; COPY is Postgres-specific with LOAD DATA INFILE as the MySQL analogue; WITH RECURSIVE is broadly available but MySQL only from 8.0.

A short list covers most legitimate uses. Each of these is either impossible or clumsy through an object-oriented mapping layer, and each replaces several round trips or an application-side race with one statement the database executes atomically.

  • Upsert — INSERT ... ON CONFLICT DO UPDATE ... RETURNING, which removes the check-then-insert race entirely.
  • Atomic counters — UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1, where the predicate makes the check and the write one operation (Atomic Operations).
  • Claim-a-row queues — SELECT ... FOR UPDATE SKIP LOCKED LIMIT 10, the standard pattern for many workers over one table (Job Queues).
  • Analytical reads — window functions, running totals, grouped aggregates with comparisons across periods.
  • Recursive traversal — WITH RECURSIVE for trees and graphs held relationally (Subqueries, CTEs, EXISTS, UNION, CASE).
  • Bulk writes — a single multi-row INSERT, or COPY, instead of thousands of statements.
  • Set-based maintenance — one UPDATE ... WHERE instead of loading and saving a million objects.

How to build it

Most important first.

  • Give every statement a name and a home: a module of query functions with typed signatures, not strings inside handlers.
  • Parameterize without exception. If a value cannot be a parameter — an identifier — it must come from a fixed map in code, never from input.
  • Type the result explicitly at the boundary and convert there: numerics, dates, nullable columns. Do not let driver-shaped rows travel into the domain.
  • Test against a real database of the same engine and version. A statement that uses dialect features cannot be meaningfully tested against a different one (Test Against the Real Database).
  • Attach an application comment to expensive statements so they are identifiable in the database's own logs: a /* route=reports.revenue */ prefix survives into pg_stat_statements and the slow query log.
  • Use RETURNING (Postgres, SQLite, MariaDB) to avoid a second round trip after a write, where the dialect supports it.

What can go wrong

Failure modes
  • One interpolation among a hundred parameterized statements. It only takes one, and code review is the only thing that catches it.
  • Silent breakage after a schema change, discovered by users. Nothing type-checks a string.
  • Dialect lock-in arriving quietly: three window functions and a SKIP LOCKED later, the "we could switch databases" conversation is over.
  • Numeric precision bugs from treating a NUMERIC column as a float because the driver did not.
  • A statement without LIMIT on a table that grew, returning millions of rows into process memory (Memory Leaks in Backend Services is the symptom people report).
  • An IN list built by expanding placeholders in a loop, which is fine until the list is long enough to exceed the driver's parameter limit.
What can race
  • A read-modify-write done as two statements races; done as one UPDATE ... SET n = n + 1 it does not, because the database applies it atomically under a row lock (Atomic Operations).
  • INSERT ... ON CONFLICT DO UPDATE (Postgres) and INSERT ... ON DUPLICATE KEY UPDATE (MySQL) exist precisely to close the check-then-insert race, which application-side "does it exist?" logic cannot (Backend Races).
  • SELECT ... FOR UPDATE SKIP LOCKED is the standard way for several workers to claim rows from one queue table without colliding (Job Queues).
Security
  • Parameterize values; allow-list identifiers. Those two sentences are the whole security content of this lesson, and both are routinely violated under deadline.
  • Multi-statement execution — sending a; b in one call — should be disabled or avoided; it converts an injection from "read a row" into "drop a table".
  • Connect as a least-privileged database user. If the application never issues DDL, the user it connects as should not be able to (Database Privileges and Blast Radius).
  • Never log a statement together with its bound parameters at info level. Parameters are the values, and values are frequently personal data (Secrets in Logs).
Misreads
  • "Raw SQL is dangerous." Parameterized raw SQL is exactly as injection-safe as an ORM. Concatenated SQL is dangerous — in any layer, including inside an ORM.
  • "Raw SQL is faster." The same statement performs the same regardless of who typed it. It is more expressive, which usually means fewer statements.
  • "Escaping input is enough." Escaping is a filter with edge cases; binding is structural. Use binding.
  • "We use an ORM, so we do not have raw SQL." Check for the raw-fragment helpers. Almost every codebase has them, and that is where the injection is.

Operating it

How you see it in production
  • Named statements plus an embedded route comment make the database's slow query log joinable with application traces (Correlation Ids That Survive Every Hop).
  • Statement-level metrics per named query — count, duration distribution, rows returned — because a hand-written query is a component with its own behaviour.
  • Plan drift: the same statement can get a different plan when data volume or statistics change. That is Database Engineering's territory to analyse and yours to notice (Reading EXPLAIN ANALYZE).
What changes at 10x and 100x
  • Hand-written SQL scales well by construction — you can see and control the round trips. What breaks is unbounded results, which grow with the table.
  • At larger volumes the analytical statements are the first candidates to move off the primary, to a replica or a warehouse (Read Replicas From the Application).
  • Team scale is the real limit: a shared file of two hundred statements needs conventions, naming and tests, or it becomes the thing the ORM was adopted to avoid.
What this costs
  • You get expressiveness and control; you pay with mapping code, refactoring risk and a schema coupling that no tool tracks.
  • Dialect features are the reason to write it by hand and the reason it is not portable. Take the features knowingly.
  • Real-database tests are slower than unit tests and are the only tests worth having here.

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.

  • GENERALParameter binding, naming and result mapping apply to every driver and language.
  • DATABASE-SPECIFICPlaceholder syntax and features diverge: Postgres uses $1, MySQL and SQLite use ?; RETURNING exists on Postgres, SQLite and MariaDB but not MySQL; ON CONFLICT is Postgres/SQLite while MySQL spells it ON DUPLICATE KEY UPDATE with different semantics around which constraint matched.
  • LANGUAGE-SPECIFICSome ecosystems can type-check SQL against a live schema at build time (sqlc for Go, sqlx for Rust, PgTyped for TypeScript), which removes the "nothing checks the string" objection; most cannot, and the objection stands there.

Where the depth lives

This domain teaches the application-side mechanism and hands the rest off.

Databasesql-basicssubqueries-and-cteswindow-functionsreading-explaincost-based-optimization
Securitysql-injectiondatabase-privilegesinput-validation
Performanceslow-query-workflow