Test data is persisting making the tests non-repeatable

Questions / Help
, ,
1

I’m using Phoenix (1.2.1) and having trouble in my test environment. The problem is that the data being inserted (updated, deleted, etc) across tests are being persisted in the test database making my testing non-repeatable.

First Run:

root@d7594ec7baf0:/mnt/sidecar_api# mix test
................................
Finished in 2.2 seconds

32 tests, 0 failures

Randomized with seed 180820

Second Run:

root@d7594ec7baf0:/mnt/sidecar_api# mix test
...........................

  1) test user signup with POST /v1/users with valid attributes (SidecarApi.V1.UserControllerTest)
     test/controllers/v1/user_controller_test.exs:15
     Assertion with == failed
     code: response == expected
     lhs:  %{"jsonapi" => %{"version" => "1.0"}, "data" => %{"attributes" => %{"first-name" => "Juan", "last-name" => "Dela Cruz", "primary-email" => "jdelacruz@example.com", "primary-mobile-number" => "639123456789", "username" => "jdelacruz"}, "type" => "user", "id" => "2"}}
     rhs:  %{"jsonapi" => %{"version" => "1.0"}, "data" => %{"attributes" => %{"first-name" => "Juan", "last-name" => "Dela Cruz", "primary-email" => "jdelacruz@example.com", "primary-mobile-number" => "639123456789", "username" => "jdelacruz"}, "type" => "user", "id" => "1"}}
     stacktrace:
       test/controllers/v1/user_controller_test.exs:36: (test)

....

Finished in 2.3 seconds
32 tests, 1 failure

Randomized with seed 556822

The only relevant info in these instances is the object ID. I’m checking if the inserted record has the ID 1 which is my assumption on the state of the DB before the execution of all my tests.

Here’s my test.exs:

use Mix.Config

# We don't run a server during test. If one is required,
# you can enable the server option below.
config :sidecar_api, SidecarApi.Endpoint,
  http: [port: 4001],
  server: false

# Print only warnings and errors during test
config :logger, level: :warn

# Configure your database
config :sidecar_api, SidecarApi.Repo,
  adapter: Ecto.Adapters.Postgres,
  username: "sidecar",
  password: "sidecardb",
  database: "sidecar_test",
  hostname: "sidecar-db-main",
  pool: Ecto.Adapters.SQL.Sandbox

and test_helper.exs

ExUnit.start

Ecto.Adapters.SQL.Sandbox.mode(SidecarApi.Repo, :manual)

Note that after each tests, checking the content of postgres from psql itself is reflecting empty databases. Both for development and testing environments. Right now, I’m confused where to look at.

sidecar_dev=# select * from users;
 id | username | password | first_name | last_name | inserted_at | updated_at
----+----------+----------+------------+-----------+-------------+------------
(0 rows)

sidecar_test=# select * from users;
 id | username | password | first_name | last_name | inserted_at | updated_at
----+----------+----------+------------+-----------+-------------+------------
(0 rows)

Thanks in advance!

2

I’m guessing you’re hardcoding the id as 1 on your assertion? You can’t do that since even though the test deletes the data after running, the ids are not reverted back to 1, so subsequent tests will not start from 1.

In such cases, I usually assert that the id is there and it’s greater than 0.

Edit: If you really want such guarantee, note that you can update the “test” alias in mix.exs so that it calls ecto.drop after testing, effectively deleting the test DB altogether.

"test": ["ecto.create --quiet", "ecto.migrate", "test", "ecto.drop"]

I don’t know if there’s a downside to this approach, though.

2 Likes
3

You can’t do that since even though the test deletes the data after running, the ids are not reverted back to 1, so subsequent tests will not start from 1.

TIL. Thank you so much!

If you really want such guarantee, note that you can update the “test” alias in mix.exs so that it calls ecto.drop after testing, effectively deleting the test DB altogether.

Yeah sure, that’s what I have for several weeks (only mine is drop, create, migrate, test) however, I’m kind of annoyed by the unnecessary overhead which what I’m seeking in this question. Knowing that the auto-incrementer is not being reset across tests sums up this inquiry.

Additionally, if the incrementer would not reset per test, the problem would still persist when I do:
Test#1: Insert user with username “xyz123”. Expect ID == 1.
Test#2: Insert user with username “abc456”. Expect ID == 2.

And since our tests should be able to run [and must yield same result] no matter in what order it is being executed, I think asserting ID is not an ideal technique.

4

Yeah, I would question why you are making any assertions about generated IDs to begin with. Unless there are specific domain requirements around the way IDs are generated usually all you are actually concerned with is that ID uniqueness is enforced (which is very easy to write a test for). The manner in which the IDs are generated is typically an implementation detail.

1 Like
5

So there is absolutely no solution to reset auto increment sequence across test cases?

6

I suppose that you can wipe out data in tables, but primary keys sequence live and has been updated while DB itself is alive.

                   List of relations
 Schema |          Name           |   Type         |  Owner
------------+-------------------------+---------------+----------
 public     | users                     | table          | postgres
 public     | users_id_seq        | sequence   | postgres

any thoughts?)

7

It all depend on database you’re using. For example with postgres, you simply drop database like @bobbypriambodo wrote or simply reset sequence for your table “ALTER SEQUENCE your_sequence_name RESTART WITH 1;”

But I agree with what everyone wrote, relying in your tests on the ID of a row is a bad test design, because I doubt somehow that you implemented your own sequence generator.

2 Likes
8

Totally agree about relying on IDs is bad design, however it would be so easy to just assert on whatever JSON-API document a controller renders. Yes, I could (and probably will) check if there are ID fields and of the correct type and disregard the actual values, but isn’t a == comparison of two structs what would come to anyone’s mind first?

2 Likes
9

Youre probably right, but then again what comes first is often not the best solution. If you really want to check if id == 1 then do as @bobbypriambodo wrote, because it’s easiest solution, drop test database before each test :slight_smile:

10

How would you solve the above issue? (Creating a list of items using a factory, hitting the HTTP endpoint and expecting the parsed response body to match an Elixir struct)

11

Did you mean across test runs? Or test cases (ie. each test blocks should start from 1)?

I believe the latter could be done by calling Ecto.Adapters.SQL.query using the query as @sztosz said (“ALTER SEQUENCE…”) on a setup block since it will be called before each test, but it might put a performance loss since you’ll be running more SQL queries per test case, meaning more DB roundtrips. You could however run this “restart autoincrement” setup only for several test cases by using tags, therefore minimizing the impact.

If you mean across test runs (ie. mix test invocations) then you might drop the database before/after each test, like what’s already said in this thread.

I agree that intuitively doing this is easier to just assert with ==. If you want, you can also:

  1. assert that id exists on the map with Map.has_key?/2
  2. remove the id field from the map with Map.drop/2
  3. assert the remaining map with the expected result (having no ids) using ==.

It’s kinda roundabout, but it should work. From my POV, the bad thing of relying on hardcoded IDs is, if it spans over multiple test cases (eg. one test use id == 1, second test use id == 2), it means you can’t run the tests asynchronously. The tests depend on a global state, which is the autoincremented id in the db.

2 Likes
12

Thank you for taking your time to answer! (Also @sztosz, @madeinussr). In my case resetting the sequence really is the best way to go. I fully agree with not relying on IDs in principle (and not doing it often), but I’m also trying to keep complexity in check and this looks like a pretty good tradeoff for testing response JSON format in this specific scenario.

I guess I was using too much ember-cli-mirage when doing client side testing, that’s why I’m expecting to get a clean sequence for each test cases :slight_smile: This also the answer to your question, I was referring to test cases (test "" do ... end), not test runs.

2 Likes
13

After adding ecto.drop to the alias and executing MIX_ENV=test mix test:

** (Mix) The database for VehiclesService.Repo couldn't be dropped: ERROR 55006 (object_in_use) database "vehicles_service_test" is being accessed by other users

There are 10 other sessions using the database.
14

mix ecto.drop will by default drop your dev database

to drop the test database use
MIX_ENV=test mix ecto.drop
then simply use
mix test

15

The alias test is already run in :test environment and as such the correct database should be dropped.

The error message does look more like some other application that is connected to the same database for some reason.

@TOAST3R could you please tell us your database config from config/dev.exs and config/test.exs?

16

I’m having the same issue. I’m using postgres from a Docker container, and I think for some reason that might be the problem. If I don’t do that, and use something like this, it works fine.

MIX_ENV=test mix ecto.drop && mix test