A hobby project of mine (in Elixir) uses SQLite as primary database. Each test runs in its own fully isolated SQLite database. No mocking (or transaction rolling back) needed. Most of these tests take less than 1ms to run (and when they take longer, it's because of something else).
This kind of setup makes the usual Ecto Sandbox approach feel slow, but I do agree that the way Elixir approaches this is great!
I actually have two projects that use this approach, FeebDB (which is the library I wrote to manage a "one SQLite database per client" approach) and HackerExperience (a game under development that uses FeebDB).
The overall idea is simple:
1. Before tests start running, create a prop of each database.
2. The prop contains the "starting database" for each test. It may contain seed data (optional).
3. For each test, copy the prop and assign it a unique shard identifier (say, cp /props/user.db /test_data/user/874125.db).
4. The test knows the `shard_id` and can do whatever it wants with it; no one else will bother it.
5. Once ExUnit is finished, delete all shards.
Both projects follow a similar approach (I wrote it first in FeebDB and copied into HackerExperience, which has some sections commented out -- I need to clean up this part of the codebase).
For both projects, you will find steps 1/5 in `test/support/db.ex`, step 2 in `test/support/db/prop.ex` and steps 3/4 in `test/support/case/db.ex`.
This kind of setup makes the usual Ecto Sandbox approach feel slow, but I do agree that the way Elixir approaches this is great!