SQL at the application boundary

From an application request to SQL

Mina’s order-history request asks for orders belonging to customer 1, newest first. The application derives that customer ID from her authenticated identity. The driver receives a query and its values separately:

const { rows } = await pool.query(
  `SELECT order_id, placed_at, status
   FROM orders
   WHERE customer_id = $1
   ORDER BY placed_at DESC, order_id DESC
   LIMIT $2`,
  [customerId, 20]
);

pool.query borrows a connection, sends the query and returns it after the query finishes. $1 and $2 mark value positions, supplied here as the customer ID and a limit of 20. The result’s rows array contains two order rows: 103, pending, followed by 101, paid. It contains no order lines yet.

A query builder expresses the same filter, order and limit through its own API. An ORM may return order objects instead of plain rows. Neither changes what the database is being asked. Read the generated SQL when checking behaviour: which customer is selected, how many rows are allowed, and whether accessing an object sends another query.

Parameters and SQL structure

A parameter supplies a value to an expression. It cannot become a new clause. A hostile string such as 1 OR TRUE bound as a customer ID will not widen the filter: PostgreSQL expects an integer there and rejects that string. Concatenated into WHERE customer_id = , the same text becomes part of the SQL and selects every customer’s orders. This is SQL injection.

Keep input values out of the text even when they seem easy to escape. Bind names, dates, amounts and lists through the driver’s documented API. node-postgres converts a JavaScript array to a PostgreSQL array parameter; the batched loader below uses that to supply several order IDs.

SQL structure needs a different decision. ORDER BY $1 does not make a bound string into a column name, and a parameter cannot stand for ASC or DESC. If Mina can choose oldest or newest first, select from two pieces of SQL owned by the application:

const sorts = new Map([
  ['newest', 'placed_at DESC, order_id DESC'],
  ['oldest', 'placed_at ASC, order_id ASC']
]);
const orderBy = sorts.get(requestedSort);
if (!orderBy) throw new Error('Unsupported sort');

const { rows } = await pool.query(
  `SELECT order_id, placed_at, status
   FROM orders WHERE customer_id = $1
   ORDER BY ${orderBy} LIMIT $2`,
  [customerId, 20]
);

The interpolation is safe because orderBy can contain only one of those two fixed strings. The request chooses a key in the map; it never supplies the SQL fragment. For genuinely variable identifiers, use the library’s identifier-quoting facility and still decide which objects the caller is allowed to name.

Binding values does not require naming a prepared statement. node-postgres accepts the text and values above without a name. Supplying a name in its query configuration additionally asks it to reuse a server-side prepared statement on each connection that uses it. That concerns repeated preparation and planning; it is separate from keeping values out of executable text.

Mapping the result

The result is an interface to the rest of the application. Its contract includes the selected column names, their types, the meaning of one row and how many rows may appear. Selecting explicit columns keeps a schema addition from silently becoming part of that interface. When a join returns two columns with the same name, give them distinct aliases before mapping them to an object.

The database and the language do not necessarily represent a value the same way. An exact decimal in PostgreSQL should not silently become an approximate calculation in application code. An instant should not turn into an unexplained local clock reading.

For a lookup that should find at most one row, check rows.length before using rows[0]. An empty array means no row was returned. A row containing null exists and carries a missing value in that column. In the table’s left-join result, the payment key cannot itself be null in a stored payment row, so it distinguishes an unmatched payment from a matched one.

Choose where conversion happens and test it with the actual driver configuration. A type annotation on a query result does not validate the database response or install a parser. Custom parsers, JSON construction inside SQL and ORM mappings can all change these defaults. Values, missing data and equality develops the stored meanings; the boundary must carry those meanings into the program.

Reading a write’s result

A write has two useful results: how many rows it affected, and any rows it explicitly returned. In node-postgres an UPDATE without RETURNING can have rowCount === 1 and an empty rows array. Adding RETURNING stock makes the changed stock available too.

UPDATE products SET stock = stock - $2
WHERE product_id = $1 AND stock >= $2
RETURNING stock;

With values ['P7', 1] before order 103, this changes one row and returns stock 0. Run the same change again after the purchase and it changes zero rows, returning none. The statement completed successfully; its condition did not match. It does not tell the caller whether the product was absent or lacked enough stock. The purchase code must recognise the unsuccessful allocation and abandon the whole purchase, including any earlier stock change.

Consuming a large result

The history query asks for at most 20 orders and returns them as one array. An export may need far more rows. Increasing the limit and collecting everything in rows can make the application hold the whole result before it can finish processing it. An incremental reader delivers parts of one result as the application is ready for them.

A cursor keeps a position in that result. With pg-cursor, the application asks for a batch, processes it, then asks for the next. The batch size bounds the rows delivered by each read; it is different from a SQL LIMIT, which bounds the result itself. Reading Mina’s four order lines in two-row batches still occupies one connection between batches, even while the application processes the previous rows.

This helper uses pg-cursor 2.22.1 and that same small batch size so the example is easy to follow. Its promise-based read returns an array of rows directly. The caller supplies an asynchronous consume function, such as a bounded writer to an export file. It can return false to stop early.

import Cursor from 'pg-cursor';

async function consumeOrderLines(pool, customerId, consume) {
  const client = await pool.connect();
  let discard = true;
  try {
    const cursor = client.query(new Cursor(
      `SELECT i.order_id, i.line_no, i.product_id,
              i.quantity, i.unit_price
       FROM orders AS o
       JOIN order_items AS i ON i.order_id = o.order_id
       WHERE o.customer_id = $1
       ORDER BY i.order_id, i.line_no`,
      [customerId]
    ));
    for (;;) {
      const rows = await cursor.read(2);
      if (rows.length === 0) break;
      if (await consume(rows) === false) break;
    }
    await cursor.close();
    discard = false;
  } finally {
    client.release(discard);
  }
}

Each await consume(rows) finishes before the next read. This pacing is called backpressure: a slow consumer slows the producer instead of building an ever-growing queue in application memory. The consumer must honour that contract. Launching a write without awaiting it, or appending every batch to a growing array, would accumulate work again. In a real export, choose the batch size for row width as well as count; two large documents can cost more memory than a hundred narrow rows.

On completion, a read returns an empty batch. On an early stop, unread rows remain. Both successful paths await cursor.close() before returning the connection. If a read, the consumer or closing throws, this deliberately conservative helper calls release(true) to discard the client, and the original error escapes. It never lends an unfinished result to the next borrower. Releasing an open cursor normally would mark the client available while its result still owns the connection; another query can then wait behind work nobody will finish.

Incremental consumption bounds neither all server work nor the lifetime of the connection. A sort may need substantial work before producing its first row, and a slow export keeps its connection and database resources occupied. If a surrounding transaction owns the reader, closing the result does not finish that transaction: its owner still commits or rolls back before release.

This helper owns its connection and runs without an explicit surrounding transaction. It also needs the deadline policy described below if a read or consumer stops responding; returning false handles an orderly early stop, not cancellation of in-flight work.

Reading a result in batches does not make it resumable after a broken connection. For an export that must resume or describe one consistent view, Pagination and consistent exports develops the continuation, ordering and snapshot choices.

The N+1 query problem

The history query loaded two orders. Suppose the application now loops over them and queries each order’s lines. It sends one query for the list, then one for 103 and another for 101: three queries. With N orders, that shape is N+1 queries. An ORM can hide the loop when reading order.lines lazily loads the relationship.

Two alternatives remove that query-per-order loop: fetch all the selected orders’ lines in one batch, or join the orders to their lines. They save different work, so compare the returned rows as well as the number of queries.

The batched version keeps the two order IDs from the first result and asks for all their lines together. In node-postgres its values argument is [[103, 101]]: one parameter whose value is an array.

SELECT order_id, line_no, product_id, quantity, unit_price
FROM order_items
WHERE order_id = ANY($1::integer[])
ORDER BY order_id, line_no;

Group those rows by order_id and attach each group to its order. Keep the parent list’s existing order: this line query orders by order ID, which does not replace the requested newest-first order of the parents. An order with no lines gets an empty collection rather than disappearing.

The batch needs two queries and transfers six rows, two parents and four lines. A joined load needs one query and transfers four joined rows, repeating each order’s columns beside each of its two lines. On a larger collection that repetition can be substantial. Neither count tells you the bytes transferred or all the work inside the server, but three sequential round trips put three network waits on the path. Query count is a useful first observation, not the whole cost.

Choosing what to load

If the page only shows the order ID and status, do not load its lines. If it needs the complete lines, select a bounded set of orders first. A limit placed after joining counts joined rows:

SELECT o.order_id, o.placed_at, o.status,
       i.line_no, i.product_id, i.quantity, i.unit_price
FROM orders AS o
LEFT JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.customer_id = $1
ORDER BY o.placed_at DESC, o.order_id DESC, i.line_no
LIMIT 2;

For Mina, LIMIT 2 returns the two lines of order 103 and nothing from 101. It has selected two rows, not two orders. A different limit can even cut an order’s collection in half. Put the parent selection inside the query instead:

WITH selected_orders AS (
  SELECT order_id, placed_at, status
  FROM orders
  WHERE customer_id = $1
  ORDER BY placed_at DESC, order_id DESC
  LIMIT $2
)
SELECT o.order_id, o.placed_at, o.status,
       i.line_no, i.product_id, i.quantity, i.unit_price
FROM selected_orders AS o
LEFT JOIN order_items AS i ON i.order_id = o.order_id
ORDER BY o.placed_at DESC, o.order_id DESC, i.line_no;

With values [1, 2], the inner query selects orders 103 and 101. The outer left join attaches their four lines and would retain an order with no lines. A batched load makes the same separation through two statements. A bound on parents does not bound the total number of lines if one order can contain many; decide what the response can accommodate.

ORMs offer these choices under names such as lazy loading, joined eager loading and select-in loading. SQLAlchemy, for example, documents select-in loading as a separate query using parent keys, while a joined load repeats parent columns across the joined rows. Inspect SQL and query counts with the intended relationships loaded. One method call is not a promise of one database query.

Several queries and one result

The two batched queries are also two observations. Another transaction may commit between them. Holding one connection does not by itself freeze the data. Even inside a PostgreSQL transaction at the default Read Committed isolation level, successive SELECT statements can see different committed states.

For an order-history screen, seeing a newly added line alongside the order selected a moment earlier may be acceptable. For an export that must describe one point in time, it may not be. State that requirement before choosing the loader. One statement gives this ordinary PostgreSQL read one statement snapshot; a multi-statement result that needs a shared snapshot requires an isolation choice. Transactions and isolation develops that design.

Connections and transaction ownership

Go back to before order 103. It and its lines do not exist yet; P3 has stock 12 and P7 has stock 1. The purchase must allocate two teas and one mug, insert order 103 and insert its two lines. One transaction owns those changes.

A PostgreSQL transaction belongs to a database session, reached through one connection. Several calls to pool.query can borrow different connections. Sending BEGIN through that convenience method does not make later pool calls join its transaction.

Check out a client once and pass it into every helper that participates in the transaction. Here tx is that client. The helper checks each guarded change and writes the known order facts. PurchaseRejected is an application error indicating that an allocation did not match; it must escape to the transaction owner.

async function writeOrder103(tx) {
  for (const [productId, quantity] of [['P3', 2], ['P7', 1]]) {
    const changed = await tx.query(
      `UPDATE products SET stock = stock - $2
       WHERE product_id = $1 AND stock >= $2
       RETURNING stock`,
      [productId, quantity]
    );
    if (changed.rowCount !== 1) {
      throw new PurchaseRejected(productId);
    }
  }
  await tx.query(
    `INSERT INTO orders (order_id, customer_id, placed_at, status)
     VALUES ($1, $2, $3, 'pending')`,
    [103, 1, '2026-10-20T10:15:00Z']
  );
  await tx.query(
    `INSERT INTO order_items
       (order_id, line_no, product_id, quantity, unit_price)
     VALUES ($1, 1, 'P7', 1, '20.00'),
            ($1, 2, 'P3', 2, '4.50')`,
    [103]
  );
}

The helper does not acquire another connection, commit or catch and ignore a rejection. Its caller owns completion. The successful path has this shape:

const tx = await pool.connect();
// The owner retains tx until commit/rollback and cleanup finish.
await tx.query('BEGIN');
await writeOrder103(tx);
await tx.query('COMMIT');
tx.release();

After a confirmed commit, P3 stock is 10 and P7 stock is 0. Order 103 is pending, with line value 29.00 and no payment. A helper that commits its own part could leave reduced stock even if a later insert fails. Keep the ownership at the level that can decide whether the whole purchase is acceptable.

The owner also handles failure. A rejected allocation or SQL error abandons the whole purchase and requires rollback before a usable connection returns to the pool. An unusable connection is discarded. If the connection fails after COMMIT was sent, cleanup cannot establish whether the purchase committed: retain that uncertainty and recover the same request’s outcome. The following sections develop those obligations.

Borrowing and returning a connection

Keep a pool for the process, rather than constructing one for each HTTP request. Borrow a connection for the database work and return it on every completed path. A pool with all its clients checked out makes the next borrower wait. A forgotten release can therefore stall requests that have nothing to do with the failed one. Pool budgets and overload policy belong to Connections, overload and capacity.

Returning a client with release() makes it available to another borrower. release(true) removes and disconnects that client instead. pool.end() closes the pool during process shutdown or at the end of a script; it is not the way to finish each request. Handle the pool’s documented background error event too, because an idle connection can fail while no request is using it.

Do request validation and unrelated remote waits before beginning this transaction where possible. Waiting for an email provider while holding the purchase open keeps its connection and any locks occupied. Moving that call after commit reduces the time held, but raises a separate delivery problem later in this article.

Session state survives a request

A reusable connection reaches a reusable database session. That session can retain settings after the request ends. Returning it to the pool is not itself a promise to reset them.

Establish stable defaults when a connection is created, and scope temporary settings to the transaction where appropriate. PostgreSQL’s SET LOCAL reverts at commit or rollback; it does not clean up earlier session-level changes. Apply the same care to settings such as statement timeouts and schema lookup. A connection reset policy must account for all the session features the application uses.

ORM sessions and commits

An ORM’s session or unit of work tracks objects and their pending changes. Changing an object, flushing its changes as SQL and committing the database transaction are distinct steps. A flush can send an UPDATE without committing it. Some operations flush automatically: SQLAlchemy can flush pending changes before an ORM query and flushes them before commit. Lazy relationship access can also read the database.

Locate the transaction owner in the ORM API and pass its context through participating helpers, just as with tx. Keep one mutable unit of work local to the request or concurrent task it serves; SQLAlchemy specifies a Session per thread and an AsyncSession per task. Sharing one between unrelated requests can mix their pending changes and transaction decisions. The useful review question remains “which statements run, on whose transaction, and who commits them?”

Errors and connection cleanup

The request can fail without the database reporting an error. A zero-row guard is one example; an exception in the application is another. A database rejection is different again. PostgreSQL reports a stable five-character SQLSTATE code, with a separate constraint name for an integrity violation. Preserve those fields through the data layer. Match an expected code and constraint when translating a rejection; do not classify it by searching human-readable message text.

For example, 23505 means a uniqueness violation, but does not by itself say “this logical request was already completed”. It could name an unrelated unique constraint. A syntax error is a programming fault, not a customer rejection, and a broken socket does not carry the same knowledge as a database answer. Keep the original cause when adding application context.

A stock rejection, a SQL error and a broken connection require different conclusions. A known failure can justify rollback; losing a connection while committing may leave the outcome unknown. In every case, settle the connection’s safe reuse separately from what can be concluded about the purchase.

After a statement error inside an ordinary PostgreSQL transaction, subsequent commands are rejected with 25P02, “in failed SQL transaction”, until the transaction is rolled back. The full rollback here abandons the purchase. Savepoints allow narrower recovery in other designs, but merely catching the driver exception does not restore this transaction.

Put release in the owner’s final cleanup path, after outstanding work and transaction completion. If rollback fails or the connection’s usable state cannot be established, discard the client instead of lending it to another request. Keep the original purchase or database failure as the main error, with cleanup failure recorded alongside it. Otherwise an exception from ROLLBACK can hide the reason cleanup was needed.

Deadlines and cancellation

A request deadline can expire while waiting to borrow a connection, while a query runs or while commit is in flight. Those are different situations. A client-side timer that stops waiting does not prove the server stopped. Nor does the browser cancelling its HTTP request automatically cancel a database query.

PostgreSQL’s statement_timeout limits how long a statement runs at the server. It does not include the application’s earlier wait for a pool client or bound the whole HTTP request. A transaction-local timeout can protect this purchase’s statements without leaving a changed session timeout for the next borrower.

When the request is abandoned, stop scheduling further purchase work. Use the driver’s documented cancellation mechanism if available, then wait for the original query’s completion or dispose of its connection. PostgreSQL cancellation can arrive too late to affect a finished statement; sending the cancellation request alone is not confirmation. A confirmed statement cancellation inside the transaction still requires rollback before reuse. Keep time for that cleanup, or discard the connection if it cannot finish safely within the cleanup budget.

When the commit reply is missing

Before COMMIT is sent, the owner can abandon its open transaction. After it is sent, losing the reply leaves a different question: did the server commit? The same connection failure can follow a commit or precede it. Neither cancellation nor a later rollback attempt proves that an already committed purchase failed.

Return that uncertainty to the caller. Disposing of the connection protects the next borrower; it does not resolve whether order 103 exists. Do not turn “commit not confirmed” into “nothing happened” and repeat the purchase. Retries and uncertain commit outcomes develops request identity, deduplication and recovery of the recorded result. A specific database rejection of commit can provide a known failure; a missing answer cannot.

External work after a database change

Suppose the shop emails Mina when it accepts order 103. Sending the email inside the transaction holds database resources while the provider answers. It also cannot make the email roll back: the provider might accept it before a later database failure. Sending it after commit leaves the opposite gap. The process can stop after storing the order and before making the call.

Record a durable intention to send the confirmation in the same database transaction as the order. That intention and the purchase then commit together. A separate worker delivers it later. This is a transactional outbox. It closes the gap between the committed order and the existence of work to deliver, rather than making the external service part of the database transaction.

The worker still needs to handle a lost provider reply, duplicate delivery and its own failure. Outboxes and database-backed jobs develops those protocols. Fire-and-forget code after commit supplies no durable record from which to recover.

The identity behind a connection

The database authenticates the account that opened the connection. That is often an application account shared by requests from many customers. It is different from Mina’s authenticated customer identity. Binding customerId safely prevents it from changing the SQL structure; it does not decide which customer Mina is allowed to request.

Derive or validate the row scope through the application’s authorisation rules. Where database row policies also enforce scope, establish their context deliberately on the actual connection and give it the same lifecycle care as other session state. A stale customer context on a reused connection is an access failure, even if every query is parameterised.

Runtime requests, schema migrations and support investigations also do different work. Give each deliberate database authority rather than using a powerful administrative identity for all three. Database access and least privilege develops that design and its verification.