Elixir Without Ecto

Feb 05, 2022

In this post we'll explore using PostgreSQL directly from an Elixir (or Phoenix) application. I want to keep this post objective, but I will say that, having used various data mapping patterns and tools, I've found that using SQL directly almost always results in simpler yet more flexible code. The only case where I'd consider a data mapping layer is if I had to support multiple databases (e.g. PostgreSQL, MySQL, Sqlite) within the same codebase.

Getting started is easy:

Add a dependency to Postgrex (the same driver used by Ecto)
Use Postgrex.start_link to start a named connection process, probably as part of your root/application supervisor
Use Postgrex.query/4, Postgrex.query!/4 and Postgrex.transaction/3

Let's look at a basic example:

defmodule MyApp do
  use Application

  def start(_type, _args) do
    # Probably want to store this in a config
    # and load via Application.fetch_env!(:myapp, :db)
    db_config = [
      name: :mydb,
      hostname: "127.0.0.1",
      database: "postgres",
    ]

    children = [
      {Postgrex, db_config},
      ...
    ]

    Supervisor.start_link(children, [strategy: :one_for_one, name: __MODULE__])
  end
end

The above, a fragment of what your application's root supervisor would look like, starts a connection manage named :mydb, which we can use like so:

Postgrex.query!(:mydb, "select * from table where id = $1", [id])

You can find a list of configuration options in the documentation, but the most useful options are: name, hostname, database, username, password and pool_size.

I also recommend that you also set the application_name of the parameters field:

name: :mydb,
hostname: "127.0.0.1",
database: "postgres",
parameters: [application_name: "my_app"]

Which is used by PostgreSQL for various things (such as the application_name column of the pg_stat_activity view).

Helper Functionality

The above is all we need to work with PostgreSQL directly. But we can make this more developer-friendly. Specifically, we can use a base module to encapsulate the code to more easily manage the connection name, as well as provide wrappers to the query function for specific patterns (like returning a single column from a single row). As a start, consider:

defmodule A.DB do
  defmacro __using__(_) do
    quote location: :keep do
      @name __MODULE__

      def child_spec(opts) do
        %{
          id: __MODULE__,
          start: {__MODULE__, :start_link, opts}
        }
      end

      def start_link(config) do
        defaults = [name: @name, pool_size: 5]
        config = Keyword.merge(defaults, config)
        Postgrex.start_link(config)
      end

      def query!(sql, values) do
        Postgrex.query!(@name, sql, values)
      end
    end
  end
end

It's a decent amount of code, but it doesn't do very much (yet). To use it, we create a new module and use A.DB:

defmodule MyApp.DB do
  use A.DB

  # Feel free to add other app/db-specific things in here
end

Which can then be started in application's supervisor (instead of starting Postgrex directly, as we first saw):

children = [
  {MyApp.DB, db_config},
  ...
]

Finally, we can use MyApp.DB.query!(sql, values) in our code.

You can add application-specific code to the MyApp.DB module. But there's also more generic code we can add to the base A.DB. Right now our query!/2 is a thin wrapper around Postgrex.query!, which returns rows and columns. But you probably execute code that: needs to know the number of affected rows (for updates and deletes), return a single value or a single row, or maybe returns the row(s) as map(s). All of these can be trivially added to A.DB:

# our existing query!
def query!(sql, values) do
  Postgrex.query!(@name, sql, values)
end

# For queries that return 1 row with 1 column
def scalar!(sql, values) do
  case query!(sql, values) do
    %{rows: []} -> nil
    %{rows: [[value]]} -> value
    %{rows: _} ->
      # maybe log sql and values?
      raise "scalar returned multiple columns and/or rows"
  end
end

# For queries that return 1 row
def row!(sql, values) do
  case query!(sql, values) do
    %{rows: []} -> nil
    %{rows: [row]} -> row
    _ ->
      # maybe log sql and values?
      raise "row! returned multiple rows"
  end
end

# For queries that return mulitple rows
def rows!(sql, values), do: query!(sql, values).rows

# number of affected rows (generally used for updates or deletes)
def affected!(sql, values), do: query!(sql, values).num_rows

# For queries that return 1 row, which we want to turn into a map
def map!(sql, values) do
  case query!(sql, values) do
    %{rows: []} -> nil
    %{rows: [row], columns: columns} -> mapify(columns, row)
    _ ->
      # maybe log sql and values?
      raise "map! returned multiple rows"
  end
end

# For queries that return multiple rows, which we want to turn into an array of maps
def maps!(sql, values) do
  case query!(sql, values) do
    %{rows: []} -> nil
    %{rows: rows, columns: columns} ->
      Enum.map(rows, fn row -> mapify(columns, row) end)
    _ ->
      # maybe log sql and values?
      raise "maps! returned multiple rows"
  end
end

defp mapify(columns, row) do
  columns
  |> Enum.zip(row)
  |> Map.new()
end

The map(s) returned from map!/2 and maps!/2 have string-based keys, based on the column names being returned. You might want to atomify those keys, which is fine, so long as your column names aren't dynamic and you understand that atom's aren't garbage collected. You can change the above code to always use atom keys, or you can make it an optional argument to the functions.

The final piece that we're missing are transactions. So far, the first parameter that we've passed to the Postgrex.query! function has been the name of our connection (i.e. @name which is our module name, MyApp.DB in this case). However, this function also accepts a connection value created by Postgrex.transaction. We can use this and make all of our above function either take a connection object directly, or default to using @name. So, query! becomes:

def query!(sql, values), do: query!(@name, sql, values)
def query!(conn, sql, values, opts \\ []) do
  Postgrex.query!(conn, sql, values, opts)
end

And scalar! becomes:

def scalar!(sql, values), do: scalar!(@name, sql, values)
def scalar!(conn, sql, values, opts \\ []) do
  case query!(conn, sql, values, opts)  do
    %{rows: []} -> nil
    %{rows: [[value]]} -> value
    %{rows: unknown} ->
      Log.error("scalar!", sql: sql, values: values)
      raise "scalar returned multiple columns and/or rows"
  end
end

We can repeat this pattern for each function, and, finally, add a thin wrapper for Postgrex.transaction:

def transaction!(fun, opts \\ []) do
  case Postgrex.transaction(@name, fun, opts) do
    {:ok, res} -> res
    {:error, :rollback} -> raise "transaction! rollback"
    err -> err
  end
end

Which we can then use like so:

alias MyApp.DB

DB.transaction!(fn tx ->
  if DB.affected!(tx, "update users set status = 'deleted' where id = $1", [id]) == 1 do
    DB.query!(tx, "insert into audit_log ....", ...)
  end
end, [timeout: 30_000]) # optional parameters to pass to Postgrex

This gist contains the full code. You should keep in mind that this is only meant as a base template. You should personalize and optimize this to fit your needs. In the next part, we'll look at writing a simple query object, which will help use write safe dynamic SQL while staying relatively close to raw SQL.