Upsert record and insert associated record together

Questions / Help
1

In a mobile app I’m building, login and signup flow are the same. User submits phone number, receives a OTP (one time password) via SMS, inputs into the app, and gets logged in.

users and one_time_passwords are in separate tables

  1. If user exists (unique constraint on phone_number) new associated one_time_password record should be created
  2. If user does not exist, both should be created

I figure on the user table, this is an upsert, so Repo.insert call should have the on_conflict param set (prob to :nothing for now), and then, somehow, I should insert the one_time_password. However the docs warn about upserts and associations:

Because of the inability to know if the struct is up to date or not, inserting a struct with associations and using the :on_conflict option at the same time is not recommended, as Ecto will be unable to actually track the proper status of the association.

I have 2 questions:

  1. How do I insert the one_time_password associated with its user, in one go (one transaction?), after user upsert?
  2. Does the warning from the docs above relate to my issue or am I misunderstanding the intent?
2

Yes, that advice applies to your situation. You should use Ecto.Multi, which will run all the steps in a single transaction. A minor optimization I’ve done is to first do a Repo.update_all which will return a count of rows updated. If it is 1, then it means the record already existed (and now it’s updated, job done), if it is 0, then insert to both tables. Ecto.Multi lets you see the results of prior steps within your transaction and have conditional logic like that.

1 Like