AyeSQL is a library for using raw SQL.

Aye /ʌɪ/ exclamation (archaic dialect): said to express assent; yes.

Overview

Inspired by Clojure library Yesql, AyeSQL tries to find a middle ground between strings with raw SQL queries and SQL DSLs by:

• Keeping SQL in SQL files.
• Generating Elixir functions for every query.
• Supporting mandatory and optional named parameters.
• Allowing query composability with ease.
• Working out of the box with PostgreSQL using Ecto or Postgrex:
• Being extended to support other databases using the behaviour AyeSQL.Runner.

Small Example

Let's say we have a SQL query to retrieve the click count of a certain type of link every day of the last X days. In raw SQL this could be written as:

    WITH computed_dates AS (
           SELECT dates::date AS date
             FROM generate_series(
                    current_date - $1::interval,
                    current_date - interval '1 day',
                    interval '1 day'
                  ) AS dates
         )
  SELECT dates.date AS day, count(clicks.id) AS count
    FROM computed_dates AS dates
         LEFT JOIN clicks AS clicks ON date(clicks.inserted_at) = dates.date
   WHERE clicks.link_id = $2
GROUP BY dates.date
ORDER BY dates.date;

The equivalent query in Ecto would be:

dates = ~s(
SELECT generate_series(
         current_date - ?::interval,
         current_date - interval '1 day',
         interval '1 day'
       )::date AS d
)

from(
  c in "clicks",
  right_join: day in fragment(dates, ^days),
  on: day.d == fragment("date(?)", c.inserted_at),
  where: c.link_id = ^link_id
  group_by: day.d,
  order_by: day.d,
  select: %{
    day: fragment("date(?)", day.d),
    count: count(c.id)
  }
)

Using fragments can get convoluted and difficult to maintain. In AyeSQL, the equivalent would be to create an SQL file with the query e.g. queries.sql:

-- name: get_day_interval
SELECT datetime::date AS date
  FROM generate_series(
        current_date - :days::interval, -- Named parameter :days
        current_date - interval '1 day',
        interval '1 day'
      );

-- name: get_avg_clicks
-- docs: Gets average click count.
    WITH computed_dates AS ( :get_day_interval ) -- Composing with another query
  SELECT dates.date AS day, count(clicks.id) AS count
    FROM computed_date AS dates
        LEFT JOIN clicks AS clicks ON date(clicks.inserted_at) = dates.date
  WHERE clicks.link_id = :link_id -- Named parameter :link_id
GROUP BY dates.date
ORDER BY dates.date;

In Elixir, we would load all the queries in this file by creating the following module:

defmodule Queries do
  use AyeSQL, repo: MyRepo

  defqueries("queries.sql") # File name with relative path to SQL file.
end

or using the macro defqueries/3:

import AyeSQL, only: [defqueries: 3]

defqueries(Queries, "queries.sql", repo: MyRepo)

Both approaches will create a module called Queries with all the queries defined in queries.sql.

And then we could execute the query as follows:

iex> params = [
...>   link_id: 42,
...>   days: %Postgrex.Interval{secs: 864_000} # 10 days
...> ]
iex> Queries.get_avg_clicks(params)
{:ok,
  [
    %{day: ..., count: ...},
    %{day: ..., count: ...},
    %{day: ..., count: ...},
    ...
  ]
}

AyeSQL also allows you to choose the type of returned data structures. Instead of the default map you can also pass an into option to your query possible values are:

• an empty map: Map.new() or %{}
• an empty list: Keyword.new() or []
• a struct
• :raw which returns the unmodified Postgrex result

iex> Queries.get_avg_clicks(params, into: [])
{:ok,
  [
    [day: ..., count: ...],
    [day: ..., count: ...],
    [day: ..., count: ...],
    ...
  ]
}

iex> defmodule AvgClicks do defstruct [:day, :count] end
iex> Queries.get_avg_clicks(params, into: AvgClicks)
{:ok,
  [
    %AvgClicks{day: ..., count: ...},
    %AvgClicks{day: ..., count: ...},
    %AvgClicks{day: ..., count: ...},
    ...
  ]
}