Testing raw sql queries in elixir

Question

I'm trying to unit test a raw sql query but not getting any results. My test does a number of inserts like:

Repo.insert!(%Inquiry{<fields>})

My inquiries select part does truncating of a datetime to a date and some other stuff which is why I have it setup as a raw inquiry but in attempting to get this to work, I've just simplified it to something like the following:

query = "SELECT * FROM inquiries"
results = Ecto.Adapters.SQL.query(Repo, query, [])

In my test this does not return any rows but a command such as the following does:

Repo.all(Inquiry)

I've tried to setup to share the connection but it doesn't work. Any secret to getting this to work?

setup do
  :ok = Ecto.Adapters.SQL.Sandbox.checkout(Repo)
  Ecto.Adapters.SQL.Sandbox.mode(Repo, {:shared, self()})
end

Answer 1

You can issue raw SQL queries through the database adapter (most commonly Ecto.Adapters.SQL for PostGreSQL). For example:

query = """
  SELECT id, name, inserted_at
  FROM users
  UNION
    SELECT id, name, inserted_at
    FROM customers
    WHERE name = $1
"""

Ecto.Adapters.SQL.query!(MyApp.Repo, query, ["Carol"])

If you don't have any positional bind variables (e.g. $1), the 3rd argument can be an empty list [].

To test these queries, you should be able to execute them manually in your SQL editor.

Note that the shape of the result is different than what you would get via regular Ecto query. Sample results might look like

%Postgrex.Result{
  columns: ["foo"],
  command: :select,
  connection_id: 22172,
  messages: [],
  num_rows: 2,
  rows: [
    ["10.5240/C895-1116-F0F4-68A3-FEDC-2"],
    ["10.5240/5063-763E-052E-BF58-693C-5"]
  ]
}