Tracking Clicks

Lost update. What read-modify-write does wrong under load: two concurrent clicks both read 5, both compute 6, and both write 6 — so two hits move the counter by one and one click vanishes.

Why Do the Math in SQL

The whole chapter turns on this one design choice, so it's worth settling before we write a test. Two redirects for the same code arrive at once, both starting from clicks = 5. With read-modify-write, both SELECT and see 5, both add 1 in their own copy of the value, and both write 6 back — the second write silently clobbers the first, and the counter lands on 6 instead of 7. Two hits happened; only one was counted. That's the lost update, and it's invisible: no error, no crash, just a number that's quietly too low.

The fix is to never pull the value into application memory at all:

clicks = clicks + 1 is computed by Postgres under the row lock the UPDATE already takes, so two concurrent statements serialize: one runs, then the other reads the already-updated value and adds to it. The counter lands on 7 no matter how many hits arrive at once. That's the entire reason to do the arithmetic in SQL rather than in JS — and it's exactly what the diagram above contrasts.

One thing we don't need: a migration. The clicks column already exists — we added it back in chapter 11 (clicks Int @default(0)), so a freshly shortened URL already starts at 0. This chapter touches nothing in prisma/; all we add is the code that moves an existing column.

Step 1: The Failing Test (Red)

We drive this with an integration test, because the behaviour we care about is a real number landing in a real urls row. The test persists a URL, hits GET /:code five times, then reads clicks straight from the database and asserts it equals five.

Check out the start branch and bring up the database:

The new suite wires buildApp with PrismaUrlRepository so it exercises the full path through Postgres. The driver case persists a URL, hits GET /:code five times, then reads clicks straight from the database:

That's the real driver. Two sibling cases pass on the start branch already — "starts at zero" (the column defaults to 0) and "no increment on a 404" (an unknown code is never stored). The honest red is this one: the handler doesn't count hits yet, so after five GETs the row's clicks stays at its default. We assert against the database directly with prisma.url.findUnique, because that row is the source of truth the stats endpoint (chapter 16) will read later.

Run the integration suite:

One honest behavioural failure — the redirect returns its 302 five times, but nothing increments the counter, so the row reads 0 instead of 5. With the container up, this is a real behaviour gap, not a connectivity or compile error.

Step 2: Add incrementClicks to the Seam (Green)

Switch to the finish branch:

The new behaviour goes behind the same UrlStore interface the redirect already depends on. We add one method, incrementClicks, and implement it in both backends. The handler then just calls it — it never learns which store it's talking to.

The in-memory store keeps a second Map for the counts. save seeds a new code at 0 so it mirrors the database default. incrementClicks is a no-op for a code that was never saved — that matches "no row to update" in the database. getClicks is test-only scaffolding — a string a key returns to so the Docker-free unit tests can assert the counter without a database. It is deliberately not on the UrlStore interface, because the route never needs to read a count.

The Prisma store does the increment the way that matters — atomically.

{ clicks: { increment: 1 } } is Prisma's atomic-increment shorthand. It compiles to UPDATE urls SET clicks = clicks + 1 WHERE short_code = $1 — one statement, the database doing the arithmetic. This is the read-modify-write fix from earlier, now in real code: no SELECT, no value in JS, no lost-update window.

Step 3: Count the Hit in the Handler

With the seam in place, the redirect handler counts the hit after a successful lookup and before the 302. The 404 branch is untouched — an unknown code is never counted.

The handler stays thin: it has no direct database access and talks only to the UrlStore seam, so the exact same code runs against the in-memory store (unit tests) and Prisma (integration and production). That's the chapter-6 seam paying off once more — a new storage capability slots in behind the interface without the route changing shape.

Awaited vs Fire-and-Forget

There is one real decision left: do we await the increment, or fire it off and return the redirect immediately? We chose to await — and the trade-off is worth understanding because the other choice is defensible in a different context.

Why awaited here. At this stage correctness and simplicity beat shaving a few milliseconds off a redirect. Awaiting keeps the count exact and lets a failed increment surface instead of vanishing. Fire-and-forget — return the 302 first, run the increment in the background — is the right call when redirect latency dominates and an occasional lost count is acceptable. But it needs an explicit .catch() so an unhandled rejection can't crash the process, and it trades a guarantee for speed. We note the trade-off and pick the guarantee.

A Note on Concurrency

The atomic increment we just wrote already makes concurrent click counting correct. Ten redirects for the same code arriving at once all run SET clicks = clicks + 1, the statements serialize on the row lock, and the counter moves by exactly ten. No application-level lock, no retry loop — the database handles it. We flag concurrency here so it's fresh, but this particular race is solved.

A different operation is still racy, and it's the one chapter 19 tackles: short-code generation. Two concurrent POST /shorten requests can independently generate the same code, both check "is it free?", both see "yes", and then one INSERT wins while the other hits the urls_short_code_key unique constraint. The @unique index makes a duplicate impossible to persist, but turning that constraint violation into graceful behaviour — retry with a fresh code, or serialize generation with a PostgreSQL advisory lock — is the chapter-19 topic. So: atomic increment solves concurrency for counting; advisory locks solve it for unique-code generation.

Step 4: Green on Both Suites

The finish branch also adds three Docker-free unit cases that exercise the in-memory store's counter directly (start at zero, N increments yield N, ignore an unknown code), so the increment logic is proven fast without a container. Run everything:

Green on both. The unit suite proves the increment logic in milliseconds; the integration test proves five real redirects land clicks = 5 in an actual Postgres row.

When you're done, stop the container:

What's Next

The database is accumulating real usage data, but there's no way to see what's stored. Next we list all URLs with a paginated GET /urls endpoint, with schema-validated paging params.