Postgres with Drizzle · Chapter 40 of 40

The outbox pattern

By the end of this chapter you can save a change and the event that announces it in one transaction, and deliver those events later, safely, with several workers at once. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Mastery
  • 9 tasks
  • PostgreSQL
  • In the all labs plan

The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.

Why you need this

Meera's new feature tells followers when someone posts. The first version saved the post, then called the notification service. When the service was down, the post was saved and the followers were never told. Calling the service first is worse: when the insert then fails, followers hear about a post that does not exist.

A transaction covers the database, not the notification service: the dual-write problem. Anya's fix writes the event into a table, outbox, in the same transaction as the post. A worker later reads it, calls the service, and marks each event done.

What you build: 9 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    createOutboxTable: the outbox table

    Write createOutboxTable(). It drops outbox if it exists, then creates outbox with the columns id (bigserial primary key), aggregate_type (text, not null), aggregate_id (integer, not null), event_type (text, not null), payload (jsonb, not null), created_at (timestamptz, not null, default now) and processed_at (timestamptz, nullable: NULL means not delivered yet).

    Send raw SQL with getPool().query. It returns nothing. The check calls it before every task, so until it works every task fails.

  2. Task 2

    createOutboxTable: an index for undelivered events

    The same createOutboxTable() also creates a partial index on outbox: on created_at, holding only the rows where processed_at IS NULL. Any index name works (idx_outbox_unprocessed, for example).

    The check reads pg_indexes and looks for an index on outbox whose definition contains processed_at IS NULL.

  3. Task 3

    createPostWithOutboxEvent: a post and its event together

    Write createPostWithOutboxEvent(authorId, content). In one transaction on a client of its own: insert a post (author_id authorId, content, type text) and keep the whole new row; insert an outbox row with aggregate_type post, aggregate_id the post's id, event_type post.created and payload the JSON object { authorId, content } (those two key names); commit.

    Return the new post row as one object (with id, author_id and content). processed_at stays NULL.

  4. Task 4

    createPostWithOutboxEvent: a failure saves neither

    When any statement fails, the same createPostWithOutboxEvent must roll the transaction back and throw the error again, leaving neither a post nor an outbox row. The check calls it with author 999999, who does not exist, catches the error, and expects no outbox row for a post.

  5. Task 5

    processOutboxEvents: a worker delivers a batch

    Write processOutboxEvents(handler, batchSize). handler is an async function that takes one event; batchSize is a number. In one transaction on a client of its own: select the outbox rows whose processed_at is NULL, oldest created_at first, at most batchSize of them, locking them with FOR UPDATE SKIP LOCKED; await handler(event) for each row (the whole row), in that order; set processed_at to now for those rows; commit. On an error, roll back and throw it again.

    Return how many events were processed, a number. The check adds three posts with your task 3 function and expects 3, with every event delivered afterwards.

  6. Task 6

    processOutboxEvents: no more than the batch

    The same processOutboxEvents must process at most batchSize events: with three waiting and a batch size of 2, it returns 2 and one event stays undelivered.

  7. Task 7

    getUnprocessedEventCount: the backlog

    Write getUnprocessedEventCount(). It counts the outbox rows whose processed_at is NULL.

    Return that count as a number, not a string (0 for an empty outbox).

  8. Task 8

    replayFailedEvents: retry events that got stuck

    Write replayFailedEvents(since, handler). since is a moment as an ISO date string. In one transaction on a client of its own: select the outbox rows whose processed_at is NULL and whose created_at is before since, oldest first, locked with FOR UPDATE SKIP LOCKED; await handler(event) for each; set their processed_at to now; commit. On an error, roll back and throw it again.

    Return how many events were replayed, a number. The check backdates one undelivered event by two hours and passes a moment one hour ago: 1, and nothing left undelivered.

  9. Task 9

    cleanupProcessedEvents: delete old delivered events

    Write cleanupProcessedEvents(olderThanDays). olderThanDays is a number. It deletes the outbox rows that were processed more than that many days ago: processed_at is not NULL and earlier than now minus olderThanDays days. Undelivered rows and recently delivered rows stay.

    Return how many rows were deleted, a number. The check has one event delivered 30 days ago and one just now, passes 7, and expects 1 deleted and 1 left.

What you will learn

  • The outbox table: events stored beside the data they describe, committed or rolled back together with it.
  • FOR UPDATE SKIP LOCKED: each worker claims rows no other worker holds, without waiting.
  • Replay and cleanup: retry events that got stuck, and delete old delivered ones.

More in Mastery

Build The outbox pattern yourself

MingleSphereQL grows up: its backend talks to Postgres through Drizzle. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.