So I was reading of the multiple modes for Ecto.Adapters.SQL.Sandbox, it seems you have to use only one of the modes for your test suite so you have to pick one of the three options available: :auto
(default), :manual
or :shared
.
Trying to figure out which is the best, so I want to poll opinions 
Found this related article which explains it a little more Making Sense of Ecto 2 SQL.Sandbox and Connection Ownership Modes
The links you shared are a little out of date
The updated documentation is at Ecto.Adapters.SQL.Sandbox — Ecto SQL v3.9.2 - Your version is Ecto 2.0 beta. Ecto is now on version 3.9
You probably want to leave it on :auto
or set it to :manual
- I set it to :manual
in test_helpers.exs
If you use :shared
, you can no longer run your tests async. This is because :shared
mode can introduce non-determinism because the test processes / transactions can see each of the other test transactions data it has inserted into the database. See 8 Common Causes of Flaky Tests in Elixir | AppSignal Blog for more info (#2 in the list)
2 Likes
Oh you are right that I linked an older version of ecto
. Well it seems like the docs for Ecto.Adapters.SQL.Sandbox have not changed much between versions and it works the same, unless I missed some relevant difference. So the links I shared still remain relevant. Did notice one difference: :auto
mode in newer version does not require checkin/1
to return connections to the pool.
Yeah lets talk about this!
So there is :auto
and :manual
like you mention, they have the advantage of running async
, then there is :shared
and it has the limitation of not allowing async
tests. These differences and nuances are covered in the article I shared so I won’t go over them.
The point here is that running tests synchronously is the trade-off from using :shared
, but that does not mean that :shared
is less capable, it simply offers different advantages.
For example, you can’t easily use setup_all/1
with :manual
or :auto
modes. In :manual
you need to create an allowance for each test, whilst in :auto
you have to use on_exit/2
callback to manually clean-up all of the changes on the database from your setup_all/1
block.
I suppose you don’t use setup_all
much so you have no need to worry about these issues?
I don’t think it is easy to tell what people probably want without being nuanced with these topics. 
So tell me about your experience with :manual
mode, any quirks that you have ran into? Do you have to write allowances often?
Another of the tricky points with :manual
mode is that if your production code uses concurrent queries to the database, then you cannot write allowances for those processes from your test files.
This makes me think of another question… Can you mix modes for different tests or is that bound to cause chaos?