Keep database state after failed test

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.

Use testcomtainers for elixir, coupled with testcontainers desktop which lets you freeze container shutdown (and thus also the test)? EDIT: nvm, not applicable for this use case.

I know what I can do from IEx.
I was just looking for an easy way to use sqlite itself. I like to be able to type raw sql queries sometimes.
I know I can do that with ecto as well but it’s a tiny bit more work.

Furthermore having the database available outside of the tests allows me to copy it and do other things with it as well.

Well no, and that’s not what I proposed.

I am not sure you have fully grasped the concept of using Elixir iex and IEx.pry where you are in Elixir at a break point in the code in the context of the test process and sandbox transaction where something has failed or is about to fail, and where you have access to inspect all variables and the full repertoire of Ecto queries at your fingertips to also inspect the database contents from the perspective of that sandbox transaction.

You are not running sqlite command on a different database connection outside of the Elxir/Ecto sandbox transaction.

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.

Yes, you can do it from within elixir.

But I don’t think I can reuse the single database connection to use the command line tool sqlite?

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.

yeah, I’m thinking that I could probably set a tag and if that tag has been set, change the sandbox mode.
But doing it manually is good enough for now.

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.

Can you query data that hasn’t been commited by a transaction?
That doesn’t work as far as I know.
Of course you can inspect it via the elixir tools, but not with the database tools.