Are changes to database within a test automatically reverted when the test is completed?


I want some help in understanding where the following magic is coming from. Please see the code below. In the second test below, the line Gamestatus.change_newplayersallowed(false, "game-1234") correctly changes the value of newplayersallowed to false. I know this because, one, the tests passes and, two, the output of IO.inspect is false. However, after the test is completed, when I check the value of newplayersallowed using psql, the value of newplayersallowed is true. So it appears that the change made by function Gamestatus.change_newplayersallowed(false, "game-1234") is reverted to the value before that change. How does Elixir/Phoenix know which changes to revert? Where can I read about which changes to the database within tests will be reverted?

Thank you.

defmodule Games.GamestatusTest do
  use GamesWeb.ConnCase
  # use ExUnit.Case

  alias Games.Gamestatus

  test "Returns the correct newplayerallowed status" do
    assert Gamestatus.newplayersallowed?("game-1234") == true

  test "Updates the newplayersallowed status" do
    Gamestatus.change_newplayersallowed(false, "game-1234")
    {status, _msg} = Gamestatus.newplayersallowed?("game-1234")
    IO.inspect(status, label: "newplayersallowed")
    assert status == false

end ## end module

The following is the code for the function Gamestatus.change_newplayersallowed.

  Change newplayersallowed to `set_to_val` for the given gameid
  def change_newplayersallowed(set_to_val, gameid) do
    sql = "UPDATE gamesstatus SET newplayersallowed = $1" <>
            " WHERE gameid = $2;"

      [set_to_val, gameid]


The short answer is: it depends.

It’s not uncommon to use the Ecto.Adapters.SQL.Sandbox adapter (Ecto.Adapters.SQL.Sandbox — Ecto SQL v3.8.3) for testing, part of what it does is wrap the tests in a big database transaction so it can be rolled back when done. Also, the internal transactional management of the database calls will play a factor, too… if they don’t commit, they’ll not be visible outside the transaction or be rolledback, etc.

I can tell you that without doing anything special such as using the Sandbox adapter that the database access in tests are just normal database accesses and will persist. In my testing setup, especially around integration tests, I expect database changes to be persistent and I have no trouble with getting that.

I’d probably need to see more about the configuration or whatnot to know why it might be going south for you.


The machinery that makes this work is the Ecto.Adapters.SQL.Sandbox

The short short version: there’s a SQL BEGIN before every test, and a ROLLBACK afterwards.


Thank you very much @sbuttgereit and @al2o3cr. That is great.

Is use GamesWeb.ConnCase in a Phoenix application ensuring the use of Sandbox adapter?

@sbuttgereit, I like the reverting of the database to its original condition (it is not going south for me). In fact, I was pleasantly surprised that I don’t need to revert the change manually. I just wanted to understand how it was happening.

Thank you again!

Possibly - you can check by looking for code similar to this setup block from the documentation:

  setup do
    # Explicitly get a connection before each test
    :ok = Ecto.Adapters.SQL.Sandbox.checkout(Repo)

checkout is what actually opens the transaction.

1 Like

Thank you for explaining this. I will check the source.

When I was using ExUnit.Case, I had to explicitly add that block for the tests to even work. Switching to GamesWeb.ConnCase allowed me to remove that block. So, probably that’s what it is.