# Querying raw SQL with Elixir and Ecto

TL;DR:

```elm
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`](https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.html#query/4) . The result object we get back is a `%Postgrex.Result{}` struct that has the rows as a flat two-dimensional array:

```elm
%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`](https://hexdocs.pm/ecto/Ecto.Repo.html#c:load/2) function to map our rows into the proper ecto schema (`Animal` in the example above) so we can work with them as normal.