Every time node --watch restarts the server, the coffees go back to being two and order ord_5001 comes back to life as pending_payment. The in-memory arrays have served us well for building the contract without distractions, but they are not a database: they do not survive a restart, they cannot guarantee that deducting stock across three items happens atomically, and they do not scale beyond a few thousand records. Today we replace them with SQLite via better-sqlite3 behind the repository pattern, and we will do it while keeping the promise we made in 03-01: not a single line of the controllers or the services is touched. Along the way we will see Aroma Store's SQL schema with money stored in whole cents, versioned migrations, prepared statements and why they eliminate SQL injection, transactions for creating an order, optimistic concurrency control, the N+1 problem and cursor pagination implemented for real.

1. What changes in the project today
2. The repository pattern
3. Which database, and why SQLite in this course
4. better-sqlite3: synchronous and free of surprises
5. Aroma Store's SQL schema
6. snake_case in the database and camelCase in the JSON
7. Migrations: what they are and why they are versioned
8. The migration script and the seed data
9. The connection: src/config/database.js
10. src/repositories/coffees-sqlite.js with prepared statements
11. Why prepared statements prevent SQL injection
12. Safe dynamic queries: filters, sorting and pagination
13. Transactions: creating an order while deducting stock
14. Optimistic concurrency and version_conflict
15. The N+1 problem when loading the items
16. Cursor pagination for real
17. ORMs: when they pay off
18. Connections and graceful shutdown

The fact that the list of modifications is so short is the outcome we were after. If the controllers had talked directly to the array, today we would be touching twenty files.

A repository is an object that offers operations over a collection of entities and completely hides where and how they are stored. The interface we have been using since 03-02:

The three properties that make it worthwhile:

• Substitutability. coffees-memory.js and coffees-sqlite.js honour the same interface. Switching from one to the other means changing one import, and in 03-08 we will use precisely the in-memory one as a test double.
• Localised SQL. All the coffee SQL is in one file. When a query runs slowly, you know where to look; when a column changes, you know what to review.
• Domain vocabulary. The service asks for findById('cof_001'), it does not run a SELECT. The business logic reads without technical noise.

And the limit of the pattern, said honestly: it does not make databases magically interchangeable. A query using SQLite's JSON_EXTRACT does not work the same way in MongoDB. What the repository guarantees is that, if it has to be rewritten, it is rewritten in one place.

Why SQLite in the course: because there is nothing to install or configure, the database is a file you can delete and regenerate in a second, and the SQL we will write is standard SQL, so what you learn transfers. What you learn about prepared statements, transactions, indexes and N+1 applies exactly the same in PostgreSQL.

What would change with PostgreSQL. Little in the design and quite a lot in the mechanics: you would use the pg package with a connection pool, the calls would be asynchronous (await client.query(...)), the placeholders would be $1, $2 instead of ?, and transactions would be written with explicit BEGIN/COMMIT on a connection reserved from the pool. The types would gain precision: TIMESTAMPTZ for dates, NUMERIC available for money, JSONB with indexes for the tasting notes. The structure of the repository, however, would be the same; that is why starting with SQLite is not a dead end.

What would change with MongoDB. More: joins disappear, an order would store its items embedded in the document itself —which, curiously, eliminates the N+1 problem of section 15 at the root— and multi-document transactions exist but are used far less. It is a reasonable choice for catalogues with highly variable attributes; for orders, with their referential integrity and their totals, the relational model fits better.

The oddity of better-sqlite3 is that its API is synchronous: db.prepare(sql).get(id) returns the row directly, with no await. In an ecosystem where everything touching input/output is asynchronous, this feels wrong. And it is correct:

• SQLite is not a server: there is no network and no latency. The query is a file read, often from the operating system's cache, taking microseconds.
• Wrapping that operation in a promise would add more overhead than the query itself.
• The resulting code is simpler and transactions are trivial: with no await in the middle, there is no risk of another request slipping into the middle of a transaction.

The trade-off: if a query takes 200 ms, it blocks the event loop and no other request makes progress during that time. The practical consequence is that queries must be kept fast —with indexes, without full scans— and heavy reports must not run in the same process that serves the API. With PostgreSQL and pg, everything would be asynchronous and this problem would not exist.

Our services have been written synchronously since 03-03, so the migration is straightforward. If tomorrow we moved to PostgreSQL, we would have to turn the repository's, the service's and the controller's methods into async —and that is where the asyncHandler() wrapper we will see in 03-07 becomes essential.

Schema decisions worth justifying one by one:

price_cents INTEGER. It is the decision of 02-05 carried into the database. A REAL (floating point) would be a design error: SELECT SUM(price) FROM ... would accumulate rounding errors and the order total could differ by cents from the one shown to the customer. With integers, the sum is exact by definition. In PostgreSQL you could use NUMERIC(10,2), which is also exact; the integer-cents approach works in any engine.

Text primary keys (cof_001). It breaks the habit of INTEGER AUTOINCREMENT, and in exchange it gives us what we decided in 02-02: opaque identifiers that say what type they are and that do not leak how many records exist. The cost is a slightly larger index and string comparisons instead of integer ones, which is irrelevant at this scale.

CHECK on the enums. Zod's validation (03-04) already rejects an invalid roast, but the CHECK protects against every way in: an import script, a manual fix with sqlite3, a future bug. It is defence in depth, and it does not replace validation at the edge, it complements it: a CHECK produces a database error, not a friendly 400.

tasting_notes as JSON text. SQLite has no array type. We store '["citrus","floral"]' and parse it when reading. It is acceptable because we never filter by a specific note with SQL; ?q= searches inside the text. If tomorrow we needed to filter by note, the right answer would be a coffee_notes table with one row per note. In PostgreSQL we would use JSONB with a GIN index and there would be no debate.

active INTEGER. SQLite has no boolean type: 1 and 0 are used. And there is an important practical detail: better-sqlite3 rejects JavaScript booleans as parameters; you must pass 1 or 0 explicitly. It is a mistake that surprises everyone the first time.

Dates as TEXT in ISO-8601 UTC. SQLite has no date type either. The ISO-8601 format has an extremely valuable property: alphabetical order coincides with chronological order, so ORDER BY created_at DESC works correctly over text. That is exactly what makes the cursor pagination of section 16 possible.

version INTEGER. It is the counter for the optimistic concurrency control of section 14.

We have three vocabularies and you need to know where each translation happens:

And the two boundaries:

• DB → internal model: in the repository, inside a toModel(row) function.
• Internal model → JSON: in the mapper of 03-03 (coffeeToRepresentation).

We could have saved ourselves the first translation by using camelCase for the columns too. We do not, because snake_case is the universal convention in SQL —tools, dumps and DBAs expect it— and because in PostgreSQL identifiers with uppercase letters have to be quoted in every query. Having the translation live in a single repository function makes the cost negligible.

A migration is a schema change written as a SQL file: numbered, immutable and versioned in Git.

The problem it solves is this: you create the coffees table on your laptop by running SQL by hand. Your colleague does not have it. The test server has an old version without the version column. Production has yet another one. Nobody knows which schema is where, and applying a change becomes a manual ritual with minutes taken.

The rules of migrations, which admit no exceptions:

1. Numbered and ordered: 001-initial.sql, 002-add-discounts.sql.
2. Immutable: an applied migration is never edited. If it was wrong, you fix it with a new one.
3. Versioned in Git, alongside the code that needs them.
4. Recorded: the database itself keeps track of which ones have been applied.

That fourth rule requires one more table, which we add at the start of the migrations file:

And the seeding, which loads the data we work with throughout the course:

With the scripts in package.json:

Seeding and migration are different things. A migration changes the structure and runs in every environment, production included. Seeding inserts data and only makes sense in development and testing. Mixing them —an INSERT inside a migration— is a classic mistake that ends up putting test coffees into production.

The foreign_keys = ON line is the one that prevents the most grief: without it, SQLite happily accepts an order item pointing at a nonexistent coffee and the problem surfaces months later as orphaned data.

About generating identifiers: nextNumber computes the existing maximum and adds one. It works because SQLite serialises writes, and we will wrap it in the same transaction as the insert. In PostgreSQL we would use a SEQUENCE, and in a distributed system the usual answer would be a ULID or a prefixed UUID (cof_01HQ...), generated without querying anything and revealing nothing about business volume.

Compare these two ways of searching by origin:

With the first one, if the client sends ?origin=x' OR '1'='1, the SQL that runs is:

It returns the entire catalogue. And with a bit more imagination —x'; DROP TABLE orders; --— it destroys data. That technique has topped the security risk lists for twenty years and it still works in real applications.

Why the ? version is immune. It is not that it "escapes the quotes": it is that the SQL and the data travel along separate paths. The statement is parsed and compiled before the values are known, producing an execution plan with holes in it. When it runs, each value is dropped into its hole as data, already inside the engine, without going through the parser again. By definition, a value cannot become an instruction: x' OR '1'='1 is looked up literally as an origin, finds nothing, and returns zero rows. The engine does not see a special quote; it sees a twenty-character string.

Three practical consequences:

• Placeholders for every value. Always. Even if the data "comes from inside", even if it is a number, even if you are sure.
• Placeholders only work for values, not for table names, column names or ASC/DESC. Those are handled with allow-lists, as we will see right now.
• The validation of 03-04 does not replace this. It is defence in depth: validation filters shapes, prepared statements make the attack impossible.

findAll() has to combine optional filters. The technique is to build the list of conditions and the list of parameters at the same time, never concatenating a value:

Three critical points:

The total comes from a separate query with the same WHERE. It cannot be obtained from the paginated query, because LIMIT trims it. And it must carry exactly the same filters, or the client will miscalculate the number of pages. It is one extra COUNT(*) per request, and it is the price of offset pagination.

ORDER BY is built by concatenation, but only with values from SORTABLE_COLUMNS. The name the client sends is used as a lookup key in the map, never as SQL text. If it is not in the map, there is no column. It is impossible to inject anything through there.

The LIKE wildcard goes in the parameter. '%' || ? || '%' would also be valid, but putting the complete pattern in the parameter is clearer. What can never be done is LIKE '%${q}%'.

A performance note: LIKE '%text%' with a leading wildcard cannot use an index and forces a full table scan. With 200 coffees it is irrelevant; with 200,000 records you would need a full-text index (FTS5 in SQLite, tsvector in PostgreSQL) or a dedicated search engine. It is a known and accepted limitation of the ?q= we promised in 02-06.

Finally, the implementation selector:

And in src/services/coffees.js one line changes:

There is the promise kept. Restart, try curl -s http://localhost:3000/v1/coffees | jq .total and check that everything answers the same as before… and that it now survives restarts.

Creating an order is not one operation: it is several that must all happen or none at all.

1. Check that each coffee exists and has stock.
2. Insert the order row.
3. Insert one item per coffee.
4. Deduct the stock of each coffee.

Without a transaction, a failure at step 4 —because the stock no longer suffices, for instance— would leave a chargeable order with the inventory untouched. That is data corruption.

A transaction guarantees the four ACID properties; the one that matters here is atomicity: either every change is applied, or none is.

What happens if it fails halfway. Suppose the second item runs out of stock because another customer got in 30 milliseconds earlier. The exception propagates, better-sqlite3 runs ROLLBACK and the state of the database goes back exactly to what it was: there is no order, there are no items, and the stock of the first coffee has not been deducted. The service translates that error into 409 insufficient_stock and the client can retry. Without a transaction we would have an incomplete order, an orphaned item and stock deducted for an order that never existed.

Why the stock is checked twice. The first read (coffeeForOrder) is there to give a useful error message with the coffee's name and the units left. The second one, the AND stock >= ? condition inside the UPDATE, is what genuinely protects: it is a check and a write in a single atomic statement, so there is no window between reading and writing. This pattern —check in the WHERE of the update and inspect changes— is the correct way of stopping two simultaneous requests from selling the same last bag of coffee.

One better-sqlite3 detail that must be respected: there can be no await inside a transaction. Since its API is synchronous, we do not need one; but if you mixed an asynchronous call in there, the transaction would close early. With PostgreSQL, by contrast, the whole block would be async and you would have to reserve a connection from the pool for the entire transaction.

Another concurrency problem, distinct from stock: the lost update.

Without any control, the second PATCH would trample the new price with the one B read a minute ago, and nobody would notice. Optimistic locking detects it: every update demands the version the client read.

And the service translates that null into the catalogue's 409 version_conflict:

Two clarifications about the catalogue of 02-04. There, version_conflict was associated with 412 Precondition Failed, which is the right code when the client sends the condition in the If-Match header with an ETag —the standard HTTP mechanism, which we will see alongside conditional caching in 04-06. Here the condition travels as a version field in the body, with no conditional header, and then the appropriate code is 409 Conflict: the request was valid but it clashes with the resource's current state. Same problem, same catalogue code, two HTTP codes depending on how the condition is expressed. And a nuance about the word "optimistic": it is called that because it locks nothing; it assumes conflicts are rare and simply detects them. Pessimistic locking (SELECT ... FOR UPDATE) locks the row, and only pays off when conflicts are frequent.

This is how not to load orders together with their items:

With 20 orders that is 21 queries. The code looks innocent because the query is hidden inside the loop, and that is why the N+1 problem is so common. In SQLite, with the database on the local disk, the cost is small; with PostgreSQL on another machine and 2 ms of network per query, those 21 round trips are 42 ms of pure latency to return one page. And if the list were 100 orders, 200 ms.

The solution: a second query for all the items at once.

Two queries, whatever the number of orders. The alternative would be a single JOIN, which also works but returns the order repeated as many times as it has items and forces you to deduplicate. With two queries the code is clearer and the performance is equivalent.

A warning: IN (...) has a parameter limit (999 by default in SQLite). Since our pages are 100 items at most, it does not affect us; in a batch process you would have to chunk them.

In 02-06 we decided that /v1/orders would use a cursor, and now you can see why. With OFFSET, the engine has to walk through and discard every preceding row:

This is deep paging: page 1 is instantaneous and page 5,000 takes seconds. And it is also unstable: if a new order comes in while the client is paginating, everything shifts by one position and they see a repeated order.

The cursor solves both problems by remembering the last item seen instead of a position number:

Tuple comparison (a, b) < (x, y) is standard SQL and means "a < x, or a = x and b < y". It is exactly the tie-break we needed, expressed as a single condition.

Three decisions to underline. The cursor is opaque: base64 of an internal JSON object. It is not encrypted —anyone can decode it— but it is explicitly "none of your business": the contract of 02-06 forbids building it by hand, and that lets us change its contents without breaking anyone. Asking for limit + 1 rows is the standard trick for knowing whether there is more without an extra COUNT. And cursor pagination offers neither a total nor a jump to the last page: that is the price you pay, which is why the contract reserved the cursor for /v1/orders —where the history is huge and you only ever move forward— and left offset pagination on /v1/coffees, where the catalogue is small and knowing the total matters.

The index idx_orders_customer_date (customer_id, created_at DESC, id DESC) was built for exactly this query: the engine locates the cut-off point with a tree lookup and reads 21 consecutive rows. The cost does not depend on how far away the page is.

In favour: less boilerplate, built-in migrations, typed results, portability between engines, and relations loaded without writing the JOIN yourself.

Against: one more abstraction to learn and debug; generated queries that surprise you; the N+1 problem hidden —an .items that looks like a property access and in reality fires a query—; and difficulty expressing advanced SQL.

The criterion: if your application has many entities with similar relationships and repetitive CRUD, an ORM saves weeks. If it has few entities and demanding queries, direct SQL behind a repository —what we have done— is simpler and faster. And with the repository pattern, the decision is reversible: adopting Prisma tomorrow would mean rewriting the files in src/repositories/, and nothing else.

SQLite is a file and needs no pool: one single connection per process, opened at start-up. WAL mode allows concurrent reads alongside a write, and busy_timeout handles collisions between writers.

With PostgreSQL a pool would be necessary: opening a TCP connection costs tens of milliseconds, so a reusable set is kept alive (new Pool({ max: 10 })). That brings its own problems —pool exhaustion from connections that are never returned, transactions that must run on a reserved connection— which we do not have here.

What we do have to do is close the database on shutdown. We update src/server.js:

better-sqlite3's close() flushes the WAL into the main file and releases the lock. Without it, an abrupt shutdown leaves stray -wal and -shm files that SQLite knows how to recover, but there is no reason to depend on that.

1. Concatenating values into SQL. Guaranteed injection. Placeholders always, with no exceptions and no "but this value comes from inside".

2. Forgetting PRAGMA foreign_keys = ON. SQLite ignores them by default and you end up with items pointing at nonexistent orders.

3. Passing a JavaScript boolean as a parameter. better-sqlite3 rejects it. Convert to 1 / 0.

4. Storing money in REAL. The most expensive mistake on the list. INTEGER cents, or NUMERIC where it exists.

5. Editing an already-applied migration. Your database updates, your colleagues' do not, and the migrations record lies. Always fix with a new migration.

6. Mixing seeding and migration. The test data ends up in production.

7. Querying inside a loop. That is N+1. If you see a prepare inside a for, stop: there is almost always a version with IN (...).

8. Calculating the total from the paginated query. The COUNT goes with the same filters and without LIMIT.

9. Preparing statements inside the handler. Calling db.prepare() on every request wastes the SQL parsing. Prepare when the module loads and reuse.

Tip: when a query runs slowly, ask for the plan before touching anything: EXPLAIN QUERY PLAN SELECT .... If you see SCAN TABLE, an index is missing; if you see SEARCH TABLE ... USING INDEX, it is fine.

Write the migration 002-add-discounts.sql, adding a discount_percent column to coffees (integer, between 0 and 50, defaulting to 0). Explain why this migration is a backward-compatible change for the API's consumers (02-07) and what else would be needed for the field to appear in the JSON.

Implement findReviewsForCoffee(coffeeId, { status, limit, offset }) in a new src/repositories/reviews-sqlite.js. It must return { items, total }, filter by status if one is given, sort by created_at DESC with a tie-break on id, and not return reviews for a nonexistent coffee without distinguishing that case from a coffee with no reviews. State which index in the schema supports it.

Two employees open the record of cof_001 (version: 7) at the same time. The first changes the price to €15.90 and the second, thirty seconds later, changes the stock to 200 using the version 7 they read. Describe step by step what happens with updateCoffeeWithVersion, what the API answers the second one, and compare it with what would happen without version control. Then explain why this mechanism would not work for the order's stock deduction and what is used instead.

Solution 1

Why it is backward compatible: the column has DEFAULT 0 and NOT NULL, so existing rows fill themselves in and no previous insert stops working. From the API's point of view, adding a field to a representation is an additive change (02-07): a consumer that ignores discountPercent carries on working exactly as before, because a well-written JSON client ignores fields it does not know. No v2 would be needed.

What is missing for it to appear in the JSON: three things, and none of them in the database. Add discountPercent: row.discount_percent in the repository's toModel(); add it to coffeeToRepresentation() in the mapper; and add it to openapi.yaml with its description. That exactly those three places are needed, and that they are always the same three, is the sign that the layered architecture is properly in place.

Solution 2

The index that supports it: idx_reviews_coffee ON reviews (coffee_id, status). It covers the mandatory filter on coffee_id and, when status is added, that one too. The created_at DESC sort is not covered, so the engine sorts that coffee's subset of reviews in memory —acceptable, because there are few of them. If a coffee ever had thousands, the ideal index would be (coffee_id, status, created_at DESC, id DESC), which would serve both the filter and the sort at once. Check it with EXPLAIN QUERY PLAN.

Solution 3

Step by step:

1. Both read cof_001 with version: 7.
2. The first sends their change with version: 7. The UPDATE ... WHERE id = 'cof_001' AND version = 7 finds the row, applies the price and raises the version to 8. changes === 1, and the API answers 200 with the updated representation.
3. The second sends their change with version: 7. The WHERE ... AND version = 7 finds no row, because it is now 8. changes === 0 and the coffee does exist → conflict.
4. The API answers 409 version_conflict with the message asking them to reload and repeat.

Without version control: the second UPDATE would have written its complete copy of the coffee, including the price_cents of 1450 that it read half a minute earlier. The first employee's price change disappears without a trace: no error, no log, no clue. That is the lost update, and its seriousness lies in being silent: the first employee sees their 200 OK, walks away satisfied, and the old price reappears.

Why it does not work for the order's stock. They are two different problems. Optimistic locking requires the client to have read the resource and to return its version, and it protects a complete replacement of the resource. The stock deduction, on the other hand, is a relative operation —"subtract 2 from whatever is there"— on something nobody has read beforehand: the buyer does not send the coffee's version and has no reason to know it. If they did, two simultaneous purchases of different coffees from the same order would produce 409 constantly and the shop would be unusable.

What is used instead is the condition inside the UPDATE itself:

Here the read and the write happen in the same atomic statement, so there is no window between checking and updating. If two requests try to buy the last bag, one gets changes === 1 and the other changes === 0, and the latter receives 409 insufficient_stock. The general rule: optimistic locking for absolute updates; a condition in the WHERE for relative ones.

Aroma Store's data now lives in a real database, and the change cost three import lines outside the repositories/ folder. That is what the repository pattern buys you: the rest of the application never knew where the coffees were. The SQL schema translates the contract's decisions into structure —an integer price_cents because money is never floating point, prefixed text primary keys, CHECK on the enums as defence in depth, ISO-8601 dates whose alphabetical order is chronological, and indexes placed exactly where 02-06 said there would be filters and sorting. Numbered, immutable migrations turn "the schema" into something that gets deployed, reviewed in a pull request and reproduced on any machine with npm run migrate.

And you have seen the four problems that separate a naive data layer from a professional one. Prepared statements, which do not escape quotes but send the SQL and the data along different paths, with an allow-list for the one thing that admits no placeholders, the ORDER BY. Transactions, which make creating an order while deducting stock across several items an all-or-nothing affair, with the AND stock >= ? condition in the UPDATE as the real protection against two simultaneous buyers. Optimistic concurrency control with version, which turns a silent lost update into a visible 409 version_conflict. And N+1, together with cursor pagination that makes page five thousand cost the same as the first.

One door is still wide open: anyone can create, modify and delete coffees, and GET /v1/orders returns every customer's orders to whoever asks. In 03-06, Authentication and Authorisation, we close it: we will distinguish authentication from authorisation —the 401 from the 403 that the contract has separated since 02-04—, register customers with their password protected by bcrypt, issue and verify JWTs signed with the secret that already lives in .env, write the middleware that tells a missing token from an expired one and returns WWW-Authenticate, and apply the roles customer, employee, administrator and partner with a permission matrix per endpoint and resource-level ownership checks.