Implementing batch insert preview with Ecto transactions

Questions / Help
1

This seems to be working but it feels a bit weird to me and I wonder if there might be a better approach. I’m working on a feature which imports data from an uploaded csv. The data structures that are inserted are quite involved, I group the csv data and insert some fairly deeply nested associations. In some cases I even insert data that makes reference to pre-existing database rows. I want to provide a way to preview the data that will be inserted without actually inserting it yet. I’ve been able to do so by running the insert work inside a transaction then rolling it back passing the temporarily inserted data as the “error” in the call to rollback. Looking forward to hearing any feedback or suggestions. Here is a code sample:

  # Call this to actually insert data
  def do_import(%RegistrationImportJob{} = registration_import_job) do
    write_to_database(registration_import_job)
  end

  # Call this to get a preview of what will be inserted but rollback so we don't actually insert
  def preview_import(%RegistrationImportJob{} = registration_import_job) do
    {:error, result} = Repo.transaction(fn() ->
      result = do_import(registration_import_job)
      Repo.rollback(result)
    end)
    result
  end

Aside from general feedback I’m interested in thoughts on:

  • Can you think of cases where there might be a risk that the rollback isn’t run and we insert data unintentionally?
  • Treating this as an error is misleading, is there another way to write this that indicates that the rollback is actually the intended behaviour?
2

Haven’t tried yet, but I think that temp table will work without pollute the main table (like increase the increment, …)

Given you have the schema:

defmodule Post do
	 use Ecto.Schema
	schema "post" do
		field :title, :string
		field :slug, :string
		field :description, :utc_datetime
end

Your temp schema:

defmodule TempPost do
	 use Ecto.Schema
	schema "temp_post" do
		field :title, :string
		field :slug, :string
		field :description, :utc_datetime
end

Run

Multi.new()
|> Multi.run(:create_temp_table, fn repo, _ ->
repo.query(
  "CREATE TEMP TABLE temp_post AS SELECT * from post LIMIT 0;"
)
|> Multi.insert_all(:insert_all, :temp_post, data)
|> ... # Select data to preview
end)
3

Interesting, thanks for the suggestion! That’s a really interesting approach.