It sounds like your use case is going beyond running your elixir tests and the specific need of being able to diagnose a specific test failure.

That said, if you must “copy it and do other things with it as well”, then simply force commit the transaction from IEx.pry by typing something like Ecto.Adapters.SQL.query!(MyApp.Repo, "COMMIT") to preserve the transaction in the database and then exit iex and then you can copy the database and do all those other things you want.

Not entirely true. Transactions certainly do work in SQLite. With how ecto does sandbox rollbacks, things get a little weird. As long as your journal mode is set to WAL and the filesystem that the system is running on is not an NFS mount, it should work just fine.

You could run your tests in iex and use IEx.pry to inspect things.

This is not a mode of working that I use but here are some posts that explains how to start tests with iex and use IEx.pry:

• Debugging · Elixir School
• How to Use IEx.pry in Elixir Tests - Elixir Tutorials

You could look into setting the sandbox mode to manual before running a failing test. That would skip the whole transaction wrapping. That might change how the test works across multiple processes though, so keep that in mind.

Yes. Within a single database connection, a SELECT statement always sees all changes to the database that are completed prior to the start of the SELECT statement, whether committed or uncommitted.

Summary fom the docs:

• Transactions in SQLite are SERIALIZABLE.

• Changes made in one database connection are invisible to all other database connections prior to commit.

• A query sees all changes that are completed on the same database connection prior to the start of the query, regardless of whether or not those changes have been committed.

• If changes occur on the same database connection after a query starts running but before the query completes, then it is undefined whether or not the query will see those changes.

• If changes occur on the same database connection after a query starts running but before the query completes, then the query might return a changed row more than once, or it might return a row that was previously deleted.

• For the purposes of the previous four items, two database connections that use the same shared cache and which enable PRAGMA read_uncommitted are considered to be the same database connection, not separate database connections.