Transactional user registration

Sounds like a job for Ecto.Multi.run functions (3- and 5-arity). Have you looked at them?

This cannot be done. Postgres transactions can only provide guarantees between postgres operations, and sending an email isn’t a postgres operation. What you can do is put the user data in a “pending” state, and change that state to “complete” or something after the email successfully fires.

I am talking about framework-level transactions, not database-level ones. The framework-level transactions can (and should) utilise underlying DBMS mechanisms but rolling back can be triggered by various events. In Rails I could simply do:

User.transaction do
  user.save!
  user.send_confirmation_email!
end

regardless of which (transactions supporting) database backend is in use, Postgres or other. If the second message raises, the changes get rolled back. Or I could put the send_email_confirmation! e.g. in an after_create callback (which is transaction-wrapped) to achieve that result.

I find it hard to believe that “This cannot be done” in Phoenix.

Not yet. I understand you mean these, right? Checking out and looking for examples now.

This didn’t work in Rails either. You can do it, but it doesn’t actually work the way you think it does. The email might go out, and then the transaction might fail because the database terminates, and then the email is not unsent. This means that it is not actually transactional.

It does mean that if the email fails to send, the user will be rolled back. BUT the inverse isn’t true. The transaction might rollback after the email has gone out for any number of reasons, and the email isn’t reverted because it’s an external side effect.

Arguably, sending emails inside a transaction is actually an anti pattern because you hold a database transaction open while waiting on an external API call which is unaffected by the transaction itself.

The after create is in the transaction sure, but the mail server is not in your database. Eventually your “in transaction” calls become API or SMTP calls to the mail server, and at that point stop being managed by the transaction.

You can do the same psuedo transaction in Elixir:

Repo.transaction(fn ->
  user |> Repo.insert!
  user |> send_welcome_email!()
end)

This suffers from the same limitations though. If the sending fails, then the user rolls back, which is good. BUT if the email works, and the transaction aborts for any number of reasons, the user will be rolled back, but the email won’t be unsent. This is just how the transactions work.

Yep, those.

@benwilson512 is right that in principle you shouldn’t do this but I understand there are times when you would want to abuse DB transactions to try and encapsulate such more complex workflows. (You do also have to keep some transient state like “in progress” or something.)

Do take special care. These 3rd party API calls (in your case, sending an email) must finish very quickly – 2s maximum.

Perhaps you can apply the “saga” pattern to come close to what you actually want?

(Never used it, only heard about it in podcasts)

In addition to the issues already mentioned here, there is another problem. If the connection between the app server and the mail server breaks before the mail server managed to send a confirmation response, you might end up rolling back a perfectly valid transaction, while the mail is already sent. This is just a how distributed transactions work. As @benwilson512 said, you can’t use db transaction mechanism to commit or rollback the e-mail.

This is basically impossible to guarantee. If the network is a bit slower, the network request might take longer. You could try to enforce some timeout, but that will increase the likelihood of the aforementioned scenario (mail is sent while the transaction is rolled back).

IMO the proper way to tackle this is to use a db-backed queue to send mails. My current clients have switched to using oban for that. Basically, inside a db transaction an Oban job is created. Once the transaction is committed, the send mail job will be started. If this job fails (e.g. mail server is not reachable), we’ll retry it (after some delay).

A nice consequence is that with such approach we don’t need to pay so much attention in the transaction code. If you’re doing an immediate send from the transaction, you need to make sure it’s the very last thing you do (otherwise you increase the chance of e-mail being sent but the transaction failing). With the persistent queue, such issue doesn’t exist, since we enqueue the job in the same db, so we can do it anywhere inside the transaction. The job will only be executed if the transaction is committed.

Here’s another thing that won’t work reliably with immediate send:

Repo.transaction(fn ->
  do_stuff_with_db()
  send_mail_to_user_1()
  send_mail_to_user_2()
end)

If the second send fails, we’ll rollback the transaction, but one user will be notified about the success, and we can’t undo that. Using the queue approach, things will work as expected.

So tl;dr use the queue (managed in the same db) to enqueue the pending send jobs (or any other kinds of jobs which involve an external remote system).

True. Might also happen the other way around. I might think I “sent” the mail but I actually only placed it into send queue, which means it may still fail to send while the DB data remains stored. I am aware of that and I know it works the way I think it does That was a simplified example to explain what I understand as framework-level transaction (capable of encompassing non-DB operations as potential rollback triggers).

I don’t say it’s the right approach. I asked for “best practice” suggestion so how would you suggest approaching this type of problem?

Normally they return very quickly (local stuff). but if there’s a better approach, I’d be happy to use it.

Hm. Doesn’t this suffer the same limitation as previously mentioned naive approaches? And even more? Yes, I can put another queue in the same DB and transactionally insert both the user data and sending job, but then the data is stored, and nothing knows what happens further with the job. So I might be missing something but to me it looks like adding complexity (and dependency) for doing virtually the same as sending it immediately to local sendmail (which is what I use in such situations), which maintains its own queue, does retries, etc. It could be of help if we wanted to deliver to final MX (or even to another, external SMTP) directly from the application, but I avoid such ways.