Dual Write Problem: Have you ever faced it?

Imagine you have to trigger an action right after a database change.
Let's say you just inserted an user to the database. Then you want to send a welcome email.
But then for some reason your app crashed and restarted mid processing. The user was inserted but no email was sent.
You try the following approaches:

• Changing the order, but then the email may send and the insert is not performed.
• Use a database transaction, but that transaction cannot include the email service
• Use a message broker, but the problem persists, because it may fail before the message is published.

How do you guarantee that the user is inserted and the email service is always triggered together? That is the dual write problem.

Solution

The most common solution is using the Transactional Outbox Pattern.
It consists of creating a outbox table, where you store the event, in our example it would be UserCreated. Then every time an user is inserted, a new record is added to this outbox table. You should also add the other core information in this same record, like the username and email. The consumer will need them. Don't forget to add a status column to keep track if the information was processed or not.
Then wrap the user insert and the outbox insert in a transaction, to guarantee they will always be inserted together.

BEGIN;

INSERT INTO users (name, email)
VALUES ('Fulano', '[e-mail]');

INSERT INTO outbox (type, payload, status)
VALUES ('UserCreated', 
'{"name": "Fulano", "email": "[e-mail]"}', 
'PENDING');

COMMIT;

Then an outbox consumer will be looking for new records in the outbox table, and then sends the email and change the outbox status to Processed.
That solves the problem.