Keep database state after failed test

Questions / Help
,
1

I’m using ecto with sqlite3. I was wondering if it’s possible to configure the ecto sandbox in such a way that the database is not cleaned up after a failed test, so that I can inspect the database.

2

I don’t think the database is cleaned after each test with sqlite3 sandbox, at least maybe I have something misconfigured, because I am clearing the database manually in data_case.ex after each test.

3

The ecto sandbox works with wrapping everything in a transaction, so data is never durably written to the db in the first place.

4

yes, I was wondering if there was an easy way to commit the transaction, so that I can inspect the data.

5

for me it is cleaned if I use the sandbox.

6

There is no concept of rollback transactions in ecto sqlite3, at least this is what I understood from @warmwaffles .

7

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:

1 Like
8

That’s some nice info, but I don’t think it helps. Because nothing in there is about commiting the transaction.

I think the easiest way to do this (for sqlite), if anyone is interested:

  1. remove use Module.DataCase from your test
  2. add something like @tag :single to the test you want to run
  3. run the test with mix test --only single
  4. don’t forget to delete the test database after you’re done and add the DataCase again.
9

The point of using IE.pry is that the transaction is still alive as you can set a breakpoint so you can do whatever inspection you want, check values, even run db queries in the context of the active transaction.

10

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.

11

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.

1 Like
12

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.

13

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.

1 Like
14

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?

15

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.

2 Likes
16

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.

1 Like
17

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.

18

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.

19

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.

4 Likes