Map a Postgrex Row to a Struct

Here's how to map a Postgrex wow to a struct.

Postgrex Rows

Postgrex is a postgres driver for Elixir. It's great for running raw sql against your Postgres database. Your selection/projection gets returned as rows/columns. The columns are returned as a list of strings. The rows are returned as a list of a list of various value types, depending on the database column type. What we want is to convert this data into a list of structs.

Our Struct

Let's say we have a struct for a book:

defmodule Book do
  defstruct title: "", author: "", publish_date: ~D[0000-01-01]

Map the Result

Let's say that our query is selecting these same values, and we need to map the result. We'll use Postgrex.query!, but note that you can access the same functionality in Ecto via Ecto.Adapters.SQL.query!

Postgrex.query!(
  MyProject.Repo,
  "select title, author, publish_date from books",
  []
)
|> case do
  %Postgrex.Result{columns: columns, rows: rows} ->
    column_atoms = Enum.map(columns, &String.to_atom/1)
    books =
      rows
      |> Enum.map(fn row ->
        map_row(column_atoms, row)
      end)

    {:ok, books}

  _ ->
    {:error,  "Failed to query books"}
end

# ...

defp map_row(column_atoms, row) do
  book_keywords = Enum.zip(column_atoms, row)
  book = struct!(MyProject.Book, book_keywords)
  book
end

In the end, we have a list of Book structs. How is it done?

1. First, we destructure the Postgrex.Result to access the columns and rows.
2. Next, we convert the columns (a list of strings) to a list of atoms.
3. We loop through each row, mapping each
4. For each row, we create a keyword list. For instance, book_keywords = [{title: "War and Peace"}, {author: "Leo Tolstoy"}, {publish_date: ~D[1867-07-04]}].
5. We pass this keyword list and the struct definition to the struct! function, which is a built-in global.

And there you have it. 73 steps later, a list of structs.