1

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!

pablo.meier
  • 2,339
  • 4
  • 21
  • 29

2 Answers2

1

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.

JustAnotherSoul
  • 350
  • 3
  • 14
  • I appreciate this! I was trying to use the `create` interface to test more of the code (to serve as an integration test rather than a unit test), but in the absence of a way to force the queries to execute this is a solid solution. – pablo.meier Jan 02 '19 at 19:19
  • 1
    If you wanted it to be more of an integration test rather than a unit test, you might consider the alternative of ordering_by to take care of the case where multiple records are inserted at the same second. Glad it was helpful and good luck! – JustAnotherSoul Jan 03 '19 at 00:04
-1

You are testing Ecto and your SQL driver. You should not do that, it makes zero sense.

The only thing you might want to test here (although I am positive it’s redundant as well,) is that inserted_at field gets populated.

Ecto.Query.order_by/3 works; if you don’t trust it you’d better use some other library.

Aleksei Matiushkin
  • 119,336
  • 10
  • 100
  • 160
  • No, I'm testing the logic of a context function, which has a contract that one of its fields in its return values is sorted. This function is implemented with a substantial and complex SQL query, called with `Ecto.Adapters.SQL.query!`. I'm not using the query DSL for reasons specific to what it's trying to accomplish. – pablo.meier Jan 02 '19 at 19:15
  • Downvoting because this is a bad answer. It is perfectly valid to be testing that a function is supposed to order by a particular attribute. – Pulkit Goyal Dec 24 '20 at 04:12