Confusing behaviour of Ecto Sandbox not rolling back the changes

Questions / Help
1

Hi everyone,

I’ve been reading the “Designing Elixir Systems with OTP” book, and got to the chapter on unit testing the persistence layer of the application. The sample app uses Ecto and PostgreSql.

For full context, the unit test in question is here on Github: https://github.com/iarekk/designing-elixir-systems-otp/blob/main/mastery/test/mastery_test.exs

The book provides the following code for the unit test to ‘configure the test environment to use a sandbox environ- ment that rolls back new changes after every test’:

in config/test.exs:

use Mix.Config
  config :mastery_persistence, MasteryPersistence.Repo, database: "mastery_test",
    hostname: "localhost",
    pool: Ecto.Adapters.SQL.Sandbox

and in the unit test setup:

# called in the setup()
defp enable_persistence() do
   :ok = Ecto.Adapters.SQL.Sandbox.checkout(Repo)
end

After reading Ecto documentation, I ended up adding this line to the method above

Ecto.Adapters.SQL.Sandbox.mode(Repo, {:shared, self()})

to make the unit tests revert the changes in the DB, but I don’t seems to understand why it’s needed, or why ‘revert all unit test DB changes’ isn’t the default behaviour.

Commit adding that line here: commit

So mostly I’m confused on:

  • why is the ‘sandbox’ mode not sandboxing/rolling back the changes by default, contrary to the documentation claims
  • why the magical command of setting the mode to ‘shared’ enables the rollback

For example, here the documentation states in passing “Since the SQL Sandbox mode rolls back transactions, tests might report false positives because deferred constraints are never checked by the database…”

https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.Sandbox.html#module-deferred-constraints

2

In the default :auto mode, the sandbox only affects the process that calls Sandbox.checkout.

Setting the mode to :manual makes the sandbox affect the process that calls checkout and additional processes that are allowed.

Setting the mode to {:shared, self()} makes the sandbox affect all processes.

In your example, the test process calls Sandbox.checkout but Mastery.Boundary.Proctor does not so the database calls are not affected by the sandbox in :auto mode.

Unrelated note: Enum.sort has some newer conveniences that can tidy up Proctor - for instance, Enum.sort(list_of_datetimes, Datetime) does the same comparison as Proctor.date_time_less_than_or_equal?/2

1 Like
3

It makes so much sense now, thank you!

I completely missed the fact that the unit test kicks off a whole series of processes :smiley:

P.S. The Enum.sort bit is extremely cool, thanks for pointing that out! So much cleaner than the custom function - gonna give it a try :slight_smile:

4

Update: I’ve added a fairly awkward bit of code to illustrate the manual sandboxing, changeset here: https://github.com/iarekk/designing-elixir-systems-otp/commit/32b2765cd0d7835ac13f5a6fd94de583d014485d

Essentially the code below finds the correct QuizSession process (it’s dynamically supervised) using the same routing mechanism (:via tuples) as the application itself.

Then it marks it as an ‘allowed’ pid for this instance of the sandbox to write to.

  defp allow_session_to_write_to_db(self_pid, session) do
    allow_pid = GenServer.whereis(QuizSession.via(session))
    Ecto.Adapters.SQL.Sandbox.allow(Repo, self_pid, allow_pid)
  end

End result: the test passes, and still no new records in the test DB.