How do I store a record in two tables with ecto?

Let's say I have a schema

schema "posts"
  field ...
  field :post_id (UUID4)
  field :revision_id (UUID4)
end

On commit, I want to insert two copies of this data into two tables:

UPSERT table(:posts) on :post_id, with :post_id as primary
INSERT table(:revisions) on :revision_id as primary

Here's my code for inserting the two records:

  Multi.new()
  |> Multi.insert(:revisions, post)
  |> Multi.insert(:posts, post, on_conflict: :nothing)
  |> Repo.transaction()

My schema is defined on Post.

Currently it seems to try to insert the record twice on the same table (posts) and ignores the revisions table.

How can I force it to write to two separate tables?

Solution

Using Ecto.Multi

Here is a "textbook" example of using Ecto.Multi to create 2 related records: a User record and an Email record (this assumes a 1-to-many relationship between users and emails):

alias Ecto.Multi

Multi.new()
|> Multi.insert(:user, User.changeset(%User{}, user_params) end)
|> Multi.insert(:email, fn %{user: %User{id: user_id}} ->
  Email.changeset(%Email{user_id: user_id}, email_params)
end)
|> Repo.transaction()

For your case, you would need to adapt the models to "posts" and "revisions", and you would need to adapt the query(ies) to have an "upsert" functionality. Make sure you are capturing the necessary data from the first table and passing it to the second so they can be properly associated in the database.

Using Repo.transaction

I find that Repo.transaction/1 is more readable and more flexible (you can even use it while operating against external events such an API calls etc). An outline of using a transaction here would look something like this:

  def save_with_revision(params) do
    Repo.transaction fn ->
      with {:ok, revision} <- insert_revision(params) do
           {:ok, post} <- params |> Map.put(:revision_id, revision.id) |> upsert_post()
      do
        {:ok, post}
      else
        {:error, e} -> Repo.rollback(e)
      end
    end
  end
  
  defp insert_revision(params) do
    # ...
    {:ok, revision}
  end

  defp upsert_post(params) do
    # ...
    {:ok, post}
  end

You would need to implement functions to insert the revision and upsert the post, and I am assuming here that your revisions table is uses an id field as its primary key, but this outline should serve as a useful guide. The important thing is that if either operation fails, execution is routed to Repo.rollback/1: the rows go in together successfully or not at all.