Atomic multi-record writes

Every benchmark so far has been about writing one record per request. POST /users writes one user. POST /orders writes one order. That is the easy case. Real applications are rarely that simple.

Think about placing an order. When a customer clicks “buy,” the system needs to create an orders record and decrement the inventory count for each product they bought. Those two writes have to succeed together or fail together. If the order is created but inventory never gets decremented, you oversell. If inventory is decremented but the order is never created, the customer has been charged for a product they will not receive.

This lesson is about why flat files cannot solve this problem and what databases give you that flat files cannot. It is also, genuinely, the single biggest reason databases exist.

The naive attempt

Let us try to do this with flat files and see where it breaks. Imagine we have orders.jsonl and products.jsonl on disk, with in-memory maps for each.

export function placeOrder(userId: string, productId: string, quantity: number): Order {
  // 1. Look up the product to check inventory
  const product = products.get(productId);
  if (!product || product.stock < quantity) {
    throw new Error("insufficient stock");
  }

  // 2. Decrement inventory
  product.stock -= quantity;
  appendFileSync(PRODUCTS_FILE, JSON.stringify(product) + "\n");
  products.set(product.id, product);

  // 3. Create the order
  const order: Order = {
    id: randomUUID(),
    userId,
    productId,
    quantity,
    created_at: new Date().toISOString(),
  };
  appendFileSync(ORDERS_FILE, JSON.stringify(order) + "\n");
  orders.set(order.id, order);

  return order;
}

Looks fine, right? Read the product, check stock, decrement, write both records. Return the order.

It is full of bugs. Let us go through them.

Bug 1: the process crashes between steps 2 and 3. We have written the new product record (with decremented stock) to disk. We have not written the order. The process dies. On restart, the product’s stock is permanently decremented but no order exists to justify it. The customer who triggered this will get nothing. You have “sold” a unit of inventory you will never ship.

Bug 2: two server processes running this code at the same time. Inside one Node process, this function runs to completion before another request can start, so it is safe against itself. The moment you have two processes (for redundancy, rolling deploys, autoscaling), both can read product.stock = 5 at the same moment. Both pass the check. Both decrement to 4. Both append to the file. You have just sold two units of stock you only had one of, at the same nominal time.

Bug 3: disk failure after the products file was synced but before the orders file was synced. Same end state as bug 1, but now triggered by hardware rather than a crash. The product was decremented. The order was not recorded.

You can patch each of these. You can write a “pending” record before doing either operation, then a “committed” record after both succeed, and replay pending records on startup. You can introduce a global coordinator process (now you have a single point of failure) or inter-process file locks (now you have inter-process locking, which has its own failure modes). You can fsync between every write and take the latency hit.

Do all of that and you have written a transaction system. Probably a buggy one.

What a transaction gives you

Here is the same thing in SQLite. The database does almost all of the work for us.

const selectProduct = db.prepare("SELECT id, stock FROM products WHERE id = ?");
const updateStock = db.prepare("UPDATE products SET stock = stock - ? WHERE id = ?");
const insertOrder = db.prepare(
  "INSERT INTO orders (id, user_id, product_id, quantity, created_at) VALUES (?, ?, ?, ?, ?)",
);

function placeOrder(userId: string, productId: string, quantity: number) {
  db.exec("BEGIN");
  try {
    const product = selectProduct.get(productId) as { id: string; stock: number } | undefined;

    if (!product || product.stock < quantity) {
      throw new Error("insufficient stock");
    }

    updateStock.run(quantity, productId);
    insertOrder.run(randomUUID(), userId, productId, quantity, new Date().toISOString());

    db.exec("COMMIT");
  } catch (err) {
    db.exec("ROLLBACK");
    throw err;
  }
}

// Later, in your handler:
placeOrder(userId, productId, quantity);

Walk through it. At the top we prepare three statements once, outside any function, because preparing is not free and we want to reuse them.

Inside placeOrder, db.exec("BEGIN") opens a transaction. From this moment until either COMMIT or ROLLBACK, every statement we run is part of one atomic unit. The database is tracking those changes, but has not yet applied them to the version of the database that other readers can see.

We check the stock. If insufficient, we throw. The catch block catches it, rolls back (undoing anything the transaction did up to that point), and re-throws so the caller knows about the failure.

We run the UPDATE and the INSERT. These modify the transaction’s pending state.

We COMMIT. At this point, SQLite writes the whole batch atomically, fsyncs the WAL, and makes the changes visible to everyone. The moment COMMIT returns, the change is durable.

If anything throws, the catch runs ROLLBACK and all the pending changes are discarded as if they never happened. No partial write. No inventory decremented without an order. No order without an inventory decrement.

node:sqlite does not ship a db.transaction(fn) wrapper like some other SQLite clients do, which is why we wrap BEGIN / COMMIT / ROLLBACK manually. The mechanics are the same.

The four guarantees behind the magic word

When people say “transactions” they almost always mean four specific guarantees. The acronym is ACID.

Atomicity. Either every statement inside the transaction commits, or none of them do. There is no halfway. If anything throws, SQLite rolls back the entire transaction and the database looks like nothing happened.

Consistency. The database moves from one valid state to another valid state. If you have a constraint like CHECK (stock >= 0), an UPDATE that would leave stock negative fails, and the whole transaction rolls back with it. You never see an invalid state, even transiently.

Isolation. Concurrent transactions do not see each other’s in-progress state. If two customers try to buy the last unit of inventory at the same instant, SQLite serializes them. The first one gets it; the second one sees the updated stock and fails cleanly. You do not both succeed and end up with negative inventory.

Durability. Once COMMIT returns, the data is on disk for real. SQLite fsyncs the WAL at commit time. If the power dies one nanosecond after commit returns, the next process start will see the committed state. No silent loss.

Those four properties are what database engineers refer to as “non-negotiable for serious data.” They sound abstract until you have tried to implement them yourself and spent a weekend chasing a recovery bug that only triggers under a specific sequence of crashes. Then they sound like a gift.

The cost of doing this yourself

Let us entertain the idea that you want to stay on flat files anyway. What would it take to implement these four guarantees?

You would need:

• A write-ahead log (WAL). Every operation is first recorded in a dedicated file, with all its parts described. (“Decrement product X by N. Create order Y.”) Only after that log entry is durably on disk do you apply the operation to the main data files.
• A commit marker. A separate “this transaction is complete” record you write to the WAL after all the operations are recorded. Recovery code on startup replays the WAL up to the last commit marker. Anything after that, from an incomplete transaction, gets discarded.
• Locking. Either a global mutex (simple, only one transaction at a time, slow under load) or per-record locks (concurrent transactions on different data, much harder to get right).
• Recovery logic. On startup, read the WAL, classify each entry as committed or incomplete, replay the committed ones, discard the rest.
• WAL rotation. The log cannot grow forever. You have to truncate it once its operations have been safely applied to the main data files, which itself requires careful coordination.

This is half a database. It is also a security and correctness nightmare to get right. The recovery logic alone has subtle bugs that only show up in specific crash sequences, which are exactly the sequences you cannot easily test for.

The honest answer to “should I implement transactions on flat files” is: no. If you need transactions, use a database. SQLite ships with Node via node:sqlite. You are one import away from correct.

Patterns that get you partway

There are a handful of patterns that give you a bit of what transactions offer without actually requiring a database. They are worth knowing because real production systems lean on all of them, even inside an app that uses a database.

Idempotent writes. Design every operation so running it twice has the same effect as running it once. setStock(productId, 5) is idempotent. decrementStock(productId, 1) is not. When you can make your writes idempotent, retries on failure are safe, and you need far less coordination.

Event sourcing. Instead of writing the new state, write an event that describes the change. OrderPlaced { userId, productId, quantity } is one event. The current state is the projection of all the events in order. Recovery is replaying the event log. This is what systems like Kafka-backed architectures and EventStore are built around. Your users.jsonl is a very simple version of this for inserts.

The outbox pattern. When a single operation needs to update the database and send a message (email, webhook, queue message), put the message into an outbox table as part of the same transaction. A separate worker reads the outbox and dispatches. If the dispatcher crashes, the message is still there, and the database still records both the state change and the pending message together. You avoid the classic “we wrote to the database but the email failed” problem.

Compensating transactions. If you cannot have a real transaction (maybe the two things you are coordinating are on different systems), design every operation to have an explicit inverse. If inventory was decremented but the order failed, run a compensating “increment inventory” operation. This is the core idea behind Saga patterns in distributed systems. Hard to get right at scale, but sometimes the only option.

These are tools that real systems use alongside database transactions, not instead of them. They extend transactional thinking across systems that do not share a single database.

When you can ignore all this

If your app only ever writes one record per request, and that record is independent of every other record, you do not need transactions. POST /users creates a user. There is no second record to coordinate with. A flat file is fine.

The moment you have a relationship, where “this update only makes sense if that other update also happens,” you need transactions. And transactions, like indexes and joins, are exactly what databases give you.

Back in section 1 we listed the structural reasons you eventually need a real database, and one of those reasons was atomic multi-record writes. Now we have a name for it. Not “two writes that happen around the same time,” but the four specific ACID guarantees that databases have been refining for fifty years.

What this section taught

Section 1 was about reads. Section 2 was about writes. Together they tell the same story from two angles.

Reads. Flat files are fast for the narrow case where all you need is primary-key lookup. The moment you need anything else (secondary queries, joins, range scans), you pay an enormous engineering cost to build it yourself. SQLite gives you all of it for a small throughput cost on the simple case, under 1.5x versus an in-memory map.

Writes. Flat files are fast for the narrow case where you write one record at a time, accept eventual durability, and run exactly one writer process. The moment you need atomic multi-record writes, multiple writer processes, or guaranteed durability, you have to build a transaction system. SQLite gives you a real one for free.

The pattern is consistent across both. Flat files are amazing for the narrow case. Databases are slightly slower on the narrow case and vastly cheaper on everything else. The question is never “database or no database.” The question is: am I still inside the narrow case, or have I drifted out of it without noticing?

Where to go next

If you want to go deeper, here are four places worth your time.

• Designing Data-Intensive Applications by Martin Kleppmann is the textbook on storage engines, transactions, and distributed systems. Long, but the best thing you can read after this course.
• The SQLite documentation is genuinely good, and short. The pages on WAL mode, isolation, and the file format will sharpen everything you learned here.
• The Postgres documentation on MVCC explains how a database can handle hundreds of concurrent writers without locking, which is the main contrast with SQLite and worth knowing.
• The LSM-tree paper if you want to see how RocksDB and Cassandra think about storage under the hood.

You now have the vocabulary and the working models to read those resources critically. You can see when a database is making different tradeoffs from another, and whether those tradeoffs are the right ones for what you are building.

That was the whole point of this course. The next time someone asks “what database should I use,” your first questions are not “Postgres or MySQL.” They are: how many users? At what request rate per user? Read-heavy or write-heavy? Single machine or distributed? Do we need atomic multi-record writes? You now know what each answer implies, and what it costs.

That is the whole course.