I'd like to write tests to validate that the SQL queries in my application return data adhering to certain constraints: namely, that return values are presented in descending insertion order. In the application, I'm using timestamps() in my schemas to include an inserted_at field to the DB, and querying against it in my queries (SELECT … ORDER BY inserted_at DESC).
My trouble comes in my tests: if I have test code like
person1_params = %{name: "Tidehunter"}
{:ok, tidehunter} = People.create_person(person1_params)
person2_params = %{name: "Kunkka"}
{:ok, kunkka} = People.create_person(person2_params)
person3_params = %{name: "Windrunner"}
{:ok, windrunner} = People.create_person(person3_params)
and I'd like to assert on their order, like
people = People.get_in_order_of_recency()
assert Enum.at(people, 0).name == "Windrunner"
this fails, even though in manual tests it all appears to work. Upon inspection, I see that inserted_at for all three records is identical. I've tried adding :timer.sleep() calls, but it doesn't change the outcome, suggesting some batching or laziness at the Ecto/Postgrex layer.
The "simplest" solution I could think of would be some way to "force" a transaction to occur at the call site, so I could :timer.sleep(1) between them, giving me distinct inserted_at fields (thus the question title) but at the risk of XY problem I'm open to other suggestions. Thanks!
Since it seems that you are trying to test the get_in_order_of_recency method as opposed to the datetime features of your database/ecto, you could alias <MYPROJ>.Repo and then do something like:
Repo.insert!(%People{inserted_at: ~N[2019-01-01 00:00:10], name: "Tidehunter"})
Repo.insert!(%People{inserted_at: ~N[2019-01-01 00:00:11], name: "Kunkka"})
Repo.insert!(%People{inserted_at: ~N[2019-01-01 00:00:12], name: "Windrunner"})
In your test instead of using the create interface. Which would allow you to verify that your method is correctly retrieving the people in the order you want.
As an alternative, consider the test failure legitimate. Your desired ordering is not maintained when multiple records are introduced simultaneously. As a remedy add the id as a second column to order by in descending order which will enforce the order you're looking for.