Building Software
Sections

Contents Section 6, Design

Where Logic Meets the Database

The boundary between application code and storage: where business rules live, what queries get generated, and how the two stay consistent.

Section 6 of 9, subsection 5 of 6. Depth: do. Core 15 of 25. A 9-minute read.

Mistakes to catch in review

  1. N+1 queries: one query for a list and then one more for every item, usually hidden inside an ORM's lazy loading.

  2. Uniqueness checked only in application code, so two simultaneous requests both pass the check and create duplicates.

  3. A whole table fetched into memory to be filtered or sorted in code instead of in the query.

  4. The same business rule written in both a query and application code, drifting apart over time.

The Queries Behind Your Code

An agent builds you an endpoint that returns the 50 most recent orders, each with the customer's name. It is a few lines of TypeScript on TypeORM, the tests pass, and on your laptop the page loads instantly:

TypeScript
app.get("/orders/recent", async (req, res) => {
  const orders = await orderRepo.find({ order: { createdAt: "DESC" }, take: 50 });
  const rows = [];
  for (const o of orders) {
    const customer = await o.customer;
    rows.push({ id: o.id, total: o.totalCents, customer: customer.name });
  }
  res.json({ orders: rows });
});

In production the page is several times slower than on your laptop, and the database is doing far more work than one list should need. Nothing in the code looks slow. There is one query, a loop, and an object per row. The cost is in a line that does not look like a database call at all: await o.customer. It reads like waiting for a value the order already holds.

Code that uses an ORM (object-relational mapper, the library that turns database rows into objects) reads like ordinary object access. In TypeORM, a relation declared with the type Promise<Customer> is lazy: nothing is loaded until the first time you await it. Underneath, every relation that isn't already loaded becomes a SQL statement sent over a network to another process. To review this kind of code you need to see those statements, count them, and know where the rules about your data are actually enforced.

What actually gets sent

A database is a separate program, usually on a separate machine. Every query is a round trip: your code sends SQL, waits, and receives rows. The query itself may take microseconds, but the round trip has a fixed cost on top, typically somewhere around half a millisecond to a millisecond inside one data center, and tens of milliseconds if the app and database are in different regions.

Here is what the endpoint above sends, trimmed from the query log. TypeORM prints every statement when the data source has logging: ["query"], Prisma does the same with log: ["query"], and every ORM and database has an equivalent.

SQL
SELECT id, customer_id, total_cents, created_at FROM orders
  ORDER BY created_at DESC LIMIT 50;
SELECT id, name, email FROM customers WHERE id = 812;
SELECT id, name, email FROM customers WHERE id = 97;
SELECT id, name, email FROM customers WHERE id = 812;
-- ... 47 more, one per order

The first query loads the orders. Each order row carries customer_id, but the customer relation is loaded lazily: the ORM fetches it the first time you touch it. The loop touches it 50 times, so the ORM sends 50 more queries, including repeats for a customer who placed two orders. That is 51 queries for one page.

This is the N+1 pattern: one query for the list, then one for each of the N items. At 1 ms per round trip, 51 queries spend about 50 ms waiting on the network. With the database in another region at 30 ms per round trip, it is about 1.5 seconds. On your laptop the database is on the same machine and has ten test orders, which is why you never saw it.

A tall box, the endpoint's loop, holds a column of statements: the orders list with LIMIT 50, WHERE id = 812 from customers, WHERE id = 97 from customers, WHERE id = 812 again, highlighted as the same customer again, and 47 more, one per order. Each statement has its own arrow to a database cylinder on the right, marked another process; the last arrow is dashed. Notes read at 30 ms each: about 1.5 seconds, and 51 round trips for one page.
One query loads the list, then await o.customer sends one more per order, repeats included: 51 round trips for one page.

To count, remember that the first access to each uncached relation issues a query, and a relation that is already cached or prefetched issues none. Inside a loop over fresh objects, that is one query per pass. If the loop is inside another loop, the inner access runs once per inner pass, so the counts multiply. A useful detail is that o.customerId costs nothing, because the ID is a column on the order row. await o.customer costs a query, because the name lives in another table.

You can watch the count happen. The block below stands in for the database with two tables in memory and a log of every statement. Each order gets a lazy customer that queries on first access and keeps the result, the way the ORM's does. It uses a plain getter instead of a promise, which changes nothing about the count.

TypeScriptRuns in your browser
const customers = new Map([[812, "Maya"], [97, "Ravi"], [301, "Lena"]]);
const buyers = [812, 97, 812, 301, 97];
const orderRows = Array.from({ length: 50 }, (_, i) => ({ id: 5000 - i, customerId: buyers[i % 5] }));
const log: string[] = [];
const send = (sql: string) => log.push(sql);

function loadOrders() {
  send("SELECT id, customer_id FROM orders ORDER BY created_at DESC LIMIT 50");
  return orderRows.map((row) => {
    let customer: { name: string } | undefined;
    return {
      ...row,
      get customer() {
        if (!customer) {
          send(`SELECT name FROM customers WHERE id = ${row.customerId}`);
          customer = { name: customers.get(row.customerId) ?? "?" };
        }
        return customer;
      },
    };
  });
}

// The endpoint's loop: read the ID (a column), then the name (another table), twice.
for (const o of loadOrders()) {
  o.customerId;
  o.customer.name;
  o.customer.name;
}
const repeats = log.filter((q) => q.endsWith("= 812")).length;
console.log(`lazy loop: ${log.length} queries, customer 812 fetched ${repeats} times`);
for (const rtt of [1, 30]) console.log(`  at ${rtt} ms per round trip: ${log.length * rtt} ms waiting`);

// Batched: the list, then every customer it needs in one query.
log.length = 0;
const orders = loadOrders();
const ids = [...new Set(orders.map((o) => o.customerId))];
send(`SELECT id, name FROM customers WHERE id IN (${ids.join(", ")})`);
console.log(`batched load: ${log.length} queries`);
for (const q of log) console.log(`  ${q}`);

The second read of o.customer.name on each order sent nothing, because that object had already cached its customer. The repeats come from different order objects that point at the same customer.

There are two standard fixes. A join asks the database to combine the rows in one query. This is a place where writing the query yourself, with the ORM's query builder, says exactly what gets sent:

TypeScript
const rows = await orderRepo
  .createQueryBuilder("o")
  .innerJoin("o.customer", "c")
  .select("o.id", "id")
  .addSelect("o.totalCents", "total")
  .addSelect("c.name", "customer")
  .orderBy("o.createdAt", "DESC")
  .limit(50)
  .getRawMany();
SQL
SELECT orders.id, orders.total_cents, customers.name
  FROM orders JOIN customers ON customers.id = orders.customer_id
  ORDER BY orders.created_at DESC LIMIT 50;

A batched load sends a second query that fetches all the related rows at once, then matches them up in memory:

TypeScript
const orders = await orderRepo.find({ order: { createdAt: "DESC" }, take: 50 });
const ids = [...new Set(orders.map((o) => o.customerId))];
const found = await customerRepo.findBy({ id: In(ids) });
const nameById = new Map(found.map((c) => [c.id, c.name]));
const rows = orders.map((o) => ({ id: o.id, total: o.totalCents, customer: nameById.get(o.customerId) }));

That is two queries no matter how many orders there are. The DataLoader library does the same batching for GraphQL resolvers. Joins suit one-to-one and many-to-one relations. Batching suits one-to-many relations, where a join would repeat the parent row once per child.

Then lock the number in with a test, so the next change can't quietly bring it back:

TypeScript
test("recent orders page sends one query", async () => {
  await makeOrders(50);
  const sent = await countQueries(() => request(app).get("/orders/recent"));
  expect(sent).toBe(1);
});

countQueries is a small helper that counts the statements the ORM reports while the callback runs. TypeORM accepts a custom logger whose logQuery method is called once per statement, which is all the helper needs.

Measure the change as well as asserting it. Record the query count and the response time before and after, against a database with realistic row counts and at the same network distance as production. The count should drop from 51 to 1, and the time saved grows with the round-trip cost.

When you review a page, open the query log for one page load, count the statements, and say which line of code produced each one. If you can't match a query to a line, you don't yet understand what the page does.

Filtering in the wrong place

A second shape looks like this:

TypeScript
const all = await orderRepo.find();
const biggest = all
  .filter((o) => o.status === "open")
  .sort((a, b) => b.totalCents - a.totalCents)
  .slice(0, 10);

It returns the right ten orders. It gets them by loading every order in the table into the app, checking each one, sorting the survivors and throwing away all but ten. With 2 million orders at a few hundred bytes each, that is several hundred megabytes crossing the network, and more than that in memory once each row becomes a JavaScript object. The database could have done the same work with an index and returned ten rows:

TypeScript
const biggest = await orderRepo.find({
  where: { status: "open" },
  order: { totalCents: "DESC" },
  take: 10,
});
SQL
SELECT ... FROM orders WHERE status = 'open'
  ORDER BY total_cents DESC LIMIT 10;
Two lanes. Top lane: an orders cylinder marked 2 million rows sends every row to an app box marked filter, sort, slice, which produces ten orders. A note under the arrow reads several hundred MB. Bottom lane: a highlighted orders cylinder marked filters with an index sends ten rows to an app box, which produces the same ten orders. A note reads same answer, ten rows.
Both versions return the same ten orders. Filtering in the app moves all 2 million rows across the network first; filtering in the database uses an index and sends back ten.

The signs to look for in review are a find() with no where followed by .filter, .sort or .length on the result, and slicing after a loop. Taking .length of a find result loads every matching row to count them; orderRepo.count({ where }) sends SELECT COUNT(*) and returns one number. Filtering in code is fine when the set is small and bounded by design, such as the eight line items on one order. It is a bug when the set grows with your data.

Two requests at once

Now a rule. Usernames must be unique, and the agent enforces it like this:

TypeScript
async function createAccount(username: string) {
  const taken = await accountRepo.findOneBy({ username });
  if (taken) throw new UsernameTaken(username);
  return accountRepo.save({ username });
}

With one user at a time, this works. With two requests for the same name arriving a few milliseconds apart, here is what happens:

Time Request A Request B
t1 SELECT: is "maya" taken? No rows.
t2 SELECT: is "maya" taken? No rows.
t3 INSERT "maya"
t4 INSERT "maya"

Both checks ran before either insert, so both passed, and the table now has two accounts named maya. In production this is easy to trigger. A user double-clicks the submit button, or a mobile client retries after a slow response, and the two requests land within milliseconds of each other.

Wrapping the two statements in a transaction does not fix this at PostgreSQL's default isolation level. Each request's SELECT only sees rows that other transactions have already committed, and neither has committed when the other checks.

The block below runs that interleaving. A 5 ms timer stands in for each round trip, and two signups for maya start at the same moment. The second half runs the same requests against a table with a unique index, where the database checks and writes in one step.

TypeScriptRuns in your browser
const roundTrip = () => new Promise((r) => setTimeout(r, 5));

const rows: string[] = [];
async function createAccount(username: string) {
  await roundTrip();
  if (rows.includes(username)) return `${username}: taken`;
  await roundTrip();
  rows.push(username);
  return `${username}: created`;
}
console.log(await Promise.all([createAccount("maya"), createAccount("maya")]));
console.log("rows:", rows);

const index = new Set<string>();
const indexed: string[] = [];
async function insertAccount(username: string) {
  await roundTrip();
  const key = username.toLowerCase();
  if (index.has(key)) return `${username}: unique violation, shown as taken`;
  index.add(key);
  indexed.push(username);
  return `${username}: created`;
}
console.log(await Promise.all([insertAccount("maya"), insertAccount("maya"), insertAccount("Maya")]));
console.log("rows:", indexed);

The fix is to let the database enforce the rule, because the database is the one place that sees every write in order:

SQL
CREATE UNIQUE INDEX accounts_username_lower ON accounts (lower(username));

Now the second INSERT fails no matter how the requests interleave. The index on lower(username) also stops "Maya" and "maya" from both existing, which the application check would have allowed. The application catches the database's error and turns it into the same friendly message:

TypeScript
async function createAccount(username: string) {
  try {
    return await accountRepo.insert({ username });
  } catch (e) {
    if (isUniqueViolation(e, "accounts_username_lower")) throw new UsernameTaken(username);
    throw e;
  }
}

// PostgreSQL reports a unique violation as error code 23505 and names the index it hit.
function isUniqueViolation(e: unknown, name: string): boolean {
  const pg = (e as { driverError?: { code?: string; constraint?: string } } | null)?.driverError;
  return pg?.code === "23505" && pg.constraint === name;
}

The helper checks the index by name, since a different constraint on the same table would raise the same kind of error, and any other error is thrown on unchanged. You can keep the lookup in front as well, to show a nice message in the common case. It is a convenience; the index is what makes the rule true.

Deciding where a rule lives

Four writers stacked on the left: the web app, a background job, an admin SQL fix at midnight, and next year's service in a different language. Each sends an arrow into one highlighted database cylinder marked constraints live here; the top and bottom arrows turn into its top and bottom. A note above it reads sees every write.
The web app is one writer among several; only a constraint in the database applies to all of them.

The question to ask of any rule is whether it must hold for every row, from every writer, forever. Writers include the web app, a background job, an admin running a SQL fix at midnight, and next year's service in a different language. If the answer is yes, it belongs in the database as a constraint: UNIQUE, NOT NULL, FOREIGN KEY, or CHECK (total_cents >= 0). The database enforces these under concurrency, and a check in application code does not.

If the rule needs context the database doesn't have, such as the current user's permissions, the pricing plan, or an external service, it belongs in application code. Many rules belong in both: the constraint guarantees the invariant, and the application check gives the user a clear message before the write.

Rule Where Reason
Username is unique Constraint, plus app check for the message Concurrent writes
Order total is not negative CHECK constraint Impossible state; every writer must respect it
Order points to a real customer Foreign key Deleting a customer must not orphan orders silently
Free plan allows 3 projects Application code Depends on the plan, which changes with pricing

That last row has the same race as the username check. Two requests can each count two projects and each insert a third. If an occasional fourth project is acceptable, application code is enough. If it isn't, the check has to lock something, for example by selecting the account row with SELECT ... FOR UPDATE inside the transaction, so the second request waits until the first commits.

The same rule in two places

The last mistake takes months to show up. The rule for an "active customer" gets written once as a getter for the account page:

TypeScript
class Customer {
  get isActive(): boolean {
    return this.status === "active" && this.deletedAt === null;
  }
}

and once, a month later, as a query for the admin dashboard:

TypeScript
const activeCount = await customerRepo.count({ where: { status: "active" } });

The query forgot deletedAt, so the dashboard counts deleted customers as active. Later someone adds a "trial" status and updates the getter, and the query drifts further. Neither version is obviously wrong when you read it alone. You find the bug when two screens disagree about the same number:

SQLRuns on SQLite in your browser
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, status TEXT, deleted_at TEXT);
INSERT INTO customers VALUES
  (1, 'Maya', 'active', NULL),
  (2, 'Ravi', 'active', '2026-08-02'),
  (3, 'Lena', 'active', NULL),
  (4, 'Omar', 'cancelled', NULL);

-- The getter's rule next to the dashboard's query.
SELECT
  (SELECT COUNT(*) FROM customers WHERE status = 'active' AND deleted_at IS NULL) AS account_pages,
  (SELECT COUNT(*) FROM customers WHERE status = 'active') AS dashboard;

-- One definition that every caller reads from.
CREATE VIEW active_customers AS
  SELECT * FROM customers WHERE status = 'active' AND deleted_at IS NULL;
SELECT COUNT(*) AS active FROM active_customers;
SELECT EXISTS (SELECT 1 FROM active_customers WHERE id = 2) AS ravi_is_active;

To spot it, pick a business term that appears in the product ("active", "overdue", "eligible") and search the code for every place that decides it. If you find the logic in both a query and a method, or in two queries, ask which one wins when they disagree.

Two screens across the top, the admin dashboard using count and the account page using countBy with an id, each send an arrow down into one highlighted box below them, activeWhere, the rule defined once. A note under the rule reads no caller re-derives the rule.
The dashboard count and the single-customer check both go through activeWhere, so they cannot disagree about who is active.

The fix is to define the rule once, in a form that produces a query, and have every caller go through it. That can be a database view like the one above, a repository function, or in TypeORM one exported condition such as const activeWhere = { status: "active", deletedAt: IsNull() } that every count and lookup uses. Keeping all queries for a table in one module helps here, since there is one file to read to find every definition. When a page needs to know whether a single customer is active, it asks the same query, for example customerRepo.countBy({ ...activeWhere, id }), instead of re-deriving the rule.

When you review code that touches a database, read the query log for each page it changes and count the statements, and for each rule it enforces, ask whether two concurrent requests could both pass the check. If they can, the rule needs a constraint or a lock.

Check your understanding

7 questions on this lesson. Pick an answer to see whether it holds and why.

  1. 1An agent wrote this loop over 20 posts, where author is a lazy relation. How many queries does it send?
    TypeScript
    const posts = await postRepo.find({ take: 20 });
    for (const p of posts) {
      const author = await p.author;
      const again = await p.author;
      console.log(p.authorId, author.name, again.avatarUrl);
    }
    Show the answer

    Answer B. The list is one query. Each post object loads its author the first time it is awaited and keeps it, so the second await on the same post sends nothing, and p.authorId is a column already on the row. That makes 1 + 20. The answer 41 assumes every await queries again, but the cache lives on each object.

  2. 2Each order may have at most one refund. Two refund requests for the same order arrive 3 ms apart. Which change stops the second refund?
    TypeScript
    async function refund(orderId: number) {
      const existing = await refundRepo.countBy({ orderId });
      if (existing > 0) throw new AlreadyRefunded(orderId);
      return refundRepo.insert({ orderId, at: new Date() });
    }
    Show the answer

    Answer C. Only the database sees both inserts in order, so a unique index makes the second one fail however the requests interleave. A transaction at the default level does not help: each request's count sees only committed rows, and neither has committed when the other counts. Disabling the button does nothing about a client retry or a script.

  3. 3This runs on every dashboard load against 3 million invoices. What is wrong with it?
    TypeScript
    const now = new Date();
    const overdue = (await invoiceRepo.find())
      .filter((i) => i.paidAt === null && i.dueAt < now)
      .length;
    Show the answer

    Answer A. find() with no where is one query, but it returns all 3 million rows, which the app then filters and counts. invoiceRepo.count({ where }) with the same conditions makes the database send back one number. It is not N+1, since there is only one statement, and < compares Date objects by their time value, so the count itself is right.

  4. 4Which of these rules belongs in application code rather than in a database constraint?
    Show the answer

    Answer D. The plan limit depends on pricing, which the database does not know and which changes, so it lives in code, with a lock if an occasional extra member is unacceptable. The other three must hold for every writer, which is what UNIQUE, CHECK and a foreign key enforce. Email uniqueness is the tempting one to leave in code, and it is the one two simultaneous signups break.

  5. 5Invoice pages use isOverdue. The dashboard's count disagrees with them. Which fix stops this kind of disagreement from coming back?
    TypeScript
    const isOverdue = (inv: Invoice) =>
      inv.paidAt === null && inv.dueAt < today && inv.status !== "void";
    
    const overdueCount = await invoiceRepo.count({
      where: { paidAt: IsNull(), dueAt: LessThan(today) },
    });
    Show the answer

    Answer B. Adding the missing void check makes the two agree today, but the next change to one copy, such as a new status or a grace period, will not reach the other, and a comment does not keep code in step. One definition that produces a query serves the count and the single-invoice check alike, so there is nothing left to drift.

  6. 6This is the query log for one load of a cart page. What is the fix?
    SQL
    SELECT id, product_id, qty FROM cart_items WHERE cart_id = 77;
    SELECT id, name, price_cents FROM products WHERE id = 4;
    SELECT id, name, price_cents FROM products WHERE id = 19;
    SELECT id, name, price_cents FROM products WHERE id = 23;
    -- ... one per cart item, 24 product queries in all
    Show the answer

    Answer A. The log shows one query for the items and then one per item, the N+1 shape, so the time goes to 25 round trips. Each lookup is already by primary key, which is indexed, so another index changes nothing. A join or a batched IN query brings it to one or two statements. A cache hides the pattern on warm pages and keeps it on cold ones.

  7. 7A developer replaces a lazy loop with a join, times the page on a laptop with a local database and 10 test rows, sees 31 ms become 29 ms, and calls the change pointless. What is wrong with the measurement?
    Show the answer

    Answer C. The lazy loop costs one round trip per row, and on a laptop with ten rows there are few trips and each is nearly free. Measure with realistic row counts and at production's network distance, and record the query count beside the time. The drop in query count shows the change worked even where the timing cannot.

You understand it when you can

Drill

An agent built a team dashboard that loads teams through an ORM, then lazily loads each member's recent tasks inside a loop, and enforces unique team names by looking the name up before inserting. Count the queries for a page showing 30 teams of 8 people, and find the race that still creates duplicate names.

Topics

Rules in Code and in Constraints
Using database constraints for invariants that must never break, and application code for rules that need context.
ORMs and Query Builders
What object-relational mappers generate, when they help, and when to write the query yourself.
Query Shape and the N+1 Problem
Designing data access around what a page or job actually needs, in as few round trips as possible.
Transaction Boundaries
Deciding which changes must commit together, and keeping slow network calls out of open transactions.
Data Access Layers
Keeping storage details behind one module so schema changes don't ripple through the codebase.

Further reading

Start here

Short reads

Watch

Books

  • SQL Antipatterns, Volume 1: Avoiding the Pitfalls of Database Programming. Bill Karwin, 2022.

    Each chapter takes a plausible-looking pattern (keyless entries, rules kept only in application code, fetching everything and filtering in code) and shows the cost it hides and the constraint or query that fixes it.

  • Designing Data-Intensive Applications: The Big Ideas Behind Reliable, Scalable, and Maintainable Systems. Martin Kleppmann and Chris Riccomini, 2026, 2nd edition.

    The transactions chapter is the clearest treatment of isolation anomalies and of which invariants need a constraint or serializable isolation rather than an application-level check.

  • Patterns of Enterprise Application Architecture. Martin Fowler, 2002.

    The source for the patterns underneath every ORM (Active Record, Data Mapper, Unit of Work, Lazy Load, Repository), which explains where N+1 queries come from and how a data access layer hides the schema.

Primary sources

  • PostgreSQL Documentation: Constraints. Manual.

    Documents check, not-null, unique, primary-key, foreign-key and exclusion constraints, the database-side rules that hold under concurrent writes where an application-side check does not.

  • Django Documentation: Database access optimization. Manual.

    Official guidance on profiling queries, doing work in the database instead of in Python, and fixing N+1 access with select_related and prefetch_related. The same ideas carry over to any ORM.