How to Fix the Dual-Write Problem in Node.js with the Outbox Pattern

<p>Imagine you're building an e-commerce platform where placing an order needs to trigger several things at once: the warehouse has to be told to prepare the shipment, the email service has to send a confirmation, and the fraud checker has to review the transaction.</p> <p>The order service handles the checkout, saves the order to its database, and then publishes an <code>order.created</code> event to a message queue so every downstream system can react independently.</p> <p>This is a common and reasonable design, but it has a reliability problem that's easy to miss until something goes wrong in production.</p> <p>When a customer places an order and the payment goes through, the application needs to do two things: save the order to the database and publish the event to the queue. These are two separate writes to two separate systems, and there's no way to make them share a single atomic transaction. If the process crashes, the network hiccups, or a deployment rolls out between the two writes, one side commits and the other does not. The order sits confirmed on the customer's screen while the warehouse has no idea it exists.</p> <p>The <a href="https://microservices.io/patterns/data/transactional-outbox.html">transactional outbox pattern</a> is the standard solution to this problem. In this article, we'll build it from scratch in Node.js, using PostgreSQL for the order service database, SQS for the queue, and DynamoDB as the fulfillment service's database. For local development, we'll use <a href="https://floci.io">floci</a>, a free open-source AWS emulator that runs all three with a single Docker container.</p> <h2 id="heading-what-well-cover">What We'll Cover</h2> <ul> <li><p><a href="#heading-prerequisites">Prerequisites</a></p> </li> <li><p><a href="#heading-the-problem-with-two-writes">The Problem with Two Writes</a></p> </li> <li><p><a href="#heading-the-outbox-pattern">The Outbox Pattern</a></p> </li> <li><p><a href="#heading-what-well-build">What We'll Build</a></p> </li> <li><p><a href="#heading-project-setup">Project Setup</a></p> </li> <li><p><a href="#heading-database-schema">Database Schema</a></p> </li> <li><p><a href="#heading-the-request-handler">The Request Handler</a></p> </li> <li><p><a href="#heading-the-relay-worker">The Relay Worker</a></p> </li> <li><p><a href="#heading-the-consumer">The Consumer</a></p> </li> <li><p><a href="#heading-running-the-whole-thing">Running the Whole Thing</a></p> </li> <li><p><a href="#heading-going-to-production">Going to Production</a></p> </li> <li><p><a href="#heading-conclusion">Conclusion</a></p> </li> </ul> <h2 id="heading-prerequisites">Prerequisites</h2> <p>To follow along, you should be comfortable with:</p> <ul> <li><p>Node.js and async/await</p> </li> <li><p>Database transactions (BEGIN, COMMIT, ROLLBACK)</p> </li> <li><p>The general concept of a message queue</p> </li> </ul> <p>You don't need prior experience with AWS, SQS, or DynamoDB. We'll be running everything locally.</p> <p>You will need Node.js 20 or later and Docker installed on your machine.</p> <h2 id="heading-the-problem-with-two-writes">The Problem with Two Writes</h2> <p>The order service scenario from the intro is one place this problem appears, but the same pattern comes up in many other contexts.</p> <p>A user registers and the app inserts their account record, then sends a message to trigger the welcome email and the onboarding workflow. A file is uploaded and the API writes the metadata to the database, then publishes a message to kick off a processing worker for virus scanning or thumbnail generation. A payment webhook arrives, the handler records it in the database, then notifies downstream services that the payment is confirmed.</p> <p>In every case, the application needs two writes to succeed together: one to the database and one to a queue or external system. If the second one is lost, the first one has no way of knowing.</p> <p>If you want a deeper look at what database transactions actually guarantee and where they stop helping, see <a href="https://blog.gaborkoos.com/posts/2026-08-01-Beyond-Happy-Path-Engineering-Databases/">Beyond Happy Path Engineering: Databases</a>.</p> <p>The naïve implementation looks straightforward:</p> <pre><code class="language-js">await db.query('INSERT INTO orders (customer_id, amount_cents) VALUES ($1, $2)', [customerId, amountCents]); await sqs.send(new SendMessageCommand({ QueueUrl: QUEUE_URL, MessageBody: JSON.stringify({ customerId, amountCents }) })); </code></pre> <p>The database write happens first, then the queue write. Under normal conditions this works fine. The problem is what happens when something goes wrong between the two.</p> <p>If the process crashes, runs out of memory, or gets killed mid-deployment after the database write but before <code>sqs.send</code> is called, the order record exists in the database but no event is ever published. The warehouse, email service, and fraud checker never find out the order happened. From the customer's perspective the order went through. From every downstream system's perspective it doesn't exist.</p> <p>The failure can also go the other way. If <code>sqs.send</code> succeeds but the database write is later rolled back due to a constraint violation or an error in a subsequent step, you've published an event for an order that doesn't actually exist. A consumer acting on that event may try to fulfill an order with no corresponding record, or charge a customer for something that was never saved.</p> <p>There's also a timing window even when both writes eventually succeed. Between the database commit and the successful <code>sqs.send</code>, a consumer that queries the database after receiving the event may not find the order yet, depending on transaction isolation and replication lag. These are two separate systems with no shared transaction boundary, and no amount of careful sequencing fully closes the gap.</p> <p>These aren't edge cases that only happen under extraordinary circumstances. Deploys restart processes mid-request. Out-of-memory kills happen without warning. Networks drop connections at any point. Any of these can interrupt the two-write sequence, and the result is a system that's silently inconsistent with no error logged and no alert fired.</p> <p>A variation I've seen a few times that looks safer but is actually worse is wrapping both operations in a database transaction:</p> <pre><code class="language-js">// PLEASE DO NOT EVER DO THIS const client = await pool.connect(); await client.query('BEGIN'); await client.query('INSERT INTO orders (customer_id, amount_cents) VALUES ($1, $2)', [customerId, amountCents]); await sqs.send(new SendMessageCommand({ QueueUrl: QUEUE_URL, MessageBody: JSON.stringify({ customerId, amountCents }) })); await client.query('COMMIT'); </code></pre> <p>The intent is to make the two writes feel like a unit, but a database transaction has no authority over SQS. The transaction can only roll back database operations. If <code>sqs.send</code> succeeds and then <code>COMMIT</code> fails, the message is already in the queue and can't be taken back. If the process crashes after <code>COMMIT</code> but before the function returns, the transaction committed and the message was sent, but the caller may retry, potentially inserting a duplicate order.</p> <p>Beyond the correctness problems, this pattern holds an open database connection and any row locks for the entire duration of the SQS network call. SQS is normally fast, but under load, retries, or a degraded queue, that call can take seconds. Every other request trying to read or write the same rows has to wait. In a busy application, this is a reliable way to exhaust the connection pool and bring down unrelated parts of the service.</p> <h2 id="heading-the-outbox-pattern">The Outbox Pattern</h2> <p>The core idea is to stop treating the queue publish as a second write that happens after the database write, and instead make it part of the same database transaction.</p> <p>Rather than calling <code>sqs.send</code> directly, the application inserts a row into an <code>outbox</code> table in the same transaction as the business record. A separate relay process reads the outbox table and publishes the messages to SQS. On the other end, a consumer receives the messages and writes to its own data store. In our case that is a fulfillment service writing to DynamoDB, completely separate from the order service's PostgreSQL database.</p> <p>If the transaction rolls back for any reason, the outbox row disappears with it. There's no orphaned message in the queue because the message was never sent. If the application crashes after committing but before the relay runs, the outbox row is still there with <code>status='pending'</code>, and the relay will pick it up on its next iteration.</p> <p>The only guarantee the pattern relies on is the one the database already provides: atomicity within a single transaction.</p> <p>The relay worker is responsible for the eventual delivery guarantee. It runs on an interval, selects pending rows, publishes them to SQS, and marks them as sent only after SQS confirms receipt. If the relay crashes mid-run, it will reprocess the same rows on the next iteration, which means SQS may receive some messages more than once.</p> <p>That's why the consumer needs to be <strong>idempotent</strong>: it must handle receiving the same message twice without creating duplicate fulfillment records. We'll cover how to implement that when we build the consumer.</p> <p>This separation of concerns is what makes the pattern practical. The request handler commits one atomic database transaction and returns. The relay handles the network call to SQS asynchronously, at its own pace, with its own retry logic, without holding database connections open or blocking request handling. The consumer is fully decoupled from the order service and owns its own data store.</p> <p>The diagram below illustrates the flow:</p> <img src="https://cdn.hashnode.com/uploads/covers/68b08746916c71e1ed2db58e/ab0620f0-65c6-43f1-a406-00bfd4880cdc.svg" alt="Diagram: outbox pattern flow" style="display:block;margin:0 auto" width="960" height="640" loading="lazy"> <h2 id="heading-what-well-build">What We'll Build</h2> <p>Now let's see the whole thing in practice. We'll implement a simple order placement API. When a customer sends a request to place an order, the order service saves it to PostgreSQL and inserts a row into the outbox table, all in one atomic transaction. A relay worker wakes up periodically, reads the pending outbox rows, and publishes each one as a message to SQS. A separate fulfillment service receives those messages from the queue and creates fulfillment records in DynamoDB.</p> <p>By the end, you'll have an HTTP endpoint you can call, and you'll be able to verify that placing an order triggers the creation of a fulfillment record in a completely separate database, owned by a completely separate service, without either service ever talking to the other directly.</p> <p>You can find the complete working code at <a href="https://github.com/gkoos/article-outbox">github.com/gkoos/article-outbox</a>.</p> <h2 id="heading-project-setup">Project Setup</h2> <p>Before you can run any code, you need to get floci running so you have local instances of PostgreSQL, SQS, and DynamoDB. You'll also need Node.js 20 or later and Docker installed.</p> <p>Start by cloning the repository and installing dependencies:</p> <pre><code class="language-bash">git clone https://github.com/gkoos/article-outbox cd article-outbox npm install </code></pre> <p>Next, start floci. This command pulls the latest floci image and starts a Docker container that exposes a local AWS API endpoint (make sure Docker is running):</p> <pre><code class="language-bash">npm run floci:start </code></pre> <p>On Linux and macOS, this just works. On Windows with Docker Desktop, <strong>you need to edit the</strong> <code>floci:start</code> <strong>script in your</strong> <code>package.json</code> <strong>to change the Docker socket mount from</strong> <code>/var/run/docker.sock</code> <strong>to</strong> <code>//var/run/docker.sock</code>.</p> <p>The floci container is now listening on port 4566 and can spin up RDS (PostgreSQL), SQS, and DynamoDB instances on demand.</p> <p>Now provision the AWS resources with a single setup command:</p> <pre><code class="language-bash">npm run setup </code></pre> <p>This script creates an RDS PostgreSQL database instance, an SQS queue named <code>orders</code>, and a DynamoDB table named <code>fulfillments</code>. It waits for RDS to become available and then writes a <code>.env</code> file with the correct connection details. The environment variables <code>PG_PORT</code>, <code>SQS_QUEUE_URL</code>, and <code>DYNAMODB_TABLE_NAME</code> now point to the local emulated services.</p> <p>Finally, create the PostgreSQL tables:</p> <pre><code class="language-bash">npm run migrate </code></pre> <p>This creates the <code>orders</code> table and the <code>outbox</code> table in PostgreSQL. You now have a fully functional local environment ready to build against.</p> <h2 id="heading-database-schema">Database Schema</h2> <p>The two tables are simple. <code>orders</code> holds the business records: each order has a customer ID, an amount in cents, and a timestamp. The <code>outbox</code> table is the heart of the pattern: it's where the application writes the event that needs to be published.</p> <pre><code class="language-sql">CREATE TABLE orders ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), customer_id TEXT NOT NULL, amount_cents INTEGER NOT NULL, created_at TIMESTAMPTZ DEFAULT now() ); CREATE TABLE outbox ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), event_type TEXT NOT NULL, payload JSONB NOT NULL, status TEXT NOT NULL DEFAULT 'pending', created_at TIMESTAMPTZ DEFAULT now(), sent_at TIMESTAMPTZ ); CREATE INDEX ON outbox (status, created_at) WHERE status = 'pending'; </code></pre> <p>The <code>orders</code> table needs nothing special. The <code>outbox</code> table stores the event metadata: what type of event it is (<code>event_type</code>), what data it contains (<code>payload</code> as JSON), and whether it has been sent yet (<code>status</code>).</p> <p>The status starts as <code>pending</code>. When the relay publishes it to SQS, it will mark it as <code>sent</code> and record the timestamp. The index on <code>(status, created_at) WHERE status = 'pending'</code> lets the relay quickly find the next batch of unsent events without scanning the entire table.</p> <h2 id="heading-the-request-handler">The Request Handler</h2> <p>This is where the pattern starts. The request handler receives an HTTP POST, inserts an order into the database, inserts a corresponding row into the outbox table, and commits everything in a single atomic transaction. The key insight is that neither write succeeds unless both succeed.</p> <pre><code class="language-js">const client = await pool.connect(); try { await client.query('BEGIN'); // Insert the order record const { rows } = await client.query( 'INSERT INTO orders (customer_id, amount_cents) VALUES ($1, $2) RETURNING *', [customerId, amountCents] ); const order = rows[0]; // Insert the outbox record in the same transaction await client.query( `INSERT INTO outbox (event_type, payload) VALUES ($1, $2)`, ['order.created', JSON.stringify({ orderId: order.id, customerId: order.customer_id, amountCents: order.amount_cents, createdAt: order.created_at })], ); await client.query('COMMIT'); res.status(201).json(order); } catch (err) { await client.query('ROLLBACK'); next(err); } finally { client.release(); } </code></pre> <p>The handler gets <code>customerId</code> and <code>amountCents</code> from the request body, starts an explicit transaction with <code>BEGIN</code>, and inserts the order. Then it inserts an outbox row with the order data as the payload.</p> <p>Everything commits atomically. If anything fails, everything rolls back and the client gets an error. If the process crashes between the commit and the response, the client won't get a 201, but the order and the outbox row are still safely committed to the database and the relay will eventually pick it up. The handler doesn't call SQS at all. That is the relay's job.</p> <h2 id="heading-the-relay-worker">The Relay Worker</h2> <p>The relay worker is a separate process that polls the outbox table every second and publishes pending rows to SQS. It runs independently of the HTTP server and has no shared state with it.</p> <pre><code class="language-js">async function relay() { const client = await pool.connect(); try { await client.query('BEGIN'); const { rows } = await client.query(` SELECT * FROM outbox WHERE status = 'pending' ORDER BY created_at LIMIT 10 FOR UPDATE SKIP LOCKED -- prevents multiple relays from processing the same rows `); for (const row of rows) { await sqsClient.send(new SendMessageCommand({ QueueUrl: QUEUE_URL, MessageBody: JSON.stringify(row.payload), MessageAttributes: { EventType: { DataType: 'String', StringValue: row.event_type }, }, })); await client.query( `UPDATE outbox SET status = 'sent', sent_at = now() WHERE id = $1`, [row.id], ); } await client.query('COMMIT'); } catch (err) { await client.query('ROLLBACK'); console.error('Relay error:', err.message); } finally { client.release(); } } setInterval(relay, 1000); </code></pre> <p><code>FOR UPDATE SKIP LOCKED</code> is the key to running multiple relay instances safely: when a relay picks up a batch of rows, it locks them. Any other relay instance trying to select the same rows will skip them and move to the next available ones, so you never get two relays publishing the same message from the same run.</p> <p>The relay marks each row as <code>sent</code> only after <code>sqsClient.send</code> returns. If the relay crashes after sending to SQS but before updating the row, the row stays <code>pending</code> and the relay will resend it on the next iteration.</p> <p>Note that the <code>UPDATE</code> happens inside the same transaction as the <code>SELECT FOR UPDATE</code>, so if the relay crashes mid-batch, the entire batch rolls back and all rows in it will be retried, including any that were already successfully sent to SQS.</p> <p>The at-least-once delivery guarantee applies at the batch level, not the individual row level. You can read about this problem in <a href="https://blog.gaborkoos.com/posts/2026-07-01-Beyond-Happy-Path-Engineering-the-Network/">Beyond Happy Path Engineering: the Network</a>: when a response is lost, the caller can't know whether the operation succeeded, so it retries, and the receiver may see the same request twice. This means the consumer may see the same message more than once, which is why idempotency matters on the consumer side.</p> <h2 id="heading-the-consumer">The Consumer</h2> <p>The consumer is a completely separate service. It knows nothing about the order service's PostgreSQL database. Its only input is the SQS queue, and its only output is the DynamoDB <code>fulfillments</code> table. This is the point of the pattern: the two services are decoupled by the queue, and each owns its own data store.</p> <p>As we saw earlier, because SQS delivers at least once (meaning a message might be delivered more than once), the consumer must be idempotent. The <code>PutItem</code> call uses a <code>ConditionExpression</code> that makes the write a no-op if a fulfillment record for that order already exists, so redelivered messages are handled safely.</p> <pre><code class="language-js">async function consume() { const { Messages } = await sqsClient.send(new ReceiveMessageCommand({ QueueUrl: QUEUE_URL, WaitTimeSeconds: 20, // long-poll: wait up to 20s for messages MaxNumberOfMessages: 10, MessageAttributeNames: ['All'], })); for (const msg of Messages ?? []) { const event = JSON.parse(msg.Body); try { await dynamoClient.send(new PutItemCommand({ TableName: 'fulfillments', Item: { orderId: { S: event.orderId }, customerId: { S: event.customerId }, amountCents: { N: String(event.amountCents) }, status: { S: 'received' }, createdAt: { S: new Date().toISOString() }, }, ConditionExpression: 'attribute_not_exists(orderId)', // idempotency check })); } catch (err) { if (err.name !== 'ConditionalCheckFailedException') throw err; // already processed, safe to continue } // delete the message only after the write succeeds (or was already done) await sqsClient.send(new DeleteMessageCommand({ QueueUrl: QUEUE_URL, ReceiptHandle: msg.ReceiptHandle, })); } } </code></pre> <p><code>ConditionExpression: 'attribute_not_exists(orderId)'</code> tells DynamoDB to reject the write if a record with that <code>orderId</code> already exists. When that happens, DynamoDB throws a <code>ConditionalCheckFailedException</code>. The consumer catches that specific error and ignores it, then deletes the message from the queue and moves on. Any other error is rethrown and the message stays in the queue to be retried.</p> <p>The <code>DeleteMessage</code> call happens after the DynamoDB write, not before. If the process crashes between the write and the delete, SQS will redeliver the message and the condition check will handle it. If the process crashes before the write, the message stays in the queue and will be processed normally on the next delivery.</p> <h2 id="heading-running-the-whole-thing">Running the Whole Thing</h2> <p>With floci running and the resources provisioned, open three terminal tabs and start each process:</p> <pre><code class="language-bash">node src/server.js # the order API on port 3000 node src/relay.js # the outbox relay node src/consumer.js # the fulfillment consumer </code></pre> <p>Now place an order:</p> <pre><code class="language-bash">curl -X POST localhost:3000/orders \ -H 'Content-Type: application/json' \ -d '{"customerId":"c1","amountCents":4999}' </code></pre> <p>You should get back a 201 with the new order record:</p> <pre><code class="language-bash">{"id":"1768d35b-083d-45f1-adb5-4063d8d7fcab","customer_id":"c1","amount_cents":4999,"created_at":"2026-07-30T20:27:10.628Z"} </code></pre> <p>Within a second the relay will pick up the outbox row and publish it to SQS. The consumer will receive the message and write a fulfillment record to DynamoDB. The repo includes a convenience script to verify this:</p> <pre><code class="language-bash">npm run check </code></pre> <p>You should see a fulfillment record with the <code>orderId</code> from the order you just placed:</p> <pre><code class="language-bash">{ orderId: 'c335640e-bc4a-47e4-afed-484c95fbd6d3', customerId: 'c1', amountCents: '4999', status: 'received', createdAt: '2026-07-30T19:02:54.929Z' } </code></pre> <h2 id="heading-going-to-production">Going to Production</h2> <p>Because the local setup uses floci to emulate AWS, switching to real AWS requires no code changes at all. The AWS SDK reads the endpoint from <code>AWS_ENDPOINT_URL</code> in the environment. In production, you simply don't set that variable and the SDK talks to real AWS using the credentials and region from the standard environment variables (<code>AWS_REGION</code>, <code>AWS_ACCESS_KEY_ID</code>, <code>AWS_SECRET_ACCESS_KEY</code>, or an IAM role if you are running on EC2 or ECS).</p> <p>Running multiple relay instances is safe out of the box because of <code>FOR UPDATE SKIP LOCKED</code>. You can scale the relay horizontally and each instance will pick up a different set of rows without duplicating messages.</p> <p>One thing worth adding before going to production is handling permanent failures in the relay. Right now the relay only uses <code>pending</code> and <code>sent</code>. You should add a <code>failed</code> status and a retry counter: after a row has failed N times, mark it <code>failed</code> and stop retrying it. Then configure a dead-letter queue on the <code>orders</code> SQS queue as well, so that messages the consumer can't process after the maximum number of retries land somewhere you can inspect rather than disappearing silently.</p> <p>For high-throughput systems where polling latency matters, <a href="https://en.wikipedia.org/wiki/Change_data_capture">change data capture</a> (CDC) is a common alternative to the polling relay. Tools like <a href="https://debezium.io/">Debezium</a> read directly from the PostgreSQL write-ahead log and publish changes to <a href="https://kafka.apache.org/">Kafka</a> or SQS without any polling delay. The outbox table and the consumer stay exactly the same, only the relay is replaced.</p> <p>This is a bigger operational commitment than a polling worker, so polling is the right starting point for most systems.</p> <h2 id="heading-conclusion">Conclusion</h2> <p>The dual-write problem is easy to overlook because the naïve implementation works correctly most of the time. It only fails in the gaps between two separate system writes, and those gaps only become visible when something goes wrong at exactly the wrong moment. By the time you notice it in production, data is already inconsistent and there is no clean way to recover.</p> <p>The transactional outbox pattern closes that gap at the database level. The outbox row is part of the same atomic commit as the business record, so the two are always in sync. The relay handles the network call to SQS independently, with its own retry logic, without touching the request lifecycle. The consumer handles at-least-once delivery with a single condition check on the write.</p> <p>Each piece is simple on its own, and together they give you reliable, decoupled event delivery without distributed transactions.</p>