Querying raw SQL with Elixir and Ecto

TL;DR:

alias MyApp.{Repo, Animal}

sql = "SELECT * FROM animals WHERE legs > $1"
params = [2]

%{rows: rows, columns: columns} = Repo.query!(sql, params)

Enum.map(rows, fn row -> Repo.load(Animal, {columns, row}) end)

It was surprisingly difficult to find an easy solution on google for querying your database using raw SQL with ecto and elixir. Above is a short snippet that runs a query with parameters, and then loads the result into an ecto schema.

Repo.query! is an alias for the underlying db driver you are using, in my case with postgres the function calls Ecto.Adapters.SQL.query . The result object we get back is a %Postgrex.Result{} struct that has the rows as a flat two-dimensional array:

%Postgrex.Result{
  command: :select,
  columns: ["id", "name", "legs"],
  rows: [
    [1, "cat", 4],
    [2, "dog", 4],
  ],
  num_rows: 2,
  connection_id: 147,
  messages: []
}

but this isn't very nice to work with, so we use the handy Repo.load function to map our rows into the proper ecto schema (Animal in the example above) so we can work with them as normal.