--- title: 'The Data Access Layer and Business Logic' source: 'https://academia.sh/en/courses/data-access-layer' language: en updated: '2026-08-23T07:00:26+00:00' license: 'CC BY-SA 4.0' lessons: 21 outcomes: - 'Builds a layer that separates data access from business logic' - 'Defines transaction boundaries in the right place' - 'Recognizes and resolves common data access performance problems' --- # The Data Access Layer and Business Logic Building the path a request takes down to the data: the trade-off between direct queries and a mapper, the repository pattern, connection pooling and schema migrations, transaction boundaries and locking, performance problems such as N+1 and pagination, and the placement of business logic across layers. ## Data Access Approaches The trade-off between a direct query and a mapper, the points where the object model and the relational model do not line up, the repository pattern that hides the persistence detail, composing queries, the saturation behavior of a connection pool, and running schema migrations forward and backward. ### Direct Query and Mapper Source: https://academia.sh/en/courses/data-access-layer/direct-query-and-mapper Building the same list two ways: hand-written SQL versus a small mapper that generates SQL from a declared mapping, counting the generated queries, and the control-versus-repetition trade-off between the two approaches. The Authentication and Authorization course closed one question: who can make a request, and with which role they can touch which resource, is now a settled decision. A request that passes the check enters the application and meets a second question waiting there. How does the call reach down to the data? This course builds that descent. The first step is the lowest layer: the translation between the objects in the application and the rows in the database. Writing the translation by hand is possible, and so is leaving it to a mapper. Both produce the same list; the SQL they produce and the number of queries they run are not the same. This lesson measures the difference. #### Shared Task and Schema The schema used throughout the course is the library schema from the SQL Fundamentals course: the branch, book, member, and loan relations. The block below builds the database from scratch; every example in this lesson runs against this file. ```sh rm -f library.db sqlite3 library.db <<'SQL' CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL); CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, publication_year INTEGER, branch_id INTEGER REFERENCES branch(branch_id)); CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT, registered_at TEXT NOT NULL); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id), member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL, return_date TEXT); INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'), (3,'Kadikoy','Istanbul'),(4,'Konak','Izmir'); INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,1),(2,'The Disconnected','Oğuz Atay',1972,1), (3,'The Book of Sand','Jorge Luis Borges',1975,2),(4,'Yaban','Yakup Kadri',1932,2), (5,'Silent House','Orhan Pamuk',1983,3),(6,'Motherland Hotel','Yusuf Atılgan',NULL,3), (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL); INSERT INTO member VALUES (1,'Alice','Kane','alice@example.test','2023-02-14'), (2,'Ben','Ortiz','ben@example.test','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'), (4,'Derek','Voss','derek@example.test','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'), (6,'Owen','Park','owen@example.test','2025-01-18'); INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'), (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'), (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'), (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'), (12,4,4,'2025-06-20','2025-07-04'); SQL ``` The task is this: list the loan records that have not been returned, together with the book's title and the member's name. This is the loan service's most frequently called read task; the same task will be taken up again through different layers across the course. #### Direct Query In the first approach, the query is written by hand. The database joins three relations in a single step, and the application converts the rows it gets back into its own object. ```js // direct.mjs — builds the open-loan list with a single query import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("library.db"); let queryCount = 0; const sql = `SELECT o.loan_id, o.pickup_date, k.title, u.first_name, u.last_name FROM loan o JOIN book k ON k.book_id = o.book_id JOIN member u ON u.member_id = o.member_id WHERE o.return_date IS NULL ORDER BY o.loan_id`; queryCount += 1; const rows = db.prepare(sql).all(); // The row -> object conversion is written by hand. const openLoans = rows.map((s) => ({ id: s.loan_id, pickupDate: s.pickup_date, bookTitle: s.title, memberName: `${s.first_name} ${s.last_name}`, })); for (const o of openLoans) console.log(`${o.id} ${o.pickupDate} ${o.bookTitle} ${o.memberName}`); console.log("queries executed:", queryCount); ``` ```sh node direct.mjs ``` ``` 3 2025-02-11 Blindness Ben Ortiz 7 2025-04-21 Silent House Derek Voss 10 2025-06-03 The Book of Sand Grace Kim queries executed: 1 ``` A single query. The number of requests sent to the database is one, the number of rows returned is three, the number of columns transferred is five. This approach's gain is directly visible: the query text can be read by eye, control over the plan is complete, and the shape of the result set is in the hands of the person who wrote it. The cost sits in the same place. The conversion inside the `rows.map` call is written by hand. When a new column is added to the loan record, both the SQL and the conversion are updated. If the application has fifteen variants of this task, the conversion is written fifteen times. #### Declared Mapping The second approach declares the conversion once. Which type corresponds to which table, and which field to which column, sits in a mapping table; the SQL is generated from it. ```js // mapper.mjs — small mapper that generates SQL from a declared mapping table import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("library.db"); export const generated = []; const mapping = { Loan: { table: "loan", key: "loan_id", fields: { id: "loan_id", pickupDate: "pickup_date", returnDate: "return_date" }, relations: { book: { target: "Book", local: "book_id" }, member: { target: "Member", local: "member_id" } }, }, Book: { table: "book", key: "book_id", fields: { id: "book_id", title: "title", author: "author" }, relations: {} }, Member: { table: "member", key: "member_id", fields: { id: "member_id", firstName: "first_name", lastName: "last_name" }, relations: {} }, }; const selectList = (type) => [ ...Object.values(mapping[type].fields), ...Object.values(mapping[type].relations).map((i) => i.local), ].join(", "); function run(sql, ...bound) { generated.push(sql); return db.prepare(sql).all(...bound); } function toObject(type, row) { const m = mapping[type]; const obj = {}; for (const [name, column] of Object.entries(m.fields)) obj[name] = row[column]; // A relation loads the moment it is read: the getter runs a query each time it is called. for (const [name, relation] of Object.entries(m.relations)) { const id = row[relation.local]; Object.defineProperty(obj, name, { get: () => find(relation.target, id) }); } return obj; } export function find(type, id) { const m = mapping[type]; const row = run(`SELECT ${selectList(type)} FROM ${m.table} WHERE ${m.key} = ?`, id)[0]; return row ? toObject(type, row) : null; } export function search(type, condition) { const m = mapping[type]; const sql = `SELECT ${selectList(type)} FROM ${m.table} WHERE ${condition} ORDER BY ${m.key}`; return run(sql).map((row) => toObject(type, row)); } ``` The mapper's work comes down to three points: it builds the select list from the mapping, converts the result into an object, and loads relations the moment they are read. The last point is the critical one. The `book` property is not a real value; it is a reader that runs a query when it is called. This behavior is called **lazy loading**. The caller no longer sees SQL. ```js // mapper-use.mjs — builds the same list with the mapper and counts the generated SQL import { search, generated } from "./mapper.mjs"; for (const o of search("Loan", "return_date IS NULL")) { console.log(`${o.id} ${o.pickupDate} ${o.book.title} ${o.member.firstName} ${o.member.lastName}`); } console.log("queries executed:", generated.length); for (const sql of generated) console.log(" " + sql.replace(/\s+/g, " ")); ``` ```sh node mapper-use.mjs ``` ``` 3 2025-02-11 Blindness Ben Ortiz 7 2025-04-21 Silent House Derek Voss 10 2025-06-03 The Book of Sand Grace Kim queries executed: 10 SELECT loan_id, pickup_date, return_date, book_id, member_id FROM loan WHERE return_date IS NULL ORDER BY loan_id SELECT book_id, title, author FROM book WHERE book_id = ? SELECT member_id, first_name, last_name FROM member WHERE member_id = ? SELECT member_id, first_name, last_name FROM member WHERE member_id = ? SELECT book_id, title, author FROM book WHERE book_id = ? SELECT member_id, first_name, last_name FROM member WHERE member_id = ? SELECT member_id, first_name, last_name FROM member WHERE member_id = ? SELECT book_id, title, author FROM book WHERE book_id = ? SELECT member_id, first_name, last_name FROM member WHERE member_id = ? SELECT member_id, first_name, last_name FROM member WHERE member_id = ? ``` The output is the same, but the query count is ten instead of one. The number comes from two sources. The first is the nature of lazy loading: the book and the member are read separately for each of the three loan records. The second is sneakier. In the print line, `o.member.firstName` and `o.member.lastName` do two separate reads; each read runs the reader again, so the member query comes back twice per loan. Together the three make 1 + 3 + 6 = 10. This is the central risk of using a mapper. **The query is invisible while reading the code.** The difference between a property access and a database round trip has disappeared from the writer's view. #### Identity Map Part of the number can be closed inside the mapper itself. If a row with the same identity and the same type is read once and kept, a second access produces no query. The structure that keeps loaded objects by their identity is called an **identity map**. ```js // mapper-identity.mjs — version that stores loaded objects by their identity import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("library.db"); export const generated = []; const identityMap = new Map(); // type:id -> object const mapping = { Loan: { table: "loan", key: "loan_id", fields: { id: "loan_id", pickupDate: "pickup_date", returnDate: "return_date" }, relations: { book: { target: "Book", local: "book_id" }, member: { target: "Member", local: "member_id" } }, }, Book: { table: "book", key: "book_id", fields: { id: "book_id", title: "title", author: "author" }, relations: {} }, Member: { table: "member", key: "member_id", fields: { id: "member_id", firstName: "first_name", lastName: "last_name" }, relations: {} }, }; const selectList = (type) => [ ...Object.values(mapping[type].fields), ...Object.values(mapping[type].relations).map((i) => i.local), ].join(", "); function run(sql, ...bound) { generated.push(sql); return db.prepare(sql).all(...bound); } function toObject(type, row) { const m = mapping[type]; const obj = {}; for (const [name, column] of Object.entries(m.fields)) obj[name] = row[column]; for (const [name, relation] of Object.entries(m.relations)) { const id = row[relation.local]; Object.defineProperty(obj, name, { get: () => find(relation.target, id) }); } return obj; } export function find(type, id) { const mapKey = `${type}:${id}`; if (identityMap.has(mapKey)) return identityMap.get(mapKey); const m = mapping[type]; const row = run(`SELECT ${selectList(type)} FROM ${m.table} WHERE ${m.key} = ?`, id)[0]; const obj = row ? toObject(type, row) : null; identityMap.set(mapKey, obj); return obj; } export function search(type, condition) { const m = mapping[type]; const sql = `SELECT ${selectList(type)} FROM ${m.table} WHERE ${condition} ORDER BY ${m.key}`; return run(sql).map((row) => toObject(type, row)); } ``` ```js // identity-use.mjs — builds the same list with the identity-map version import { search, generated } from "./mapper-identity.mjs"; for (const o of search("Loan", "return_date IS NULL")) { console.log(`${o.id} ${o.pickupDate} ${o.book.title} ${o.member.firstName} ${o.member.lastName}`); } console.log("queries executed:", generated.length); ``` ```sh node identity-use.mjs ``` ``` 3 2025-02-11 Blindness Ben Ortiz 7 2025-04-21 Silent House Derek Voss 10 2025-06-03 The Book of Sand Grace Kim queries executed: 7 ``` Ten queries dropped to seven: touching the same member twice now reads once. The identity map gives a second guarantee too. Because it returns a single object for the same row, a change made in one place also shows up in a copy read somewhere else; the chance of two different copies overwriting each other disappears. The number seven is still greater than one, and it grows together with the number of records in the list. Seven for three records means hundreds of queries for three hundred. This pattern is called the **N+1 query problem**; it will be measured and resolved in the Performance Problems topic. #### Where the Trade-off Stands The choice between the two approaches does not reduce to which one is better; what matters is which kind of cost gets lowered. A direct query maximizes **control**. The SQL that runs is exactly as written; which columns come back, how the join is built, and what the plan will look like are all visible. Complex reports, aggregate functions, and hand-tuned joins stay on this side. Its cost is repetition: the conversion code is rewritten for every read task. A mapper lowers **repetition**. The mapping is declared once, and dozens of read tasks are generated from it; adding a column to the schema asks for a change in a single place. Its cost is the loss of visibility: how many queries ran can only be learned by counting them. In practice the two approaches sit side by side in the same application. The split is made by the type of work: record lifecycle and single-object reads go to the mapper, multi-table reports and bulk operations are left to the direct query. The condition for the decision is measurability. Whichever approach is chosen, there must be a hook that lets you see the generated SQL and the query count; the `generated` array in this lesson is the smallest form of that hook. #### Summary - The same read task can be built with hand-written SQL or with a mapper that generates SQL from a declared mapping; both paths give the same output. - The measurement showed the difference: the direct query ran 1 query, the mapper with lazy loading ran 10, and the mapper with the identity map added ran 7. - Lazy loading turns a property access into an invisible database round trip; touching the same relation twice produces two queries. - The identity map makes sure the same identity is represented by a single object, cuts repeated reads, and prevents copies from overwriting one another. - A direct query maximizes control, a mapper lowers repetition; the choice is made by the type of work, and either way the generated SQL must be countable. #### Next Step The SQL the mapper produced ran more queries than expected, but that is not the real problem. In the mapping table, the `Loan` type and the `loan` relation lined up one to one: every field matched a column, every relation matched a foreign key. Real domain models do not form this alignment: an inheritance hierarchy has no single-table equivalent, object identity is not the same thing as a primary key, and a two-way relation has only one owner in the relational model. The next lesson examines where these mismatches come from and which mapping decision closes each one. ### Object–Relational Impedance Mismatch Source: https://academia.sh/en/courses/data-access-layer/object-relational-impedance-mismatch The four points where the object model and the relational model do not line up: identity, inheritance, relation direction, and granularity. Each mismatch is measured and shown together with the mapping decision that closes it. The previous lesson built a small mapper, and there the `Loan` type and the `loan` relation lined up one to one: every field matched a column, every relation matched a foreign key. That alignment comes from an easy example, not from a rule. The object model and the relational model are products of two separate theories. In the object model, identity is reference, inheritance exists, links are two-way, and one object can hold other objects embedded inside it. In the relational model, identity is a key value, there is no inheritance, a link is built one-way through a foreign key, and a table is a flat list of tuples. The points where the two models do not line up are called the **object–relational impedance mismatch**. The mismatch is not a bug; it is a gap that needs to be closed. This lesson measures the four gaps in order. The block below builds the database used in the first section of this lesson. ```sh rm -f library.db sqlite3 library.db <<'SQL' CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT, registered_at TEXT NOT NULL); CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, publication_year INTEGER); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id), member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL, return_date TEXT); INSERT INTO member VALUES (1,'Alice','Kane','alice@example.test','2023-02-14'), (2,'Ben','Ortiz','ben@example.test','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'); INSERT INTO book VALUES (1,'Blindness','José Saramago',1995),(6,'Motherland Hotel','Yusuf Atılgan',NULL); INSERT INTO loan VALUES (3,1,2,'2025-02-11',NULL),(11,6,2,'2025-06-11','2025-06-25'); SQL ``` #### Identity Mismatch In a relation, two rows being "the same" means their primary keys are equal. In the object world there are two kinds of equality instead: being the same reference and carrying the same value. When a mapper converts a row into an object, these two equalities pull apart. ```js // identity-difference.mjs — reading the same row twice produces two separate objects import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("library.db"); const read = (id) => db.prepare("SELECT member_id, first_name, last_name FROM member WHERE member_id = ?").get(id); const a = read(2); const b = read(2); console.log("same row (primary key):", a.member_id === b.member_id); console.log("same object (reference):", a === b); a.first_name = "Benedict"; console.log("a.first_name:", a.first_name, "| b.first_name:", b.first_name); const cache = new Map(); const readCached = (id) => { if (!cache.has(id)) cache.set(id, read(id)); return cache.get(id); }; const c = readCached(2), d = readCached(2); c.first_name = "Benedict"; console.log("same object with the identity map:", c === d, "| d.first_name:", d.first_name); ``` ```sh node identity-difference.mjs ``` ``` same row (primary key): true same object (reference): false a.first_name: Benedict | b.first_name: Ben same object with the identity map: true | d.first_name: Benedict ``` The two reads fetched the same row but produced two separate objects. A change made in one does not show up in the other; if both objects write back to the same row, one overwrites the other. The last line of the output shows the fix: the **identity map** built in the previous lesson represents every row with a single object for the lifetime of a request, and makes the two equalities coincide again. This has a second consequence. An object not yet written to the database has no primary key. If you make the key the sole criterion for identity, two unsaved objects look equal to each other. This is why, in the mapping layer, new objects either come to life with an identifier generated by the application, or are compared only by reference equality until a key is assigned. #### Inheritance Mismatch If the library starts holding both print and audio works, a supertype and two subtypes appear in the object model: shared fields on top, specific fields underneath. In the relational model, there is no such thing as a subtype. The hierarchy can be brought down to tables in three ways. ```sh rm -f inheritance.db sqlite3 inheritance.db <<'SQL' -- 1. Single table: all subtypes in one relation, with a discriminator column CREATE TABLE single_table_work ( work_id INTEGER PRIMARY KEY, kind TEXT NOT NULL, title TEXT NOT NULL, author TEXT NOT NULL, page_count INTEGER, duration_minutes INTEGER, narrator TEXT); INSERT INTO single_table_work VALUES (1,'print','Blindness','José Saramago',352,NULL,NULL), (2,'print','The Disconnected','Oğuz Atay',724,NULL,NULL), (3,'audio','The Book of Sand','Jorge Luis Borges',NULL,214,'Nina Brooks'), (4,'audio','Yaban','Yakup Kadri',NULL,398,'Leo Marsh'); -- 2. Table per concrete type: shared columns repeat in every table CREATE TABLE concrete_print (work_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, page_count INTEGER NOT NULL); CREATE TABLE concrete_audio (work_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, duration_minutes INTEGER NOT NULL, narrator TEXT NOT NULL); INSERT INTO concrete_print VALUES (1,'Blindness','José Saramago',352),(2,'The Disconnected','Oğuz Atay',724); INSERT INTO concrete_audio VALUES (3,'The Book of Sand','Jorge Luis Borges',214,'Nina Brooks'), (4,'Yaban','Yakup Kadri',398,'Leo Marsh'); -- 3. Table per type: shared fields on top, subtype fields in the child relation CREATE TABLE work (work_id INTEGER PRIMARY KEY, kind TEXT NOT NULL, title TEXT NOT NULL, author TEXT NOT NULL); CREATE TABLE work_print (work_id INTEGER PRIMARY KEY REFERENCES work(work_id), page_count INTEGER NOT NULL); CREATE TABLE work_audio (work_id INTEGER PRIMARY KEY REFERENCES work(work_id), duration_minutes INTEGER NOT NULL, narrator TEXT NOT NULL); INSERT INTO work VALUES (1,'print','Blindness','José Saramago'),(2,'print','The Disconnected','Oğuz Atay'), (3,'audio','The Book of Sand','Jorge Luis Borges'),(4,'audio','Yaban','Yakup Kadri'); INSERT INTO work_print VALUES (1,352),(2,724); INSERT INTO work_audio VALUES (3,214,'Nina Brooks'),(4,398,'Leo Marsh'); SQL ``` All three store the same four works. The difference shows up in the cost of a "fetch all works" request. ```js // inheritance-measure.mjs — query count and null-value count across three mapping strategies import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("inheritance.db"); function measure(name, queries) { let nullValues = 0, rows = 0; for (const s of queries) { for (const r of db.prepare(s).all()) { rows += 1; nullValues += Object.values(r).filter((v) => v === null).length; } } console.log(`${name.padEnd(22)} query=${queries.length} row=${rows} null_value=${nullValues}`); } measure("single table", ["SELECT * FROM single_table_work"]); measure("concrete per type", ["SELECT * FROM concrete_print", "SELECT * FROM concrete_audio"]); measure("per type (joined)", [ `SELECT e.work_id, e.kind, e.title, e.author, b.page_count, s.duration_minutes, s.narrator FROM work e LEFT JOIN work_print b ON b.work_id = e.work_id LEFT JOIN work_audio s ON s.work_id = e.work_id`, ]); ``` ```sh node inheritance-measure.mjs ``` ``` single table query=1 row=4 null_value=6 concrete per type query=2 row=4 null_value=0 per type (joined) query=1 row=4 null_value=6 ``` The numbers give the character of the three strategies. **Single table** reads with one query but produces null values; every column specific to a subtype stays `NULL` on the other subtype's rows. Subtype-specific fields cannot be made `NOT NULL`, because the constraint applies to every row. **Table per concrete type** produces no null values at all and allows the `NOT NULL` constraint; its cost is the query count, and every question asked through the supertype needs a union. **Table per type** reflects the hierarchy in the schema one to one; it asks for an outer join on read and an insert into two tables on write. Table per concrete type costs one more constraint: a foreign key that refers to the whole hierarchy cannot be built. ```sh sqlite3 inheritance.db <<'SQL' PRAGMA foreign_keys = ON; CREATE TABLE reservation (reservation_id INTEGER PRIMARY KEY, work_id INTEGER NOT NULL REFERENCES concrete_print(work_id), member_id INTEGER NOT NULL); INSERT INTO reservation VALUES (1, 1, 3); INSERT INTO reservation VALUES (2, 3, 3); SQL ``` ``` Runtime error near line 5: FOREIGN KEY constraint failed (19) ``` The reservation on the print work went through; the one on the audio work was rejected. A foreign key can point at only one relation; once the hierarchy is split across two relations, referential integrity is handed off to the application. If a reference to the supertype is needed, the table-per-type strategy is chosen, because there the `work` relation represents the whole hierarchy. #### Relation Direction Mismatch In an object graph, a link is one-way; if both directions are wanted, two separate references are kept, and the two are kept consistent by hand. In the relational model, a single foreign key column answers both directions. ```js // direction-difference.mjs — two directions on the object side, one foreign key on the relational side import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("library.db"); // Object graph: a two-way link. Both sides are held by hand. const member = { id: 2, firstName: "Ben", loans: [] }; const loan = { id: 99, bookId: 6, member: null }; loan.member = member; // only one direction was set console.log("loan.member.id:", loan.member.id); console.log("member.loans.length:", member.loans.length); member.loans.push(loan); // the second direction is added by hand console.log("after the second direction is set:", member.loans.length); // Relational side: a single column carries both directions. db.exec("CREATE TEMP TABLE temp AS SELECT * FROM loan"); db.prepare("INSERT INTO temp VALUES (?,?,?,?,NULL)").run(99, 6, 2, "2025-07-01"); const forward = db.prepare("SELECT member_id FROM temp WHERE loan_id = 99").get(); const backward = db.prepare("SELECT loan_id FROM temp WHERE member_id = 2 ORDER BY loan_id").all(); console.log("forward direction (loan -> member):", forward.member_id); console.log("backward direction (member -> loan):", backward.map((r) => r.loan_id).join(", ")); ``` ```sh node direction-difference.mjs ``` ``` loan.member.id: 2 member.loans.length: 0 after the second direction is set: 1 forward direction (loan -> member): 2 backward direction (member -> loan): 3, 11, 99 ``` On the object side, setting one direction left the other empty; on the relational side, a single row answered both questions. The mapping decision that closes this gap is **choosing the owner of the relation**: which side's write changes the foreign key, and the other side only reads. If no owner is chosen, two updates coming from the two sides contradict each other, and which one lands on disk depends on call order. In many-to-many relations the mismatch is even more visible. The link built with two lists on the object side has no relational counterpart; the junction table introduced in the SQL Fundamentals course is required. If the junction table has its own attributes (such as a pickup date), it is no longer just a link but an entity in its own right, and it must be reflected as an entity in the object model too. #### Granularity Mismatch In the object model, every concept can be its own type. If a member's address is represented with an `Address` type, that type has no identity; two addresses are the same if they carry the same fields. Types with no identity, defined only by their value, are called **value objects**. In the relational model, every table has a primary key. Give a value object a table, and you have invented an identity it does not have; do not give it one, and you embed its fields inside the owning relation. The second decision is called **embedding**, and it is usually the right one: the `member` relation carries the `address_city`, `address_district`, and `address_postal_code` columns, and the mapper builds the `Address` type from these three columns. The criterion for the decision is sharing. If a value object is shared by more than one owner and needs to be updated from a single point, embedding is not enough; the concept is then really an entity and deserves an identity. In the library example, the address is specific to the member and gets embedded; the branch, by contrast, is shared by books and staff, and stays in its own relation. #### Summary - The object–relational impedance mismatch shows up at four points — identity, inheritance, relation direction, and granularity — and each one is closed by a separate mapping decision. - Reading the same row twice produced two separate objects; the identity map restored the one-to-one link between a row and an object. - The three inheritance strategies were measured: single table produced 1 query and 6 null values, table per concrete type produced 2 queries and 0 null values, and table per type produced 1 query and 6 null values. - In the table-per-concrete-type strategy, a foreign key referring to the whole hierarchy could not be built; referential integrity was handed off to the application. - The relation direction mismatch is closed by choosing an owner; the granularity mismatch is closed by embedding the value object into the owning relation. #### Next Step So far, the decisions that close the mismatch have been scattered through the calling code: which strategy was chosen, which side owns a relation, which field is embedded — this information repeats everywhere a query is written. The structure that gathers this information in one place, leaving the caller an interface that speaks only in domain concepts, is called the repository pattern. The next lesson builds this interface, and shows that the persistence detail is truly hidden by testing the business logic without a database, through a fake implementation of the repository. ### Repository Pattern Source: https://academia.sh/en/courses/data-access-layer/repository-pattern The repository pattern, which gathers the persistence detail behind a single interface: testing business rules with a fake repository and no database, applying the same contract to two implementations, and measuring the persistence leak per module. The previous lesson showed that the object model and the relational model do not line up at four points, and named the decision that closes each mismatch. Unless these decisions are written down somewhere, they are made again at every call site that writes a query: which strategy brought down the inheritance hierarchy, which side owns a relation, which field is embedded — this information scatters through the code. This lesson's subject is the interface that gathers that information in one place. A **repository** is a boundary that speaks to the caller only in domain concepts and hides how persistence actually happens. Whether the boundary is truly closed is not shown by asserting it, but by testing it: if the business rules run without a database, the boundary is closed. #### The Repository's Promise A repository is not a tool for writing queries. The caller does not ask it for a table, a column, or a join; it asks for a loan record. The five operations below are the entire set of points where the loan service needs data. | Operation | Meaning | |---|---| | `find(loanId)` | Returns the loan record with the given identifier, or `null` | | `booksOpenRecord(bookId)` | Returns the book's unreturned record, if one exists | | `membersOpenRecords(memberId)` | The list of the member's unreturned records | | `add(record)` | Stores a new record and returns its identifier | | `markReturned(loanId, date)` | Moves the record into the closed state | There is no `SELECT` in this list, no connection, no row. This is the repository's definition: **the interface is written in the language of the domain, the implementation in the language of persistence.** #### The Rules Do Not Depend on the Repository Issuing a loan carries two rules: a book already on loan cannot be issued again, and a member cannot hold more than three books at once. These rules contain no persistence detail whatsoever. ```js // loan-service.mjs — business rules; contains no SQL, no driver name export const MEMBER_LIMIT = 3; export class LoanService { constructor(repository) { this.repository = repository; } issue(memberId, bookId, date) { if (this.repository.booksOpenRecord(bookId) !== null) { return { status: "rejected", reason: "book is already on loan" }; } const open = this.repository.membersOpenRecords(memberId); if (open.length >= MEMBER_LIMIT) { return { status: "rejected", reason: `member limit exceeded (${MEMBER_LIMIT})` }; } const id = this.repository.add({ bookId, memberId, pickupDate: date, returnDate: null }); return { status: "issued", loanId: id }; } takeReturn(loanId, date) { const record = this.repository.find(loanId); if (record === null) return { status: "not found" }; if (record.returnDate !== null) return { status: "already returned" }; this.repository.markReturned(loanId, date); return { status: "returned" }; } } ``` The service receives its repository through the constructor. It does not build a repository itself and does not know which one will arrive. This detail carries the whole lesson: receiving the repository from the outside makes it possible to hand it a different repository during testing. #### Fake Repository The smallest implementation that satisfies the contract is an array. ```js // memory-store.mjs — fake implementation of the repository; no database export class MemoryLoanRepository { constructor(initial = []) { this.records = initial.map((k) => ({ ...k })); this.nextId = this.records.length + 1; } find(loanId) { return this.records.find((k) => k.loanId === loanId) ?? null; } booksOpenRecord(bookId) { return this.records.find((k) => k.bookId === bookId && k.returnDate === null) ?? null; } membersOpenRecords(memberId) { return this.records.filter((k) => k.memberId === memberId && k.returnDate === null); } add(record) { const loanId = this.nextId++; this.records.push({ loanId, ...record }); return loanId; } markReturned(loanId, date) { const k = this.find(loanId); if (k !== null) k.returnDate = date; } } ``` With the fake repository, the business rules can be tested directly. The test file uses the `loan-service.mjs` and `memory-store.mjs` modules defined by the previous two blocks. ```js // service.test.mjs — business rules are tested with the fake repository import { test } from "node:test"; import assert from "node:assert/strict"; import { LoanService } from "./loan-service.mjs"; import { MemoryLoanRepository } from "./memory-store.mjs"; test("a book with no open loan is issued", () => { const service = new LoanService(new MemoryLoanRepository()); assert.deepEqual(service.issue(1, 5, "2025-07-01"), { status: "issued", loanId: 1 }); }); test("a book already on loan is rejected", () => { const repository = new MemoryLoanRepository([ { loanId: 1, bookId: 5, memberId: 2, pickupDate: "2025-06-01", returnDate: null }, ]); const result = new LoanService(repository).issue(1, 5, "2025-07-01"); assert.equal(result.status, "rejected"); assert.match(result.reason, /already on loan/); }); test("exceeding the member limit is rejected", () => { const repository = new MemoryLoanRepository([ { loanId: 1, bookId: 1, memberId: 4, pickupDate: "2025-06-01", returnDate: null }, { loanId: 2, bookId: 2, memberId: 4, pickupDate: "2025-06-02", returnDate: null }, { loanId: 3, bookId: 3, memberId: 4, pickupDate: "2025-06-03", returnDate: null }, ]); const result = new LoanService(repository).issue(4, 7, "2025-07-01"); assert.equal(result.status, "rejected"); assert.equal(result.reason, "member limit exceeded (3)"); }); test("a returned book can be issued again", () => { const repository = new MemoryLoanRepository([ { loanId: 1, bookId: 5, memberId: 2, pickupDate: "2025-06-01", returnDate: null }, ]); const service = new LoanService(repository); assert.equal(service.takeReturn(1, "2025-06-20").status, "returned"); assert.equal(service.issue(1, 5, "2025-07-01").status, "issued"); }); ``` ```sh node --test service.test.mjs ``` ``` ✔ a book with no open loan is issued (0.905208ms) ✔ a book already on loan is rejected (0.105625ms) ✔ exceeding the member limit is rejected (0.050667ms) ✔ a returned book can be issued again (0.059709ms) ℹ tests 4 ℹ suites 0 ℹ pass 4 ℹ fail 0 ℹ cancelled 0 ℹ skipped 0 ℹ todo 0 ℹ duration_ms 37.269625 ``` The durations in parentheses and the `duration_ms` field change on every run; what matters is the `pass 4` and `fail 0` lines. Four rules were tested without a database file, without setting up a schema, and without opening a single connection. The test's setup step comes down to writing an array of records; the starting state that feeds the rules can be read directly. #### The Fake's Fidelity A fake repository carries a risk: if it behaves differently from the real implementation, the tests pass but the application does not work. The method that closes this risk is **applying the same test suite to both implementations**. The real implementation comes first. ```js // sqlite-store.mjs — real implementation of the repository; all SQL is gathered here const TO_RECORD = (s) => (s === undefined ? null : { loanId: s.loan_id, bookId: s.book_id, memberId: s.member_id, pickupDate: s.pickup_date, returnDate: s.return_date, }); export class SqliteLoanRepository { constructor(db) { this.db = db; } find(loanId) { return TO_RECORD(this.db.prepare("SELECT * FROM loan WHERE loan_id = ?").get(loanId)); } booksOpenRecord(bookId) { return TO_RECORD(this.db.prepare( "SELECT * FROM loan WHERE book_id = ? AND return_date IS NULL").get(bookId)); } membersOpenRecords(memberId) { return this.db.prepare( "SELECT * FROM loan WHERE member_id = ? AND return_date IS NULL ORDER BY loan_id") .all(memberId).map(TO_RECORD); } add(record) { const result = this.db.prepare( "INSERT INTO loan (book_id, member_id, pickup_date, return_date) VALUES (?,?,?,?)") .run(record.bookId, record.memberId, record.pickupDate, record.returnDate); return Number(result.lastInsertRowid); } markReturned(loanId, date) { this.db.prepare("UPDATE loan SET return_date = ? WHERE loan_id = ?").run(date, loanId); } } ``` The implementation also receives its connection from the outside. If it kept the file name inside itself, it could not be pointed at an in-memory database during testing. ```js // contract.test.mjs — the same test suite is applied to both implementations import { test, describe } from "node:test"; import assert from "node:assert/strict"; import { DatabaseSync } from "node:sqlite"; import { MemoryLoanRepository } from "./memory-store.mjs"; import { SqliteLoanRepository } from "./sqlite-store.mjs"; const INITIAL = [ { loanId: 1, bookId: 5, memberId: 2, pickupDate: "2025-06-01", returnDate: null }, { loanId: 2, bookId: 6, memberId: 2, pickupDate: "2025-06-02", returnDate: "2025-06-25" }, ]; const setUpMemory = () => new MemoryLoanRepository(INITIAL); function setUpSqlite() { const db = new DatabaseSync(":memory:"); db.exec(`CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT)`); for (const k of INITIAL) { db.prepare("INSERT INTO loan VALUES (?,?,?,?,?)") .run(k.loanId, k.bookId, k.memberId, k.pickupDate, k.returnDate); } return new SqliteLoanRepository(db); } for (const [name, setUp] of [["memory", setUpMemory], ["sqlite", setUpSqlite]]) { describe(`repository contract: ${name}`, () => { test("an open record is found, a closed record is not", () => { const d = setUp(); assert.equal(d.booksOpenRecord(5).loanId, 1); assert.equal(d.booksOpenRecord(6), null); }); test("a member's open records are only the open ones", () => { assert.deepEqual(setUp().membersOpenRecords(2).map((k) => k.loanId), [1]); }); test("an added record gets a new identifier and can be found", () => { const d = setUp(); const id = d.add({ bookId: 7, memberId: 3, pickupDate: "2025-07-01", returnDate: null }); assert.equal(d.find(id).bookId, 7); }); test("marking a return closes the open record", () => { const d = setUp(); d.markReturned(1, "2025-07-02"); assert.equal(d.booksOpenRecord(5), null); }); test("a nonexistent identifier returns null", () => { assert.equal(setUp().find(999), null); }); }); } ``` ```sh node --test contract.test.mjs ``` ``` ▶ repository contract: memory ✔ an open record is found, a closed record is not (0.665792ms) ✔ a member's open records are only the open ones (0.293875ms) ✔ an added record gets a new identifier and can be found (0.079417ms) ✔ marking a return closes the open record (0.080666ms) ✔ a nonexistent identifier returns null (0.049959ms) ✔ repository contract: memory (1.656583ms) ▶ repository contract: sqlite ✔ an open record is found, a closed record is not (0.361625ms) ✔ a member's open records are only the open ones (0.122083ms) ✔ an added record gets a new identifier and can be found (0.135166ms) ✔ marking a return closes the open record (0.08875ms) ✔ a nonexistent identifier returns null (0.093833ms) ✔ repository contract: sqlite (0.925875ms) ℹ tests 10 ℹ suites 2 ℹ pass 10 ℹ fail 0 ℹ cancelled 0 ℹ skipped 0 ℹ todo 0 ℹ duration_ms 38.271041 ``` Ten tests, two implementations, zero failures. This test suite is the **repository's contract**; it reaffirms on every run that the fake stays faithful to the real thing. When a new implementation is added — another data store, a caching layer, a version that keeps records in a file — the same suite is applied to it too. The contract test has a limit. Isolation level, locking behavior, and concurrent update conflicts cannot be produced inside an in-memory array; these are tested only against the real implementation. The fake repository's scope is **the business rules**, not persistence behavior. #### Measuring the Leak How closed the boundary is can be counted. How many times, and in which module, do persistence traces — SQL keywords, the driver type, the prepared-statement call — show up? For comparison, a version that does the same job without a repository is also worth writing. ```js // leaky-service.mjs — version that uses a direct connection instead of the repository export class LeakyLoanService { constructor(db) { this.db = db; } issue(memberId, bookId, date) { const openBook = this.db.prepare( "SELECT 1 FROM loan WHERE book_id = ? AND return_date IS NULL").get(bookId); if (openBook !== undefined) return { status: "rejected", reason: "book is already on loan" }; const count = this.db.prepare( "SELECT count(*) AS n FROM loan WHERE member_id = ? AND return_date IS NULL").get(memberId).n; if (count >= 3) return { status: "rejected", reason: "member limit exceeded (3)" }; const result = this.db.prepare( "INSERT INTO loan (book_id, member_id, pickup_date, return_date) VALUES (?,?,?,NULL)") .run(bookId, memberId, date); return { status: "issued", loanId: Number(result.lastInsertRowid) }; } } ``` ```js // leak-measure.mjs — how many persistence traces are in which module import { readFileSync } from "node:fs"; const TRACES = /\b(SELECT|INSERT|UPDATE|DELETE|prepare|DatabaseSync)\b/g; for (const file of ["loan-service.mjs", "leaky-service.mjs", "sqlite-store.mjs", "memory-store.mjs"]) { const trace = readFileSync(file, "utf8").match(TRACES) ?? []; console.log(`${file.padEnd(22)} persistence trace = ${trace.length}`); } ``` ```sh node leak-measure.mjs ``` ``` loan-service.mjs persistence trace = 0 leaky-service.mjs persistence trace = 6 sqlite-store.mjs persistence trace = 10 memory-store.mjs persistence trace = 0 ``` The numbers show the distribution. In the service that uses the repository, the trace count is zero; all of persistence is gathered into a single module, as ten traces. In the leaky service, six traces are mixed in among the business rules. These six traces are the reason that service cannot be tested with a fake repository: the `prepare` call asks for a capability an array has no counterpart for. The measurement is a blunt instrument, not conclusive proof. A module can bind itself to persistence without writing SQL; a `find` call relying on the database's default ordering for the order it returns rows in is a dependency too. The number's job is to make where the boundary is drawn visible to the eye. #### The Repository's Boundary and Query Proliferation Running the same service with the real repository asks for no further code change. ```js // run-with-real.mjs — the same service, this time with a repository connected to the database import { DatabaseSync } from "node:sqlite"; import { LoanService } from "./loan-service.mjs"; import { SqliteLoanRepository } from "./sqlite-store.mjs"; const repository = new SqliteLoanRepository(new DatabaseSync("library.db")); const service = new LoanService(repository); console.log("book 1 (has an open record):", JSON.stringify(service.issue(6, 1, "2025-07-15"))); console.log("book 7 (free): ", JSON.stringify(service.issue(6, 7, "2025-07-15"))); console.log("member 4 open record count: ", repository.membersOpenRecords(4).length); ``` This block expects the `library.db` file carrying the library schema from the SQL Fundamentals course. The command below sets up the schema and the rows this lesson runs against. ```sh rm -f library.db sqlite3 library.db <<'SQL' CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT); INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'), (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'), (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'), (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'), (12,4,4,'2025-06-20','2025-07-04'); SQL node run-with-real.mjs ``` ``` book 1 (has an open record): {"status":"rejected","reason":"book is already on loan"} book 7 (free): {"status":"issued","loanId":13} member 4 open record count: 1 ``` Book 1's request was rejected because it has an open record; the free book 7 was issued and got the identifier thirteen. Not a single line changed in the service code. The repository pattern's cost shows up here. Because the interface is written in the domain's language, every new question asks for a new method: overdue records, open records at a given branch, records issued within a date range, both overdue and at a given branch. Because criteria can be combined, the method count grows with the combination of criteria. Past a certain point, the repository interface starts to resemble the query language it was trying to hide. #### Summary - A repository is an interface that speaks to the caller in domain concepts; it keeps the query, the connection, and the row-to-object conversion inside itself. - The service receiving its repository from the outside makes it possible to hand it an in-memory fake repository during testing; four business rules were tested without a database. - The same contract test was applied to both implementations, and all ten tests passed; this is the continuous verification that the fake stays faithful to the real thing. - Counting persistence traces gave zero in the service that uses the repository and six in the leaky service; this is how the boundary's closure is made visible. - Isolation and concurrency behavior cannot be produced in the fake repository; the fake's scope is the business rules. #### Next Step As the repository's interface closed off, the number of questions to ask grew, and every question asked for a new method. Overdue records, records filtered by branch, records limited to a date range, and their combinations bloat the repository when they are each written out separately. The problem is not in the criteria themselves; it is in the criteria being embedded into fixed method names on the interface. The next lesson turns a criterion into an object: each criterion produces its own SQL fragment and bound parameters, criteria are combined with "and", "or", and "not", and the repository meets every combination through a single method. ### Query Objects and Specifications Source: https://academia.sh/en/courses/data-access-layer/query-objects-and-specifications Turning a criterion into an object: each specification produces its own SQL fragment and bound values, combines with and-or-not, the same criterion works as an in-memory predicate, and identifiers are limited with an allowlist. The repository pattern closed the boundary, but it bloated the interface. Every new question asked for a new method: open records, a member's open records, records within a date range, a member's open records within a specific date range. Because criteria can be combined, the method count grows with the combination of criteria. The problem is not in the criteria; it is in the criteria being embedded in a method name. Inside the name `membersOpenRecords`, two criteria and a combinator are hidden; none of them can be used on its own. This lesson takes the criterion out of the name and makes it an object. A criterion that is an object can be combined; a combined criterion produces a single query. #### The Criterion Becomes an Object A **specification** carries three things: an SQL fragment, the values that correspond to the placeholders in that fragment, and a predicate that is the same criterion's in-memory counterpart. ```js // specification.mjs — every specification carries both an SQL fragment and an in-memory predicate const specification = (sql, values, predicate) => ({ sql, values, predicate }); export const open = () => specification("return_date IS NULL", [], (k) => k.returnDate === null); export const byMember = (memberId) => specification("member_id = ?", [memberId], (k) => k.memberId === memberId); export const byBook = (bookId) => specification("book_id = ?", [bookId], (k) => k.bookId === bookId); export const pickedUpBetween = (start, end) => specification("pickup_date BETWEEN ? AND ?", [start, end], (k) => k.pickupDate >= start && k.pickupDate <= end); export const and = (...specs) => specification(`(${specs.map((x) => x.sql).join(" AND ")})`, specs.flatMap((x) => x.values), (k) => specs.every((x) => x.predicate(k))); export const or = (...specs) => specification(`(${specs.map((x) => x.sql).join(" OR ")})`, specs.flatMap((x) => x.values), (k) => specs.some((x) => x.predicate(k))); export const not = (spec) => specification(`NOT ${spec.sql}`, spec.values, (k) => !spec.predicate(k)); ``` The combinators' work is in three steps: they join the sub-specifications' SQL fragments inside parentheses, concatenate the value arrays end to end **in the same order**, and connect the predicates logically. The second step is critical. Whatever order the placeholders appear in the text, the values array must follow the same order; `flatMap` preserves this order because it keeps the same order as the `map` call inside it. #### Making the Generated Query Visible A specification's correctness is checked by looking at the text it produces. ```js // specification-print.mjs — the SQL and bound values a composite specification produces import { open, byMember, byBook, pickedUpBetween, and, or, not } from "./specification.mjs"; const examples = { "open": open(), "member's open records": and(byMember(4), open()), "not open, first half of 2025": and(not(open()), pickedUpBetween("2025-01-01", "2025-06-30")), "member 1 or member 2, book 1": and(or(byMember(1), byMember(2)), byBook(1)), }; for (const [name, b] of Object.entries(examples)) { console.log(name); console.log(" sql :", b.sql); console.log(" values:", JSON.stringify(b.values)); } ``` ```sh node specification-print.mjs ``` ``` open sql : return_date IS NULL values: [] member's open records sql : (member_id = ? AND return_date IS NULL) values: [4] not open, first half of 2025 sql : (NOT return_date IS NULL AND pickup_date BETWEEN ? AND ?) values: ["2025-01-01","2025-06-30"] member 1 or member 2, book 1 sql : ((member_id = ? OR member_id = ?) AND book_id = ?) values: [1,2,1] ``` The last example shows how the order is preserved: three placeholders, three values, the first two from the inner `or` specification, the third from the outer `byBook` specification. In the third example, the `not` combinator carries no value at all, because the specification it wraps carried none either; the values array therefore holds only the two dates. If the parenthesization is skipped, the error stays silent. If `or`'s output had no parentheses, `(A OR B) AND C` would read as `A OR (B AND C)`; the SQL would still be valid, the result would still be non-empty, but it would be wrong. #### Single-Method Repository Once a criterion becomes an object, the repository's interface shrinks. Together with requests other than filtering — sorting, limit, offset — the criterion is gathered into a single record. This record is called a **query object**. ```js // query-store.mjs — a single search method; the criterion arrives from outside const TO_RECORD = (s) => ({ loanId: s.loan_id, bookId: s.book_id, memberId: s.member_id, pickupDate: s.pickup_date, returnDate: s.return_date }); export class QueryRepository { constructor(db) { this.db = db; this.generated = []; } search(query) { const { specification, orderBy = "loan_id", direction = "ASC", limit = null, offset = 0 } = query; let sql = `SELECT * FROM loan WHERE ${specification.sql} ORDER BY ${orderBy} ${direction}`; const values = [...specification.values]; if (limit !== null) { sql += " LIMIT ? OFFSET ?"; values.push(limit, offset); } this.generated.push({ sql, values }); return this.db.prepare(sql).all(...values).map(TO_RECORD); } } ``` Four separate requests pass through a single method. ```js // repository-run.mjs — the same repository method meets three different criteria import { DatabaseSync } from "node:sqlite"; import { QueryRepository } from "./query-store.mjs"; import { open, byMember, byBook, pickedUpBetween, and, or, not } from "./specification.mjs"; const repository = new QueryRepository(new DatabaseSync("library.db")); const requests = [ ["all open records", { specification: open() }], ["member 4's open records", { specification: and(byMember(4), open()) }], ["closed, first half of 2025, most recent", { specification: and(not(open()), pickedUpBetween("2025-01-01", "2025-06-30")), orderBy: "pickup_date", direction: "DESC", limit: 2 }], ["book 1 picked up by member 1 or 2", { specification: and(or(byMember(1), byMember(2)), byBook(1)) }], ]; for (const [name, query] of requests) { const result = repository.search(query); console.log(`${name}: ${result.map((k) => k.loanId).join(", ") || "(empty)"}`); } console.log("\ngenerated queries:"); for (const u of repository.generated) console.log(` ${u.sql}\n <- ${JSON.stringify(u.values)}`); ``` The command below first sets up the loan relation of the library schema, then runs the script. ```sh rm -f library.db sqlite3 library.db <<'SQL' CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT); INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'), (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'), (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'), (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'), (12,4,4,'2025-06-20','2025-07-04'); SQL node repository-run.mjs ``` ``` all open records: 3, 7, 10 member 4's open records: 7 closed, first half of 2025, most recent: 12, 11 book 1 picked up by member 1 or 2: 1, 3 generated queries: SELECT * FROM loan WHERE return_date IS NULL ORDER BY loan_id ASC <- [] SELECT * FROM loan WHERE (member_id = ? AND return_date IS NULL) ORDER BY loan_id ASC <- [4] SELECT * FROM loan WHERE (NOT return_date IS NULL AND pickup_date BETWEEN ? AND ?) ORDER BY pickup_date DESC LIMIT ? OFFSET ? <- ["2025-01-01","2025-06-30",2,0] SELECT * FROM loan WHERE ((member_id = ? OR member_id = ?) AND book_id = ?) ORDER BY loan_id ASC <- [1,2,1] ``` The third query shows that the limit and the offset are also added as bound values: after the criterion's two dates come `2` and `0`. The `generated` array is this layer's counterpart to the counting hook built in the first lesson; which criterion turned into which text can be seen at run time. #### The Same Criterion, Two Places The specification's second field — the predicate — has not been used yet. Its value is that the criterion can be asked without going to the database. Whether an unsaved candidate record satisfies the rule is tested in memory. ```js // validate-in-memory.mjs — the same specification applied to a candidate record without touching the database import { open, byMember, pickedUpBetween, and } from "./specification.mjs"; const rule = and(byMember(4), open(), pickedUpBetween("2025-01-01", "2025-12-31")); const candidates = [ { loanId: null, memberId: 4, bookId: 7, pickupDate: "2025-07-15", returnDate: null }, { loanId: null, memberId: 5, bookId: 7, pickupDate: "2025-07-15", returnDate: null }, { loanId: null, memberId: 4, bookId: 7, pickupDate: "2024-12-30", returnDate: null }, ]; for (const a of candidates) { console.log(`member=${a.memberId} pickup=${a.pickupDate} -> ${rule.predicate(a)}`); } console.log("the same specification's SQL counterpart:", rule.sql); ``` ```sh node validate-in-memory.mjs ``` ``` member=4 pickup=2025-07-15 -> true member=5 pickup=2025-07-15 -> false member=4 pickup=2024-12-30 -> false the same specification's SQL counterpart: (member_id = ? AND return_date IS NULL AND pickup_date BETWEEN ? AND ?) ``` None of the three candidates is in the database; their identifiers being `null` shows this. The rule was applied anyway. Defining the rule in a single place prevents the query and the check from drifting apart: if two definitions are written, one goes silently stale when the other changes. The cost of this duality is that the two definitions must stay consistent. In the `pickedUpBetween` specification, the SQL side uses `BETWEEN`, and the predicate side does two separate comparisons; both must include the boundary values. Every time such a pair is written, it must be tested that the two sides give the same result. #### Bound Value and Identifier Specifications carrying their values through a placeholder is not a choice; it is a requirement. The difference between the same input entering a query through two different paths can be measured. ```js // binding.mjs — the difference a bound value and string concatenation make on the same input import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("library.db"); const input = "2 OR 1=1"; const bound = db.prepare("SELECT count(*) AS n FROM loan WHERE member_id = ?").get(input).n; const concatenated = db.prepare(`SELECT count(*) AS n FROM loan WHERE member_id = ${input}`).get().n; console.log("rows returned with a bound value :", bound); console.log("rows returned with string concatenation:", concatenated); console.log("total rows in the table :", db.prepare("SELECT count(*) AS n FROM loan").get().n); ``` ```sh node binding.mjs ``` ``` rows returned with a bound value : 0 rows returned with string concatenation: 12 total rows in the table : 12 ``` The input sent as a bound value settled into the right-hand side of a comparison, not into the query's structure; no row matched. With string concatenation, the same input became the `OR 1=1` condition and returned the whole table. This is the application layer's view of the SQL injection introduced in the Advanced SQL course. Binding does not work everywhere. Table and column names — that is, **identifiers** — cannot be bound values; the query plan cannot be built without knowing them. The `orderBy` and `direction` fields in the `QueryRepository` class entered the text directly. If a value coming from outside reaches these fields, the check is done with a list. ```js // identifier-check.mjs — the sort column cannot be bound, so it is limited with an allowlist import { DatabaseSync } from "node:sqlite"; const SORTABLE = new Set(["loan_id", "pickup_date", "return_date"]); const DIRECTIONS = new Set(["ASC", "DESC"]); function safeSort(db, orderBy, direction) { if (!SORTABLE.has(orderBy)) throw new Error(`cannot sort by column: ${orderBy}`); if (!DIRECTIONS.has(direction)) throw new Error(`invalid direction: ${direction}`); return db.prepare(`SELECT loan_id FROM loan ORDER BY ${orderBy} ${direction} LIMIT 3`).all() .map((r) => r.loan_id); } const db = new DatabaseSync("library.db"); console.log("pickup_date DESC:", safeSort(db, "pickup_date", "DESC").join(", ")); for (const bad of ["member_id", "loan_id; DROP TABLE loan"]) { try { safeSort(db, bad, "ASC"); } catch (h) { console.log("rejected:", h.message); } } console.log("the loan table is still in place, row count:", db.prepare("SELECT count(*) AS n FROM loan").get().n); ``` ```sh node identifier-check.mjs ``` ``` pickup_date DESC: 12, 11, 10 rejected: cannot sort by column: member_id rejected: cannot sort by column: loan_id; DROP TABLE loan the loan table is still in place, row count: 12 ``` The allowlist approach is safer than searching for escape characters, because it counts what is permitted; it does not try to guess what is forbidden. `member_id`, which is not on the list, was rejected too: even though this column is valid, it does not pass if it is not on the list. #### The Pattern's Limit A specification makes a criterion portable but does not hide the schema. The `member_id` column name appears inside the specification; if this name changes in the schema, the specification changes too. This is the repository's promise pulled back one step: the caller no longer writes a query, but it knows the field names. The trade-off is accepted knowingly; the alternative is writing a separate repository method for every combination of criteria. The second limit is scope. These specifications work on a single relation. Criteria that need a join (filtering by branch, for instance) require the specification to affect not only the `WHERE` fragment but the `FROM` fragment too; this complicates the pattern quickly. Multi-table reports stay on the direct-query side of the split made in the first lesson. #### Summary - Making a criterion an object instead of embedding it in a method name lets criteria be combined with and-or-not and pass through a single repository method. - Every specification produces its own SQL fragment and bound values; the combinators keep the value order the same as the placeholder order in the SQL text. - Alongside the criterion, the query object also carries sorting, limit, and offset; the generated text and values can be verified by printing them. - The same specification's predicate field checks an unsaved candidate without going to the database; the rule stays defined in a single place. - Values are bound, identifiers cannot be: the sort column and direction are limited with an allowlist. #### Next Step Queries are now generated from a single point, but they all need the same thing: a connection. So far, every script has opened its own connection and the process ended once the work was done. A continuously running service cannot behave this way; opening a connection is expensive, and the number of concurrent connections a database can accept is limited. The next lesson builds a pool that opens connections ahead of time and lends them out, measures the relationship between pool size and queue wait time with parallel calls by varying the pool size, and shows where the saturation point appears. ### Connection Pool Source: https://academia.sh/en/courses/data-access-layer/connection-pool Managing a connection as an expensive resource: measuring the opening cost, a fixed-size pool and queue, measuring the relationship between pool size and wait time with parallel requests, the queue timeout at saturation, and carrying over session state. The previous two lessons made the query generated from a single point. One more thing is needed for the query to run: a connection. So far, every script has opened its own connection, finished its job, and the process ended. A continuously running service cannot behave this way. Opening a connection is not free, and the number of concurrent connections a database will accept is limited. A **connection pool** is a structure that keeps already-opened connections and lends them out to requests. This lesson builds the pool, measures how long requests wait in the queue by varying the pool size, and shows the two traps the pool brings. #### Opening Cost The first question is whether the cost really exists. ```js // opening-cost.mjs — opening a connection on every request versus reusing a single connection import { DatabaseSync } from "node:sqlite"; const N = 2000; const QUERY = "SELECT count(*) AS n FROM loan WHERE return_date IS NULL"; let t = performance.now(); for (let i = 0; i < N; i++) { const db = new DatabaseSync("library.db"); db.prepare(QUERY).get(); db.close(); } const openClose = performance.now() - t; const db = new DatabaseSync("library.db"); t = performance.now(); for (let i = 0; i < N; i++) db.prepare(QUERY).get(); const reuse = performance.now() - t; db.close(); console.log(`open-close per request : ${openClose.toFixed(0)} ms (${(openClose / N).toFixed(3)} ms/request)`); console.log(`reuse : ${reuse.toFixed(0)} ms (${(reuse / N).toFixed(3)} ms/request)`); console.log(`ratio : ${(openClose / reuse).toFixed(1)}x`); ``` The command below sets up the schema and runs the measurement. ```sh rm -f library.db sqlite3 library.db <<'SQL' CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT); INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(3,1,2,'2025-02-11',NULL), (7,5,4,'2025-04-21',NULL),(10,3,5,'2025-06-03',NULL),(12,4,4,'2025-06-20','2025-07-04'); SQL node opening-cost.mjs ``` ``` open-close per request : 113 ms (0.057 ms/request) reuse : 10 ms (0.005 ms/request) ratio : 11.3x ``` The durations depend on the hardware and the file system; the numbers change on another machine. What matters is the ratio: opening the connection every time to do the same work cost eleven times more than reusing an open connection. This measurement is against a local file. In a database spoken to over the network, the opening cost also includes session setup, authentication, and an encryption handshake; the ratio grows. #### Pool and Queue The pool manages two states: the list of idle connections and the queue of those waiting for a free connection. If an idle connection exists, the request is served immediately; if not, it enters the queue. ```js // pool.mjs — a fixed-size resource pool and its queue export class Pool { constructor(create, size, queueTimeoutMs) { this.idle = Array.from({ length: size }, (_, i) => create(i)); this.queue = []; this.queueTimeoutMs = queueTimeoutMs; this.size = size; } acquire() { if (this.idle.length > 0) return Promise.resolve(this.idle.pop()); return new Promise((resolve, reject) => { const waiter = { resolve, reject }; waiter.timer = setTimeout(() => { this.queue.splice(this.queue.indexOf(waiter), 1); reject(new Error("queue timeout")); }, this.queueTimeoutMs); this.queue.push(waiter); }); } release(connection) { const waiter = this.queue.shift(); if (waiter === undefined) { this.idle.push(connection); return; } clearTimeout(waiter.timer); waiter.resolve(connection); } } ``` The `release` method's behavior deserves attention: the queue is checked before the released connection goes back to the idle list. If someone is waiting in the queue, the connection is handed to them directly. This is both faster than putting it on the idle list and taking it again, and it preserves the waiters' order. The pool must not make a connection wait forever. Once the `queueTimeoutMs` duration elapses, the waiting request is removed from the queue and gets an error. This is a **queue timeout**; in its absence, a database that slows down leads to every request waiting indefinitely and the application coming to a complete halt. #### Pooled Service On every request the service acquires a connection from the pool, runs the query, and releases the connection back in a `finally` block. The `finally` is mandatory: a connection not released in an error case is permanently lost from the pool. ```js // server.mjs — every request takes a connection from the pool, runs the query, releases it back import { createServer } from "node:http"; import { DatabaseSync } from "node:sqlite"; import { Pool } from "./pool.mjs"; const SIZE = Number(process.env.POOL_SIZE ?? 4); const ROUND_TRIP_MS = Number(process.env.ROUND_TRIP_MS ?? 40); const TIMEOUT_MS = Number(process.env.TIMEOUT_MS ?? 2000); const pool = new Pool(() => new DatabaseSync("library.db"), SIZE, TIMEOUT_MS); const measurements = []; const wait = (ms) => new Promise((c) => setTimeout(c, ms)); const server = createServer(async (request, response) => { const path = new URL(request.url, "http://local").pathname; if (path === "/shutdown") { response.end("shutting down\n"); server.close(); return; } if (path === "/report") { const waits = measurements.map((o) => o.wait).sort((x, y) => x - y); const percentile = (p) => (waits.length === 0 ? 0 : +waits[Math.min(waits.length - 1, Math.floor(waits.length * p))].toFixed(1)); response.writeHead(200, { "content-type": "application/json" }); response.end(JSON.stringify({ pool_size: SIZE, requests: measurements.length, rejected: measurements.filter((o) => o.rejected).length, wait_median_ms: percentile(0.5), wait_p95_ms: percentile(0.95), wait_max_ms: waits.length === 0 ? 0 : +waits[waits.length - 1].toFixed(1), })); return; } const queuedAt = performance.now(); let connection; try { connection = await pool.acquire(); } catch { measurements.push({ wait: performance.now() - queuedAt, rejected: true }); response.writeHead(503, { "content-type": "text/plain" }); response.end("pool full\n"); return; } const waitMs = performance.now() - queuedAt; try { const row = connection.prepare( "SELECT count(*) AS n FROM loan WHERE return_date IS NULL").get(); await wait(ROUND_TRIP_MS); // model of the database round trip measurements.push({ wait: waitMs, rejected: false }); response.writeHead(200, { "content-type": "text/plain" }); response.end(`open record: ${row.n} wait_ms: ${waitMs.toFixed(1)}\n`); } finally { pool.release(connection); } }); server.listen(8080, () => console.log(`pool size ${SIZE}, port 8080`)); ``` The `ROUND_TRIP_MS` duration is a model, and it stays a model throughout this lesson. Because the database here runs inside the process, there is no real network round trip; the response time of a database sitting on a separate server is represented by this wait. What is measured is not the round trip itself, but **the effect on the queue of the connection being busy for that duration**. #### Pool Size and Wait Time The measurement setup is simple: send twenty-four requests at the same time, vary the pool size, and look at how long the requests wait in the queue. ```sh # measure.sh — measures the wait time of 24 parallel requests as the pool size varies for size in 1 2 4 8 24; do POOL_SIZE=$size ROUND_TRIP_MS=40 node server.mjs >/dev/null & server=$! sleep 0.6 requests="" start=$(date +%s%N) for i in $(seq 24); do curl -s -o /dev/null http://localhost:8080/loan & requests="$requests $!" done wait $requests end=$(date +%s%N) printf 'total_ms=%s ' $(( (end - start) / 1000000 )) curl -s http://localhost:8080/report echo curl -s -o /dev/null http://localhost:8080/shutdown wait $server done ``` ```sh sh measure.sh ``` ``` total_ms=1035 {"pool_size":1,"requests":24,"rejected":0,"wait_median_ms":493.8,"wait_p95_ms":904.2,"wait_max_ms":945.1} total_ms=533 {"pool_size":2,"requests":24,"rejected":0,"wait_median_ms":241.7,"wait_p95_ms":444.5,"wait_max_ms":445.1} total_ms=284 {"pool_size":4,"requests":24,"rejected":0,"wait_median_ms":115.3,"wait_p95_ms":196.3,"wait_max_ms":196.7} total_ms=161 {"pool_size":8,"requests":24,"rejected":0,"wait_median_ms":37,"wait_p95_ms":76.5,"wait_max_ms":76.5} total_ms=83 {"pool_size":24,"requests":24,"rejected":0,"wait_median_ms":0,"wait_p95_ms":0.1,"wait_max_ms":0.3} ``` The absolute durations depend on the machine; the relationship does not. Every time the pool size doubles, the median wait halves: 494, 242, 115, 37 milliseconds. The reason is directly visible. Twenty-four requests are processed in $\lceil 24/b \rceil$ rounds by a pool of size $b$; each round takes about one service duration, so the average wait in the queue is proportional to the round count. When the pool is sized equal to the request count, the wait drops to zero. This does not give the conclusion "growing the pool is always good." The wait dropping to zero does not mean the work is done faster; it means the queue has moved from the application to the database. The number of concurrent connections and the number of concurrent queries a database can handle are limited; if the pool grows past that limit, the wait accumulates in the database instead of the application, and it becomes harder to measure there. The second point is the multiplication. If the application runs as multiple instances, the total connection count is the number of instances multiplied by the pool size. Eight instances with a pool of twenty connections ask the database for one hundred sixty connections. #### Saturation When every connection in the pool is busy and the queue wait exceeds the timeout, the pool has reached **saturation**. In this case the request is rejected. ```sh # saturation.sh — small pool, short queue timeout: rejected requests POOL_SIZE=2 ROUND_TRIP_MS=40 TIMEOUT_MS=200 node server.mjs >/dev/null & server=$! sleep 0.6 requests="" for i in $(seq 24); do curl -s -o /dev/null -w '%{http_code}\n' http://localhost:8080/loan >> codes.txt & requests="$requests $!" done wait $requests echo "response code distribution:" sort codes.txt | uniq -c rm -f codes.txt curl -s http://localhost:8080/report; echo curl -s -o /dev/null http://localhost:8080/shutdown wait $server ``` ```sh sh saturation.sh ``` ``` response code distribution: 12 200 12 503 {"pool_size":2,"requests":24,"rejected":12,"wait_median_ms":200.4,"wait_p95_ms":201.6,"wait_max_ms":201.7} ``` Half of the twenty-four requests were answered; the other half were rejected with 503 after waiting two hundred milliseconds. The ratio of accepted to rejected requests changes from run to run depending on the machine's load at that moment; what does not change is that the rejected requests' waits cluster just above the timeout value. This is the sign that the limit is working. Rejecting looks like a failure, but the alternative is worse. Without the timeout, these twelve requests would keep waiting, client-side timeouts would kick in, clients would retry, and the queue would grow even longer. A bounded queue produces a fast, readable error. #### Carrying Over Session State The pool's second trap concerns the connection itself. A connection is not just a pipe; it can carry temporary tables, settings, and an open transaction on it. This accumulation is called **session state**, and it passes to the next taker along with a released connection. ```js // session-state.mjs — leftover state on a released connection passes to the next taker import { DatabaseSync } from "node:sqlite"; import { Pool } from "./pool.mjs"; const pool = new Pool(() => new DatabaseSync("library.db"), 1, 1000); const first = await pool.acquire(); first.exec("CREATE TEMP TABLE temp_report (n INTEGER)"); first.exec("INSERT INTO temp_report VALUES (42)"); pool.release(first); // no cleanup was done const second = await pool.acquire(); const leftover = second.prepare( "SELECT count(*) AS n FROM temp.sqlite_master WHERE name = 'temp_report'").get().n; console.log("leftover tables seen by the second taker:", leftover); if (leftover > 0) console.log("value inside it:", second.prepare("SELECT n FROM temp_report").get().n); second.exec("DROP TABLE IF EXISTS temp_report"); // cleanup before release pool.release(second); const third = await pool.acquire(); console.log("leftover tables after cleanup :", third.prepare( "SELECT count(*) AS n FROM temp.sqlite_master WHERE name = 'temp_report'").get().n); ``` ```sh node session-state.mjs ``` ``` leftover tables seen by the second taker: 1 value inside it: 42 leftover tables after cleanup : 0 ``` The second taker saw a table it never created, and the value inside it. This means two requests reading each other's state. There must be a cleanup step on the pool's release path: an open transaction is rolled back, temporary objects are dropped, and session settings return to their defaults. The pooling levels introduced in the Relational Database Administration course are different answers to this problem. In session-level pooling, the connection belongs to the client for the whole session, and session state is preserved. In transaction-level pooling, the connection is taken back at the end of every transaction; this raises pool utilization at the cost of session-bound features such as prepared statements and temporary tables becoming unusable. #### Summary - Opening the connection on every request cost eleven times more in this measurement than reusing an open connection; this difference is the pool's reason for existing. - The pool keeps two structures: the idle connections and the queue of waiters. A released connection is handed to the queue before it goes back to the idle list. - Across twenty-four parallel requests, the median wait halved every time the pool size doubled (494 → 242 → 115 → 37 ms); the wait dropped to zero once the pool was sized equal to the request count. - Growing the pool does not eliminate the wait; it moves the queue to the database. The total connection count is multiplied by the number of application instances. - At saturation, the queue timeout rejects the request quickly; a fast error is preferred over an unbounded queue. - Session state on a released connection passes to the next taker; cleanup on the release path is mandatory. #### Next Step When the pool is built, every connection sees the same schema. This assumption holds until the schema changes. When a column is added, removed, or renamed, the application's running instances and the database's state pull apart; an old instance might see the new schema, and a new instance the old one. The next lesson breaks a schema change into versioned migration steps, runs them forward and backward, and shows how a column is renamed without an outage, by implementing a three-step scheme. ### Schema Migrations Source: https://academia.sh/en/courses/data-access-layer/schema-migrations Splitting a schema change into versioned steps: forward and backward migration scripts, the version table, the atomic rollback of a failed step, and changing a column without an outage through an expand-write-contract scheme. The connection pool works on the assumption that every connection sees the same schema. The assumption holds until the schema changes. When a column is added, removed, or renamed, the application's running instances and the database's state pull apart: which instance sees which schema depends on when each instance was last refreshed. The SQL Fundamentals course introduced a schema change as a command run once, on its own. At the application layer, the same command becomes a **migration** step: versioned, applied in sequence, reversible, and recorded so that it is known which step has been applied in which environment. This lesson builds a migration runner, runs it in both directions, and changes a column without an outage. #### Steps and the Version Table Migration steps are files. Each step's forward and backward direction lives in a separate file; the name begins with a sequence number. ```sh mkdir -p migrations cat > migrations/001-loan-table.up.sql <<'SQL' CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT, registered_at TEXT NOT NULL); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL, return_date TEXT); SQL cat > migrations/001-loan-table.down.sql <<'SQL' DROP TABLE loan; DROP TABLE member; SQL cat > migrations/002-overdue-fine.up.sql <<'SQL' ALTER TABLE loan ADD COLUMN overdue_fine REAL NOT NULL DEFAULT 0; SQL cat > migrations/002-overdue-fine.down.sql <<'SQL' ALTER TABLE loan DROP COLUMN overdue_fine; SQL cat > migrations/003-open-loan-index.up.sql <<'SQL' CREATE INDEX loan_open_index ON loan (member_id) WHERE return_date IS NULL; SQL cat > migrations/003-open-loan-index.down.sql <<'SQL' DROP INDEX loan_open_index; SQL ``` The information about which step has been applied lives in the database itself. If it were kept on the file system or in a configuration file, it would not travel with the database when the database is copied. ```js // migrate.mjs — runs migration steps up and down by checking the version table import { DatabaseSync } from "node:sqlite"; import { readdirSync, readFileSync } from "node:fs"; const db = new DatabaseSync(process.env.DB_PATH ?? "library.db"); db.exec(`CREATE TABLE IF NOT EXISTS schema_version ( version INTEGER PRIMARY KEY, name TEXT NOT NULL, applied_at TEXT NOT NULL)`); const steps = [...new Set(readdirSync("migrations").map((d) => d.replace(/\.(up|down)\.sql$/, "")))] .sort() .map((name) => ({ version: Number(name.slice(0, 3)), name })); const appliedVersions = () => db.prepare("SELECT version FROM schema_version ORDER BY version") .all().map((r) => r.version); function runStep(step, direction) { const sql = readFileSync(`migrations/${step.name}.${direction}.sql`, "utf8"); db.exec("BEGIN"); try { db.exec(sql); if (direction === "up") { db.prepare("INSERT INTO schema_version VALUES (?,?,datetime('now'))").run(step.version, step.name); } else { db.prepare("DELETE FROM schema_version WHERE version = ?").run(step.version); } db.exec("COMMIT"); console.log(`${direction === "up" ? "up " : "down"} ${step.name}`); } catch (err) { db.exec("ROLLBACK"); console.log(`ERROR ${step.name}: ${err.message}`); process.exit(1); } } const [command, targetArg] = process.argv.slice(2); const target = targetArg === undefined ? null : Number(targetArg); if (command === "status") { const done = new Set(appliedVersions()); for (const step of steps) console.log(`${done.has(step.version) ? "[x]" : "[ ]"} ${step.name}`); console.log("current version:", Math.max(0, ...done)); } else if (command === "up") { const done = new Set(appliedVersions()); for (const step of steps) { if (!done.has(step.version) && (target === null || step.version <= target)) runStep(step, "up"); } } else if (command === "down") { const doneVersions = appliedVersions().sort((x, y) => y - x); for (const v of doneVersions) { if (v > target) runStep(steps.find((step) => step.version === v), "down"); } } else { console.log("usage: node migrate.mjs status | up [version] | down "); } ``` The runner makes three decisions. The step and the version record are written **in the same transaction**; steps are ordered by file name, so the numbering determines the sequence; and the order reverses in the backward direction. #### Forward Execution ```sh node migrate.mjs status node migrate.mjs up 3 node migrate.mjs status ``` ``` [ ] 001-loan-table [ ] 002-overdue-fine [ ] 003-open-loan-index current version: 0 up 001-loan-table up 002-overdue-fine up 003-open-loan-index [x] 001-loan-table [x] 002-overdue-fine [x] 003-open-loan-index current version: 3 ``` The three steps were applied in sequence and written to the version table. The second `status` call reads the information from the database, not from the files; if the same migration directory were run against a different database, every box there would show empty. #### Atomicity of a Failed Step If an error occurs partway through a step, that step must not be left half-applied. The step below deliberately produces an error on its second line. ```sh cat > migrations/004-broken-example.up.sql <<'SQL' ALTER TABLE loan ADD COLUMN branch_id INTEGER; ALTER TABLE loan ADD COLUMN branch_id INTEGER; SQL cat > migrations/004-broken-example.down.sql <<'SQL' ALTER TABLE loan DROP COLUMN branch_id; SQL node migrate.mjs up 4 echo "exit code: $?" node migrate.mjs status sqlite3 library.db "PRAGMA table_info(loan);" | grep -c branch_id ``` ``` ERROR 004-broken-example: duplicate column name: branch_id exit code: 1 [x] 001-loan-table [x] 002-overdue-fine [x] 003-open-loan-index [ ] 004-broken-example current version: 3 0 ``` Three results appear together: the runner returned a nonzero exit code, the step was not marked in the version table, and the number on the last line shows that the `branch_id` column is not in the table. The first `ALTER TABLE` command had succeeded; when the transaction rolled back, it was rolled back too. This behavior is engine-dependent. Not every relational database can roll back schema-changing commands inside a transaction; on engines that cannot, each step is shrunk to a single command, because being left half-applied requires manual repair. #### Backward Execution The backward direction runs the applied steps in reverse. ```sh node migrate.mjs down 1 node migrate.mjs status ``` ``` down 003-open-loan-index down 002-overdue-fine [x] 001-loan-table [ ] 002-overdue-fine [ ] 003-open-loan-index [ ] 004-broken-example current version: 1 ``` The index was dropped first, then the column was removed. If the order were reversed, an index that used the column would be left dangling. If the backward direction is left unwritten, the migration becomes one-directional, and there is no way back from a faulty version. #### Zero-Downtime Column Change The real difficulty is a change made while the application keeps running. The `email` column on the `member` relation is being renamed to `email_address`. Renaming it in a single step is a **breaking change**: the moment the renaming command runs, application instances that have not yet been refreshed start failing. The solution is to split the change into three steps. The Relational Database Administration course covered this scheme under the name **expand–contract migration**; a writing phase sits between the two ends. 1. **Expand:** the new column is added, and the existing values are copied over. The old column stays. 2. **Write:** the new version of the application writes to both columns and reads from the new one. The writing behavior in this phase is called **dual write**. The old version keeps running too. 3. **Contract:** once every instance has been refreshed, the old column is removed. The steps are three migration files. ```sh cat > migrations/005-email-expand.up.sql <<'SQL' ALTER TABLE member ADD COLUMN email_address TEXT; UPDATE member SET email_address = email WHERE email_address IS NULL; SQL cat > migrations/005-email-expand.down.sql <<'SQL' ALTER TABLE member DROP COLUMN email_address; SQL cat > migrations/006-email-backfill.up.sql <<'SQL' UPDATE member SET email_address = email WHERE email_address IS NULL AND email IS NOT NULL; SQL cat > migrations/006-email-backfill.down.sql <<'SQL' SELECT 1; SQL cat > migrations/007-email-contract.up.sql <<'SQL' ALTER TABLE member DROP COLUMN email; SQL cat > migrations/007-email-contract.down.sql <<'SQL' ALTER TABLE member ADD COLUMN email TEXT; UPDATE member SET email = email_address; SQL ``` Two versions of the code stand in for the application's old and new instances. ```js // old-code.mjs — running old version: knows only the email column import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("library.db"); try { db.prepare("INSERT INTO member (first_name, last_name, email, registered_at) VALUES (?,?,?,?)") .run("Old", "Record", "old@example.test", "2025-07-20"); const n = db.prepare("SELECT count(*) AS n FROM member WHERE email IS NOT NULL").get().n; console.log(`old code: ran, rows with email = ${n}`); } catch (err) { console.log(`old code: ERROR -> ${err.message}`); } ``` ```js // new-code.mjs — new version: reads the email_address column; writes to the old one too when DUAL_WRITE=1 import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("library.db"); const dual = process.env.DUAL_WRITE === "1"; try { if (dual) { db.prepare("INSERT INTO member (first_name, last_name, email_address, email, registered_at) VALUES (?,?,?,?,?)") .run("New", "Record", "new@example.test", "new@example.test", "2025-07-20"); } else { db.prepare("INSERT INTO member (first_name, last_name, email_address, registered_at) VALUES (?,?,?,?)") .run("New", "Record", "new@example.test", "2025-07-20"); } const n = db.prepare("SELECT count(*) AS n FROM member WHERE email_address IS NOT NULL").get().n; console.log(`new code: ran (dual write=${dual ? "on" : "off"}), rows with email_address = ${n}`); } catch (err) { console.log(`new code: ERROR -> ${err.message}`); } ``` The script below sets up the database from scratch and runs both code versions after every step. The broken example step is deleted so that it does not block the steps that follow. ```sh # zero-downtime.sh — the state of the two code versions across a three-step column change rm -f library.db migrations/004-broken-example.up.sql migrations/004-broken-example.down.sql node migrate.mjs up 3 >/dev/null sqlite3 library.db "INSERT INTO member (first_name,last_name,email,registered_at) VALUES ('Alice','Kane','alice@example.test','2023-02-14'), ('Clara','Diaz',NULL,'2024-01-09');" echo "== version 3: only the old column exists ==" node old-code.mjs node new-code.mjs echo "== version 5 (expand): new column added and backfilled ==" node migrate.mjs up 5 node old-code.mjs DUAL_WRITE=1 node new-code.mjs echo "== version 6 (backfill): what the old code wrote moved to the new column ==" node migrate.mjs up 6 node old-code.mjs DUAL_WRITE=1 node new-code.mjs echo "== version 7 (contract): old column removed ==" node migrate.mjs up 7 node old-code.mjs node new-code.mjs echo "-- rows with no address --" sqlite3 -header -column library.db "SELECT member_id, first_name, last_name FROM member WHERE email_address IS NULL;" ``` ```sh sh zero-downtime.sh ``` ``` == version 3: only the old column exists == old code: ran, rows with email = 2 new code: ERROR -> table member has no column named email_address == version 5 (expand): new column added and backfilled == up 005-email-expand old code: ran, rows with email = 3 new code: ran (dual write=on), rows with email_address = 3 == version 6 (backfill): what the old code wrote moved to the new column == up 006-email-backfill old code: ran, rows with email = 5 new code: ran (dual write=on), rows with email_address = 5 == version 7 (contract): old column removed == up 007-email-contract old code: ERROR -> table member has no column named email new code: ran (dual write=off), rows with email_address = 6 -- rows with no address -- member_id first_name last_name --------- ---------- --------- 2 Clara Diaz 6 Old Record ``` The two middle phases are the proof of zero downtime: at versions 5 and 6, both the old and the new code ran without error. The only moment the change is breaking is version 7, and that moment can be deferred until every instance has been refreshed. #### Condition of the Contract Step The last part of the output carries a warning. Two rows are left with an empty `email_address` column. Of these, `Clara` had no address from the start; `Old Record` is the problematic one. The old code wrote that row **after** the backfill step: it wrote to the old column, the new column stayed empty, and the value was lost when the contract step removed the old column. The condition for the contract step follows from this: **no instance may still be writing to the old column.** The condition is met not by repeating the backfill step, but by confirming that the old instances have been fully withdrawn. In practice, this confirmation is made in two ways: verifying that the rollout has finished, and checking, immediately before the contract step, that the number of rows left empty is zero. #### The Limit of a Down Migration Reversing the contract step is possible, but the reversal brings back the schema, not the data. ```sh node migrate.mjs down 6 sqlite3 -header -column library.db "SELECT member_id, first_name, email, email_address FROM member WHERE member_id IN (1,2,6);" ``` ``` down 007-email-contract member_id first_name email email_address --------- ---------- ------------------ ------------------ 1 Alice alice@example.test alice@example.test 2 Clara 6 Old ``` The `email` column came back and was filled from the `email_address` values. Row six's address is empty again: the information was lost for good when the contract step deleted it. A down migration is a **rollback plan**, not a backup. The backward direction of a data-losing step is meaningful only if the deleted data was saved somewhere else; otherwise, the reversal only repairs the schema. #### Summary - A migration step is a versioned, two-directional unit applied in sequence; the information about which step has been applied lives in the version table inside the database itself. - The step itself and the version record are written in the same transaction; on the step that failed, both were rolled back together, and the version table did not change. - Changing a column without an outage takes three phases: expand, write with dual write, contract. In the two middle phases, the old and new code versions ran together. - The contract step cannot be applied while an instance is still writing to the old column; when the rule was not followed, the value of a row written after the backfill was lost. - A down migration undoes the schema, not deleted data; a rollback plan does not stand in for a backup. #### Next Step The migration runner opened every step with `BEGIN`, closed it with `COMMIT`, and called `ROLLBACK` on error; that is what made the step and the version record get written together. The same question applies to the application's ordinary operation: which writes does a loan request cover, must all of these writes become final together, and which layer holds the code that opens and closes the transaction? The next topic opens with this question, and its first lesson builds the transaction boundary on the definition of a unit of work: a boundary drawn too narrow produces half-finished states, and one drawn too wide produces locks held for a long time. ## Transactions and Consistency Where to draw the boundary of a unit of work, the effect isolation levels have as seen from the application, how optimistic and pessimistic locking resolve conflicts, the cost of atomicity across services, and the immutable transaction design that makes retrying safe. ### Transaction Boundaries Source: https://academia.sh/en/courses/data-access-layer/transaction-boundaries Where to draw the boundary of a unit of work: the half-finished state that unbounded writes leave behind, why opening a transaction per repository fails to compose, a unit of work that keeps the transaction boundary in one place, the savepoint used at nested boundaries, and the effect boundary width has on wait time. The migration runner opened every step with `BEGIN`, closed it with `COMMIT`, and called `ROLLBACK` on failure. That was what made the step itself and the version record get written together. The same question holds for the application's normal operation. A loan request is not a single write: a loan record is added, the book's status changes, a row is written to the entry log. Must all of these commit together, where is the boundary drawn, and who opens it? The concept of a transaction was defined in the Data Modeling and Relational Theory course; this lesson establishes where the boundary sits in the application layer. #### A Task with Three Writes The schema below adds the book's status and the entry log. The entry type is constrained by a check condition; throughout the lesson, this constraint is what produces the error. ```sh # setup.sh — sets up the lesson database from scratch rm -f library.db sqlite3 library.db >/dev/null <<'SQL' PRAGMA journal_mode = WAL; CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'on_shelf' CHECK (status IN ('on_shelf','on_loan'))); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id), member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT); CREATE TABLE entry (entry_id INTEGER PRIMARY KEY, loan_id INTEGER NOT NULL, type TEXT NOT NULL CHECK (type IN ('issued','returned')), date TEXT NOT NULL); INSERT INTO book VALUES (1,'Blindness','on_shelf'),(2,'The Disconnected','on_shelf'),(3,'The Book of Sand','on_shelf'); SQL ``` Being able to see the status at every step requires a small reader. ```js // status.mjs — prints the row counts of the three relations and the book's status import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("library.db"); const count = (t) => db.prepare(`SELECT count(*) AS n FROM ${t}`).get().n; console.log(`loan=${count("loan")} entry=${count("entry")} book1_status=${ db.prepare("SELECT status FROM book WHERE book_id = 1").get().status}`); ``` #### Without a Boundary The first version runs the three writes back to back; there is no boundary between them. ```js // no-transaction.mjs — three writes, no transaction boundary import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("library.db"); const type = process.argv[2] ?? "issued"; const result = db.prepare( "INSERT INTO loan (book_id, member_id, pickup_date) VALUES (?,?,?)").run(1, 4, "2025-07-20"); const loanId = Number(result.lastInsertRowid); db.prepare("UPDATE book SET status = 'on_loan' WHERE book_id = ?").run(1); try { db.prepare("INSERT INTO entry (loan_id, type, date) VALUES (?,?,?)") .run(loanId, type, "2025-07-20"); console.log("all three writes completed"); } catch (h) { console.log("third write failed:", h.message); } ``` The error is produced by giving the third write an invalid type. ```sh sh setup.sh node no-transaction.mjs "overdue" node status.mjs ``` ``` third write failed: CHECK constraint failed: type IN ('issued','returned') loan=1 entry=0 book1_status=on_loan ``` The resulting state is inconsistent. The loan record exists, the book shows as on loan, but there is no corresponding row in the entry log. From the application's point of view, this is a book that has been "issued but not recorded as issued"; it does not appear in any report, and it is unclear which rule the return flow should apply. #### Inside the Boundary The result changes when the same three writes are placed inside a transaction boundary. ```js // with-transaction.mjs — the same three writes, inside a single transaction boundary import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("library.db"); const type = process.argv[2] ?? "issued"; db.exec("BEGIN"); try { const result = db.prepare( "INSERT INTO loan (book_id, member_id, pickup_date) VALUES (?,?,?)").run(1, 4, "2025-07-20"); const loanId = Number(result.lastInsertRowid); db.prepare("UPDATE book SET status = 'on_loan' WHERE book_id = ?").run(1); db.prepare("INSERT INTO entry (loan_id, type, date) VALUES (?,?,?)") .run(loanId, type, "2025-07-20"); db.exec("COMMIT"); console.log("transaction committed"); } catch (h) { db.exec("ROLLBACK"); console.log("transaction rolled back:", h.message); } ``` ```sh sh setup.sh node with-transaction.mjs "overdue" node status.mjs node with-transaction.mjs issued node status.mjs ``` ``` transaction rolled back: CHECK constraint failed: type IN ('issued','returned') loan=0 entry=0 book1_status=on_shelf transaction committed loan=1 entry=1 book1_status=on_loan ``` The first call left no write in place; the second left all three. There is no in-between state. This is the definition of a **unit of work**: a set of writes that looks like a single change from the outside, and is applied either completely or not at all. The boundary of a unit of work is the boundary of the business rule, not a technical boundary. "Issuing a loan" is a unit of work because half of it is meaningless. "Preparing the end-of-day report" and "issuing a loan" are separate units of work, because the failure of one does not invalidate the other. #### If the Boundary Is Drawn at the Repository Where the boundary should not be drawn can be seen by looking at what happens when it is. If every repository method opens and closes its own transaction, two methods cannot be atomic together. ```js // own-transaction.mjs — what happens if each repository method opens and closes its own transaction import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("library.db"); const loanRepository = { add(bookId, memberId, date) { db.exec("BEGIN"); const s = db.prepare("INSERT INTO loan (book_id, member_id, pickup_date) VALUES (?,?,?)") .run(bookId, memberId, date); db.exec("COMMIT"); return Number(s.lastInsertRowid); }, }; const entryRepository = { add(loanId, type, date) { db.exec("BEGIN"); try { db.prepare("INSERT INTO entry (loan_id, type, date) VALUES (?,?,?)") .run(loanId, type, date); db.exec("COMMIT"); } catch (h) { db.exec("ROLLBACK"); throw h; } }, }; try { const id = loanRepository.add(1, 4, "2025-07-20"); entryRepository.add(id, "overdue", "2025-07-20"); } catch (h) { console.log("service got an error:", h.message); } console.log("try opening an outer transaction:"); db.exec("BEGIN"); try { loanRepository.add(2, 5, "2025-07-21"); } catch (h) { console.log(" ->", h.message); db.exec("ROLLBACK"); } ``` ```sh sh setup.sh node own-transaction.mjs node status.mjs ``` ``` service got an error: CHECK constraint failed: type IN ('issued','returned') try opening an outer transaction: -> cannot start a transaction within a transaction loan=1 entry=0 book1_status=on_shelf ``` Two separate problems show up in the same output. First: the loan record committed, the entry could not be written; even though the service caught an error, a half-finished state remained in the database. Second: when the service tried to solve the problem by opening a transaction itself, the repository method could not open a second transaction from inside and raised an error. The result is the rule: **the repository does not open the transaction.** The repository works on the connection it is given; a higher layer decides where the boundary begins and ends. #### The Unit of Work The structure that carries the boundary is the same structure that supplies the connection the repositories will use. ```js // unit-of-work.mjs — a unit of work that keeps the transaction boundary in one place; repositories take the connection from outside export class UnitOfWork { constructor(db) { this.db = db; this.depth = 0; } run(work) { const isNested = this.depth > 0; const savepoint = `savepoint_${this.depth}`; this.db.exec(isNested ? `SAVEPOINT ${savepoint}` : "BEGIN"); this.depth += 1; try { const result = work(this.db); this.db.exec(isNested ? `RELEASE ${savepoint}` : "COMMIT"); return result; } catch (h) { this.db.exec(isNested ? `ROLLBACK TO ${savepoint}` : "ROLLBACK"); throw h; } finally { this.depth -= 1; } } } export const loanRepository = { add(db, bookId, memberId, date) { return Number(db.prepare("INSERT INTO loan (book_id, member_id, pickup_date) VALUES (?,?,?)") .run(bookId, memberId, date).lastInsertRowid); }, }; export const bookRepository = { markLoan(db, bookId) { db.prepare("UPDATE book SET status = 'on_loan' WHERE book_id = ?").run(bookId); }, }; export const entryRepository = { add(db, loanId, type, date) { db.prepare("INSERT INTO entry (loan_id, type, date) VALUES (?,?,?)") .run(loanId, type, date); }, }; ``` The depth counter distinguishes nested calls. The outermost call opens the real transaction; the inner calls set up the **savepoint** introduced in the Advanced SQL course. This way, the failure of an inner unit rolls back only its own writes, without bringing down the outer unit. ```js // service.mjs — the unit of work gathers three repository calls into a single boundary import { DatabaseSync } from "node:sqlite"; import { UnitOfWork, loanRepository, bookRepository, entryRepository } from "./unit-of-work.mjs"; const unitOfWork = new UnitOfWork(new DatabaseSync("library.db")); function issueLoan(bookId, memberId, date, type) { return unitOfWork.run((db) => { const loanId = loanRepository.add(db, bookId, memberId, date); bookRepository.markLoan(db, bookId); entryRepository.add(db, loanId, type, date); return loanId; }); } for (const [bookId, type] of [[1, "overdue"], [2, "issued"]]) { try { console.log(`book ${bookId} issued, loan_id =`, issueLoan(bookId, 4, "2025-07-20", type)); } catch (h) { console.log(`book ${bookId} failed:`, h.message); } } ``` ```sh sh setup.sh node service.mjs node status.mjs sqlite3 -header -column library.db "SELECT book_id, status FROM book;" ``` ``` book 1 failed: CHECK constraint failed: type IN ('issued','returned') book 2 issued, loan_id = 1 loan=1 entry=1 book1_status=on_shelf book_id status ------- -------- 1 on_shelf 2 on_loan 3 on_shelf ``` The first request was rolled back completely: book 1 stayed on the shelf. The second request committed all three writes together. The service code contains no `BEGIN`; the boundary is the `run` call itself. The behavior of a nested boundary can be seen as well. ```js // nested.mjs — the outer unit of work still commits even though the inner one fails import { DatabaseSync } from "node:sqlite"; import { UnitOfWork, loanRepository, bookRepository, entryRepository } from "./unit-of-work.mjs"; const unitOfWork = new UnitOfWork(new DatabaseSync("library.db")); unitOfWork.run((db) => { const loanId = loanRepository.add(db, 3, 6, "2025-07-22"); bookRepository.markLoan(db, 3); entryRepository.add(db, loanId, "issued", "2025-07-22"); try { unitOfWork.run((inner) => entryRepository.add(inner, loanId, "notification", "2025-07-22")); } catch (h) { console.log("inner unit of work rolled back:", h.message); } console.log("outer unit of work continues, loan_id =", loanId); }); ``` ```sh sh setup.sh node nested.mjs node status.mjs ``` ``` inner unit of work rolled back: CHECK constraint failed: type IN ('issued','returned') outer unit of work continues, loan_id = 1 loan=1 entry=1 book1_status=on_shelf ``` The inner unit's write was rolled back, the outer three writes committed. The entry count is one; if it had been two, the inner unit would not have been rolled back. #### The Width of the Boundary Drawing the boundary wide increases atomicity, but it has a cost: the locks a transaction holds do not release for as long as it stays open. The cost can be measured. ```js // long-transaction.mjs — keeps a write transaction open for the given duration import { DatabaseSync } from "node:sqlite"; import { writeFileSync, rmSync } from "node:fs"; const db = new DatabaseSync("library.db"); const durationMs = Number(process.argv[2]); db.exec("BEGIN IMMEDIATE"); db.prepare("UPDATE book SET status = 'on_loan' WHERE book_id = 1").run(); writeFileSync("ready", ""); // B can now try const end = Date.now() + durationMs; while (Date.now() < end) { /* model of waiting on an outbound call */ } db.exec("COMMIT"); rmSync("ready"); console.log(`A: transaction stayed open for ${durationMs} ms and committed`); ``` ```js // waiting-write.mjs — how long the second connection waits to write to the same row import { DatabaseSync } from "node:sqlite"; import { existsSync } from "node:fs"; while (!existsSync("ready")) { /* wait for A to open its transaction */ } const db = new DatabaseSync("library.db"); db.exec("PRAGMA busy_timeout = 10000"); const t = performance.now(); db.exec("BEGIN IMMEDIATE"); db.prepare("UPDATE book SET status = 'on_shelf' WHERE book_id = 1").run(); db.exec("COMMIT"); console.log(`B: waited ${(performance.now() - t).toFixed(0)} ms to write`); ``` ```sh # boundary-width.sh — the longer the transaction stays open, the longer the second writer waits for duration in 100 400 800; do sh setup.sh >/dev/null rm -f ready node waiting-write.mjs & waiting=$! node long-transaction.mjs $duration wait $waiting done ``` ```sh sh boundary-width.sh ``` ``` A: transaction stayed open for 100 ms and committed B: waited 114 ms to write A: transaction stayed open for 400 ms and committed B: waited 403 ms to write A: transaction stayed open for 800 ms and committed B: waited 844 ms to write ``` The second writer's wait tracks the first transaction's open duration. The times are machine-dependent; what does not change is that the relationship is linear. The loop inside `long-transaction.mjs` is a model, and it represents waiting inside a transaction for an outbound call — sending a notification, calling a payment service, writing a file. Two rules follow from this. No outbound call is made inside a transaction boundary; an outbound call runs either before or after the boundary. And the boundary is never held one write wider than the business rule requires. #### Summary - When one of the three writes failed, the unbounded version left a half-finished state: the loan record was added and the book's status changed, but the entry log stayed empty. - When the same writes were placed inside a single transaction boundary, the result was either all of them or none; a unit of work is the name for this set, and the business rule determines its boundary. - Opening a transaction per repository produced two problems: the calls failed to compose, and they conflicted with a transaction opened from outside. The repository does not open the transaction; a higher layer does. - The unit of work sets up a real transaction at the outermost level and a savepoint on the inside; the failure of an inner unit did not bring down the outer one. - For as long as a transaction stays open, the second writer waits: transactions that stayed open 100, 400, and 800 ms produced waits of 114, 403, and 844 ms respectively. An outbound call is not placed inside a transaction boundary. #### Next Step In this lesson, two connections wanted to write to the same row, and one waited for the other. A write–write conflict is the most visible case; the ones on the read side are quieter. If another transaction reads the same rows while one is in progress, what does it see: the state before the change, the state after, or a mix of both depending on timing? The answer depends on the isolation level, and the level chosen changes the data the application sees. The next lesson runs the same query at two different levels and measures how a nonrepeatable read and a phantom row look from the application. ### Application Impact of Isolation Levels Source: https://academia.sh/en/courses/data-access-layer/application-impact-of-isolation-levels How read anomalies look from the application: measuring nonrepeatable reads and phantom rows by running the same query twice, an invariant between two aggregate queries violated across thousands of samples, and the cost of isolation. The previous lesson measured two connections wanting to write to the same row, and one waiting for the other. A write–write conflict is a visible event: one waits, then writes. The ones on the read side are quiet. What does another connection see if it reads the same rows while a transaction is in progress? The state before the change, the state after, or a mix that shifts from query to query? The answer depends on the isolation level. The Advanced SQL course defined the levels and the anomalies they permit; this lesson measures the same anomalies from application code and shows what choosing a level means in practice. #### The Setup The schema is small: one member, five books, loan records. ```sh # setup.sh — resets the lesson database and signal files rm -f library.db library.db-wal library.db-shm read-done write-done sqlite3 library.db >/dev/null <<'SQL' PRAGMA journal_mode = WAL; CREATE TABLE member (member_id INTEGER PRIMARY KEY, name TEXT NOT NULL, loan_limit INTEGER NOT NULL); CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, status TEXT NOT NULL CHECK (status IN ('on_shelf','on_loan'))); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, return_date TEXT); INSERT INTO member VALUES (4,'Derek',3); INSERT INTO book VALUES (1,'Blindness','on_shelf'),(2,'The Disconnected','on_shelf'), (3,'The Book of Sand','on_shelf'),(4,'Yaban','on_shelf'),(5,'Silent House','on_shelf'); SQL ``` The two processes wait for each other using signal files. This is the smallest way to produce a specific ordering without leaving the timing to chance. #### Two Anomalies, One Scenario The reader runs the same two queries twice: the value of one row and the row count of one relation. The writer steps in between. ```js // reader.mjs — runs the same two queries twice; mode: autocommit | transaction import { DatabaseSync } from "node:sqlite"; import { writeFileSync, existsSync } from "node:fs"; const mode = process.argv[2]; const db = new DatabaseSync("library.db"); const read = () => ({ limit: db.prepare("SELECT loan_limit AS s FROM member WHERE member_id = 4").get().s, books: db.prepare("SELECT count(*) AS n FROM book").get().n, }); if (mode === "transaction") db.exec("BEGIN"); const first = read(); writeFileSync("read-done", ""); while (!existsSync("write-done")) { /* wait for the writer to commit */ } const second = read(); if (mode === "transaction") db.exec("COMMIT"); console.log(`mode=${mode}`); console.log(` first read : limit=${first.limit} books=${first.books}`); console.log(` second read: limit=${second.limit} books=${second.books}`); console.log(` nonrepeatable read: ${first.limit !== second.limit}`); console.log(` phantom row : ${first.books !== second.books}`); ``` ```js // writer.mjs — updates and adds a new row after the reader's first read import { DatabaseSync } from "node:sqlite"; import { writeFileSync, existsSync } from "node:fs"; while (!existsSync("read-done")) { /* wait for the reader's first read */ } const db = new DatabaseSync("library.db"); db.exec("BEGIN IMMEDIATE"); db.prepare("UPDATE member SET loan_limit = 5 WHERE member_id = 4").run(); db.prepare("INSERT INTO book VALUES (6,'Motherland Hotel','on_shelf')").run(); db.exec("COMMIT"); writeFileSync("write-done", ""); console.log("writer: limit set to 5, new book added"); ``` The same scenario runs in two modes. In `autocommit` mode, the reader opens no transaction; each query runs on its own. In `transaction` mode, the two reads sit inside a single transaction boundary. ```sh # experiment.sh — the same scenario in two modes for mode in autocommit transaction; do sh setup.sh node writer.mjs >/dev/null & writer=$! node reader.mjs $mode wait $writer echo done ``` ```sh sh experiment.sh ``` ``` mode=autocommit first read : limit=3 books=5 second read: limit=5 books=6 nonrepeatable read: true phantom row : true mode=transaction first read : limit=3 books=5 second read: limit=3 books=5 nonrepeatable read: false phantom row : false ``` Both anomalies showed up in the first mode. The `loan_limit` value turned from 3 into 5: the same row, the same query, a different result. This is called a **nonrepeatable read**. The book count rose from 5 to 6: the query asked the same condition, and a new row entered the result set. This is called a **phantom row**. The difference between the two is that the first is in the value of an existing row, and the second is in the membership of the result set. In the second mode, both reads gave the picture from the first moment. Once a transaction opens, the reader works on a **snapshot**, and that snapshot stays fixed for the duration of the transaction. The real observation here concerns the application. The code ran the same queries in both modes; the only thing that changed was a single `BEGIN` call. **The isolation level the application sees is determined by whether reads are placed inside a transaction boundary.** A service that runs queries without a boundary cannot see consistency between two consecutive reads, no matter what level the engine offers. #### The Invariant's Violation Rate The scenario above set up the timing by hand. Under real load, the timing is not set up; it is left to chance. In that case, the question about an anomaly is no longer "does it happen" but "how often does it happen." The library must maintain an **invariant**: the number of books showing as on loan must equal the number of loan records without a return date. The writer never breaks this invariant; it performs every step inside a single transaction. ```js // roaming-writer.mjs — continuously issues and returns books; each step is a single transaction import { DatabaseSync } from "node:sqlite"; import { writeFileSync, rmSync } from "node:fs"; const db = new DatabaseSync("library.db"); db.exec("PRAGMA busy_timeout = 5000"); const end = Date.now() + Number(process.argv[2] ?? 1500); writeFileSync("writing", ""); let step = 0; while (Date.now() < end) { const bookId = (step % 5) + 1; db.exec("BEGIN IMMEDIATE"); const status = db.prepare("SELECT status FROM book WHERE book_id = ?").get(bookId).status; if (status === "on_shelf") { db.prepare("UPDATE book SET status = 'on_loan' WHERE book_id = ?").run(bookId); db.prepare("INSERT INTO loan (book_id, member_id, return_date) VALUES (?,4,NULL)").run(bookId); } else { db.prepare("UPDATE book SET status = 'on_shelf' WHERE book_id = ?").run(bookId); db.prepare(`UPDATE loan SET return_date = '2025-07-25' WHERE loan_id = (SELECT max(loan_id) FROM loan WHERE book_id = ? AND return_date IS NULL)`).run(bookId); } db.exec("COMMIT"); step += 1; } rmSync("writing"); console.log(`writer: committed ${step} transactions`); ``` The reader continuously samples the invariant and counts how many samples it saw as broken. ```js // inconsistency-count.mjs — samples the invariant between two aggregate queries import { DatabaseSync } from "node:sqlite"; import { existsSync } from "node:fs"; const mode = process.argv[2]; const db = new DatabaseSync("library.db"); db.exec("PRAGMA busy_timeout = 5000"); while (!existsSync("writing")) { /* wait for the writer to start */ } let samples = 0, violations = 0; while (existsSync("writing")) { if (mode === "transaction") db.exec("BEGIN"); const booksOnLoan = db.prepare("SELECT count(*) AS n FROM book WHERE status = 'on_loan'").get().n; const openLoans = db.prepare("SELECT count(*) AS n FROM loan WHERE return_date IS NULL").get().n; if (mode === "transaction") db.exec("COMMIT"); samples += 1; if (booksOnLoan !== openLoans) violations += 1; } console.log(`mode=${mode} samples=${samples} invariant violations=${violations}`); ``` ```sh # invariant.sh — the same invariant sampled in two read modes for mode in autocommit transaction; do sh setup.sh rm -f writing node inconsistency-count.mjs $mode & reader=$! node roaming-writer.mjs 1500 wait $reader done ``` ```sh sh invariant.sh ``` ``` writer: committed 27750 transactions mode=autocommit samples=21024 invariant violations=3489 writer: committed 24922 transactions mode=transaction samples=19476 invariant violations=0 ``` The numbers vary by machine and by the load at the time; how many transactions commit in a second and a half is not fixed. What does not change is the distinction: in the unbounded reads, more than three thousand of twenty-one thousand samples saw the invariant broken; in the reads inside a transaction, none did. The ratio is roughly one-sixth. This means the corresponding report returns an inconsistent pair of numbers on one out of every six calls. Nothing lands in the error log; the queries succeed, there is no constraint violation, only the two numbers fail to agree. #### What Levels Mean for the Application The Advanced SQL course listed four anomalies: dirty reads, nonrepeatable reads, phantom rows, and serializability violations. On the application side, their counterpart comes down to three questions. **Which queries are meaningful together?** Queries that are meaningful together are placed inside the same transaction boundary. The two aggregate queries above were meaningful together; the pair they produced when run separately was meaningless. **Is a decision being made on a read value?** If a write is made by looking at a read value, that value must not change in the gap between the read and the write. A service that reads the loan limit and writes on the basis of "there is room" must be protected against a change in that gap. This is a problem the isolation level does not solve on its own, and it is the subject of the next lesson. **Is stale data tolerable?** A snapshot is consistent, but that is not the same as saying it is current. If a transaction runs long, the data it read may be stale by the time it commits. This is acceptable for long reports; it is not acceptable for something like a money transfer. The cost of isolation sits at this third point. A high level either holds locks and makes others wait, or keeps old versions around and produces storage and cleanup costs. The mechanism covered in the Relational Database Administration course as multiversion concurrency control follows this second path: old versions cannot be deleted for as long as a reader needs them, and long-running open read transactions delay the cleanup. #### Summary - The same two queries, run unbounded, saw a row whose value had changed (a nonrepeatable read) and a new row entering the result set (a phantom row); inside a transaction boundary, neither showed up. - The isolation level the application sees is determined by whether reads are placed inside a transaction boundary; with the code unchanged, a single `BEGIN` call changed the result. - The invariant between the two aggregate queries showed up broken in roughly one-sixth of twenty-one thousand unbounded-read samples; in the reads inside a transaction, it never broke. - This kind of inconsistency produces no error: the queries succeed, but together they are meaningless. - The cost of high isolation is waiting or keeping old versions around; long open read transactions delay the cleanup. #### Next Step Isolation ensures that the data read is consistent; it does not guarantee the correctness of a write made on the basis of a read value. If two members want to borrow the last remaining copy at the same time, both see "book on shelf," both write, and one invalidates the other's result. There are two ways to resolve this conflict: check, at write time, that the record is still in the state it was read in, or lock the record at read time and make the other one wait. The next lesson builds both, produces the conflict, shows the lock error directly, and measures which one is cheaper under which load. ### Optimistic and Pessimistic Locking Source: https://academia.sh/en/courses/data-access-layer/optimistic-and-pessimistic-locking Two solutions to a concurrent update conflict: optimistic locking, which detects the conflict at write time with a version column; pessimistic locking, which makes the decision under a lock; the fact that an upgrade conflict cannot be resolved by waiting; and a comparison of the two approaches' attempt count and duration. Isolation ensures that the data read is internally consistent. It does not guarantee the correctness of a write made on the basis of a read value. If two members want to borrow the last remaining copy at the same time, both see "book on shelf." What both of them see is true; the book really was on the shelf at the moment they read it. The problem arises in the gap between the decision and the write. This lesson builds two ways of closing that gap, actually produces the conflict, and measures the cost of each. #### Lost Update The schema holds a single book and a version column. The version column will not be used in the first section; it comes into play in the second. ```sh # setup.sh — single-book race setup rm -f library.db library.db-wal library.db-shm sqlite3 library.db >/dev/null <<'SQL' PRAGMA journal_mode = WAL; CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, status TEXT NOT NULL CHECK (status IN ('on_shelf','on_loan')), version INTEGER NOT NULL DEFAULT 1); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, return_date TEXT); INSERT INTO book VALUES (1,'Blindness','on_shelf',1); SQL ``` ```js // count.mjs — prints the open loan count and version for one book import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("library.db"); const k = db.prepare("SELECT status, version FROM book WHERE book_id = 1").get(); const n = db.prepare("SELECT count(*) AS n FROM loan WHERE book_id = 1 AND return_date IS NULL").get().n; console.log(`book status=${k.status} version=${k.version} open loans=${n}`); ``` The first version reads, decides, writes. The write is inside a boundary; it follows the previous lesson's rule. It still is not enough. ```js // unprotected.mjs — read, decide, write; nothing protects the gap import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("library.db"); db.exec("PRAGMA busy_timeout = 5000"); const memberId = Number(process.argv[2]); const delayMs = Number(process.argv[3] ?? 120); const book = db.prepare("SELECT status FROM book WHERE book_id = 1").get(); const end = Date.now() + delayMs; while (Date.now() < end) { /* model of decision time */ } if (book.status === "on_shelf") { db.exec("BEGIN IMMEDIATE"); db.prepare("UPDATE book SET status = 'on_loan' WHERE book_id = 1").run(); db.prepare("INSERT INTO loan (book_id, member_id, return_date) VALUES (1,?,NULL)").run(memberId); db.exec("COMMIT"); console.log(`member ${memberId}: got the book`); } else { console.log(`member ${memberId}: book on loan, rejected`); } ``` ```sh sh setup.sh node unprotected.mjs 4 & node unprotected.mjs 5 & wait node count.mjs ``` ``` member 4: got the book member 5: got the book book status=on_loan version=1 open loans=2 ``` The single-copy book was issued to two members at once. This is the application-side form of the **lost update** defined in the Data Modeling and Relational Theory course: the second write went through even though it invalidated the fact the first write relied on. It is not enough for the read and the write to each be correct on their own; the assumption between them has to be tested for whether it still holds. The `delayMs` loop is a model; it represents the fact that the decision is not instantaneous, and that time passes for rule checks and external validations. As the gap narrows, the conflict becomes rarer, but it does not disappear. #### Optimistic Locking The first solution does not try to prevent the conflict; it detects it when it happens. The record is taken together with the version it had at the moment it was read, and that version is placed in the write condition. If the version has changed, the write affects no row. ```js // optimistic.mjs — the read version goes into the write condition; the affected row count tells whether there was a conflict import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("library.db"); db.exec("PRAGMA busy_timeout = 5000"); const memberId = Number(process.argv[2]); const delayMs = Number(process.argv[3] ?? 120); const book = db.prepare("SELECT status, version FROM book WHERE book_id = 1").get(); const end = Date.now() + delayMs; while (Date.now() < end) { /* model of decision time */ } if (book.status !== "on_shelf") { console.log(`member ${memberId}: book on loan, rejected`); } else { db.exec("BEGIN IMMEDIATE"); const result = db.prepare( "UPDATE book SET status = 'on_loan', version = version + 1 WHERE book_id = 1 AND version = ?") .run(book.version); if (result.changes === 0) { db.exec("ROLLBACK"); console.log(`member ${memberId}: conflict (read version ${book.version}, affected rows 0)`); } else { db.prepare("INSERT INTO loan (book_id, member_id, return_date) VALUES (1,?,NULL)").run(memberId); db.exec("COMMIT"); console.log(`member ${memberId}: got the book (version ${book.version} -> ${book.version + 1})`); } } ``` ```sh sh setup.sh node optimistic.mjs 4 & node optimistic.mjs 5 & wait node count.mjs ``` ``` member 5: got the book (version 1 -> 2) member 4: conflict (read version 1, affected rows 0) book status=on_loan version=2 open loans=1 ``` Which member wins changes from run to run; what does not change is that one wins and the other reports a conflict. The open loan count dropped to one. The method's operating principle rests on a single number: the **affected row count**. A value of one says the record was found in the state it was read in; a value of zero says someone else got in between. Because this number can be read from the driver, no additional infrastructure is required. A last-modified timestamp can be used in place of the version column, but if the timestamp's resolution is not fine enough, two changes carry the same value and the conflict slips through. An integer that only ever increases does not carry this risk. What to do once a conflict is detected is a separate decision. The options are telling the user "the record has changed," rereading and retrying the operation, or merging the two changes. In the loan example, the correct behavior is to retry; on the second attempt, the book will show as on loan, so the request is rejected properly. #### Pessimistic Locking The second solution never lets the conflict form at all. The record is locked before the decision is made; the second client either waits or is rejected. ```js // pessimistic.mjs — the write lock is held for the duration of the decision import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("library.db"); db.exec(`PRAGMA busy_timeout = ${process.env.WAIT_MS ?? 0}`); const memberId = Number(process.argv[2]); const delayMs = Number(process.argv[3] ?? 120); try { db.exec("BEGIN IMMEDIATE"); } catch (h) { console.log(`member ${memberId}: could not get lock -> ${h.message}`); process.exit(0); } const book = db.prepare("SELECT status FROM book WHERE book_id = 1").get(); const end = Date.now() + delayMs; while (Date.now() < end) { /* model of decision time */ } if (book.status === "on_shelf") { db.prepare("UPDATE book SET status = 'on_loan', version = version + 1 WHERE book_id = 1").run(); db.prepare("INSERT INTO loan (book_id, member_id, return_date) VALUES (1,?,NULL)").run(memberId); db.exec("COMMIT"); console.log(`member ${memberId}: got the book`); } else { db.exec("COMMIT"); console.log(`member ${memberId}: book on loan, rejected`); } ``` With the wait time at zero, the second client does not wait for the lock at all. ```sh sh setup.sh node pessimistic.mjs 4 & sleep 0.05; node pessimistic.mjs 5 & wait node count.mjs ``` ``` member 5: could not get lock -> database is locked member 4: got the book book status=on_loan version=2 open loans=1 ``` The error text comes from the driver and varies by engine; what is common is that there is a distinct error class saying the lock could not be acquired. The application has to distinguish this error from others: a lock error can be retried, a constraint violation cannot. When a wait time is given, the second client waits for the lock and, once its turn comes, reads the **current** data. ```sh sh setup.sh WAIT_MS=5000 node pessimistic.mjs 4 & sleep 0.05; WAIT_MS=5000 node pessimistic.mjs 5 & wait node count.mjs ``` ``` member 4: got the book member 5: book on loan, rejected book status=on_loan version=2 open loans=1 ``` The second member got a "book on loan" response, not a "conflict" response. The difference matters: in the optimistic approach, the application had to handle a conflict; in the pessimistic one, the business rule's normal rejection took over. The cost is that the second member waits for the duration of the first member's decision time. #### Upgrade Conflict Setting up pessimistic locking correctly depends on the lock being acquired **before the read**. Two transactions that read first and then try to write produce a conflict that cannot be resolved by waiting. ```js // upgrade.mjs — two transactions that read first and then try to write import { DatabaseSync } from "node:sqlite"; import { writeFileSync, existsSync } from "node:fs"; const role = process.argv[2]; const db = new DatabaseSync("library.db"); db.exec("PRAGMA busy_timeout = 3000"); db.exec("BEGIN"); // deferred: the snapshot is taken at the read db.prepare("SELECT status FROM book WHERE book_id = 1").get(); if (role === "a") { writeFileSync("a-read", ""); while (!existsSync("b-read")) { /* wait */ } } else { writeFileSync("b-read", ""); while (!existsSync("a-read")) { /* wait */ } } if (role === "b") { while (!existsSync("a-wrote")) { /* let A write first */ } } try { db.prepare("UPDATE book SET version = version + 1 WHERE book_id = 1").run(); db.exec("COMMIT"); if (role === "a") writeFileSync("a-wrote", ""); console.log(`${role}: wrote and committed`); } catch (h) { db.exec("ROLLBACK"); console.log(`${role}: upgrade failed -> ${h.message}`); } ``` ```sh sh setup.sh rm -f a-read b-read a-wrote node upgrade.mjs a & node upgrade.mjs b & wait node count.mjs ``` ``` b: upgrade failed -> database is locked a: wrote and committed book status=on_shelf version=2 open loans=0 ``` B's wait time was three seconds; even so, it got the error without waiting. The reason is this: B's transaction opened on a snapshot, and that snapshot is now stale. Waiting does not refresh the snapshot, because it has to stay fixed for the duration of the transaction. The only solution is to roll the transaction back and start over. This is the situation covered in the Advanced SQL course as an **upgrade conflict**. It gives the rule on the application side: if there is a write intent, the lock is acquired before the read (with a write-intent declaration such as `BEGIN IMMEDIATE`); if not, the conflict is handled with the optimistic method. There is no third option sitting between the two. #### Measuring the Cost The difference between the two approaches shows up under contention. The setup below increments a single counter with four workers; each increment is a read, a three-millisecond decision time, and a write. ```sh # counter-setup.sh — single-row counter rm -f counter.db counter.db-wal counter.db-shm sqlite3 counter.db >/dev/null <<'SQL' PRAGMA journal_mode = WAL; CREATE TABLE counter (name TEXT PRIMARY KEY, value INTEGER NOT NULL, version INTEGER NOT NULL); INSERT INTO counter VALUES ('loan_count', 0, 1); SQL ``` ```js // counter-optimistic.mjs — read, decide, write; retry on conflict import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("counter.db"); db.exec("PRAGMA busy_timeout = 10000"); const count = Number(process.argv[2]); const thinkMs = Number(process.argv[3]); const think = () => { const end = Date.now() + thinkMs; while (Date.now() < end) { /* decision time */ } }; let attempts = 0; for (let i = 0; i < count; i++) { for (;;) { attempts += 1; const s = db.prepare("SELECT value, version FROM counter WHERE name = 'loan_count'").get(); think(); db.exec("BEGIN IMMEDIATE"); const result = db.prepare( "UPDATE counter SET value = ?, version = version + 1 WHERE name = 'loan_count' AND version = ?") .run(s.value + 1, s.version); db.exec(result.changes === 0 ? "ROLLBACK" : "COMMIT"); if (result.changes === 1) break; } } console.log(`optimistic worker: ${count} increments, ${attempts} attempts`); ``` ```js // counter-pessimistic.mjs — the lock is taken first, the decision is made under lock import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("counter.db"); db.exec("PRAGMA busy_timeout = 10000"); const count = Number(process.argv[2]); const thinkMs = Number(process.argv[3]); const think = () => { const end = Date.now() + thinkMs; while (Date.now() < end) { /* decision time */ } }; for (let i = 0; i < count; i++) { db.exec("BEGIN IMMEDIATE"); const s = db.prepare("SELECT value FROM counter WHERE name = 'loan_count'").get(); think(); db.prepare("UPDATE counter SET value = ?, version = version + 1 WHERE name = 'loan_count'") .run(s.value + 1); db.exec("COMMIT"); } console.log(`pessimistic worker: ${count} increments, ${count} attempts`); ``` ```sh # counter-measure.sh — four workers, 40 increments per worker, 3 ms decision time for approach in optimistic pessimistic; do sh counter-setup.sh start=$(date +%s%N) workers="" for i in 1 2 3 4; do node counter-$approach.mjs 40 3 & workers="$workers $!"; done wait $workers end=$(date +%s%N) printf '%s: total %s ms, final value = %s\n\n' "$approach" $(( (end-start)/1000000 )) \ "$(sqlite3 counter.db 'SELECT value FROM counter;')" done ``` ```sh sh counter-measure.sh ``` ``` optimistic worker: 40 increments, 119 attempts optimistic worker: 40 increments, 126 attempts optimistic worker: 40 increments, 135 attempts optimistic worker: 40 increments, 155 attempts optimistic: total 510 ms, final value = 160 pessimistic worker: 40 increments, 40 attempts pessimistic worker: 40 increments, 40 attempts pessimistic worker: 40 increments, 40 attempts pessimistic worker: 40 increments, 40 attempts pessimistic: total 713 ms, final value = 160 ``` The final value is 160 in both approaches; both are correct. The durations and attempt counts vary by machine and timing; the relationship does not. The optimistic approach spent well over a hundred attempts for every forty increments — between 119 and 155 per worker — and roughly seven attempts in ten were wasted. Even so, the total duration came out shorter, because the workers spent their decision time in parallel. The pessimistic approach wasted no attempts at all, but the decision times were serialized. The rule for choosing follows from this. When conflict is rare, the optimistic approach is cheap: the number of wasted attempts stays low, and no one waits. When conflict is frequent, the optimistic approach repeats the same work over and over; the pessimistic approach sets up the order from the start and cuts off that waste. When the decision time is long, the pessimistic approach's cost grows quickly, because the lock is held for that entire duration. #### Summary - The unprotected read–decide–write flow issued the single-copy book to two members; the lost update occurred even though the write was inside a transaction. - Optimistic locking places the read version into the write condition; an affected row count of zero is the sign of a conflict, and the losing side retries. - Pessimistic locking makes the decision under a lock; with the wait time at zero it produced a lock error, and with a wait time given it produced a normal rejection with current data. - The transaction that read first and then tried to write got an upgrade conflict; this error is not resolved by waiting, the transaction is started over. - In the measurement, the optimistic approach spent well over a hundred attempts for forty increments but finished faster; the choice depends on how frequent the conflict is and how long the decision takes. #### Next Step Both solutions in this lesson worked inside a single database; the same engine managed both the lock and the version counter. The picture changes if the loan service is not on its own. If the book catalog sits in one service, member records in another, and fine collection in a third, then the "issue a loan" operation touches three separate databases, and none of them can see the others' transaction. The next lesson shows that atomicity cannot cross a service boundary, measures what that loss means by modeling it, and builds the compensation and outbox patterns. ### Distributed Transaction Problem Source: https://academia.sh/en/courses/data-access-layer/distributed-transaction-problem Atomicity's failure to cross a service boundary: writes to two separate databases left half-finished, unwinding with compensating steps, the fact that compensation can fail too, and the outbox pattern, where the status change and the message are written in the same transaction. Both solutions in the previous lesson worked inside a single database. The same engine managed both the lock and the version counter; a `ROLLBACK` call rolled back all the writes together. This foundation disappears if the loan service is not on its own. If the book catalog sits in one service and member records in another, an "issue a loan" request touches two separate databases, and neither can see the other's transaction. This lesson builds that situation, measures the loss, and shows two patterns. #### The Limit of the Model The setup below is not a real distributed system. The two services are two modules running on the same machine, on two separate SQLite files; there is no network between them, and calls are made in-process. **The one property the model carries is that the two databases do not share a common transaction boundary.** The loss of atomicity arises from this property; real distributed-system problems such as network latency, partial failure, and timeouts are not in the model and cannot be inferred from it. ```sh # setup.sh — two services' separate databases rm -f catalog.db membership.db sqlite3 catalog.db >/dev/null <<'SQL' CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, status TEXT NOT NULL CHECK (status IN ('on_shelf','on_loan'))); CREATE TABLE outbox (message_id INTEGER PRIMARY KEY, type TEXT NOT NULL, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, status TEXT NOT NULL DEFAULT 'pending'); INSERT INTO book VALUES (1,'Blindness','on_shelf'),(2,'The Disconnected','on_shelf'),(3,'The Book of Sand','on_shelf'); SQL sqlite3 membership.db >/dev/null <<'SQL' CREATE TABLE member (member_id INTEGER PRIMARY KEY, name TEXT NOT NULL, open_loans INTEGER NOT NULL, limit_count INTEGER NOT NULL); INSERT INTO member VALUES (4,'Derek',2,3),(5,'Grace',3,3),(6,'Owen',0,9); SQL ``` Each service establishes its own transaction boundary internally. It exposes to the outside only calls that carry business meaning. ```js // services.mjs — two separate databases, two separate services; no shared transaction import { DatabaseSync } from "node:sqlite"; export const catalog = { db: new DatabaseSync("catalog.db"), markLoan(bookId) { this.db.exec("BEGIN IMMEDIATE"); const s = this.db.prepare( "UPDATE book SET status = 'on_loan' WHERE book_id = ? AND status = 'on_shelf'").run(bookId); this.db.exec(s.changes === 1 ? "COMMIT" : "ROLLBACK"); if (s.changes !== 1) throw new Error("catalog: book not on shelf"); }, markShelved(bookId) { if (process.env.CATALOG_UNREACHABLE === "1") throw new Error("catalog: service unreachable"); this.db.exec("BEGIN IMMEDIATE"); this.db.prepare("UPDATE book SET status = 'on_shelf' WHERE book_id = ?").run(bookId); this.db.exec("COMMIT"); }, status(bookId) { return this.db.prepare("SELECT status FROM book WHERE book_id = ?").get(bookId).status; }, }; export const membership = { db: new DatabaseSync("membership.db"), addLoan(memberId) { this.db.exec("BEGIN IMMEDIATE"); const s = this.db.prepare( "UPDATE member SET open_loans = open_loans + 1 WHERE member_id = ? AND open_loans < limit_count") .run(memberId); this.db.exec(s.changes === 1 ? "COMMIT" : "ROLLBACK"); if (s.changes !== 1) throw new Error("membership: loan limit exceeded"); }, count(memberId) { return this.db.prepare("SELECT open_loans FROM member WHERE member_id = ?").get(memberId).open_loans; }, }; ``` #### The Loss of Atomicity The most direct way to write this makes the two calls in sequence. ```js // uncompensated.mjs — calls the two services in sequence; no way back if the second one fails import { catalog, membership } from "./services.mjs"; const [bookId, memberId] = process.argv.slice(2).map(Number); try { catalog.markLoan(bookId); membership.addLoan(memberId); console.log(`loan issued: book ${bookId} -> member ${memberId}`); } catch (h) { console.log("failed:", h.message); } console.log(` catalog: book ${bookId} status = ${catalog.status(bookId)}`); console.log(` membership: member ${memberId} open loans = ${membership.count(memberId)}`); ``` Member 4 has two open records and a limit of three; the request should go through. Member 5 is at the limit; the request should be rejected. ```sh sh setup.sh node uncompensated.mjs 1 4 echo "---" node uncompensated.mjs 2 5 ``` ``` loan issued: book 1 -> member 4 catalog: book 1 status = on_loan membership: member 4 open loans = 3 --- failed: membership: loan limit exceeded catalog: book 2 status = on_loan membership: member 5 open loans = 3 ``` The second request was rejected, but the book stayed on loan in the catalog. There is now a loaned book that no one took and that appears in no member's account. No one can borrow it; it cannot be returned either, because there is no record to return. The `try` block does not fix this. The error was caught, the code reported it; the change in the catalog had already committed. **Atomicity holds inside a transaction boundary; it does not hold between two separate boundaries.** The classic solution is two-phase commit: a coordinator asks every participant "are you ready," and if all answer yes, it says "commit." The cost is that participants have to hold their locks between the vote and the decision; if the coordinator becomes unreachable in that gap, the participants wait in an undetermined state. In systems where service boundaries are separated, this cost is mostly unacceptable, and the two patterns below are preferred instead. #### The Compensation Pattern The first pattern imitates atomicity. A **compensating step** is defined that undoes the effect of each step; when a step fails, the ones that already completed are compensated in reverse. This pattern is called a **saga**. ```js // saga.mjs — every step has a compensating step; on failure, completed steps are compensated in reverse import { catalog, membership } from "./services.mjs"; const [bookId, memberId] = process.argv.slice(2).map(Number); const steps = [ { name: "catalog.markLoan", run: () => catalog.markLoan(bookId), compensate: () => catalog.markShelved(bookId) }, { name: "membership.addLoan", run: () => membership.addLoan(memberId), compensate: () => {} }, ]; const completed = []; try { for (const s of steps) { s.run(); completed.push(s); console.log(` step done: ${s.name}`); } console.log(`loan issued: book ${bookId} -> member ${memberId}`); } catch (h) { console.log(" error:", h.message); let missing = 0; for (const s of completed.reverse()) { try { s.compensate(); console.log(` compensated: ${s.name}`); } catch (t) { missing += 1; console.log(` COMPENSATION FAILED: ${s.name} -> ${t.message}`); } } console.log(missing === 0 ? "request rolled back" : `request left half-finished (${missing} compensation pending)`); } console.log(` catalog: book ${bookId} status = ${catalog.status(bookId)}`); console.log(` membership: member ${memberId} open loans = ${membership.count(memberId)}`); ``` ```sh sh setup.sh node saga.mjs 2 5 ``` ``` step done: catalog.markLoan error: membership: loan limit exceeded compensated: catalog.markLoan request rolled back catalog: book 2 status = on_shelf membership: member 5 open loans = 3 ``` The book went back to the shelf. From the outside, the result looks like a rollback, but it is not the same thing. In a rollback, the change never becomes visible; in compensation, the change is visible for a while and is then closed off by an opposite change. Anyone who queries the book in that interval sees it as on loan. The system is inconsistent during that interval and reaches consistency **later**. The compensating step itself is also a business decision. "Remove the loan mark" is a step that can be compensated. "Send the member a notification" cannot; a notification that has already been sent cannot be taken back, though it can be corrected with a second notification. This is why steps that cannot be compensated are placed at the end of the sequence. #### The Limit of Compensation The compensating call is itself a remote call, and it can fail. ```sh sh setup.sh CATALOG_UNREACHABLE=1 node saga.mjs 2 5 ``` ``` step done: catalog.markLoan error: membership: loan limit exceeded COMPENSATION FAILED: catalog.markLoan -> catalog: service unreachable request left half-finished (1 compensation pending) catalog: book 2 status = on_loan membership: member 5 open loans = 3 ``` This returns to the exact same inconsistent state as the beginning. The saga pattern did not remove the problem, it carried the responsibility: there is now a request sitting in a "compensation pending" state in the system, and someone has to retry it. This is why compensating steps are recorded durably; even if the process crashes, a trace of the pending compensation has to remain. #### The Outbox The second pattern catches the problem from a different angle. While the catalog service changes the status, it also writes the "this happened" information to its own database, **in the same transaction**. Because the two writes sit inside a single boundary, either both happen or neither does. ```js // write-to-outbox.mjs — writes the status change and the message in the same transaction import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("catalog.db"); const [bookId, memberId] = process.argv.slice(2).map(Number); db.exec("BEGIN IMMEDIATE"); const s = db.prepare( "UPDATE book SET status = 'on_loan' WHERE book_id = ? AND status = 'on_shelf'").run(bookId); if (s.changes !== 1) { db.exec("ROLLBACK"); console.log("book not on shelf"); process.exit(1); } db.prepare("INSERT INTO outbox (type, book_id, member_id) VALUES ('loan_issued',?,?)") .run(bookId, memberId); db.exec("COMMIT"); console.log(`catalog: book ${bookId} on loan, message written to outbox`); ``` Getting the message to its destination is a separate process. This pattern is called an **outbox**. ```js // relay.mjs — sends outbox messages to the destination service; stops before marking with --crash import { DatabaseSync } from "node:sqlite"; import { membership } from "./services.mjs"; const crash = process.argv.includes("--crash"); const db = new DatabaseSync("catalog.db"); const pending = db.prepare( "SELECT message_id, book_id, member_id FROM outbox WHERE status = 'pending' ORDER BY message_id").all(); for (const m of pending) { membership.addLoan(m.member_id); console.log(`relay: message ${m.message_id} sent (member ${m.member_id})`); if (crash) { console.log("relay: crashed before marking"); process.exit(0); } db.prepare("UPDATE outbox SET status = 'sent' WHERE message_id = ?").run(m.message_id); } console.log(`relay: ${pending.length} messages processed`); ``` The gain is this: if the status changed, the message is guaranteed to exist. If the relay crashes, the message waits in the outbox, and it is sent once the relay restarts. No information is lost. #### At-Least-Once Delivery The gain has a cost. The relay can crash after sending the message but before marking the outbox. ```sh sh setup.sh node write-to-outbox.mjs 3 6 node relay.mjs --crash echo "-- rerun --" node relay.mjs sqlite3 membership.db "SELECT member_id, open_loans FROM member WHERE member_id = 6;" sqlite3 catalog.db "SELECT message_id, status FROM outbox;" ``` ``` catalog: book 3 on loan, message written to outbox relay: message 1 sent (member 6) relay: crashed before marking -- rerun -- relay: message 1 sent (member 6) relay: 1 messages processed 6|2 1|sent ``` A single loan was issued, and the member's open loan count rose to two. The message was delivered twice, and its effect was applied twice. This is unavoidable. The relay writing the "I sent it" information and the destination processing the message happen in two separate databases; that is, the exact same problem from the first section is back in front of us, one layer down. The only way to cut off the infinite regress is to lower the guarantee and fix up the result: delivery is made **at least once**, and the receiver produces no additional effect when it receives the same message a second time. The receiver carrying this property is the condition the outbox pattern has to satisfy. #### Summary - Writes made to two separate databases do not share a common transaction boundary; when the second call failed, the first one stayed committed, and a loaned book that no one had taken resulted. - Two-phase commit preserves atomicity; its cost is that participants hold locks while waiting for the decision. - The compensation pattern unwinds completed steps in reverse; its difference from a rollback is that the inconsistent state is visible for a while. - The compensating call can fail too; in that case the pattern does not remove the problem, it leaves a pending compensation record and requires a retry. - The outbox prevents information loss by writing the status change and the message in the same transaction; its cost is that the message is delivered at least once — sometimes more than once. #### Next Step The outbox pattern has one unmet condition left: processing the same message a second time must not produce an additional effect. In this measurement, the member's open loan count rose to two, so the condition was not met. The same problem shows up in every retry that comes over the network; when a client gets a timeout and repeats a request, the server needs to know whether it already processed the first one. The next lesson defines why a repeated write produces no additional effect, separates writes that naturally carry this property from ones that do not, and makes the ones that do not safe with an idempotency key. ### Idempotent Transactions Source: https://academia.sh/en/courses/data-access-layer/idempotent-transactions Making retries safe: measuring how three write shapes behave under repetition, making a retry idempotent through assignment, derivation, and conflict behavior, storing an idempotency key together with the response in the same transaction, and fixing the outbox consumer. The previous lesson built the outbox scheme and left one condition unmet: processing the same message a second time should not have produced an additional effect. In the measurement, the member's open loan count rose from one to two; the condition was not met. The same problem appears on every retry. When a client gets a timeout and repeats a request, whether the server processed the first one is not known; the response may have been lost along the way. This lesson defines when a repeat is harmless and makes the cases where it is not harmless. A write is an **idempotent operation** if its second and later runs produce no effect beyond the first. The Web API Design course covered the same property for HTTP methods under the name **idempotent** method; what matters here is how that property gets built at the data access layer. #### Three Write Shapes Issuing a loan involves three writes: the book's status changes, the member's open loan count increases, and a loan row is inserted. The three behave differently under the same repetition. ```sh # setup.sh — empty starting state for the measurement rm -f library.db sqlite3 library.db >/dev/null <<'SQL' CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, status TEXT NOT NULL CHECK (status IN ('on_shelf','on_loan'))); CREATE TABLE member (member_id INTEGER PRIMARY KEY, name TEXT NOT NULL, open_loans INTEGER NOT NULL); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT); INSERT INTO book VALUES (1,'Blindness','on_shelf'); INSERT INTO member VALUES (4,'Derek',0); SQL ``` ```js // three-writes.mjs — three write shapes, each run three times in a row import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("library.db"); const writes = { "status assignment ": () => db.prepare("UPDATE book SET status = 'on_loan' WHERE book_id = 1").run(), "counter increment ": () => db.prepare("UPDATE member SET open_loans = open_loans + 1 WHERE member_id = 4").run(), "record insert ": () => db.prepare( "INSERT INTO loan (book_id, member_id, pickup_date) VALUES (1,4,'2025-07-20')").run(), }; for (const [name, write] of Object.entries(writes)) { for (let i = 0; i < 3; i++) write(); } const b = db.prepare("SELECT status FROM book WHERE book_id = 1").get().status; const m = db.prepare("SELECT open_loans AS n FROM member WHERE member_id = 4").get().n; const l = db.prepare("SELECT count(*) AS n FROM loan").get().n; console.log(`after three runs: book.status=${b} member.open_loans=${m} loan rows=${l}`); ``` ```sh sh setup.sh node three-writes.mjs ``` ``` after three runs: book.status=on_loan member.open_loans=3 loan rows=3 ``` The distinction is clear. **Assignment** is idempotent: the `status = 'on_loan'` write brings the target to the same value no matter how many times it runs. **Increment** is not idempotent, because the result depends on the previous value. **Insert** is not idempotent, because every call produces a new row. The rule follows from this: a write is idempotent if its result depends only on the target state; it is not if the result depends on how many times it has run. #### Converting to an Idempotent Shape Both problem writes can be fixed by changing shape. For the record insert, a business-meaningful uniqueness constraint is defined, and the conflict behavior is specified. The counter increment is replaced with an assignment instead: the counter stops being an accumulation and becomes a value derived from the existing rows. ```sh # setup2.sh — business-meaningful uniqueness constraint for the record insert rm -f library.db sqlite3 library.db >/dev/null <<'SQL' CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, status TEXT NOT NULL CHECK (status IN ('on_shelf','on_loan'))); CREATE TABLE member (member_id INTEGER PRIMARY KEY, name TEXT NOT NULL, open_loans INTEGER NOT NULL); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT); CREATE UNIQUE INDEX loan_unique ON loan (book_id, member_id, pickup_date); INSERT INTO book VALUES (1,'Blindness','on_shelf'); INSERT INTO member VALUES (4,'Derek',0); SQL ``` ```js // three-writes-idempotent.mjs — the same three tasks, in a shape where repetition produces no effect import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("library.db"); const writes = { "status assignment": () => db.prepare("UPDATE book SET status = 'on_loan' WHERE book_id = 1").run(), "record insert": () => db.prepare( `INSERT INTO loan (book_id, member_id, pickup_date) VALUES (1,4,'2025-07-20') ON CONFLICT (book_id, member_id, pickup_date) DO NOTHING`).run(), "counter derivation": () => db.prepare( `UPDATE member SET open_loans = (SELECT count(*) FROM loan WHERE member_id = 4 AND return_date IS NULL) WHERE member_id = 4`).run(), }; for (let i = 0; i < 3; i++) { const affected = Object.entries(writes).map(([name, write]) => `${name}=${write().changes}`); console.log(`round ${i + 1}: ${affected.join(" ")}`); } const b = db.prepare("SELECT status FROM book WHERE book_id = 1").get().status; const m = db.prepare("SELECT open_loans AS n FROM member WHERE member_id = 4").get().n; const l = db.prepare("SELECT count(*) AS n FROM loan").get().n; console.log(`after three rounds: book.status=${b} member.open_loans=${m} loan rows=${l}`); ``` ```sh sh setup2.sh node three-writes-idempotent.mjs ``` ``` round 1: status assignment=1 record insert=1 counter derivation=1 round 2: status assignment=1 record insert=0 counter derivation=1 round 3: status assignment=1 record insert=0 counter derivation=1 after three rounds: book.status=on_loan member.open_loans=1 loan rows=1 ``` After three rounds, a single loan row and the correct counter value remain. The affected-row counts show the mechanism: from the second round on, the record insert has zero effect. The counter derivation keeps affecting one row every round, but the value it writes does not change; idempotence does not mean the affected-row count is zero, it means **the result does not change**. Which columns the uniqueness constraint is built from is a business decision. The constraint here says a member cannot borrow the same book twice on the same day. If the rule is wrong, the idempotence is built wrong too; the constraint must be chosen from the columns that genuinely define the same business intent. #### Idempotency Key A business-meaningful uniqueness cannot always be found. The same member can return the same book and borrow it again the same day; in that case, the second request is not a duplicate request, it is a separate one. The way to tell the intent apart is for the requester to give it a name. The **idempotency key**, introduced in the Web API Design course, is written to a relation at the data access layer. The critical detail is the moment of writing: the key and the effect must be written **in the same transaction**. If they are written in separate transactions, a process that crashes between the two leaves either an unused key or an effect with no key. ```sh # setup3.sh — idempotency-relation setup rm -f library.db sqlite3 library.db >/dev/null <<'SQL' CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, status TEXT NOT NULL CHECK (status IN ('on_shelf','on_loan'))); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT); CREATE TABLE idempotency (key TEXT PRIMARY KEY, response TEXT NOT NULL); INSERT INTO book VALUES (1,'Blindness','on_shelf'),(2,'The Disconnected','on_shelf'); SQL ``` ```js // idempotency.mjs — key and response are written in the same transaction as the effect import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("library.db"); function issueLoan(key, bookId, memberId, date) { db.exec("BEGIN IMMEDIATE"); const previous = db.prepare("SELECT response FROM idempotency WHERE key = ?").get(key); if (previous !== undefined) { db.exec("ROLLBACK"); return { ...JSON.parse(previous.response), replayed: true }; } const s = db.prepare( "UPDATE book SET status = 'on_loan' WHERE book_id = ? AND status = 'on_shelf'").run(bookId); let response; if (s.changes !== 1) { response = { status: "rejected", reason: "book not on shelf" }; } else { const id = Number(db.prepare( "INSERT INTO loan (book_id, member_id, pickup_date) VALUES (?,?,?)") .run(bookId, memberId, date).lastInsertRowid); response = { status: "issued", loanId: id }; } db.prepare("INSERT INTO idempotency (key, response) VALUES (?,?)").run(key, JSON.stringify(response)); db.exec("COMMIT"); return { ...response, replayed: false }; } for (const [key, bookId] of [["LN-7f21", 1], ["LN-7f21", 1], ["LN-7f21", 1], ["LN-9c04", 1]]) { console.log(`${key} book=${bookId} ->`, JSON.stringify(issueLoan(key, bookId, 4, "2025-07-20"))); } console.log("loan rows:", db.prepare("SELECT count(*) AS n FROM loan").get().n); console.log("idempotency rows:", db.prepare("SELECT count(*) AS n FROM idempotency").get().n); ``` ```sh sh setup3.sh node idempotency.mjs ``` ``` LN-7f21 book=1 -> {"status":"issued","loanId":1,"replayed":false} LN-7f21 book=1 -> {"status":"issued","loanId":1,"replayed":true} LN-7f21 book=1 -> {"status":"issued","loanId":1,"replayed":true} LN-9c04 book=1 -> {"status":"rejected","reason":"book not on shelf","replayed":false} loan rows: 1 idempotency rows: 2 ``` Three calls with the same key produced a single loan record, and all three returned the same identifier. Storing the response does more than just block the effect: the duplicate request also gets a successful response. If the response were not stored, the second call would have to say "already done," and the client might interpret that as an error. The fourth row shows an important distinction. A request with a different key is not a duplicate request; it was processed and rejected under the business rule. A rejection is a result too, and it was stored as well — the idempotency relation has two rows. If it were not stored, retrying the same rejected request would run the rule again. The idempotency relation grows without bound. Keys need a retention window; the window is chosen longer than the clients' retry period and short enough that the storage cost stays acceptable. #### The Outbox Consumer The measurement from the previous lesson can now be fixed. As the consumer processes a message, it records the message identifier in the same transaction. ```sh # setup4.sh — the consumer side rm -f membership.db sqlite3 membership.db >/dev/null <<'SQL' CREATE TABLE member (member_id INTEGER PRIMARY KEY, name TEXT NOT NULL, open_loans INTEGER NOT NULL); CREATE TABLE processed_message (message_id INTEGER PRIMARY KEY, processed_at TEXT NOT NULL); INSERT INTO member VALUES (6,'Owen',0); SQL ``` ```js // consume.mjs — the message identifier is written in the same transaction as the effect; the second delivery stays idempotent import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("membership.db"); const messageId = Number(process.argv[2]); const memberId = Number(process.argv[3]); db.exec("BEGIN IMMEDIATE"); const fresh = db.prepare( "INSERT INTO processed_message (message_id, processed_at) VALUES (?, datetime('now')) ON CONFLICT DO NOTHING") .run(messageId); if (fresh.changes === 0) { db.exec("ROLLBACK"); console.log(`message ${messageId}: already processed, effect not applied`); } else { db.prepare("UPDATE member SET open_loans = open_loans + 1 WHERE member_id = ?").run(memberId); db.exec("COMMIT"); console.log(`message ${messageId}: processed`); } console.log(` member ${memberId} open loans =`, db.prepare("SELECT open_loans AS n FROM member WHERE member_id = ?").get(memberId).n); ``` ```sh sh setup4.sh node consume.mjs 1 6 node consume.mjs 1 6 node consume.mjs 2 6 ``` ``` message 1: processed member 6 open loans = 1 message 1: already processed, effect not applied member 6 open loans = 1 message 2: processed member 6 open loans = 2 ``` Message one's two deliveries produced a single effect; message two was processed as a separate task. The counter that rose to two in the previous lesson is now one. The counter-increment write itself is still not idempotent; what is idempotent is the consumer as a whole. **Idempotence comes from either the write's shape or a guard record; either one is enough.** Writing the guard record in the same transaction as the effect is mandatory here too. If the record is written first and the process crashes afterward, the message counts as processed but its effect was never applied; if the effect is applied first and the record fails to get written, the message gets processed again. Bringing the two under a single boundary closes off both failures. #### Summary - A write is idempotent if its result depends only on the target state; in the measurement, the status assignment gave the same result after three runs, while the counter increment produced 3 and the record insert produced 3 rows. - The record insert was converted to an idempotent shape with a business-meaningful uniqueness constraint and a conflict behavior, and the counter increment with a derived assignment; a single row remained after three rounds. - When a business-meaningful uniqueness cannot be found, the request carries its own name; the idempotency key is written in the same transaction as the effect. - Storing the response lets the duplicate request also get a successful result; a rejected result is stored too. - When the outbox consumer records the message identifier in the same transaction as the effect, a message delivered twice produced a single effect. #### Next Step The transactions topic closed the four questions that establish a write's correctness: where the boundary is drawn, what a read sees, how a concurrent update is resolved, and why a retry is safe. These questions were about correctness; none of them were about speed. A data access layer that runs correctly can still be slow, and the source of the slowness is usually not the query itself but the query **count**. The mapper built in the first lesson ran ten queries for three records. The next topic measures that count, shows how it grows with the record count, and proves through implementation how batch fetching keeps it fixed. ## Performance Problems Measuring and resolving the N+1 problem that multiplies the query count, the cost of over-fetching in bytes transferred, batch insert and update patterns, diagnosing a slow query seen from the application by reading its plan, and the cost of deep pagination. ### N+1 Query Problem Source: https://academia.sh/en/courses/data-access-layer/n-plus-1-query-problem Query count growing with record count: measurement with a counting wrapper, comparing one-by-one loading against batch fetch and join, a scaling table, the effect of round-trip cost, and writing the query budget as a test. The transactions topic established the correctness of writing: where the boundary is drawn, what a read sees, how a concurrent update is resolved, why a retry is safe. None of it was about speed. A data access layer that works correctly can still be slow, and the source of the slowness is usually not the query itself but the query **count**. This course's first lesson had a small mapper run ten queries for three loan records. This lesson measures that count, shows how it grows with the record count, and fixes it. #### Measurement Data Measurement requires a dataset of realistic size. ```js // generate.mjs — generates library data for measurement: 200 books, 60 members, 2000 loans import { DatabaseSync } from "node:sqlite"; import { rmSync } from "node:fs"; for (const d of ["library.db", "library.db-wal", "library.db-shm"]) { rmSync(d, { force: true }); } const db = new DatabaseSync("library.db"); db.exec(` CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL); CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT);`); db.exec("BEGIN"); const insertMember = db.prepare("INSERT INTO member VALUES (?,?,?)"); for (let i = 1; i <= 60; i++) insertMember.run(i, `Member${i}`, `Last${i}`); const insertBook = db.prepare("INSERT INTO book VALUES (?,?,?)"); for (let i = 1; i <= 200; i++) insertBook.run(i, `Book ${i}`, `Author ${(i % 40) + 1}`); const insertLoan = db.prepare("INSERT INTO loan VALUES (?,?,?,?,?)"); for (let i = 1; i <= 2000; i++) { const day = String((i % 28) + 1).padStart(2, "0"); const month = String((i % 12) + 1).padStart(2, "0"); insertLoan.run(i, (i % 200) + 1, (i % 60) + 1, `2025-${month}-${day}`, i % 4 === 0 ? null : "2025-12-31"); } db.exec("COMMIT"); console.log("member:", db.prepare("SELECT count(*) AS n FROM member").get().n, " book:", db.prepare("SELECT count(*) AS n FROM book").get().n, " loan:", db.prepare("SELECT count(*) AS n FROM loan").get().n, " open:", db.prepare("SELECT count(*) AS n FROM loan WHERE return_date IS NULL").get().n); ``` ```sh node generate.mjs ``` ``` member: 60 book: 200 loan: 2000 open: 500 ``` #### Making the Query Visible A count that is not measured does not fall. A thin wrapper placed in front of the connection counts the queries run and the rows returned. ```js // counter.mjs — a thin wrapper that counts the queries it runs import { DatabaseSync } from "node:sqlite"; export function countingConnection(file) { const db = new DatabaseSync(file); const counter = { query: 0, row: 0 }; return { counter, single(sql, ...d) { counter.query += 1; const r = db.prepare(sql).get(...d); if (r !== undefined) counter.row += 1; return r; }, many(sql, ...d) { counter.query += 1; const r = db.prepare(sql).all(...d); counter.row += r.length; return r; }, reset() { counter.query = 0; counter.row = 0; }, }; } ``` #### Three Approaches The task is the same: list open loans with the book title and the member's name. Three approaches are tried. **One by one** loading reads the relations separately for each record; it is the direct, written-out form of the lazy loading built in the first lesson. **Batch fetch** collects the identifiers and reads them in a single query. **Join** leaves the work to the database. ```js // n-plus-1.mjs — three approaches, same list; query and row counts are measured import { countingConnection } from "./counter.mjs"; const db = countingConnection("library.db"); const ROW_LIMIT = Number(process.argv[2] ?? 100); function oneByOne() { db.reset(); const t = performance.now(); const records = db.many( "SELECT loan_id, book_id, member_id FROM loan WHERE return_date IS NULL ORDER BY loan_id LIMIT ?", ROW_LIMIT); const list = records.map((o) => ({ id: o.loan_id, title: db.single("SELECT title FROM book WHERE book_id = ?", o.book_id).title, member: db.single("SELECT first_name FROM member WHERE member_id = ?", o.member_id).first_name, })); return { name: "one by one", list, ...db.counter, duration: performance.now() - t }; } function batchFetch() { db.reset(); const t = performance.now(); const records = db.many( "SELECT loan_id, book_id, member_id FROM loan WHERE return_date IS NULL ORDER BY loan_id LIMIT ?", ROW_LIMIT); const bookIds = [...new Set(records.map((o) => o.book_id))]; const memberIds = [...new Set(records.map((o) => o.member_id))]; const bookLookup = new Map(db.many( `SELECT book_id, title FROM book WHERE book_id IN (${bookIds.map(() => "?").join(",")})`, ...bookIds).map((k) => [k.book_id, k.title])); const memberLookup = new Map(db.many( `SELECT member_id, first_name FROM member WHERE member_id IN (${memberIds.map(() => "?").join(",")})`, ...memberIds).map((u) => [u.member_id, u.first_name])); const list = records.map((o) => ({ id: o.loan_id, title: bookLookup.get(o.book_id), member: memberLookup.get(o.member_id), })); return { name: "batch fetch", list, ...db.counter, duration: performance.now() - t }; } function join() { db.reset(); const t = performance.now(); const list = db.many( `SELECT o.loan_id AS id, k.title, u.first_name AS member FROM loan o JOIN book k ON k.book_id = o.book_id JOIN member u ON u.member_id = o.member_id WHERE o.return_date IS NULL ORDER BY o.loan_id LIMIT ?`, ROW_LIMIT); return { name: "join", list, ...db.counter, duration: performance.now() - t }; } const results = [oneByOne(), batchFetch(), join()]; const first = JSON.stringify(results[0].list); for (const s of results) { console.log(`${s.name.padEnd(14)} query=${String(s.query).padStart(4)} ` + `returned_rows=${String(s.row).padStart(4)} duration=${s.duration.toFixed(1).padStart(6)} ms ` + `result_same=${JSON.stringify(s.list) === first}`); } ``` ```sh node n-plus-1.mjs 100 ``` ``` one by one query= 201 returned_rows= 300 duration= 1.2 ms result_same=true batch fetch query= 3 returned_rows= 165 duration= 0.2 ms result_same=true join query= 1 returned_rows= 100 duration= 0.1 ms result_same=true ``` All three approaches produced the same list; the last column confirms it. The query counts are 201, 3, and 1. The shape of the first number names it: one query fetches the list, then extra queries run for each record. Two relations are read per record in this example, so the count comes to $1 + 2N$. In its general form, this is the **N+1 query problem**: one query for the list, one query per item. #### The Number's Growth The severity of the problem does not show up in a single measurement; it shows up as the record count grows. ```sh for n in 10 50 100 250 500; do echo "--- record count $n ---"; node n-plus-1.mjs $n; done ``` ``` --- record count 10 --- one by one query= 21 returned_rows= 30 duration= 0.3 ms result_same=true batch fetch query= 3 returned_rows= 30 duration= 0.1 ms result_same=true join query= 1 returned_rows= 10 duration= 0.0 ms result_same=true --- record count 50 --- one by one query= 101 returned_rows= 150 duration= 0.8 ms result_same=true batch fetch query= 3 returned_rows= 115 duration= 0.2 ms result_same=true join query= 1 returned_rows= 50 duration= 0.1 ms result_same=true --- record count 100 --- one by one query= 201 returned_rows= 300 duration= 1.2 ms result_same=true batch fetch query= 3 returned_rows= 165 duration= 0.2 ms result_same=true join query= 1 returned_rows= 100 duration= 0.1 ms result_same=true --- record count 250 --- one by one query= 501 returned_rows= 750 duration= 2.6 ms result_same=true batch fetch query= 3 returned_rows= 315 duration= 0.2 ms result_same=true join query= 1 returned_rows= 250 duration= 0.1 ms result_same=true --- record count 500 --- one by one query=1001 returned_rows=1500 duration= 5.1 ms result_same=true batch fetch query= 3 returned_rows= 565 duration= 0.4 ms result_same=true join query= 1 returned_rows= 500 duration= 0.2 ms result_same=true ``` Durations depend on the machine; query counts do not. In one-by-one loading, the count grows linearly with the record count. In batch fetch it stays at three; in join it stays at one. **Staying constant is the property being sought**: when the record count in a list doubles, the query count should not change. The returned-row counts show a second distinction. Batch fetch returned 565 rows for five hundred records; join returned 500. In the first, loan records were read once and books and members were read de-duplicated; in the second, every row carries its own book and member columns, so the same book's title is transferred over and over. This difference is the subject of the next lesson. #### What the Local Measurement Hides In the durations above, one-by-one loading took five milliseconds for five hundred records; that looks acceptable. This is a result of measuring against a local file. The round trip discussed in the Connection Pool lesson becomes decisive here. If the database sits on a separate server, every query costs a network round trip. Assume just half a millisecond per round trip: 1001 queries come to more than 500 milliseconds, a single query to half a millisecond. This is not a measurement; it is a calculation made from the measured query count. What the calculation says is that query count is a cost that stays hidden in a local environment and surfaces in a real one. For that reason, the performance metric is set as **query count**, not duration. Duration changes from environment to environment; query count is a property of the code. #### Batch Fetch or Join The choice between the two solutions depends on the shape of the data. Join is a single query and returns the fewest rows; it is the right choice when the relations are one-to-one or one-to-few. In one-to-many relations, the result set multiplies: if every loan record has three tags, the row count triples and the loan columns repeat in every row. Batch fetch adds a fixed number of extra queries but produces no multiplication. Every relation is read once, and the join happens in the application. When there are many one-to-many relations, this approach transfers less data. Batch fetch has an implementation detail: the identifier list enters the query text as question marks. As the list grows, the text grows with it, and the engine's bound-variable limit is approached. For that reason, identifiers are requested in chunks; as long as the chunk size stays fixed, the query count grows with a fixed division factor, not with the record count. #### Turning the Budget into a Test A query count that can be measured can be tested. The test below fails if the list goes over a given budget. ```js // budget.test.mjs — the query budget is written as a test import { test } from "node:test"; import assert from "node:assert/strict"; import { countingConnection } from "./counter.mjs"; const BUDGET = 5; function openLoanList(db, limit) { const records = db.many( "SELECT loan_id, book_id, member_id FROM loan WHERE return_date IS NULL ORDER BY loan_id LIMIT ?", limit); const bookIds = [...new Set(records.map((o) => o.book_id))]; const memberIds = [...new Set(records.map((o) => o.member_id))]; const bookLookup = new Map(db.many( `SELECT book_id, title FROM book WHERE book_id IN (${bookIds.map(() => "?").join(",")})`, ...bookIds).map((k) => [k.book_id, k.title])); const memberLookup = new Map(db.many( `SELECT member_id, first_name FROM member WHERE member_id IN (${memberIds.map(() => "?").join(",")})`, ...memberIds).map((u) => [u.member_id, u.first_name])); return records.map((o) => ({ id: o.loan_id, title: bookLookup.get(o.book_id), member: memberLookup.get(o.member_id) })); } for (const limit of [10, 100, 500]) { test(`open loan list for ${limit} records does not exceed the budget`, () => { const db = countingConnection("library.db"); const list = openLoanList(db, limit); assert.equal(list.length, limit); assert.ok(db.counter.query <= BUDGET, `query count ${db.counter.query} > ${BUDGET}`); }); } ``` ```sh node --test budget.test.mjs ``` ``` ✔ open loan list for 10 records does not exceed the budget (0.722292ms) ✔ open loan list for 100 records does not exceed the budget (0.57875ms) ✔ open loan list for 500 records does not exceed the budget (0.475583ms) ℹ tests 3 ℹ suites 0 ℹ pass 3 ℹ fail 0 ℹ cancelled 0 ℹ skipped 0 ℹ todo 0 ℹ duration_ms 36.655375 ``` The value of the test is that it asks for the same budget at three different record counts. If someone makes a change that reverts to lazy loading, the five-hundred-record test goes over budget and the failure is reported together with the query count. This is the only automatic path that catches the N+1 problem slipping past code review. #### Summary - Once counted with the wrapper, query count became visible: for the same list, one-by-one loading ran 201 queries, batch fetch 3, join 1. - In one-by-one loading, query count grows linearly with record count, reaching 1001 at five hundred records. It stayed constant in the other two approaches. - The gap stays in the milliseconds in a local measurement; over a network, every query costs a round trip and the same gap grows into seconds. That is why the metric is query count, not duration. - Join returns the fewest rows but multiplies the result in one-to-many relations; batch fetch produces no multiplication, in exchange for a fixed number of extra queries. - Once the query budget is turned into a test, a regression to lazy loading is caught automatically. #### Next Step This lesson brought one number down and let another slip past. Batch fetch returned 565 rows for five hundred records, join 500 — but row count is not the measure of the data transferred. The queries asked only for the needed columns. In real applications, most queries ask for every column, most lists fetch more rows than needed, and the difference stays invisible until it is measured in bytes. The next lesson pulls the same list with different column sets, measures the transferred data in bytes, and shows the payoff of limiting at the field and row level. ### Over-Fetching Source: https://academia.sh/en/courses/data-access-layer/over-fetching Measuring transferred data in bytes: the difference between fetching every column and fetching only the needed ones, the contribution of row limiting, whether aggregation belongs in the application or the database, and star selection silently dropping data on column collision. The previous lesson brought the query count down. Batch fetch returned 565 rows for five hundred records, join 500, and row count looked like the right metric. Row count is not the measure of transferred data. A row can carry five columns or twenty; one of those columns can be a short date, or a thousand-character summary. This lesson converts the metric to bytes and measures the two levels of limiting — which columns and how many rows — separately. #### Measurement Data For the measurement to be meaningful, the relations need realistic width: a summary and cover text on the book, an address and notes on the member. ```js // generate.mjs — measurement data with a book relation carrying a summary text import { DatabaseSync } from "node:sqlite"; import { rmSync } from "node:fs"; rmSync("library.db", { force: true }); const db = new DatabaseSync("library.db"); db.exec(` CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT, address TEXT, notes TEXT); CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, isbn TEXT NOT NULL, summary TEXT NOT NULL, cover_text TEXT NOT NULL); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT);`); const text = (n, seed) => `${seed} `.repeat(n).slice(0, n * 6); db.exec("BEGIN"); const insertMember = db.prepare("INSERT INTO member VALUES (?,?,?,?,?,?)"); for (let i = 1; i <= 60; i++) { insertMember.run(i, `Member${i}`, `Last${i}`, `member${i}@example.test`, text(20, `Street${i}`), text(30, "note")); } const insertBook = db.prepare("INSERT INTO book VALUES (?,?,?,?,?,?)"); for (let i = 1; i <= 200; i++) { insertBook.run(i, `Book ${i}`, `Author ${(i % 40) + 1}`, `978-0-00-${String(100000 + i).slice(0, 6)}`, text(120, "summary"), text(60, "cover")); } const insertLoan = db.prepare("INSERT INTO loan VALUES (?,?,?,?,?)"); for (let i = 1; i <= 2000; i++) { const day = String((i % 28) + 1).padStart(2, "0"); const month = String((i % 12) + 1).padStart(2, "0"); insertLoan.run(i, (i % 200) + 1, (i % 60) + 1, `2025-${month}-${day}`, i % 4 === 0 ? null : "2025-12-31"); } db.exec("COMMIT"); console.log("book:", db.prepare("SELECT count(*) AS n FROM book").get().n, " average summary length:", db.prepare("SELECT avg(length(summary)) AS n FROM book").get().n, " open loans:", db.prepare("SELECT count(*) AS n FROM loan WHERE return_date IS NULL").get().n); ``` ```sh node generate.mjs ``` ``` book: 200 average summary length: 720 open loans: 500 ``` #### Limiting at Two Levels The task is the same again: the open loan list. The fields shown on screen are the loan ID, pickup date, book title, and the member's name. Three queries pull this list at three different widths. ```js // field-measurement.mjs — pulls the same list with different column sets, measures transferred bytes import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("library.db"); const measure = (label, sql) => { const t = performance.now(); const rows = db.prepare(sql).all(); const duration = performance.now() - t; const bytes = Buffer.byteLength(JSON.stringify(rows)); console.log(`${label.padEnd(26)} rows=${String(rows.length).padStart(4)} ` + `bytes=${String(bytes).padStart(7)} per_row=${String(Math.round(bytes / rows.length)).padStart(5)} ` + `duration=${duration.toFixed(1)} ms`); return bytes; }; const allColumns = measure("all columns", ` SELECT * FROM loan o JOIN book k ON k.book_id = o.book_id JOIN member u ON u.member_id = o.member_id WHERE o.return_date IS NULL ORDER BY o.loan_id`); const neededColumns = measure("only needed columns", ` SELECT o.loan_id, o.pickup_date, k.title, u.first_name, u.last_name FROM loan o JOIN book k ON k.book_id = o.book_id JOIN member u ON u.member_id = o.member_id WHERE o.return_date IS NULL ORDER BY o.loan_id`); const neededColumnsPage = measure("needed columns + 20 rows", ` SELECT o.loan_id, o.pickup_date, k.title, u.first_name, u.last_name FROM loan o JOIN book k ON k.book_id = o.book_id JOIN member u ON u.member_id = o.member_id WHERE o.return_date IS NULL ORDER BY o.loan_id LIMIT 20`); console.log(`\ncolumn selection ratio: ${(allColumns / neededColumns).toFixed(1)}x`); console.log(`column + row ratio: ${(allColumns / neededColumnsPage).toFixed(1)}x`); ``` ```sh node field-measurement.mjs ``` ``` all columns rows= 500 bytes= 817162 per_row= 1634 duration=2.3 ms only needed columns rows= 500 bytes= 53244 per_row= 106 duration=0.3 ms needed columns + 20 rows rows= 20 bytes= 2087 per_row= 104 duration=0.0 ms column selection ratio: 15.3x column + row ratio: 391.5x ``` Row count is the same in the first two measurements: five hundred. The transferred data differs by a factor of fifteen. The difference comes entirely from column selection; 106 bytes are transferred per row instead of 1634. The summary, cover text, address, and notes columns that never appear on screen make up thirteen fourteenths of the transferred data. Row limiting adds a second multiplier. For an interface that shows twenty rows per page, pulling all five hundred rows is unnecessary; the two limits together push the ratio to 391-fold. Durations are misleading here, and they are kept in on purpose because of that: since the database runs in-process, 798 kilobytes get "transferred" in 2.3 milliseconds. Over a network, the same data is carried multiplied by bandwidth and latency. The metric is set again as bytes, not duration. #### Where to Put Aggregation The sharpest form of over-fetching is the application taking over the database's job. The question "how many loans are open" can be answered two ways. ```js // where-to-aggregate.mjs — should counting happen in the application or the database import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("library.db"); let t = performance.now(); const rows = db.prepare("SELECT * FROM loan WHERE return_date IS NULL").all(); const inApplication = rows.length; const appDuration = performance.now() - t; const appBytes = Buffer.byteLength(JSON.stringify(rows)); t = performance.now(); const inDatabase = db.prepare( "SELECT count(*) AS n FROM loan WHERE return_date IS NULL").get().n; const dbDuration = performance.now() - t; const dbBytes = Buffer.byteLength(JSON.stringify({ n: inDatabase })); console.log(`counting in the application: result=${inApplication} bytes=${appBytes} duration=${appDuration.toFixed(2)} ms`); console.log(`counting in the database : result=${inDatabase} bytes=${dbBytes} duration=${dbDuration.toFixed(2)} ms`); console.log(`byte ratio: ${Math.round(appBytes / dbBytes)}x`); ``` ```sh node where-to-aggregate.mjs ``` ``` counting in the application: result=500 bytes=45345 duration=0.30 ms counting in the database : result=500 bytes=9 duration=0.06 ms byte ratio: 5038x ``` The same number, with five-thousand-fold different data. Counting in the application grows with the record count; counting in the database returns a constant nine bytes. Work like aggregation, filtering, and sorting is done where the data lives; only the result travels to the application. This principle holds exactly the same way for grouping and extreme-value queries. #### The Second Cost of Star Selection Requesting every column does not cost only bytes. In a join, columns with the same name collide, and in the result object one overwrites the other. ```js // star-trap.mjs — star selection in a join silently merges columns with the same name import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("library.db"); const JOINED = `FROM loan o JOIN book k ON k.book_id = o.book_id JOIN member u ON u.member_id = o.member_id WHERE o.loan_id = 4`; console.log("column total of the three relations: 5 + 6 + 6 = 17"); console.log("fields returned by star select:", Object.keys(db.prepare(`SELECT * ${JOINED}`).get()).length); // Schema changes: a 'notes' column is added to the book relation too. db.exec("ALTER TABLE book ADD COLUMN notes TEXT"); db.exec("UPDATE book SET notes = 'binding is worn'"); const star = db.prepare(`SELECT * ${JOINED}`).get(); const explicit = db.prepare( `SELECT k.notes AS book_note, u.notes AS member_note ${JOINED}`).get(); console.log("fields returned by star select after the schema change:", Object.keys(star).length); console.log(" star.notes :", JSON.stringify(star.notes.slice(0, 20))); console.log(" explicit.book_note :", JSON.stringify(explicit.book_note)); console.log(" explicit.member_note :", JSON.stringify(explicit.member_note.slice(0, 20))); ``` ```sh node generate.mjs node star-trap.mjs ``` ``` book: 200 average summary length: 720 open loans: 500 column total of the three relations: 5 + 6 + 6 = 17 fields returned by star select: 15 fields returned by star select after the schema change: 15 star.notes : "note note note note " explicit.book_note : "binding is worn" explicit.member_note : "note note note note " ``` In the first line, seventeen columns were requested and fifteen fields came back: the names `book_id` and `member_id` merged because they exist in two relations at once. After a new column was added to the schema, the requested column count rose to eighteen, and the returned field count still stayed at fifteen. The `notes` field carries the member's note; the book's note silently dropped. Asked with explicit selection, both values are in place. The danger here is that it produces no error. The query succeeds, the field is filled, the value is valid — but it comes from the wrong relation. A column added in the Schema Migrations lesson can break a code path that uses star selection this way, and the migration itself produces no warning. #### How Much to Limit Limiting has an opposite direction too. A query that pulls fewer columns than needed requires a second round trip for the missing field; the previous lesson's N+1 problem can come back this way. For that reason, the metric is not "fewest columns" but the exact set of shown fields. Three rules work in practice. List views pull only the fields shown in the list; the detail view is called with a separate query. Columns carrying long text and binary content never enter list queries. Pagination writes the limit of the result set into the query itself; slicing it in the application does not reduce the transfer, because the data has already been carried over. Applying these rules depends on being able to see which fields a query returns just by reading the code. Star selection removes that visibility: someone looking at the query cannot see which fields are carried, and the transferred data grows silently when the schema changes. #### Summary - The same five-hundred-row list transferred 817162 bytes with every column, 53244 bytes with only the needed ones; with the same row count, the difference is a factor of 15.3. - Row limiting added a second multiplier; the two limits together pushed the ratio to 391-fold. - Counting in the application carried 45345 bytes, counting in the database 9 bytes; aggregation, filtering, and sorting are done where the data lives. - Star selection merged columns with the same name in the join; the value of a column added to the schema silently dropped, and no error was produced. - The metric is not "fewest columns" but the exact set of shown fields; an incomplete selection produces a second round trip. #### Next Step These two lessons covered the read path: how many queries run and how much data each query carries. The write path has its own scale. Suppose loan data is being imported from another system: should two thousand records be written one by one, inside a single transaction, or with a single statement? All three approaches produce the same rows; their durations differ by an order of magnitude. The next lesson measures that difference, shows where it comes from, and covers how batch size is chosen. ### Batch Operations Source: https://academia.sh/en/courses/data-access-layer/batch-operations Scaling the write path: writing the same two thousand rows four ways and comparing durations, the decisive share of the transaction boundary, how batch size relates to statement count and query length, and measuring statement count in a batch update. The previous two lessons measured the read path: how many queries run and how much data each query carries. The write path has its own scale. Suppose loan data is being imported from another system: two thousand records need to be written. This can be done one by one, inside a single transaction, or with a single statement. All three approaches produce the same rows. This lesson measures the difference between them and shows where it comes from. #### Four Approaches The measurement sets up the database from scratch for every approach, so none of them benefits from a cache warmed by the one before it. ```js // bulk-insert.mjs — the same 2000 rows written four ways, durations are measured import { DatabaseSync } from "node:sqlite"; import { rmSync } from "node:fs"; const ROW_COUNT = 2000; const data = Array.from({ length: ROW_COUNT }, (_, i) => [ (i % 200) + 1, (i % 60) + 1, `2025-${String((i % 12) + 1).padStart(2, "0")}-01`, ]); function freshDatabase() { rmSync("transfer.db", { force: true }); const db = new DatabaseSync("transfer.db"); db.exec(`CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL)`); return db; } function measure(label, write) { const db = freshDatabase(); const t = performance.now(); write(db); const duration = performance.now() - t; const n = db.prepare("SELECT count(*) AS n FROM loan").get().n; db.close(); console.log(`${label.padEnd(49)} rows=${n} duration=${duration.toFixed(0).padStart(6)} ms ` + `per_row=${(duration * 1000 / n).toFixed(1).padStart(6)} us`); return duration; } const a = measure("each row its own transaction", (db) => { for (const s of data) { db.prepare("INSERT INTO loan (book_id, member_id, pickup_date) VALUES (?,?,?)").run(...s); } }); const b = measure("single transaction, statement prepared per row", (db) => { db.exec("BEGIN"); for (const s of data) { db.prepare("INSERT INTO loan (book_id, member_id, pickup_date) VALUES (?,?,?)").run(...s); } db.exec("COMMIT"); }); const c = measure("single transaction, statement reused", (db) => { db.exec("BEGIN"); const insert = db.prepare("INSERT INTO loan (book_id, member_id, pickup_date) VALUES (?,?,?)"); for (const s of data) insert.run(...s); db.exec("COMMIT"); }); const d = measure("single transaction, 500-row multi-row statement", (db) => { db.exec("BEGIN"); for (let i = 0; i < data.length; i += 500) { const batch = data.slice(i, i + 500); const placeholders = batch.map(() => "(?,?,?)").join(","); db.prepare(`INSERT INTO loan (book_id, member_id, pickup_date) VALUES ${placeholders}`) .run(...batch.flat()); } db.exec("COMMIT"); }); console.log(`\nratios (relative to the first approach): ${(a/b).toFixed(1)}x ${(a/c).toFixed(1)}x ${(a/d).toFixed(1)}x`); ``` ```sh node bulk-insert.mjs ``` ``` each row its own transaction rows=2000 duration= 376 ms per_row= 188.2 us single transaction, statement prepared per row rows=2000 duration= 3 ms per_row= 1.5 us single transaction, statement reused rows=2000 duration= 1 ms per_row= 0.4 us single transaction, 500-row multi-row statement rows=2000 duration= 1 ms per_row= 0.7 us ratios (relative to the first approach): 127.9x 470.4x 276.9x ``` Durations depend on the hardware and the file system; the ratios show the structure. The biggest jump is between the first two rows. The only difference between them is the transaction boundary. In the first approach, every `INSERT` opens and closes its own transaction; closing requires durability each time, meaning the data is expected to be guaranteed on disk. Two thousand commits mean two thousand durability points. The second approach has a single commit. **In batch writing, the first decision is not the statement shape but the transaction boundary.** The SQL Fundamentals course introduced batch insert as a multi-row `INSERT` statement; the measurement shows that the real gain is not there. The difference between the second and third rows comes from statement preparation. Re-preparing the statement for every row means parsing the same text two thousand times. Setting up the prepared statement once and reusing it brings this down to a single pass. The fourth row is slower than expected in a local measurement. A multi-row statement produces a differently sized SQL text for every batch, so every batch gets re-parsed; on a local database this is more expensive than reusing a prepared statement. The gain of a multi-row statement lies elsewhere: **it lowers the statement count**, and with it the number of round trips over the network. A local measurement does not show this gain. #### Batch Size In a multi-row statement, the only number left to choose is the batch size. ```js // batch-size.mjs — the effect of batch size in a multi-row statement import { DatabaseSync } from "node:sqlite"; import { rmSync } from "node:fs"; const ROW_COUNT = 2000; const data = Array.from({ length: ROW_COUNT }, (_, i) => [ (i % 200) + 1, (i % 60) + 1, `2025-${String((i % 12) + 1).padStart(2, "0")}-01`, ]); for (const batch of [1, 10, 50, 200, 1000]) { rmSync("transfer.db", { force: true }); const db = new DatabaseSync("transfer.db"); db.exec(`CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL)`); const t = performance.now(); db.exec("BEGIN"); let statement = 0, longest = 0; for (let i = 0; i < data.length; i += batch) { const slice = data.slice(i, i + batch); const sql = `INSERT INTO loan (book_id, member_id, pickup_date) VALUES ${ slice.map(() => "(?,?,?)").join(",")}`; longest = Math.max(longest, sql.length); db.prepare(sql).run(...slice.flat()); statement += 1; } db.exec("COMMIT"); const duration = performance.now() - t; console.log(`batch=${String(batch).padStart(4)} statement=${String(statement).padStart(4)} ` + `longest_sql=${String(longest).padStart(6)} characters duration=${duration.toFixed(1).padStart(6)} ms`); db.close(); } ``` ```sh node batch-size.mjs ``` ``` batch= 1 statement=2000 longest_sql= 65 characters duration= 4.5 ms batch= 10 statement= 200 longest_sql= 137 characters duration= 1.6 ms batch= 50 statement= 40 longest_sql= 457 characters duration= 1.2 ms batch= 200 statement= 10 longest_sql= 1657 characters duration= 1.1 ms batch=1000 statement= 2 longest_sql= 8057 characters duration= 0.8 ms ``` Most of the gain comes from the early steps: going from one to ten cuts the duration to roughly a third, while going from fifty to a thousand adds only a modest further gain. The query text, in contrast, grows linearly, approaching eight kilobytes at a batch of a thousand rows. Three factors limit batch size. The number of bound variables has an upper limit set by the engine; the variable count, multiplied by the column count per row, cannot exceed that limit. A long query text raises the parsing cost and memory use. And a large batch means more work to roll back if something fails. For that reason, the batch is chosen not as "as large as possible" but at the point where the gain flattens. In this measurement that point falls between fifty and two hundred; it falls somewhere else in a different schema, but the shape of the curve is the same. #### Batch Update In an update, a different value must be written to each row; this can be done with a single `UPDATE` statement, but how to do it calls for a choice. ```js // bulk-update.mjs — three ways to write a different value to each of 1000 rows import { DatabaseSync } from "node:sqlite"; import { rmSync } from "node:fs"; const ROW_COUNT = 1000; const updates = Array.from({ length: ROW_COUNT }, (_, i) => [ i + 1, `2025-${String((i % 12) + 1).padStart(2, "0")}-15`, ]); function setup() { rmSync("transfer.db", { force: true }); const db = new DatabaseSync("transfer.db"); db.exec(`CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT)`); db.exec("BEGIN"); const insert = db.prepare("INSERT INTO loan VALUES (?,?,?,?,NULL)"); for (let i = 1; i <= ROW_COUNT; i++) insert.run(i, (i % 200) + 1, (i % 60) + 1, "2025-01-01"); db.exec("COMMIT"); return db; } function measure(label, apply) { const db = setup(); const counter = { statement: 0 }; const t = performance.now(); apply(db, counter); const duration = performance.now() - t; const n = db.prepare("SELECT count(*) AS n FROM loan WHERE return_date IS NOT NULL").get().n; const example = db.prepare("SELECT return_date FROM loan WHERE loan_id = 7").get().return_date; db.close(); console.log(`${label.padEnd(30)} updated=${n} example(7)=${example} ` + `statement=${String(counter.statement).padStart(4)} duration=${duration.toFixed(1).padStart(6)} ms`); return duration; } const a = measure("one-by-one update", (db, counter) => { db.exec("BEGIN"); const update = db.prepare("UPDATE loan SET return_date = ? WHERE loan_id = ?"); for (const [id, date] of updates) { update.run(date, id); counter.statement += 1; } db.exec("COMMIT"); }); const b = measure("update from a value list", (db, counter) => { db.exec("BEGIN"); const placeholders = updates.map(() => "(?,?)").join(","); db.prepare(`UPDATE loan SET return_date = v.return_date FROM (SELECT column1 AS id, column2 AS return_date FROM (VALUES ${placeholders})) AS v WHERE loan.loan_id = v.id`).run(...updates.flat()); counter.statement += 1; db.exec("COMMIT"); }); const c = measure("via a staging table", (db, counter) => { db.exec("BEGIN"); db.exec("CREATE TEMP TABLE staging (id INTEGER PRIMARY KEY, return_date TEXT NOT NULL)"); const insert = db.prepare("INSERT INTO staging VALUES (?,?)"); for (const [id, date] of updates) { insert.run(id, date); counter.statement += 1; } db.exec(`UPDATE loan SET return_date = (SELECT return_date FROM staging WHERE id = loan.loan_id) WHERE loan_id IN (SELECT id FROM staging)`); counter.statement += 1; db.exec("DROP TABLE staging"); db.exec("COMMIT"); }); console.log(`\nratios (relative to one-by-one update): ${(a/b).toFixed(2)}x ${(a/c).toFixed(2)}x`); ``` ```sh node bulk-update.mjs ``` ``` one-by-one update updated=1000 example(7)=2025-07-15 statement=1000 duration= 0.9 ms update from a value list updated=1000 example(7)=2025-07-15 statement= 1 duration= 1.0 ms via a staging table updated=1000 example(7)=2025-07-15 statement=1001 duration= 1.0 ms ratios (relative to one-by-one update): 0.91x 0.98x ``` The duration of all three approaches is the same in the local measurement. This is not a null result, it is a result: as long as they stay inside the same transaction boundary, statement count does not determine the cost on a local database. The distinction sits in the statement column: a thousand, one, and a thousand and one. If the database is beyond the network, this number is directly the round-trip count, and the gap then multiplies a thousandfold. The third approach has the highest statement count, yet it has a value of its own. A **staging table** lets the data first be loaded into a separate relation, validated there, and only then applied to the target. The target relation is touched with a single statement; a bad row never reaches it. Loading the staging table is itself a batch-insert job in its own right, and it follows the rules from this lesson's first measurement. #### Summary - The same two thousand rows were written four ways: each row its own transaction took 376 ms, a single transaction 3 ms, and reusing a prepared statement 1 ms. - The biggest gain came from the transaction boundary; making one commit instead of two thousand cut the duration by more than a hundred and twenty-fold. - The gain from batch size flattens early: going from 1 to 10 cut the duration to roughly a third, going from 50 to 1000 added only a modest further gain, while the query text grew linearly. - In batch update, the local duration of all three approaches came out equal; the distinction is in the statement count (1000, 1, 1001), and over a network this number turns into round-trip count. - A staging table makes it possible to validate the data before it is applied to the target, and it touches the target with a single statement. #### Next Step Everything measured up to this point was work the application did itself: how many queries ran, how many bytes were carried, how many statements were sent. A query can pass every one of these metrics and still be slow. A single query, in a single transaction, working with only the needed columns, can still take seconds — because the database decides for itself how to execute that query. The next lesson reads that decision from inside the application, logs the slow query, and shows why the plan changed. ### Reading Query Plans Source: https://academia.sh/en/courses/data-access-layer/reading-query-plans Diagnosing a slow query from the application: a wrapper that times every query and logs the one crossing a threshold together with its plan, the same query's plan without and with an index, a condition's phrasing changing the plan, and grouping slow queries by query fingerprint. Everything measured up to this point was work the application did itself: how many queries ran, how many bytes were carried, how many statements were sent. A query can pass every one of these metrics and still be slow. A single query, in a single transaction, working with only the needed columns, can still take seconds — because the database decides for itself how that query will be executed. The Advanced SQL course introduced this decision as the **query plan** and read it directly from the database shell. This lesson reads the same decision from inside the application and builds a mechanism to catch the slow query under production conditions. #### Measurement Data For the gap between a scan and an index to be measurable, the relation needs to be large enough. ```js // generate.mjs — 50000 loan records, to make the scan-versus-index gap measurable import { DatabaseSync } from "node:sqlite"; import { rmSync } from "node:fs"; rmSync("library.db", { force: true }); const db = new DatabaseSync("library.db"); db.exec(` CREATE TABLE member (member_id INTEGER PRIMARY KEY, name TEXT NOT NULL); CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT);`); db.exec("BEGIN"); const u = db.prepare("INSERT INTO member VALUES (?,?)"); for (let i = 1; i <= 60; i++) u.run(i, `Member${i}`); const k = db.prepare("INSERT INTO book VALUES (?,?)"); for (let i = 1; i <= 200; i++) k.run(i, `Book ${i}`); const o = db.prepare("INSERT INTO loan VALUES (?,?,?,?,?)"); let seed = 20250727; // deterministic pseudo-random sequence const next = (n) => { seed = (seed * 48271) % 2147483647; return seed % n; }; for (let i = 1; i <= 50000; i++) { const year = 2022 + next(4); const month = String(next(12) + 1).padStart(2, "0"); const day = String(next(28) + 1).padStart(2, "0"); o.run(i, next(200) + 1, next(60) + 1, `${year}-${month}-${day}`, next(5) === 0 ? null : `${year}-12-31`); } db.exec("COMMIT"); db.exec("ANALYZE"); console.log("loan:", db.prepare("SELECT count(*) AS n FROM loan").get().n, " open:", db.prepare("SELECT count(*) AS n FROM loan WHERE return_date IS NULL").get().n); ``` ```sh node generate.mjs ``` ``` loan: 50000 open: 10006 ``` The data is generated pseudo-randomly, but since the seed is fixed, the same rows come out on every run. The `ANALYZE` call collects the statistics introduced in the Advanced SQL course; the optimizer estimates selectivity from these statistics. #### Reading the Plan from the Application Just as the plan can be read in the database shell, it can also be read from the application connection. The wrapper below times every query and puts the one that crosses the threshold into the log, together with its plan. ```js // tracking.mjs — times every query, logs the one that crosses the threshold together with its plan import { DatabaseSync } from "node:sqlite"; export function trackingConnection(file, thresholdMs) { const db = new DatabaseSync(file); const log = []; const fingerprint = (sql) => sql.replace(/\s+/g, " ").trim(); return { log, query(sql, ...d) { const t = performance.now(); const rows = db.prepare(sql).all(...d); const duration = performance.now() - t; if (duration >= thresholdMs) { const plan = db.prepare(`EXPLAIN QUERY PLAN ${sql}`).all(...d).map((p) => p.detail); log.push({ fingerprint: fingerprint(sql), duration, rows: rows.length, plan }); } return rows; }, run(sql) { db.exec(sql); }, plan(sql, ...d) { return db.prepare(`EXPLAIN QUERY PLAN ${sql}`).all(...d).map((p) => p.detail); }, duration(sql, ...d) { const t = performance.now(); const n = db.prepare(sql).all(...d).length; return { duration: performance.now() - t, rows: n }; }, }; } ``` Two decisions sit in this design. The plan is taken only for a query that crosses the threshold; if it were taken for every query, the measurement would cost more than the work itself. And the plan is taken with the bound values the query actually runs with; this detail matters because different values can produce different plans. #### The Same Query, Two Plans ```js // read-plan.mjs — the same query without an index and with one, with plan and duration import { trackingConnection } from "./tracking.mjs"; const db = trackingConnection("library.db", 0); const QUERY = "SELECT loan_id, pickup_date FROM loan WHERE member_id = ? AND return_date IS NULL"; const report = (label) => { const { duration, rows } = db.duration(QUERY, 17); console.log(`${label}`); for (const p of db.plan(QUERY, 17)) console.log(` plan: ${p}`); console.log(` rows=${rows} duration=${duration.toFixed(2)} ms`); }; report("without an index"); db.run("CREATE INDEX loan_member_open ON loan (member_id) WHERE return_date IS NULL"); report("after adding a partial index"); db.run("DROP INDEX loan_member_open"); ``` ```sh node read-plan.mjs ``` ``` without an index plan: SCAN loan rows=180 duration=1.29 ms after adding a partial index plan: SEARCH loan USING INDEX loan_member_open (member_id=?) rows=180 duration=0.07 ms ``` The result is the same: 180 rows. The plan is different. Without an index it reads `SCAN`; all fifty thousand rows are read and the condition is applied to every one. With an index it reads `SEARCH`, and states which index is used with which condition. The format of plan text varies by engine; the words `SCAN` and `SEARCH` here are SQLite's own wording. The distinction that does not change is whether the plan reads an entire relation or goes straight to the rows it is looking for. The **partial index** introduced in the Relational Database Administration course was used here: the index covers only the records that have not been returned, taking up about a fifth of the fifty thousand rows. The duration gap at this scale is roughly eighteen-fold. As the relation grows, the gap grows: a scan grows linearly with row count, an index search grows logarithmically. #### How Phrasing Changes the Plan An index existing does not mean it will be used. How a condition is phrased can disable the index. ```js // phrasing-difference.mjs — two phrasings of the same condition, under the same index import { trackingConnection } from "./tracking.mjs"; const db = trackingConnection("library.db", 0); db.run("CREATE INDEX IF NOT EXISTS loan_pickup ON loan (pickup_date)"); const phrasings = { "function applied": "SELECT count(*) AS n FROM loan WHERE substr(pickup_date,1,4) = '2024'", "range condition ": "SELECT count(*) AS n FROM loan WHERE pickup_date >= '2024-01-01' AND pickup_date < '2025-01-01'", }; for (const [label, sql] of Object.entries(phrasings)) { const result = db.query(sql)[0].n; const { duration } = db.duration(sql); console.log(`${label} result=${result} duration=${duration.toFixed(2)} ms`); for (const p of db.plan(sql)) console.log(` plan: ${p}`); } db.run("DROP INDEX loan_pickup"); ``` ```sh node phrasing-difference.mjs ``` ``` function applied result=12574 duration=1.32 ms plan: SCAN loan USING COVERING INDEX loan_pickup range condition result=12574 duration=0.14 ms plan: SEARCH loan USING COVERING INDEX loan_pickup (pickup_date>? AND pickup_date b[1].total - a[1].total)) { console.log(`\ntotal=${g.total.toFixed(1)} ms calls=${g.count} longest=${g.longest.toFixed(2)} ms`); console.log(` ${fingerprint}`); for (const p of g.plan) console.log(` plan: ${p}`); } ``` ```sh node slow-log.mjs ``` ``` queries over threshold: 40 / 60 total=32.6 ms calls=20 longest=1.73 ms SELECT count(*) AS n FROM loan WHERE substr(pickup_date,1,4) = ? plan: SCAN loan total=17.8 ms calls=20 longest=1.11 ms SELECT loan_id FROM loan WHERE member_id = ? AND return_date IS NULL plan: SCAN loan ``` Forty of the sixty queries crossed the threshold; the twenty queries looking up by primary key never appeared. The log produced two fingerprints and sorted both by total duration. Sorting by total duration is deliberate. Looking at the single longest call is misleading; a query that takes two milliseconds and is called a thousand times a second produces more load than one that takes ten milliseconds and is called once a minute. The Relational Database Administration course covered this distinction as the difference between a symptom and a cause. #### What the Plan Does Not Say A plan is a prediction, not a measurement. Three limits are worth keeping in mind. A plan is based on **statistics**. If the statistics are stale, the optimizer mispredicts selectivity and picks the wrong plan; refreshing statistics is maintenance work in systems where relation size changes fast. A plan can change with the **bound value**. A scan might be chosen for a common value, an index search for a rare one. For that reason, the plan should be taken with the values the query actually runs with. A plan does not give a **duration**. In every measurement above, the plan and the duration were printed together; the plan says why something is slow, the duration says how slow. A diagnosis is only complete when the two are read together. #### Summary - The query plan can be read from the application connection; taking a plan only for a query that crosses the threshold is cheaper than taking it for every query. - The same query produced a `SCAN` plan without an index and a `SEARCH` plan with a partial index, and the duration dropped from 1.29 ms to 0.07 ms. - The function-applied phrasing of the same condition scanned the index, the range phrasing searched it; the indexed column must stand bare on one side of the condition. - The slow query log groups entries by query fingerprint, stripped of bound values, and sorts them by total duration; what is sought is not the single longest call but the fingerprint producing the most load. - A plan is a prediction tied to statistics and the bound value; it does not give duration, and a diagnosis is complete only when the plan and the duration are read together. #### Next Step All the queries in this lesson asked for the entire result set. Applications show most lists piece by piece, and the offset–limit pair introduced in the SQL Fundamentals course is the best-known way to do it. It is short to write, its plan is simple, and it is fast on early pages. On deep pages, its cost grows together with the number of skipped rows, and that growth is invisible just from looking at the query text. The next lesson pulls the same list with increasing offset values, measures the cost, compares it with a key-based approach, and covers which interface constraint each of the two approaches brings. ### Pagination Cost Source: https://academia.sh/en/courses/data-access-layer/pagination-cost Two problems of deep pagination: duration growing with the offset value, the constant cost of keyset pagination, records skipped when the result set changes, and the interface constraints each approach brings. All the queries in the previous lesson asked for the entire result set. Applications show most lists piece by piece; the offset–limit pair introduced in the SQL Fundamentals course is the best-known way to do it. It is short to write, its plan is simple, and it is fast on early pages. On deep pages, its cost grows together with the number of skipped rows, and that growth is invisible just from looking at the query text. This lesson measures the cost, builds the alternative, and covers the difference the two approaches bring to the interface. #### Measurement Data The measurement uses the fifty-thousand-record dataset from the previous lesson. ```js // generate.mjs — 50000 loan records import { DatabaseSync } from "node:sqlite"; import { rmSync } from "node:fs"; rmSync("library.db", { force: true }); const db = new DatabaseSync("library.db"); db.exec(` CREATE TABLE member (member_id INTEGER PRIMARY KEY, name TEXT NOT NULL); CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT);`); db.exec("BEGIN"); const u = db.prepare("INSERT INTO member VALUES (?,?)"); for (let i = 1; i <= 60; i++) u.run(i, `Member${i}`); const k = db.prepare("INSERT INTO book VALUES (?,?)"); for (let i = 1; i <= 200; i++) k.run(i, `Book ${i}`); const o = db.prepare("INSERT INTO loan VALUES (?,?,?,?,?)"); let seed = 20250727; // deterministic pseudo-random sequence const next = (n) => { seed = (seed * 48271) % 2147483647; return seed % n; }; for (let i = 1; i <= 50000; i++) { const year = 2022 + next(4); const month = String(next(12) + 1).padStart(2, "0"); const day = String(next(28) + 1).padStart(2, "0"); o.run(i, next(200) + 1, next(60) + 1, `${year}-${month}-${day}`, next(5) === 0 ? null : `${year}-12-31`); } db.exec("COMMIT"); db.exec("ANALYZE"); console.log("loan:", db.prepare("SELECT count(*) AS n FROM loan").get().n, " open:", db.prepare("SELECT count(*) AS n FROM loan WHERE return_date IS NULL").get().n); ``` ```sh node generate.mjs ``` ``` loan: 50000 open: 10006 ``` #### The Cost of Offset Two queries produce the same page. The first says how many rows to skip; the second continues from the previous page's last key. ```js // pagination.mjs — same page size, increasing offset values, and the keyset approach import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("library.db"); const PAGE_SIZE = 20; const OFFSET_QUERY = `SELECT loan_id, pickup_date FROM loan WHERE return_date IS NOT NULL ORDER BY loan_id LIMIT ? OFFSET ?`; const KEYSET_QUERY = `SELECT loan_id, pickup_date FROM loan WHERE return_date IS NOT NULL AND loan_id > ? ORDER BY loan_id LIMIT ?`; const measure = (run, repeat = 20) => { run(); const t = performance.now(); for (let i = 0; i < repeat; i++) run(); return (performance.now() - t) / repeat; }; console.log("offset offset_ms keyset_ms ratio"); for (const offset of [0, 1000, 10000, 30000, 39000]) { // The starting key for the same page is found for the keyset approach. const boundary = db.prepare( `SELECT loan_id FROM loan WHERE return_date IS NOT NULL ORDER BY loan_id LIMIT 1 OFFSET ?`).get(Math.max(0, offset - 1)); const previousKey = offset === 0 ? 0 : boundary.loan_id; const a = measure(() => db.prepare(OFFSET_QUERY).all(PAGE_SIZE, offset)); const b = measure(() => db.prepare(KEYSET_QUERY).all(previousKey, PAGE_SIZE)); console.log(`${String(offset).padStart(6)} ${a.toFixed(3).padStart(10)} ${b.toFixed(3).padStart(12)} ` + `${(a / b).toFixed(1).padStart(6)}x`); } ``` ```sh node pagination.mjs ``` ``` offset offset_ms keyset_ms ratio 0 0.012 0.013 1.0x 1000 0.030 0.012 2.4x 10000 0.203 0.013 16.2x 30000 0.616 0.011 57.1x 39000 0.793 0.011 70.7x ``` The two approaches are equal on the first page. As the offset grows, the offset query's duration grows linearly: roughly sixteen-fold at 10000, seventy-fold at 39000. The keyset query's duration does not change. The reason lies not in the query plan but in what the plan means. `OFFSET` is not a "skip" instruction; the database has to **produce** the skipped rows, then discard them. Fetching the thirty-thousandth page means thirty thousand rows get read and thrown away. In the keyset query, the condition itself gives a starting point in the index; the number of rows read is only the page size. This matches the previous lesson's metric: both queries use an index, and both plans say "search." The difference is in how many rows the plan produces. #### Traversal Total A single deep page takes milliseconds; the real difference shows up when the whole list is scanned. Exports and batch jobs do this constantly. ```js // full-traversal.mjs — total cost of walking every page from start to end import { DatabaseSync } from "node:sqlite"; const db = new DatabaseSync("library.db"); const PAGE_SIZE = 100; function offsetTraversal() { const statement = db.prepare(`SELECT loan_id FROM loan WHERE return_date IS NOT NULL ORDER BY loan_id LIMIT ? OFFSET ?`); let offset = 0, total = 0, page = 0; for (;;) { const rows = statement.all(PAGE_SIZE, offset); if (rows.length === 0) break; total += rows.length; offset += PAGE_SIZE; page += 1; } return { total, page }; } function keysetTraversal() { const statement = db.prepare(`SELECT loan_id FROM loan WHERE return_date IS NOT NULL AND loan_id > ? ORDER BY loan_id LIMIT ?`); let last = 0, total = 0, page = 0; for (;;) { const rows = statement.all(last, PAGE_SIZE); if (rows.length === 0) break; last = rows[rows.length - 1].loan_id; total += rows.length; page += 1; } return { total, page }; } for (const [label, traverse] of [["offset", offsetTraversal], ["keyset", keysetTraversal]]) { const t = performance.now(); const { total, page } = traverse(); console.log(`${label.padEnd(10)} pages=${page} rows=${total} duration=${(performance.now() - t).toFixed(0)} ms`); } ``` ```sh node full-traversal.mjs ``` ``` offset pages=400 rows=39994 duration=170 ms keyset pages=400 rows=39994 duration=7 ms ``` The same four hundred pages, the same thirty-nine thousand rows, a twenty-four-fold difference in duration. In offset traversal, the total number of rows read is proportional to the square of the page count; every page reproduces all of the ones before it. In keyset traversal, every row is read exactly once. #### The Shifting Result Set Alongside the cost, there is a correctness problem. If the result set changes between pages, the offset value no longer points at the same row. ```js // shifting-page.mjs — when the set changes between pages, offset pagination skips a record import { DatabaseSync } from "node:sqlite"; import { rmSync } from "node:fs"; rmSync("small.db", { force: true }); const db = new DatabaseSync("small.db"); db.exec(`CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, return_date TEXT); INSERT INTO loan (loan_id, return_date) VALUES (1,NULL),(2,NULL),(3,NULL),(4,NULL),(5,NULL), (6,NULL),(7,NULL),(8,NULL),(9,NULL),(10,NULL);`); const offsetPage = (offset) => db.prepare( "SELECT loan_id FROM loan WHERE return_date IS NULL ORDER BY loan_id LIMIT 5 OFFSET ?") .all(offset).map((r) => r.loan_id); const keysetPage = (lastKey) => db.prepare( "SELECT loan_id FROM loan WHERE return_date IS NULL AND loan_id > ? ORDER BY loan_id LIMIT 5") .all(lastKey).map((r) => r.loan_id); const o1 = offsetPage(0); db.prepare("UPDATE loan SET return_date = '2025-07-20' WHERE loan_id = 2").run(); // the set shrank const o2 = offsetPage(5); console.log("offset page1:", o1.join(","), " page2:", o2.join(",")); console.log(" shown:", [...o1, ...o2].join(",")); db.prepare("UPDATE loan SET return_date = NULL WHERE loan_id = 2").run(); // state is reverted const k1 = keysetPage(0); db.prepare("UPDATE loan SET return_date = '2025-07-20' WHERE loan_id = 2").run(); const k2 = keysetPage(k1[k1.length - 1]); console.log("keyset page1:", k1.join(","), " page2:", k2.join(",")); console.log(" shown:", [...k1, ...k2].join(",")); ``` ```sh node shifting-page.mjs ``` ``` offset page1: 1,2,3,4,5 page2: 7,8,9,10 shown: 1,2,3,4,5,7,8,9,10 keyset page1: 1,2,3,4,5 page2: 6,7,8,9,10 shown: 1,2,3,4,5,6,7,8,9,10 ``` When record two leaves the open list, the set shrinks to nine; the second page's start shifts by one position, and record number six never appears. In the keyset approach, the second page asked for "those greater than five"; the set shrinking did not affect that condition. The opposite direction is possible too: if the set grows, the same record appears on two pages at once. Both are silent errors; the user sees a list that is missing rows or has duplicates, and no warning is produced. The observation from the Application Impact of Isolation Levels lesson repeats here: two queries that are each correct on their own can be inconsistent together. #### Constraints of the Two Approaches Keyset pagination is not free; it brings two constraints to the interface. **Order cannot be skipped.** A "go to page twenty-seven" request cannot be satisfied, because that page's starting key is not known. Only "next" and — with the sort reversed — "previous" can be offered. The cursor-based pagination introduced in the Web API Design course rests on this constraint; the client is given a cursor instead of a page number. **Sorting must be deterministic.** If the sort key is not unique, the order among rows with equal values is undefined, and a record gets skipped or duplicated at the page boundary. For that reason, a unique column is added to the sort key; in a list sorted by pickup date, the condition takes the form "date is greater, or date is equal and the ID is greater." Offset pagination has its place too. It is sufficient for lists that stay small in page count, admin screens, and interfaces that need to show the total page count. The criterion is simple: **if the offset value has a known upper bound**, offset pagination can be used; if the list can grow without limit, the keyset approach is chosen. A third detail concerns the total count. Showing "1245 records" in an interface requires a separate counting query, and that query produces the entire set; no matter how cheap pagination itself gets, the counting query brings the cost back. For that reason, large lists show "there is more" instead of an exact total; that information is obtained by requesting one more row than the page size. #### Summary - In offset pagination, duration grew linearly with the offset value: 16-fold at 10000, 70-fold at 39000. The keyset query's duration did not change. - `OFFSET` does not keep the database from producing the skipped rows; it reads them and then discards them. - Walking all four hundred pages took 170 ms with the offset approach and 7 ms with the keyset approach; the cost of offset traversal is proportional to the square of the page count. - When the result set shrank between pages, offset pagination skipped a record; keyset pagination was unaffected. - Keyset pagination does not allow skipping order and requires deterministic sorting; offset pagination is sufficient for lists whose offset value has a known upper bound. #### Next Step The Performance Problems topic closed out the measurable side of the data access layer: query count, transferred bytes, statement count, query plan, and pagination cost. All of these metrics were about how the layer works; none of them says what the layer **should** do. Where does the lending rule live, in which layer does validation happen, is the record returned to the outside the same thing as the domain model? The next topic takes up these questions, and its first lesson separates the responsibilities of the presentation, application, domain, and infrastructure layers and checks the dependency direction through the import graph. ## Business Logic Placement The responsibility split between the presentation, application, domain and infrastructure layers, data transfer objects that separate the outer contract from the inner model, the boundary between input validation and a domain rule, domain events that couple side effects loosely, and a persistence-ignorant domain model. ### Layer Responsibilities Source: https://academia.sh/en/courses/data-access-layer/layer-responsibilities The separation of the presentation, application, domain and infrastructure layers: auditing the dependency direction rule through the import graph, counting the findings in the violating version, and inverting the dependency by moving the contract into the domain layer. The Performance Problems topic measured how the data access layer behaves: query count, bytes transferred, statement count, query plan, pagination cost. None of it said what the layer is supposed to do. Where does the loan rule live, which layer runs validation, and is the record returned to the outside the same thing as the domain model? This topic takes on those questions, and the first is the most basic one: which pieces does the code split into, and in which direction can those pieces call each other? Claiming the separation on paper is not enough; it has to be written down as a rule and audited through the **import graph**. #### The Four Layers The codebase splits into four responsibilities. The names vary, but the reasoning behind the split stays the same: each layer changes for a different reason. | Layer | Responsibility | Reason it changes | |---|---|---| | Presentation | The shape of the outside world: route, body parsing, status code | The interface or protocol changes | | Application | The order of the workflow: who to call, where to open the transaction, who gets the result | The use case changes | | Domain | Business rules: what is valid, what is forbidden | An organizational rule changes | | Infrastructure | Access to external resources: database, file, network | The tool or schema changes | As important as the layer count is the **dependency direction** between them. The rule fits in one sentence: the domain layer cannot depend on any layer. Every other direction follows from this rule. ``` presentation → application → domain ← infrastructure ``` Infrastructure's arrow points the other way. Database access does not call the domain layer; it satisfies a contract the domain layer defines. This reversed direction is the lesson's actual subject. #### The Violating Version The four modules below serve a loan request. The directory names give the layer; the code is written first, in the order that comes to mind, with direct calls. ```sh mkdir -p violating/presentation violating/application violating/domain violating/infrastructure ``` ```js // violating/domain/loan-rules.mjs — business rules, but it directly calls two lower layers import { SqliteLoanRepository } from "../infrastructure/sqlite-loan-repository.mjs"; import { response } from "../presentation/response.mjs"; export const MEMBER_LIMIT = 3; export function issueLoan(db, memberId, bookId, date) { const repository = new SqliteLoanRepository(db); if (repository.booksOpenRecord(bookId) !== null) return response(409, "book is already on loan"); if (repository.membersOpenRecords(memberId).length >= MEMBER_LIMIT) { return response(409, `member limit exceeded (${MEMBER_LIMIT})`); } return response(201, String(repository.add({ bookId, memberId, pickupDate: date }))); } ``` ```js // violating/infrastructure/sqlite-loan-repository.mjs — SQL and the driver live here import { DatabaseSync } from "node:sqlite"; export const connect = (file) => new DatabaseSync(file); export class SqliteLoanRepository { constructor(db) { this.db = db; } booksOpenRecord(bookId) { return this.db.prepare( "SELECT loan_id FROM loan WHERE book_id = ? AND return_date IS NULL").get(bookId) ?? null; } membersOpenRecords(memberId) { return this.db.prepare( "SELECT loan_id FROM loan WHERE member_id = ? AND return_date IS NULL").all(memberId); } add(l) { return Number(this.db.prepare( "INSERT INTO loan (book_id, member_id, pickup_date, return_date) VALUES (?,?,?,NULL)") .run(l.bookId, l.memberId, l.pickupDate).lastInsertRowid); } } ``` ```js // violating/presentation/response.mjs — presentation helper that formats the HTTP response export const response = (status, body) => ({ status, body }); ``` ```js // violating/application/loan-application.mjs — application service that handles the request import { issueLoan } from "../domain/loan-rules.mjs"; import { connect } from "../infrastructure/sqlite-loan-repository.mjs"; export function loanRequest(request) { const db = connect("library.db"); return issueLoan(db, request.memberId, request.bookId, request.date); } ``` ```js // violating/presentation/http-endpoint.mjs — decodes the body and calls the application service import { loanRequest } from "../application/loan-application.mjs"; export const POST_loan = (body) => loanRequest(JSON.parse(body)); ``` The code runs. The problem is not in the behavior but in the dependency direction: the business rule imports both the SQL class and the HTTP formatter. #### Auditing the Rule As long as the dependency direction stays a line in a document, it gets violated. To be auditable it has to be measurable. The script below reads every module in the directory tree, extracts the import statements, places each module into a layer by its directory, and reports every disallowed edge as a finding. ```js // layer-check.mjs — extracts the import graph and audits the dependency direction import { readdirSync, readFileSync, statSync } from "node:fs"; import { join, relative, dirname, resolve } from "node:path"; // Which layers each layer may import. Every edge outside this list is a violation. const ALLOWED = { presentation: ["presentation", "application"], application: ["application", "domain"], infrastructure: ["infrastructure", "domain", "standard"], domain: ["domain", "standard"], root: ["root", "presentation", "application", "domain", "infrastructure", "standard"], }; const layer = (path) => path.split("/")[0] in ALLOWED ? path.split("/")[0] : "root"; const externalLayer = (name) => (name === "node:sqlite" ? "infrastructure" : "standard"); function files(root, dir = root) { return readdirSync(dir).flatMap((name) => { const full = join(dir, name); if (statSync(full).isDirectory()) return files(root, full); return name.endsWith(".mjs") ? [relative(root, full)] : []; }); } const IMPORT = /^\s*(?:import|export)[^;'"]*from\s+["']([^"']+)["']/gm; function imports(root, file) { const text = readFileSync(join(root, file), "utf8"); return [...text.matchAll(IMPORT)].map((m) => m[1]).map((h) => h.startsWith(".") ? relative(root, resolve(root, dirname(file), h)) : h); } export function check(root) { const findings = []; for (const d of files(root)) { for (const h of imports(root, d)) { const source = layer(d); const target = h.startsWith("node:") ? externalLayer(h) : layer(h); if (!ALLOWED[source].includes(target)) findings.push({ d, h, source, target }); } } return findings; } // The set of every module reachable starting from one module. export function closure(root, entry) { const seen = new Set(); const stack = [entry]; while (stack.length > 0) { const next = stack.pop(); if (seen.has(next) || next.startsWith("node:")) { seen.add(next); continue; } seen.add(next); for (const h of imports(root, next)) stack.push(h); } return seen; } const root = process.argv[2]; const findings = check(root); for (const f of findings) console.log(`violation: ${f.d} -> ${f.h} (${f.source} -> ${f.target})`); console.log(`finding count = ${findings.length}`); const c = closure(root, "domain/loan-rules.mjs"); console.log(`domain closure = ${c.size} modules, node:sqlite inside = ${c.has("node:sqlite") ? "yes" : "no"}`); ``` A driver import counts as a layer too: `node:sqlite` is infrastructure, and the remaining standard library modules are marked "standard" and importable from anywhere. Files at the root of the tree belong to no layer; these count as the **composition root** and may import every layer. ```sh node layer-check.mjs violating ``` ``` violation: application/loan-application.mjs -> infrastructure/sqlite-loan-repository.mjs (application -> infrastructure) violation: domain/loan-rules.mjs -> infrastructure/sqlite-loan-repository.mjs (domain -> infrastructure) violation: domain/loan-rules.mjs -> presentation/response.mjs (domain -> presentation) finding count = 3 domain closure = 4 modules, node:sqlite inside = yes ``` Three findings and one measurement. The last line shows what the violations add up to: touching the module that holds the business rule requires all four modules plus the database driver. #### Inverting the Dependency Two of the findings share the same cause: the domain layer is trying to **build what it needs itself**. The fix is for the need to be written as a **contract** in the domain layer, with the implementation staying in infrastructure. The repository interface built in the Repository Pattern lesson was one example of this; here the contract moves into its own module explicitly. ```sh mkdir -p corrected/presentation corrected/application corrected/domain corrected/infrastructure ``` ```js // corrected/domain/loan-repository.mjs — the repository contract the domain layer needs export const REPOSITORY_CONTRACT = ["booksOpenRecord", "membersOpenRecords", "add"]; export function validateRepository(repository) { const missing = REPOSITORY_CONTRACT.filter((name) => typeof repository?.[name] !== "function"); if (missing.length > 0) throw new TypeError(`repository contract is missing: ${missing.join(", ")}`); return repository; } ``` ```js // corrected/domain/loan-rules.mjs — business rules only; no HTTP, no SQL import { validateRepository } from "./loan-repository.mjs"; export const MEMBER_LIMIT = 3; export function issueLoan(repository, memberId, bookId, date) { validateRepository(repository); if (repository.booksOpenRecord(bookId) !== null) { return { result: "rejected", reason: "book_on_loan" }; } if (repository.membersOpenRecords(memberId).length >= MEMBER_LIMIT) { return { result: "rejected", reason: "member_limit", limit: MEMBER_LIMIT }; } return { result: "issued", loanId: repository.add({ bookId, memberId, pickupDate: date }) }; } ``` The return value changed too. The violating version produced an HTTP status code; this version speaks the domain's language. Mapping the result to a status code is the presentation layer's job. ```js // corrected/infrastructure/sqlite-loan-repository.mjs — the contract's SQL implementation import { DatabaseSync } from "node:sqlite"; import { validateRepository } from "../domain/loan-repository.mjs"; export const connect = (file) => new DatabaseSync(file); export class SqliteLoanRepository { constructor(db) { this.db = db; validateRepository(this); } booksOpenRecord(bookId) { return this.db.prepare( "SELECT loan_id FROM loan WHERE book_id = ? AND return_date IS NULL").get(bookId) ?? null; } membersOpenRecords(memberId) { return this.db.prepare( "SELECT loan_id FROM loan WHERE member_id = ? AND return_date IS NULL").all(memberId); } add(l) { return Number(this.db.prepare( "INSERT INTO loan (book_id, member_id, pickup_date, return_date) VALUES (?,?,?,NULL)") .run(l.bookId, l.memberId, l.pickupDate).lastInsertRowid); } } ``` The infrastructure module now imports the domain layer. The arrow reversed, and this is the permitted direction. ```js // corrected/application/loan-application.mjs — takes the repository from outside, calls the rule import { issueLoan } from "../domain/loan-rules.mjs"; export class LoanApplication { constructor(repository) { this.repository = repository; } issue(request) { return issueLoan(this.repository, request.memberId, request.bookId, request.date); } } ``` ```js // corrected/presentation/http-endpoint.mjs — turns the domain result into an HTTP response const STATUS = { issued: 201, book_on_loan: 409, member_limit: 409 }; export function POST_loan(application, body) { const s = application.issue(JSON.parse(body)); return s.result === "issued" ? { status: STATUS.issued, body: { loanId: s.loanId } } : { status: STATUS[s.reason], body: { reason: s.reason } }; } ``` No layer builds its own dependency. The one place that brings the connection, the repository and the application service together is the composition root. ```js // corrected/compose.mjs — composition root: only this file wires the layers together import { connect, SqliteLoanRepository } from "./infrastructure/sqlite-loan-repository.mjs"; import { LoanApplication } from "./application/loan-application.mjs"; import { POST_loan } from "./presentation/http-endpoint.mjs"; const application = new LoanApplication(new SqliteLoanRepository(connect(process.argv[2]))); for (const body of ['{"memberId":6,"bookId":1,"date":"2025-07-15"}', '{"memberId":6,"bookId":7,"date":"2025-07-15"}']) { console.log(body, "->", JSON.stringify(POST_loan(application, body))); } ``` The audit runs again. ```sh node layer-check.mjs corrected ``` ``` finding count = 0 domain closure = 2 modules, node:sqlite inside = no ``` Zero findings. The second line says more than the first: the number of modules needed to reach the business rule dropped from four to two, and the database driver fell outside the closure. This version also works with real data. The command below sets up the loan relation of the library schema from the SQL Fundamentals course and runs the composition root. ```sh rm -f library.db sqlite3 library.db <<'SQL' CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT); INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'), (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'), (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'), (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'), (12,4,4,'2025-06-20','2025-07-04'); SQL node corrected/compose.mjs library.db ``` ``` {"memberId":6,"bookId":1,"date":"2025-07-15"} -> {"status":409,"body":{"reason":"book_on_loan"}} {"memberId":6,"bookId":7,"date":"2025-07-15"} -> {"status":201,"body":{"loanId":13}} ``` #### The Practical Effect of the Closure Module count looks like an abstract measure. Its effect is concrete: the smaller the closure, the more the business rule can be called without handing it anything else. The difference shows when both versions get a fake object that satisfies the contract and nothing more. ```js // try-with-fake.mjs — the same business rule, without a database, with a fake repository import { issueLoan as violating } from "./violating/domain/loan-rules.mjs"; import { issueLoan as corrected } from "./corrected/domain/loan-rules.mjs"; const fakeRepository = { booksOpenRecord: () => null, membersOpenRecords: () => [], add: () => 42, }; for (const [name, rule] of [["violating", violating], ["corrected", corrected]]) { try { console.log(`${name.padEnd(12)} ->`, JSON.stringify(rule(fakeRepository, 6, 7, "2025-07-15"))); } catch (e) { console.log(`${name.padEnd(12)} -> ${e.constructor.name}: ${e.message}`); } } ``` ```sh node try-with-fake.mjs ``` ``` violating -> TypeError: this.db.prepare is not a function corrected -> {"result":"issued","loanId":42} ``` The violating version rejects the fake repository, because it assumes the object it was given is a connection and tries to build its own SQL class. The corrected version looks at the contract, so it accepts the fake. This is how the dependency direction rule relates to testability: the rule is not a style preference, it is the precondition for the domain layer being able to run on its own. #### The Boundary Between Application and Domain Of the four layers, application and domain are the two most often confused. The split is made with one question: is the rule the organization's rule, or is it this scenario's flow? "A book already on loan cannot be issued again" is the organization's rule; the library applies it on the loan screen and in the bulk import alike. It belongs to the domain layer. "First find the member, then run the rule, then commit the transaction, then send the notification" is this scenario's order; a different scenario changes the order. It belongs to the application layer. The transaction boundary is also the application layer's decision. The Transaction Boundaries lesson showed that the unit of work's boundary belongs in the service layer; this lesson places that decision under a layer name. The domain layer never writes `BEGIN`, because it has no need to know whether a rule is being evaluated inside a transaction or outside one. The boundary between presentation and application is easier. The presentation layer knows the shape of the protocol: which route maps to which function, how the body gets decoded, which status code the result maps to. Letting this knowledge leak into the application layer would block the same scenario from being called through a second entry point — a scheduled job, a shell command, a queue consumer. #### Summary - The four layers split by their different reasons for changing; the core of the separation is not the layer count but the dependency direction between them. - The dependency direction can be audited from the import graph: the violating version produced three findings, and the finding count dropped to zero once the contract moved into the domain layer. - The domain layer's import closure shrank from four modules to two, and the database driver fell outside the closure. - The business rule in the violating version could not be called with a fake repository; the corrected version worked with the same fake. The dependency direction rule is the precondition for testing the domain layer in isolation. - The business rule belongs to the domain layer, the scenario order and the transaction boundary to the application layer, the protocol shape to the presentation layer; the composition root is the only place that wires the dependencies together. #### Next Step In the corrected version, the domain layer returned an object shaped `{ result, reason }`, and the presentation layer turned it into an HTTP body. For now this translation had three fields and was nearly one-to-one; the question sharpens once the record grows. Is the shape of the loan record returned to the outside the same thing as the domain model itself? When a new field is added to the domain model, should the body the client sees change? The next lesson produces two different external contracts from the same domain object, shows with a test that the external contract does not change when a field is added to the internal model, and counts how many internal fields returning the domain object directly leaks to the outside. ### Data Transfer Objects Source: https://academia.sh/en/courses/data-access-layer/data-transfer-objects Separating the outer contract from the inner model: producing two different views from the same domain object, testing that the outer contract does not change when a field is added to the inner model, counting the fields returning the domain object directly leaks, and mass assignment in the incoming direction. In the previous lesson's corrected version, the domain layer returned an object shaped `{ result, reason }`, and the presentation layer turned it into an HTTP body. The translation had three fields and was nearly one-to-one; the middle layer looked like an unnecessary handoff. The question sharpens once the record grows. Is the shape of the loan record returned to the outside the same thing as the domain model itself? When a new field is added to the domain model, should the body the client sees change? This lesson's subject is the thing placed between the two sides: a **data transfer object** — a plain record produced for talking to the outside, carrying no domain behavior. #### One Record, Two Contracts The loan record's domain model carries the data and the decisions that derive from that data. The class below holds eleven fields; two of them produce derived information. The lesson's files sit in two directories. ```sh mkdir -p domain presentation ``` ```js // domain/loan.mjs — the loan record's domain model export class Loan { constructor(v) { this.loanId = v.loanId; this.bookId = v.bookId; this.bookTitle = v.bookTitle; this.memberId = v.memberId; this.memberName = v.memberName; this.memberEmail = v.memberEmail; this.pickupDate = v.pickupDate; this.dueDate = v.dueDate; this.returnDate = v.returnDate; this.staffNote = v.staffNote; this.recordVersion = v.recordVersion; } get isOpen() { return this.returnDate === null; } overdueDays(today) { const end = this.returnDate ?? today; const diff = (Date.parse(end) - Date.parse(this.dueDate)) / 86400000; return Math.max(0, Math.round(diff)); } status(today) { if (!this.isOpen) return "returned"; return this.overdueDays(today) > 0 ? "overdue" : "open"; } } ``` Three fields do not belong to the outside. `memberEmail` is personal data shown only to certain roles; `staffNote` is internal correspondence; `recordVersion` is the version column from the Optimistic and Pessimistic Locking lesson itself — a persistence detail. The outside does not want a single shape either. The list screen is satisfied with four fields per row; the detail screen wants to see the relations too. ```js // presentation/views.mjs — two different outer contracts from the same domain object export const listView = (o, today) => ({ loanId: o.loanId, book: o.bookTitle, dueDate: o.dueDate, status: o.status(today), }); export const detailView = (o, today) => ({ loanId: o.loanId, book: { bookId: o.bookId, title: o.bookTitle }, member: { memberId: o.memberId, name: o.memberName }, pickupDate: o.pickupDate, dueDate: o.dueDate, returnDate: o.returnDate, status: o.status(today), overdueDays: o.overdueDays(today), }); ``` The mapping does two jobs: it leaves the internal fields out and adds the derived fields. The second job is the one that goes unnoticed most often. `status` and `overdueDays` are not values stored on the domain model, they are computed values; letting the client compute them itself would mean copying the business rule to the client. ```js // view.mjs — one domain object, two outer contracts import { Loan } from "./domain/loan.mjs"; import { listView, detailView } from "./presentation/views.mjs"; const TODAY = "2025-06-20"; const record = new Loan({ loanId: 7, bookId: 5, bookTitle: "Blindness", memberId: 2, memberName: "Alice Kane", memberEmail: "alice@example.test", pickupDate: "2025-06-01", dueDate: "2025-06-15", returnDate: null, staffNote: "called by phone", recordVersion: 3, }); console.log("list :", JSON.stringify(listView(record, TODAY))); console.log("detail:", JSON.stringify(detailView(record, TODAY))); ``` ```sh node view.mjs ``` ``` list : {"loanId":7,"book":"Blindness","dueDate":"2025-06-15","status":"overdue"} detail: {"loanId":7,"book":{"bookId":5,"title":"Blindness"},"member":{"memberId":2,"name":"Alice Kane"},"pickupDate":"2025-06-01","dueDate":"2025-06-15","returnDate":null,"status":"overdue","overdueDays":5} ``` Same object, two bodies. The list body flattened the relations, the detail body grouped them. This is not a formatting choice, it is two different contracts: the list screen can change without the detail contract being affected. #### Isolating the Contract from Internal Change The real reason for the separation sits here. What happens when a new field is added to the domain model? The file below is the same model after two internal fields have been added. ```js // domain/loan-2.mjs — the same model after two internal fields are added export class Loan { constructor(v) { this.loanId = v.loanId; this.bookId = v.bookId; this.bookTitle = v.bookTitle; this.memberId = v.memberId; this.memberName = v.memberName; this.memberEmail = v.memberEmail; this.pickupDate = v.pickupDate; this.dueDate = v.dueDate; this.returnDate = v.returnDate; this.staffNote = v.staffNote; this.recordVersion = v.recordVersion; this.renewalCount = v.renewalCount ?? 0; // new this.lastReminderDate = v.lastReminderDate ?? null; // new } get isOpen() { return this.returnDate === null; } overdueDays(today) { const end = this.returnDate ?? today; const diff = (Date.parse(end) - Date.parse(this.dueDate)) / 86400000; return Math.max(0, Math.round(diff)); } status(today) { if (!this.isOpen) return "returned"; return this.overdueDays(today) > 0 ? "overdue" : "open"; } } ``` A test compares the bodies the two versions produce. ```js // view.test.mjs — tests that the outer contract is unaffected by internal model change import { test } from "node:test"; import assert from "node:assert/strict"; import { Loan as Loan1 } from "./domain/loan.mjs"; import { Loan as Loan2 } from "./domain/loan-2.mjs"; import { listView, detailView } from "./presentation/views.mjs"; const TODAY = "2025-06-20"; const BASE = { loanId: 7, bookId: 5, bookTitle: "Blindness", memberId: 2, memberName: "Alice Kane", memberEmail: "alice@example.test", pickupDate: "2025-06-01", dueDate: "2025-06-15", returnDate: null, staffNote: "called by phone", recordVersion: 3, }; // Version 2 adds two internal fields to the same record. const BASE2 = { ...BASE, renewalCount: 2, lastReminderDate: "2025-06-18" }; const LIST_CONTRACT = ["loanId", "book", "dueDate", "status"]; const DETAIL_CONTRACT = ["loanId", "book", "member", "pickupDate", "dueDate", "returnDate", "status", "overdueDays"]; const INTERNAL_FIELDS = ["memberEmail", "staffNote", "recordVersion", "renewalCount", "lastReminderDate"]; test("list view produces the fields in the contract", () => { assert.deepEqual(Object.keys(listView(new Loan1(BASE), TODAY)), LIST_CONTRACT); }); test("detail view produces the fields in the contract", () => { assert.deepEqual(Object.keys(detailView(new Loan1(BASE), TODAY)), DETAIL_CONTRACT); }); test("internal fields do not appear in either view", () => { const text = JSON.stringify(listView(new Loan2(BASE2), TODAY)) + JSON.stringify(detailView(new Loan2(BASE2), TODAY)); for (const field of INTERNAL_FIELDS) assert.equal(text.includes(field), false, field); }); test("list view stays identical once the internal model gains a field", () => { assert.deepEqual(listView(new Loan2(BASE2), TODAY), listView(new Loan1(BASE), TODAY)); }); test("detail view stays identical once the internal model gains a field", () => { assert.deepEqual(detailView(new Loan2(BASE2), TODAY), detailView(new Loan1(BASE), TODAY)); }); ``` ```sh node --test view.test.mjs ``` ``` ✔ list view produces the fields in the contract (0.872625ms) ✔ detail view produces the fields in the contract (0.076625ms) ✔ internal fields do not appear in either view (0.350875ms) ✔ list view stays identical once the internal model gains a field (0.062292ms) ✔ detail view stays identical once the internal model gains a field (0.06175ms) ℹ tests 5 ℹ suites 0 ℹ pass 5 ℹ fail 0 ℹ cancelled 0 ℹ skipped 0 ℹ todo 0 ℹ duration_ms 36.116458 ``` The durations in parentheses change on every run; what matters is the `pass 5` line. Between the two versions, the outer bodies came out identical under `deepEqual`. The internal model changed, the outer contract did not. This is the in-application counterpart of the backward compatibility rule from the Web API Design course: the contract changing has to be a decision, not a side effect. #### The Cost of Returning Directly The reverse direction can be measured. If the endpoint returns the domain object directly, a two-way gap opens between the outer body and the contract. ```js // count-leak.mjs — what happens to the outer contract when the domain object is returned directly import { Loan as Loan1 } from "./domain/loan.mjs"; import { Loan as Loan2 } from "./domain/loan-2.mjs"; import { listView } from "./presentation/views.mjs"; const TODAY = "2025-06-20"; const BASE = { loanId: 7, bookId: 5, bookTitle: "Blindness", memberId: 2, memberName: "Alice Kane", memberEmail: "alice@example.test", pickupDate: "2025-06-01", dueDate: "2025-06-15", returnDate: null, staffNote: "called by phone", recordVersion: 3, }; const BASE2 = { ...BASE, renewalCount: 2, lastReminderDate: "2025-06-18" }; // The body the client sees is the object serialized to JSON. const externalFields = (object) => Object.keys(JSON.parse(JSON.stringify(object))).sort(); const diff = (a, b) => a.filter((x) => !b.includes(x)); for (const [name, record] of [["version 1", new Loan1(BASE)], ["version 2", new Loan2(BASE2)]]) { const mapped = externalFields(listView(record, TODAY)); const direct = externalFields(record); console.log(`${name} mapped=${mapped.length} fields direct=${direct.length} fields`); console.log(` leaked (${diff(direct, mapped).length}): ${diff(direct, mapped).join(", ")}`); console.log(` missing (${diff(mapped, direct).length}): ${diff(mapped, direct).join(", ")}`); } ``` ```sh node count-leak.mjs ``` ``` version 1 mapped=4 fields direct=11 fields leaked (9): bookId, bookTitle, memberEmail, memberId, memberName, pickupDate, recordVersion, returnDate, staffNote missing (2): book, status version 2 mapped=4 fields direct=13 fields leaked (11): bookId, bookTitle, lastReminderDate, memberEmail, memberId, memberName, pickupDate, recordVersion, renewalCount, returnDate, staffNote missing (2): book, status ``` Three findings stand out. The first is the leak itself: the list contract has four fields, and the body returned directly came out with eleven, nine of which are not in the contract. Among them, `memberEmail` is personal data, `staffNote` is internal correspondence, `recordVersion` is a persistence detail. The second is that the growth happens on its own: adding two internal fields pushed the directly-returned body to thirteen fields, while the mapped body stayed at four. On the directly-returned endpoint, every model change is a contract change, and nobody decided on it. The third is loss in the other direction. The directly-returned body has no `status` or `book` fields, because these are derived, not stored. The extra bytes measured in the Over-Fetching lesson gain a second cost here: the body is both larger than it needs to be and short on the information the client actually needs. #### The Incoming Direction The same boundary is needed in the other direction too. If the request body is assigned directly to the inner model, the client determines fields it should not be able to set. The comparison below counts this. ```js // incoming-direction.mjs — what the client determines when the request body is assigned directly const DEFAULTS = { loanId: null, bookId: null, bookTitle: null, memberId: null, memberName: null, memberEmail: null, pickupDate: null, dueDate: null, returnDate: null, staffNote: null, recordVersion: 1, }; // The body the client sent: three legitimate fields and three extras. const REQUEST = { bookId: 5, memberId: 2, pickupDate: "2025-06-01", loanId: 1, recordVersion: 99, staffNote: "mark the record closed", }; const unprotected = (request) => ({ ...DEFAULTS, ...request }); const mapped = (request) => ({ ...DEFAULTS, bookId: request.bookId, memberId: request.memberId, pickupDate: request.pickupDate, }); const determinedByClient = (object) => Object.keys(object).filter((f) => JSON.stringify(object[f]) !== JSON.stringify(DEFAULTS[f])); for (const [name, build] of [["unprotected", unprotected], ["mapped", mapped]]) { const fields = determinedByClient(build(REQUEST)); console.log(`${name.padEnd(10)} fields determined by client = ${fields.length}: ${fields.join(", ")}`); } ``` ```sh node incoming-direction.mjs ``` ``` unprotected fields determined by client = 6: loanId, bookId, memberId, pickupDate, staffNote, recordVersion mapped fields determined by client = 3: bookId, memberId, pickupDate ``` In the unprotected version, the client determined the record's identity, the version counter, and the staff note. The version counter coming from the client makes optimistic locking useless: conflict detection depends on the version number the server knows. This behavior is called **mass assignment**, and it silently widens as fields are added to the body. In the mapped version, the request body is a data transfer object; a field not named in it cannot reach the inner model. This is the same principle as the identifier allowlist in the Query Objects and Specifications lesson: turning an incoming name into internal structure rests on a fixed list. #### The Cost and Limit of Mapping The separation is not free. Every view means extra code, extra testing, and extra maintenance every time the model changes. As the field count grows the mapping functions grow too, and part of them just copy the field name as it is. The payoff is clearly collected in three cases: when the outer contract needs to change at a different pace than the inner model, when the inner model has a field that should not be shown to the outside, and when more than one view is produced from the same record. The role separation from the Authentication and Authorization course feeds the third case directly: the same loan record is visible to the member, to staff, and to the reporting interface with different field sets; the place carrying that difference is the view functions. The opposite case also exists. In an admin screen with no domain model, one that just reads a table and lists it, a separate data transfer object layer produces nothing but a copy. The test is this: **if the mapping function hides no field, derives no field, and renames no field**, that mapping is not yet carrying a contract. #### Summary - A data transfer object is a plain record produced for talking to the outside; the domain model carries the data and the decisions that derive from it. - Two separate contracts, list and detail, were produced from the same domain object; the mapping both hid internal fields and added derived fields. - When two fields were added to the internal model, both views produced identical bodies; all five tests passed. - When the domain object was returned directly, nine fields leaked relative to the list contract and two derived fields went missing; once the model grew, the leaked field count rose to eleven. - In the incoming direction, assigning the request body directly to the inner model let the client determine six fields; a mapping that names the fields brought that number down to three. #### Next Step In this lesson's incoming-direction example, the fields in the request body were accepted with no check at all: is `bookId` a number, is `pickupDate` a valid date, does the member actually exist, is that member currently eligible to borrow? All of these questions get filed under "validation", but they do not belong in the same place. One concerns the shape of the body and can be answered without looking at the data; the other is the organization's rule and cannot be answered without looking at the database. The next lesson runs the same faulty input through both layers, shows the difference between the responses they produce, and lays out why the domain rule cannot be reduced to input validation. ### Validation Layers Source: https://academia.sh/en/courses/data-access-layer/validation-layers Separating input validation from the domain rule: the different responses the same body produces in two layers, counting how many queries each layer's decision needs, the domain rule's dependence on data state, and the database constraint that gives the final guarantee. In the previous lesson's incoming-direction example, the request body was accepted with no check at all. Is `bookId` a number, is `pickupDate` a valid date, does the member actually exist, is that member currently eligible to borrow? All of these questions get filed under "validation", but they do not belong in the same place. The first two are answered by looking at the body; the third and fourth cannot be answered without looking at the database. The name for this split is **input validation** versus **domain rule**, and this lesson measures the difference and shows it directly. The measurements below run against the following database. One member is suspended, one member has reached three open records, and one book is on loan. ```sh # setup.sh — sets up the lesson database from scratch rm -f library.db sqlite3 library.db <<'SQL' CREATE TABLE member (member_id INTEGER PRIMARY KEY, name TEXT NOT NULL, status TEXT NOT NULL CHECK (status IN ('active','suspended'))); CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id), member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL, return_date TEXT); INSERT INTO member VALUES (1,'Alice Kane','active'),(2,'Ben Ortiz','suspended'),(3,'Clara Diaz','active'); INSERT INTO book VALUES (1,'Blindness'),(2,'The Disconnected'),(3,'The Book of Sand'),(4,'Puslu Kitalar'), (5,'The Time Regulation Institute'),(6,'Cocukluk'),(7,'Aylak Adam'); INSERT INTO loan VALUES (1,1,1,'2025-06-01',NULL),(2,2,3,'2025-06-02',NULL), (3,3,3,'2025-06-03',NULL),(4,4,3,'2025-06-04',NULL); SQL ``` #### Input Validation The lesson's files sit in three directories. ```sh mkdir -p presentation domain infrastructure ``` Input validation checks the shape of the body: is the field present, is the type correct, does it match the pattern. It decides by looking only at the body. The Validation Errors lesson in the Web API Design course defined this layer's output as a list: every error carries a body path and a code. ```js // presentation/input-validation.mjs — checks the shape of the body; never looks at data const RULES = { required: (d) => (d === undefined || d === null || d === "" ? { code: "required" } : null), integer: (d) => (Number.isInteger(d) && d > 0 ? null : { code: "not_an_integer" }), date: (d) => (/^\d{4}-\d{2}-\d{2}$/.test(String(d)) ? null : { code: "not_a_date" }), }; export const SCHEMA = { "/memberId": ["required", "integer"], "/bookId": ["required", "integer"], "/pickupDate": ["required", "date"], }; // The returned list is shaped { path, code }, as in the Validation Errors lesson. export function validateInput(schema, body) { const errors = []; for (const [path, rules] of Object.entries(schema)) { const value = body[path.slice(1)]; for (const name of rules) { if (name !== "required" && (value === undefined || value === null || value === "")) break; const result = RULES[name](value); if (result) { errors.push({ path, ...result }); break; } // one error per field } } return errors; } ``` There is no database in the function's signature. This is the layer's definition: the same body produces the same error list under every condition. #### The Domain Rule The domain rule asks the organization's question: can this member borrow, is this book available? The decision cannot be derived from the body. ```js // domain/loan-rules.mjs — organizational rules; the decision needs data export const MEMBER_LIMIT = 3; export function canIssueLoan(repository, memberId, bookId) { const member = repository.findMember(memberId); if (member === null) return { result: "rejected", reason: "member_not_found" }; if (member.status === "suspended") return { result: "rejected", reason: "member_suspended" }; if (!repository.bookExists(bookId)) return { result: "rejected", reason: "book_not_found" }; if (repository.booksOpenRecord(bookId) !== null) return { result: "rejected", reason: "book_on_loan" }; if (repository.membersOpenCount(memberId) >= MEMBER_LIMIT) { return { result: "rejected", reason: "member_limit", limit: MEMBER_LIMIT }; } return { result: "accepted" }; } ``` The rule takes a repository and returns a single reason. Two differences show up here: the decision's input is not the body but **state**, and its output is not a list but a **single fact**. To see what the decision costs, the repository counts the queries it runs. ```js // infrastructure/counting-repository.mjs — satisfies the contract and counts the queries it runs import { DatabaseSync } from "node:sqlite"; export class CountingRepository { constructor(file) { this.db = new DatabaseSync(file); this.queries = 0; } #one(sql, ...p) { this.queries++; return this.db.prepare(sql).get(...p) ?? null; } findMember(memberId) { return this.#one("SELECT member_id, status FROM member WHERE member_id = ?", memberId); } bookExists(bookId) { return this.#one("SELECT book_id FROM book WHERE book_id = ?", bookId) !== null; } booksOpenRecord(bookId) { return this.#one( "SELECT loan_id FROM loan WHERE book_id = ? AND return_date IS NULL", bookId); } membersOpenCount(memberId) { return this.#one( "SELECT count(*) AS n FROM loan WHERE member_id = ? AND return_date IS NULL", memberId).n; } } ``` #### The Same Input Through Two Layers Five bodies pass through the two layers in order. The first is malformed; the remaining four are well formed. ```js // two-layers.mjs — the same bodies run through input validation, then the domain rule import { validateInput, SCHEMA } from "./presentation/input-validation.mjs"; import { canIssueLoan } from "./domain/loan-rules.mjs"; import { CountingRepository } from "./infrastructure/counting-repository.mjs"; const BASE = "https://example.library/problems/"; const repository = new CountingRepository("library.db"); const BODIES = [ { memberId: "two", bookId: 0, pickupDate: "15/06/2025" }, { memberId: 2, bookId: 6, pickupDate: "2025-06-20" }, { memberId: 1, bookId: 1, pickupDate: "2025-06-20" }, { memberId: 3, bookId: 6, pickupDate: "2025-06-20" }, { memberId: 1, bookId: 6, pickupDate: "2025-06-20" }, ]; for (const body of BODIES) { console.log(`body: ${JSON.stringify(body)}`); const errors = validateInput(SCHEMA, body); console.log(` input validation: ${errors.length} errors, queries = 0`); if (errors.length > 0) { console.log(` response 422 ${JSON.stringify({ type: BASE + "validation", errors })}`); continue; } repository.queries = 0; const decision = canIssueLoan(repository, body.memberId, body.bookId); console.log(` domain rule: ${decision.result}${decision.reason ? ` (${decision.reason})` : ""}, queries = ${repository.queries}`); console.log(decision.result === "accepted" ? " response 201 " + JSON.stringify({ status: "issued" }) : " response 409 " + JSON.stringify({ type: BASE + decision.reason.replaceAll("_", "-") })); } ``` ```sh sh setup.sh node two-layers.mjs ``` ``` body: {"memberId":"two","bookId":0,"pickupDate":"15/06/2025"} input validation: 3 errors, queries = 0 response 422 {"type":"https://example.library/problems/validation","errors":[{"path":"/memberId","code":"not_an_integer"},{"path":"/bookId","code":"not_an_integer"},{"path":"/pickupDate","code":"not_a_date"}]} body: {"memberId":2,"bookId":6,"pickupDate":"2025-06-20"} input validation: 0 errors, queries = 0 domain rule: rejected (member_suspended), queries = 1 response 409 {"type":"https://example.library/problems/member-suspended"} body: {"memberId":1,"bookId":1,"pickupDate":"2025-06-20"} input validation: 0 errors, queries = 0 domain rule: rejected (book_on_loan), queries = 3 response 409 {"type":"https://example.library/problems/book-on-loan"} body: {"memberId":3,"bookId":6,"pickupDate":"2025-06-20"} input validation: 0 errors, queries = 0 domain rule: rejected (member_limit), queries = 4 response 409 {"type":"https://example.library/problems/member-limit"} body: {"memberId":1,"bookId":6,"pickupDate":"2025-06-20"} input validation: 0 errors, queries = 0 domain rule: accepted, queries = 4 response 201 {"status":"issued"} ``` Three differences were measured. **The response shape is different.** Input validation collected three separate field errors in one response; each is marked with a body path, so the client knows which input box to highlight. The domain rule returned a single reason and gave no body path: "the member is suspended" is not one field's error, it is the rejection of the operation itself. The status code diverges for the same reason: one says the body is fixable, the other says the request cannot be satisfied in its current state. **The cost is different.** Input validation decided with zero queries. The domain rule's decision ranged from one to four queries; when the rule rejected early, the query count dropped. This is the justification for the layer order: a malformed body never reached the domain layer, and four queries were not wasted. **The source of information is different.** Input validation looked at the value in the body; the domain rule looked at information that is not in the body. The request says `memberId: 2`, it does not say the member is suspended. If this information could be included in the body, it would already have been asked of the client, and the client would have filled it in as it pleased. #### The Rule's Dependence on Data The proof of the third difference is holding the body constant and changing the data. ```js // data-dependent.mjs — same body, unchanged shape, changing data import { DatabaseSync } from "node:sqlite"; import { validateInput, SCHEMA } from "./presentation/input-validation.mjs"; import { canIssueLoan } from "./domain/loan-rules.mjs"; import { CountingRepository } from "./infrastructure/counting-repository.mjs"; const BODY = { memberId: 2, bookId: 6, pickupDate: "2025-06-20" }; const writer = new DatabaseSync("library.db"); const repository = new CountingRepository("library.db"); for (const status of ["suspended", "active", "suspended"]) { writer.prepare("UPDATE member SET status = ? WHERE member_id = 2").run(status); const errors = validateInput(SCHEMA, BODY).length; const decision = canIssueLoan(repository, BODY.memberId, BODY.bookId); console.log(`member 2 status=${status.padEnd(9)} input errors=${errors} ` + `domain decision=${decision.result}${decision.reason ? ` (${decision.reason})` : ""}`); } ``` ```sh node data-dependent.mjs ``` ``` member 2 status=suspended input errors=0 domain decision=rejected (member_suspended) member 2 status=active input errors=0 domain decision=accepted member 2 status=suspended input errors=0 domain decision=rejected (member_suspended) ``` The body never changed. Input validation returned zero errors in all three rounds; the domain rule produced two different decisions. The third round restores the original status, so the script produces the same output when run again. This is the formal justification for why the domain rule cannot be reduced to input validation. Input validation is a function of the body; the domain rule is a joint function of the body and the state. Input validation can be described by a fixed schema, the domain rule cannot. #### The Third Layer Once both layers have passed, a gap remains. The domain rule said "the book is not on loan" and the write happened right after; if another request wrote the same book in between, the rule was acting on stale information. This is the same check-then-write gap as in the Optimistic and Pessimistic Locking lesson. The script below sets up the order in which two requests both evaluate the rule before writing, and runs the same order against two databases, one constrained and one unconstrained. ```js // third-layer.mjs — the domain rule passed, another request came in before the write: the constraint has the last word import { DatabaseSync } from "node:sqlite"; import { rmSync } from "node:fs"; import { canIssueLoan } from "./domain/loan-rules.mjs"; function setup(file, constrained) { rmSync(file, { force: true }); const db = new DatabaseSync(file); db.exec(`CREATE TABLE member (member_id INTEGER PRIMARY KEY, name TEXT NOT NULL, status TEXT NOT NULL); CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT); INSERT INTO member VALUES (1,'Alice Kane','active'),(2,'Ben Ortiz','active'); INSERT INTO book VALUES (6,'Cocukluk');`); if (constrained) { db.exec("CREATE UNIQUE INDEX open_loan ON loan(book_id) WHERE return_date IS NULL"); } return db; } const buildRepository = (db) => ({ findMember: (id) => db.prepare("SELECT member_id, status FROM member WHERE member_id = ?").get(id) ?? null, bookExists: (id) => db.prepare("SELECT book_id FROM book WHERE book_id = ?").get(id) !== undefined, booksOpenRecord: (id) => db.prepare( "SELECT loan_id FROM loan WHERE book_id = ? AND return_date IS NULL").get(id) ?? null, membersOpenCount: (id) => db.prepare( "SELECT count(*) AS n FROM loan WHERE member_id = ? AND return_date IS NULL").get(id).n, }); for (const constrained of [false, true]) { const db = setup(constrained ? "constrained.db" : "unconstrained.db", constrained); const repository = buildRepository(db); const write = (memberId) => db.prepare( "INSERT INTO loan (book_id, member_id, pickup_date, return_date) VALUES (6,?,'2025-06-20',NULL)") .run(memberId); // Two requests want the same book; both evaluate the rule before writing. const decision1 = canIssueLoan(repository, 1, 6).result; const decision2 = canIssueLoan(repository, 2, 6).result; write(1); let second; try { write(2); second = "written"; } catch (e) { second = `rejected (${e.message})`; } const open = db.prepare( "SELECT count(*) AS n FROM loan WHERE book_id = 6 AND return_date IS NULL").get().n; console.log(`constraint ${constrained ? "on" : "off"}: decision1=${decision1} decision2=${decision2} ` + `second write=${second} book 6 open records=${open}`); } ``` ```sh node third-layer.mjs ``` ``` constraint off: decision1=accepted decision2=accepted second write=written book 6 open records=2 constraint on: decision1=accepted decision2=accepted second write=rejected (UNIQUE constraint failed: loan.book_id) book 6 open records=1 ``` In both cases the domain rule accepted both requests; the rule was not wrong, its timing was unfortunate. Without the constraint the book ended up with two open records, a direct violation of the business rule. With the constraint the second write was rejected, and the open record count stayed at one. This is how the layers' roles split apart. The domain rule produces **the reason for the decision**: it is the only way the user can be told "this book is on loan"; a constraint error cannot say that sentence. The constraint guarantees **the invariant** and has the final word when the rule is bypassed or raced. Neither substitutes for the other: an application that relies only on the constraint cannot give the user a meaningful error, and one that relies only on the rule is exposed to corrupt data. #### Drawing the Boundary Which layer a new rule belongs to is settled by one question: **can the rule be answered by looking at the body?** "The return date cannot be in the past" is answered by looking at the body; it is a date comparison and belongs to input validation. "A member with an overdue record cannot be issued a new loan" cannot be answered by looking at the body; it is a domain rule. "The ISBN must be thirteen digits" is shape. "This ISBN must exist in the catalog" is data. There is a middle case: cross-field validation. "The pickup date must be before the due date" compares two fields, needs no data, and stays in input validation. The schema in the Validation Errors lesson could already express this kind of rule through the field path; the criterion does not change. The order also follows from this split: shape first, then rule, then constraint. The reverse order produces two costs. Running a query against a malformed body is wasted work; showing the user a constraint error instead of the rule is a response that does not explain what the problem is. #### Summary - Input validation is a function of the body and decided with zero queries; the domain rule is a joint function of the body and the state, and its decision ranged from one to four queries. - The two layers' responses differ in shape: one is an error list marked with body paths, the other produced a single rejection reason. - With the body held constant and the member's status changed, input validation returned zero errors in all three rounds while the domain rule produced two different decisions. - The domain rule is exposed to the check-then-write gap: in the unconstrained database the same book ended up with two open records, in the constrained database the second write was rejected. - The rule gives the reason, the constraint gives the invariant; one produces a meaningful error for the user, the other guarantees the data. #### Next Step The fifth body passed through both layers, and it was time to write the loan record. Other work needs to start along with the write: the member should be notified, a daily statistic should be updated, an entry should be written to the log for future reports. Writing all of this into the body of the loan-issuing function means changing that function on every new side effect. The next lesson publishes the fact the loan decision produces as a **domain event**, attaches two listeners, and measures the event's relationship to the transaction boundary: when a transaction is rolled back, the wrong notifications the listener produced are counted and brought down to zero with the outbox pattern. ### Domain Events Source: https://academia.sh/en/courses/data-access-layer/domain-events Loosely coupling side effects through event publication: attaching more than one listener to the same publish call, counting the wrong notifications an event published inside a transaction produces after a rollback, and bringing that count to zero with the outbox pattern. The loan request passed through both validation layers and the record is ready to be written. Other work needs to start along with the write: the member should be notified, a daily statistic should be updated, a line should be written to the log for future reports. Writing all of this into the body of the loan-issuing function means changing that function on every new side effect. A **domain event** loosens the link between them: the loan-issuing function announces what happened and does not know who will act on it. This lesson's actual subject is the timing of that announcement — when is publishing the event correct? #### Event, Dispatcher, Listener An event describes a fact in the past tense: "the loan was issued". It is not a request and not a command; it is the record of something that already happened. The dispatcher calls the listeners bound to the event type. The lesson's files sit in two directories. ```sh mkdir -p domain application ``` ```js // domain/event-dispatcher.mjs — the smallest dispatcher that calls listeners by event type export class EventDispatcher { constructor() { this.listeners = new Map(); } subscribe(type, listener) { if (!this.listeners.has(type)) this.listeners.set(type, []); this.listeners.get(type).push(listener); return this; } publish(event) { for (const l of this.listeners.get(event.type) ?? []) l(event); return (this.listeners.get(event.type) ?? []).length; } } ``` Listeners are the side effects. Both take the same event, and neither knows about the other. ```js // application/listeners.mjs — three side effects that react to the same event export const notificationListener = (box) => (event) => box.push({ loanId: event.loanId, memberId: event.memberId, bookId: event.bookId, text: `Book ${event.bookId} was issued on ${event.date}.` }); export const statsListener = (counts) => (event) => { counts[event.date] = (counts[event.date] ?? 0) + 1; }; export const logListener = (lines) => (event) => lines.push(`${event.date} ${event.type} loan=${event.loanId}`); ``` The measure of loose coupling is this: when a listener is added, the producing side's code does not change. ```js // publish.mjs — the same publish call, a different number of listeners import { EventDispatcher } from "./domain/event-dispatcher.mjs"; import { notificationListener, statsListener, logListener } from "./application/listeners.mjs"; const EVENTS = [ { type: "loan_issued", loanId: 1, memberId: 1, bookId: 5, date: "2025-06-20" }, { type: "loan_issued", loanId: 2, memberId: 3, bookId: 7, date: "2025-06-20" }, ]; for (const listenerCount of [2, 3]) { const box = [], counts = {}, lines = []; const dispatcher = new EventDispatcher() .subscribe("loan_issued", notificationListener(box)) .subscribe("loan_issued", statsListener(counts)); if (listenerCount === 3) dispatcher.subscribe("loan_issued", logListener(lines)); // The producing side runs the same single line in both cases. for (const event of EVENTS) dispatcher.publish(event); console.log(`listeners=${listenerCount} notifications=${box.length} ` + `stats=${JSON.stringify(counts)} log=${lines.length}`); } ``` ```sh node publish.mjs ``` ``` listeners=2 notifications=2 stats={"2025-06-20":2} log=0 listeners=3 notifications=2 stats={"2025-06-20":2} log=2 ``` The third side effect was added with no change to the line that publishes. The subscription is wired in the application layer; the domain layer only produces the event itself and does not know who subscribed to it. #### The Event's Relationship to the Transaction Boundary Where the publish is placed is a subtler problem than loose coupling. In the database below, issuing a loan performs two writes: the record is added and the book's stock drops by one. Stock cannot go below zero; this constraint makes some requests fail on the second step. ```sh # setup.sh — sets up the lesson database from scratch rm -f library.db sqlite3 library.db <<'SQL' CREATE TABLE member (member_id INTEGER PRIMARY KEY, name TEXT NOT NULL); CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, stock INTEGER NOT NULL CHECK (stock >= 0)); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT); CREATE TABLE outbox (message_id INTEGER PRIMARY KEY, body TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'pending'); INSERT INTO member VALUES (1,'Alice Kane'),(2,'Ben Ortiz'); INSERT INTO book VALUES (1,'Blindness',1),(2,'The Disconnected',0), (3,'The Book of Sand',1),(4,'Puslu Kitalar',0); SQL ``` The first version publishes the event as soon as the record is added, that is, inside the transaction boundary. ```js // in-transaction.mjs — the event is published inside the transaction; the listener already ran even if it is rolled back import { DatabaseSync } from "node:sqlite"; import { EventDispatcher } from "./domain/event-dispatcher.mjs"; import { notificationListener, statsListener } from "./application/listeners.mjs"; const db = new DatabaseSync("library.db"); const box = [], counts = {}; const dispatcher = new EventDispatcher() .subscribe("loan_issued", notificationListener(box)) .subscribe("loan_issued", statsListener(counts)); function issueLoan(memberId, bookId, date) { db.exec("BEGIN"); try { const loanId = Number(db.prepare( "INSERT INTO loan (book_id, member_id, pickup_date, return_date) VALUES (?,?,?,NULL)") .run(bookId, memberId, date).lastInsertRowid); dispatcher.publish({ type: "loan_issued", loanId, memberId, bookId, date }); db.prepare("UPDATE book SET stock = stock - 1 WHERE book_id = ?").run(bookId); db.exec("COMMIT"); return `issued (loan ${loanId})`; } catch (e) { db.exec("ROLLBACK"); return `rolled back (${e.message.split("\n")[0]})`; } } const REQUESTS = [[1, 1], [1, 2], [2, 3], [2, 4]]; for (const [memberId, bookId] of REQUESTS) { console.log(`member ${memberId} book ${bookId}: ${issueLoan(memberId, bookId, "2025-06-20")}`); } // A notification is wrong if it has no counterpart in the database. const wrong = box.filter((n) => db.prepare( "SELECT 1 FROM loan WHERE loan_id = ? AND book_id = ?").get(n.loanId, n.bookId) === undefined); console.log(`loan rows = ${db.prepare("SELECT count(*) AS n FROM loan").get().n}`); console.log(`notifications sent = ${box.length}, wrong notifications = ${wrong.length}`); console.log(`wrong ones: ${wrong.map((n) => `loan=${n.loanId} book=${n.bookId}`).join(" ")}`); console.log(`stats = ${JSON.stringify(counts)}`); ``` ```sh sh setup.sh node in-transaction.mjs ``` ``` member 1 book 1: issued (loan 1) member 1 book 2: rolled back (CHECK constraint failed: stock >= 0) member 2 book 3: issued (loan 2) member 2 book 4: rolled back (CHECK constraint failed: stock >= 0) loan rows = 2 notifications sent = 4, wrong notifications = 2 wrong ones: loan=2 book=2 loan=3 book=4 stats = {"2025-06-20":4} ``` The database holds two records, four notifications were sent. Two of them went out for loans that never existed: two members received notifications for books that were never issued to them. The stats counted all four too; the daily report shows twice the truth. One of the wrong ones is worth a second look. The identifier in the `loan=2 book=2` line **exists** in the database, but it belongs to book number three. The identifier the rolled-back transaction used was freed, and the next record took it. Checking the notification by identifier alone would have missed this error; the check depends on holding the identifier and the book together. The source of the problem is where the publish sits. When the listener was called the transaction had not yet committed, and whether it ever would was not decided. Sending a notification is not reversible — once a message is gone, it is gone. So the transaction's atomicity covered only the database writes, not the side effects. #### The Outbox The fix is the same arrangement built in the Transactions topic: the event is written to a table inside the same transaction, not directly to the listener. If the transaction commits, the message is there; if it rolls back, it is not. Sending happens in a separate step, after the commit. ```js // outbox.mjs — the event is written to the table in the same transaction, sent to listeners after it commits import { DatabaseSync } from "node:sqlite"; import { EventDispatcher } from "./domain/event-dispatcher.mjs"; import { notificationListener, statsListener } from "./application/listeners.mjs"; const db = new DatabaseSync("library.db"); const box = [], counts = {}; const dispatcher = new EventDispatcher() .subscribe("loan_issued", notificationListener(box)) .subscribe("loan_issued", statsListener(counts)); function issueLoan(memberId, bookId, date) { db.exec("BEGIN"); try { const loanId = Number(db.prepare( "INSERT INTO loan (book_id, member_id, pickup_date, return_date) VALUES (?,?,?,NULL)") .run(bookId, memberId, date).lastInsertRowid); const event = { type: "loan_issued", loanId, memberId, bookId, date }; db.prepare("INSERT INTO outbox (body) VALUES (?)").run(JSON.stringify(event)); db.prepare("UPDATE book SET stock = stock - 1 WHERE book_id = ?").run(bookId); db.exec("COMMIT"); return `issued (loan ${loanId})`; } catch (e) { db.exec("ROLLBACK"); return `rolled back (${e.message.split("\n")[0]})`; } } function drainOutbox() { const pending = db.prepare( "SELECT message_id, body FROM outbox WHERE status = 'pending' ORDER BY message_id").all(); for (const m of pending) { dispatcher.publish(JSON.parse(m.body)); db.prepare("UPDATE outbox SET status = 'sent' WHERE message_id = ?").run(m.message_id); } return pending.length; } const REQUESTS = [[1, 1], [1, 2], [2, 3], [2, 4]]; for (const [memberId, bookId] of REQUESTS) { console.log(`member ${memberId} book ${bookId}: ${issueLoan(memberId, bookId, "2025-06-20")}`); } console.log(`pending in outbox = ${db.prepare( "SELECT count(*) AS n FROM outbox WHERE status = 'pending'").get().n}`); console.log(`messages sent = ${drainOutbox()}`); const wrong = box.filter((n) => db.prepare( "SELECT 1 FROM loan WHERE loan_id = ? AND book_id = ?").get(n.loanId, n.bookId) === undefined); console.log(`loan rows = ${db.prepare("SELECT count(*) AS n FROM loan").get().n}`); console.log(`notifications sent = ${box.length}, wrong notifications = ${wrong.length}`); console.log(`stats = ${JSON.stringify(counts)}`); ``` ```sh sh setup.sh node outbox.mjs ``` ``` member 1 book 1: issued (loan 1) member 1 book 2: rolled back (CHECK constraint failed: stock >= 0) member 2 book 3: issued (loan 2) member 2 book 4: rolled back (CHECK constraint failed: stock >= 0) pending in outbox = 2 messages sent = 2 loan rows = 2 notifications sent = 2, wrong notifications = 0 stats = {"2025-06-20":2} ``` The requests are the same, the outcomes are the same; the wrong notification count dropped from two to zero. The outbox rows for the two rolled-back transactions were rolled back too, so there was no message left to send. The stats now say two as well. What is gained is that the event describes **a committed fact**. An event published inside the transaction is not a fact, it is a prediction; an event in the outbox is the record of something already written to the database. #### A Side Effect Must Not Determine the Main Operation Where the publish sits solves a second problem too. If the listener is called directly, the listener's failure invalidates the loan operation. The script below tries both arrangements for the case where the notification service does not respond for one book. ```js // listener-error.mjs — if the notification send fails, what happens to the loan record import { DatabaseSync } from "node:sqlite"; import { rmSync } from "node:fs"; import { EventDispatcher } from "./domain/event-dispatcher.mjs"; function setup(file) { rmSync(file, { force: true }); const db = new DatabaseSync(file); db.exec(`CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT); CREATE TABLE outbox (message_id INTEGER PRIMARY KEY, body TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'pending');`); return db; } // The notification service fails once, for book 3. const faultyListener = (box, faulty) => (event) => { if (faulty.has(event.bookId)) throw new Error(`notification service did not respond (book ${event.bookId})`); box.push(event.loanId); }; const REQUESTS = [[1, 1], [2, 3], [1, 5]]; function directPublish() { const db = setup("direct.db"); const box = [], faulty = new Set([3]); const d = new EventDispatcher().subscribe("loan_issued", faultyListener(box, faulty)); for (const [memberId, bookId] of REQUESTS) { db.exec("BEGIN"); try { const loanId = Number(db.prepare( "INSERT INTO loan (book_id, member_id, pickup_date, return_date) VALUES (?,?,'2025-06-20',NULL)") .run(bookId, memberId).lastInsertRowid); d.publish({ type: "loan_issued", loanId, memberId, bookId }); db.exec("COMMIT"); } catch { db.exec("ROLLBACK"); } } return { loan: db.prepare("SELECT count(*) AS n FROM loan").get().n, notifications: box.length }; } function withOutbox() { const db = setup("boxed.db"); const box = [], faulty = new Set([3]); const d = new EventDispatcher().subscribe("loan_issued", faultyListener(box, faulty)); for (const [memberId, bookId] of REQUESTS) { db.exec("BEGIN"); const loanId = Number(db.prepare( "INSERT INTO loan (book_id, member_id, pickup_date, return_date) VALUES (?,?,'2025-06-20',NULL)") .run(bookId, memberId).lastInsertRowid); db.prepare("INSERT INTO outbox (body) VALUES (?)") .run(JSON.stringify({ type: "loan_issued", loanId, memberId, bookId })); db.exec("COMMIT"); } const drain = () => { for (const m of db.prepare( "SELECT message_id, body FROM outbox WHERE status = 'pending' ORDER BY message_id").all()) { try { d.publish(JSON.parse(m.body)); db.prepare("UPDATE outbox SET status = 'sent' WHERE message_id = ?").run(m.message_id); } catch { /* the message stays pending, retried on the next round */ } } return db.prepare("SELECT count(*) AS n FROM outbox WHERE status = 'pending'").get().n; }; const firstRound = drain(); faulty.clear(); // notification service came back up const secondRound = drain(); return { loan: db.prepare("SELECT count(*) AS n FROM loan").get().n, notifications: box.length, firstRound, secondRound }; } const a = directPublish(); console.log(`direct publish : loan rows=${a.loan} notifications=${a.notifications}`); const b = withOutbox(); console.log(`outbox : loan rows=${b.loan} notifications=${b.notifications} ` + `pending messages: first round=${b.firstRound} second round=${b.secondRound}`); ``` ```sh node listener-error.mjs ``` ``` direct publish : loan rows=2 notifications=2 outbox : loan rows=3 notifications=3 pending messages: first round=1 second round=0 ``` In direct publish, two of the three requests were recorded. The third book's loan was lost even though nothing was wrong with the book or the member; a rolled-back record was decided by the notification service being unreachable. In the outbox, all three records were written; one message could not be sent on the first round and stayed pending, and was sent on the second round once the service was reachable again. The separation is that persistence and delivery carry different guarantees. Writing the loan record is the transaction's guarantee; sending the notification is work that will happen **eventually**. The condition from the Idempotent Transactions lesson holds here too: because a pending message is retried, a listener can receive the same event twice, and that second delivery must not produce a second effect. #### The Limits of the Event Event publication is not free; it has two traps. The first is **implicit control flow**. Someone reading the loan-issuing function sees the publish line but cannot see what happens next; the subscriptions live in another file. If a rule is genuinely a mandatory part of the workflow — updating the book's status, for example — binding it to an event makes the flow invisible. The test is this: **work that is necessary for the operation's correctness is not handed to a listener.** Work handed to a listener is work whose absence still leaves the loan record valid. The second is **ordering and timing**. In the outbox arrangement the listener runs after the transaction; the data may have changed in the time between. The listener should either be satisfied with the information the event carries, or read the data again and act on the current state. This is why putting decision-relevant information inside the event pays off: because `loanId` and `bookId` were inside the event, the listener could build the notification text without running a query. #### Summary - A domain event describes a fact in the past tense; the publishing side does not know who subscribed. When a third listener was added, the publishing line did not change. - When the event was published inside the transaction, notifications went out for both rolled-back transactions: two records produced four notifications, two of them wrong; the stats counted twice the truth. - Because the rolled-back transaction's identifier was freed, one of the wrong notifications carried an identifier that existed but belonged to another book; the check had to hold the identifier and the book together. - When the event was written to the outbox table in the same transaction and sent after it committed, the wrong notification count dropped to zero. - When the listener was called directly, the notification service's failure rolled back a valid loan record; with the outbox the record was written, the message stayed pending, and it was sent on the next round. #### Next Step The four lessons in this topic isolated the domain layer step by step: separated from infrastructure by the dependency direction rule, from the outer contract by data transfer objects, from the input's shape by the validation split, from side effects by events. One last question remains. The domain rules still take a repository, still run inside a transaction boundary, and still need a database file to be tested. Do the rules themselves need any of that? The course's last lesson writes the business rules as pure functions, tests them without a database, compares the testing time against the same rule's database-coupled version, and shows with a scan that no persistence trace remains in the domain module. ### Persistence-Ignorant Domain Model Source: https://academia.sh/en/courses/data-access-layer/persistence-ignorant-domain-model Writing business rules as pure functions: the decision equality of a database-free version and a database-coupled version, the duration ratio across a 64-state scan, and counting the persistence trace left in the domain module. The four lessons in this topic isolated the domain layer step by step: separated from infrastructure by the dependency direction rule, from the outer contract by data transfer objects, from the input's shape by the validation split, from side effects by events. One last link remains. The rules still take a repository and still need a database to be tested. A repository is a contract; but as long as the rule **calls** it, which queries fire in which order stays part of the rule. This lesson cuts that last link: the rule does not ask for the data itself, the data is handed to it. #### The Rule's Input Is the State Itself In the module below the rule does not take a repository; it takes a plain object that carries all the information needed to make the decision. It matches the definition of a **pure function** set out in the Component-Based Interface Development course: same input gives the same output every time, and it changes nothing outside itself. The lesson's files sit in two directories. ```sh mkdir -p domain coupled ``` ```js // domain/loan-rules.mjs — business rules as pure functions export const MEMBER_LIMIT = 3; export const LOAN_DAYS = 14; export const DAILY_FINE = 2; export function addDays(date, days) { return new Date(Date.parse(date) + days * 86400000).toISOString().slice(0, 10); } // state: { memberExists, memberStatus, memberOpenCount, bookExists, bookOnLoan } export function loanDecision(state, today) { if (!state.memberExists) return { result: "rejected", reason: "member_not_found" }; if (state.memberStatus === "suspended") return { result: "rejected", reason: "member_suspended" }; if (!state.bookExists) return { result: "rejected", reason: "book_not_found" }; if (state.bookOnLoan) return { result: "rejected", reason: "book_on_loan" }; if (state.memberOpenCount >= MEMBER_LIMIT) return { result: "rejected", reason: "member_limit" }; return { result: "accepted", dueDate: addDays(today, LOAN_DAYS) }; } // record: { dueDate, returnDate } export function overdueDays(record, today) { const end = record.returnDate ?? today; return Math.max(0, Math.round((Date.parse(end) - Date.parse(record.dueDate)) / 86400000)); } export function fine(record, today) { return overdueDays(record, today) * DAILY_FINE; } ``` Two details matter. Today's date comes from outside too; the rule does not read the system clock, or its output would depend on when it happens to run. The `state` object is a **snapshot**: gathering the data is the application layer's job, the rule interprets data already gathered. #### Testing Without a Database Testing the rule requires nothing to be set up. ```js // pure.test.mjs — the rules are tested without a database import { test } from "node:test"; import assert from "node:assert/strict"; import { loanDecision, overdueDays, fine, addDays } from "./domain/loan-rules.mjs"; const ELIGIBLE = { memberExists: true, memberStatus: "active", memberOpenCount: 0, bookExists: true, bookOnLoan: false }; const TODAY = "2025-06-20"; test("in the eligible state the loan is issued and the due date is computed", () => { assert.deepEqual(loanDecision(ELIGIBLE, TODAY), { result: "accepted", dueDate: "2025-07-04" }); }); test("a suspended member cannot borrow", () => { assert.equal(loanDecision({ ...ELIGIBLE, memberStatus: "suspended" }, TODAY).reason, "member_suspended"); }); test("a book with an open record cannot be issued again", () => { assert.equal(loanDecision({ ...ELIGIBLE, bookOnLoan: true }, TODAY).reason, "book_on_loan"); }); test("rejected once the member limit is exceeded, accepted below the limit", () => { assert.equal(loanDecision({ ...ELIGIBLE, memberOpenCount: 3 }, TODAY).reason, "member_limit"); assert.equal(loanDecision({ ...ELIGIBLE, memberOpenCount: 2 }, TODAY).result, "accepted"); }); test("rejection reasons are given in priority order", () => { const allBroken = { memberExists: true, memberStatus: "suspended", memberOpenCount: 9, bookExists: false, bookOnLoan: true }; assert.equal(loanDecision(allBroken, TODAY).reason, "member_suspended"); }); test("overdue days and the fine are computed from the return date", () => { const record = { dueDate: "2025-06-15", returnDate: null }; assert.equal(overdueDays(record, TODAY), 5); assert.equal(fine(record, TODAY), 10); assert.equal(fine({ ...record, returnDate: "2025-06-14" }, TODAY), 0); }); test("adding days crosses a month boundary", () => { assert.equal(addDays("2025-06-25", 14), "2025-07-09"); }); ``` ```sh node --test pure.test.mjs ``` ``` ✔ in the eligible state the loan is issued and the due date is computed (1.209292ms) ✔ a suspended member cannot borrow (0.061792ms) ✔ a book with an open record cannot be issued again (0.048ms) ✔ rejected once the member limit is exceeded, accepted below the limit (0.300042ms) ✔ rejection reasons are given in priority order (0.042834ms) ✔ overdue days and the fine are computed from the return date (0.054958ms) ✔ adding days crosses a month boundary (0.032375ms) ℹ tests 7 ℹ suites 0 ℹ pass 7 ℹ fail 0 ℹ cancelled 0 ℹ skipped 0 ℹ todo 0 ℹ duration_ms 33.189166 ``` The duration fields depend on the machine and the load at that moment; what matters is the `pass 7` line. Every test's setup is a single object literal. #### The Same Rule, Coupled to the Database The same rules were also written as a version that pulls the data itself. ```js // coupled/loan-rules.mjs — the same rules, directly coupled to the database import { DatabaseSync } from "node:sqlite"; export const MEMBER_LIMIT = 3; export const LOAN_DAYS = 14; export const DAILY_FINE = 2; export class CoupledRules { constructor(file) { this.db = new DatabaseSync(file); } loanDecision(memberId, bookId, today) { const member = this.db.prepare("SELECT status FROM member WHERE member_id = ?").get(memberId); if (member === undefined) return { result: "rejected", reason: "member_not_found" }; if (member.status === "suspended") return { result: "rejected", reason: "member_suspended" }; const book = this.db.prepare("SELECT book_id FROM book WHERE book_id = ?").get(bookId); if (book === undefined) return { result: "rejected", reason: "book_not_found" }; const open = this.db.prepare( "SELECT loan_id FROM loan WHERE book_id = ? AND return_date IS NULL").get(bookId); if (open !== undefined) return { result: "rejected", reason: "book_on_loan" }; const count = this.db.prepare( "SELECT count(*) AS n FROM loan WHERE member_id = ? AND return_date IS NULL").get(memberId).n; if (count >= MEMBER_LIMIT) return { result: "rejected", reason: "member_limit" }; return { result: "accepted", dueDate: this.db.prepare( "SELECT date(?, ?) AS t").get(today, `+${LOAN_DAYS} days`).t }; } fine(loanId, today) { const l = this.db.prepare( "SELECT due_date, return_date FROM loan WHERE loan_id = ?").get(loanId); const days = this.db.prepare("SELECT max(0, julianday(coalesce(?, ?)) - julianday(?)) AS d") .get(l.return_date, today, l.due_date).d; return Math.round(days) * DAILY_FINE; } } ``` When the same seven tests are written for this version, the setup grows: each test sets up a schema and writes rows. ```js // coupled.test.mjs — same rules; each test sets up the schema and rows first import { test } from "node:test"; import assert from "node:assert/strict"; import { DatabaseSync } from "node:sqlite"; import { rmSync } from "node:fs"; import { CoupledRules } from "./coupled/loan-rules.mjs"; const TODAY = "2025-06-20"; const SCHEMA = ` CREATE TABLE member (member_id INTEGER PRIMARY KEY, name TEXT NOT NULL, status TEXT NOT NULL); CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, due_date TEXT NOT NULL, return_date TEXT);`; let counter = 0; function setup(rows = []) { const file = `test-${counter++}.db`; rmSync(file, { force: true }); const db = new DatabaseSync(file); db.exec(SCHEMA); db.exec(`INSERT INTO member VALUES (1,'Alice Kane','active'),(2,'Ben Ortiz','suspended'); INSERT INTO book VALUES (1,'Blindness'),(2,'The Disconnected'),(3,'The Book of Sand'), (4,'Puslu Kitalar');`); for (const r of rows) { db.prepare(`INSERT INTO loan (book_id, member_id, pickup_date, due_date, return_date) VALUES (?,?,?,?,?)`).run(...r); } return new CoupledRules(file); } test("in the eligible state the loan is issued and the due date is computed", () => { assert.deepEqual(setup().loanDecision(1, 1, TODAY), { result: "accepted", dueDate: "2025-07-04" }); }); test("a suspended member cannot borrow", () => { assert.equal(setup().loanDecision(2, 1, TODAY).reason, "member_suspended"); }); test("a book with an open record cannot be issued again", () => { const r = setup([[1, 2, "2025-06-01", "2025-06-15", null]]); assert.equal(r.loanDecision(1, 1, TODAY).reason, "book_on_loan"); }); test("rejected once the member limit is exceeded, accepted below the limit", () => { const three = [[1, 1, "2025-06-01", "2025-06-15", null], [2, 1, "2025-06-02", "2025-06-16", null], [3, 1, "2025-06-03", "2025-06-17", null]]; assert.equal(setup(three).loanDecision(1, 4, TODAY).reason, "member_limit"); assert.equal(setup(three.slice(0, 2)).loanDecision(1, 4, TODAY).result, "accepted"); }); test("rejection reasons are given in priority order", () => { const r = setup([[1, 2, "2025-06-01", "2025-06-15", null]]); assert.equal(r.loanDecision(2, 9, TODAY).reason, "member_suspended"); }); test("overdue days and the fine are computed from the return date", () => { const r = setup([[1, 1, "2025-06-01", "2025-06-15", null], [1, 1, "2025-06-01", "2025-06-15", "2025-06-14"]]); assert.equal(r.fine(1, TODAY), 10); assert.equal(r.fine(2, TODAY), 0); }); test("adding days crosses a month boundary", () => { assert.equal(setup().loanDecision(1, 1, "2025-06-25").dueDate, "2025-07-09"); }); ``` ```sh node --test coupled.test.mjs ``` ``` ✔ in the eligible state the loan is issued and the due date is computed (2.566458ms) ✔ a suspended member cannot borrow (1.380542ms) ✔ a book with an open record cannot be issued again (1.280917ms) ✔ rejected once the member limit is exceeded, accepted below the limit (3.412958ms) ✔ rejection reasons are given in priority order (1.320708ms) ✔ overdue days and the fine are computed from the return date (1.708833ms) ✔ adding days crosses a month boundary (1.189375ms) ℹ tests 7 ℹ suites 0 ℹ pass 7 ℹ fail 0 ℹ cancelled 0 ℹ skipped 0 ℹ todo 0 ℹ duration_ms 48.288083 ``` The two totals are close, because in both cases most of the time is the runtime starting up. The difference is in the individual tests: the pure tests run under a tenth of a millisecond, the coupled tests between one and three milliseconds. At seven tests this is negligible; the real question is what happens once the count grows. #### Scanning All States The rule's five inputs combine into sixty-four states. The pure version can produce these states directly; the coupled version has to bring the database into each state. The script below runs both, compares the decisions, and measures the duration. ```js // scan.mjs — the same 64 states scanned with both versions: decision equality and duration import { DatabaseSync } from "node:sqlite"; import { rmSync } from "node:fs"; import { loanDecision } from "./domain/loan-rules.mjs"; import { CoupledRules } from "./coupled/loan-rules.mjs"; const TODAY = "2025-06-20"; const STATES = []; for (const memberExists of [true, false]) for (const memberStatus of ["active", "suspended"]) for (const bookExists of [true, false]) for (const bookOnLoan of [true, false]) for (const memberOpenCount of [0, 1, 2, 3]) STATES.push({ memberExists, memberStatus, bookExists, bookOnLoan, memberOpenCount }); rmSync("scan.db", { force: true }); const db = new DatabaseSync("scan.db"); db.exec(`CREATE TABLE member (member_id INTEGER PRIMARY KEY, status TEXT NOT NULL); CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL); CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, due_date TEXT NOT NULL, return_date TEXT);`); const coupled = new CoupledRules("scan.db"); function setupState(s) { db.exec("DELETE FROM loan; DELETE FROM member; DELETE FROM book;"); if (s.memberExists) db.prepare("INSERT INTO member VALUES (1,?)").run(s.memberStatus); if (s.bookExists) db.prepare("INSERT INTO book VALUES (1,'Blindness')").run(); const add = db.prepare(`INSERT INTO loan (book_id, member_id, pickup_date, due_date, return_date) VALUES (?,?,'2025-06-01','2025-06-15',NULL)`); if (s.bookOnLoan) add.run(1, 2); // book 1 held by another member for (let i = 0; i < s.memberOpenCount; i++) add.run(100 + i, 1); } const measure = (task) => { const t = performance.now(); task(); return performance.now() - t; }; let pureDecisions, coupledDecisions; const pureDuration = measure(() => { pureDecisions = STATES.map((s) => loanDecision(s, TODAY)); }); const coupledDuration = measure(() => { coupledDecisions = STATES.map((s) => { setupState(s); return coupled.loanDecision(1, 1, TODAY); }); }); const diverged = STATES.filter((_, i) => JSON.stringify(pureDecisions[i]) !== JSON.stringify(coupledDecisions[i])); console.log(`state count = ${STATES.length}`); console.log(`states where the two versions diverge = ${diverged.length}`); console.log(`pure version : ${pureDuration.toFixed(1)} ms (${(pureDuration / STATES.length).toFixed(4)} ms per state)`); console.log(`coupled version: ${coupledDuration.toFixed(1)} ms (${(coupledDuration / STATES.length).toFixed(4)} ms per state)`); console.log(`ratio = ${(coupledDuration / pureDuration).toFixed(0)}x`); ``` ```sh node scan.mjs ``` ``` state count = 64 states where the two versions diverge = 0 pure version : 0.6 ms (0.0090 ms per state) coupled version: 68.5 ms (1.0707 ms per state) ratio = 119x ``` The first result is about correctness: none of the sixty-four states saw the two versions diverge. The pure version is not a simplification, it is another way of writing the same rule. The second is about duration. The absolute numbers depend on the machine and the file system; a second run of the same script gave a ratio of 131x. What stays constant is the order of magnitude: the per-state cost differs by two orders of magnitude. The practical effect is that the number of states that can be tested is set free. As the rule grows more complex the state count grows combinatorially, and this difference starts to decide which states even get tested. #### Measuring the Persistence Trace The independence claim gets audited by scanning. The count from the Repository Pattern lesson is split into three measures here: SQL statement, table name, and driver import. ```js // trace-scan.mjs — per-module persistence trace: SQL, table name, driver import import { readFileSync } from "node:fs"; const SQL = /\b(SELECT|INSERT INTO|UPDATE|DELETE FROM|CREATE TABLE)\b/g; const TABLE = /\b(?:FROM|INTO|UPDATE|TABLE)\s+([a-z_]+)/g; const DRIVER = /node:sqlite/g; const FILES = ["domain/loan-rules.mjs", "pure.test.mjs", "coupled/loan-rules.mjs", "coupled.test.mjs"]; console.log("file SQL table driver table names"); for (const file of FILES) { const text = readFileSync(file, "utf8"); const sql = (text.match(SQL) ?? []).length; const tables = [...new Set([...text.matchAll(TABLE)].map((m) => m[1]))].sort(); const driver = (text.match(DRIVER) ?? []).length; console.log(`${file.padEnd(28)}${String(sql).padStart(3)} ${String(tables.length).padStart(5)}` + ` ${String(driver).padStart(6)} ${tables.join(", ")}`); } ``` ```sh node trace-scan.mjs ``` ``` file SQL table driver table names domain/loan-rules.mjs 0 0 0 pure.test.mjs 0 0 0 coupled/loan-rules.mjs 7 3 1 book, loan, member coupled.test.mjs 6 3 1 book, loan, member ``` All three measures are zero in the domain module. That the test file is also zero matters in its own right: testing the rule needed no persistence knowledge. In the coupled version, table names have spread into both the rule module and the test file; adding a column to the `loan` table in the schema affects both files at once. The measurement is a blunt tool. A module can be coupled to persistence without writing any SQL: assuming that row order is the order the database returns, or expecting the identifier to be generated by the database, are each a form of dependency too. What the count does is make how far the coupling reaches visible. #### The Limit of the Snapshot The pure rule is not free; it moves the responsibility for gathering the snapshot onto the application layer. It has two traps: pulling fields the decision will not use — the cost measured in the Over-Fetching lesson — and the snapshot going stale, the state read at one moment having changed by the time the write happens. The second is the check-then-write gap from the Validation Layers lesson; the fix is the same: the decision and the write stay in the same transaction boundary, and critical invariants are guaranteed by a database constraint. The opposite end also exists. If the domain model is reduced to a shell that only carries data and makes no decisions, the rules scatter back out to every call site. The test is this: **the code that makes the decision and the code that defines the data the decision rests on should live in the same module.** #### Summary - The rule takes a snapshot that carries the information needed to decide, instead of calling a repository; today's date also comes from outside, so the function stays pure. - Seven tests ran with no database, schema, or connection; every setup was a single object literal. - Across all sixty-four states the pure version and the coupled version gave the same decision; the pure version is not a simplification, it is another way of writing the same rule. - The per-state cost came out two orders of magnitude apart (measured ratios of 119x and 131x); absolute durations depend on the environment, the order of magnitude does not. - SQL statement, table name, and driver import counts are all zero in the domain module and its test; in the coupled version, table names have spread into both the rule file and the test file. #### Course Wrap-Up The course took on data access with four questions and answered each one by measuring it. **Data Access Approaches** asked where the query gets written: the trade-off between a direct query and a mapper, the object–relational impedance mismatch, the repository that gathers decisions in one place, specifications that turn the criterion into an object, the saturation behavior of a connection pool, and the expand–contract migration. **Transactions and Consistency** asked where the unit of work's boundary sits: keeping the boundary in the service layer, the effect isolation levels have as seen from the application, the trial-and-duration difference between optimistic and pessimistic locking, atomicity's inability to cross a service boundary, and the idempotent forms that make retrying safe. **Performance Problems** asked what the layer's operation costs: the N+1 query problem, over-fetching, batch operations, the query plan as seen from the application, and the cost of deep pagination. **Business Logic Placement** asked where the rule should stand. The dependency direction was audited through the import graph, the outer contract was separated from the inner model, input validation and the domain rule were split apart by the response and the cost each produces, side effects were bound to events, and the rules were cut loose from persistence. One assumption is left standing where the course leaves off: that every request goes to the source of the data, and that every task finishes inside the request. Neither always holds. If the same query's answer gets produced over and over, it can be cached; work like sending a notification, generating a report, or processing a file can run without making the request wait for its response. The outbox in the Domain Events lesson was the first step toward this. The next course, **Caching, Queues and Asynchronous Processing**, takes on these two paths: caching layers and invalidation strategies, the difference between a message queue and a stream, and building background jobs that hold up under retries and a dead-letter queue. The concepts measured in this course reappear there: the transaction boundary, idempotence, and persistence-ignorant rules are the precondition for asynchronous processing.