AyeSQL

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

AyeSQL is a library for using raw SQL.

Overview

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

• Keep SQL in SQL files.
• Generate easy to use Elixir functions for every query.
• Parameterize queries using maps and keyword lists.
• Allow query composablity.
• Work out-of-the-box with PostgreSQL using Ecto or Postgrex.
• Work out-of-the-box with DuckDB using Duckdbex.

If you want to know more about AyeSQL:

• Small example

• Syntax

  • Naming queries
  • Parameters
  • Mandatory parameters
  • Query composition
  • Optional fragments
  • IN statement
  • Subqueries and subfragments

• Installation

And the following additional links provide more information about the library:

• Full Documentation
• AyeSQL: Writing Raw SQL in Elixir
• Why raw SQL?
• Dynamic queries with EEx
• Adding support to other databases

Small Example

In AyeSQL, the equivalent would be to create an SQL file with the query e.g. queries.sql:

-- file: queries.sql
-- name: get_avg_clicks
-- docs: Gets average click count.
    WITH computed_dates AS (
      SELECT datetime::date AS date
      FROM generate_series(
        current_date - :days::interval, -- Named parameter :days
        current_date - interval '1 day',
        interval '1 day'
      )
    )
  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:

# file: lib/queries.ex
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:

# file: lib/queries.ex
import AyeSQL, only: [defqueries: 3]

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

Note: The file name used in defqueries macro should be relative to the file where the macro is used.

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: ...},
    ...
  ]
}

Syntax

An SQL file can have as many queries as you want as long as they are named.

For the following sections we'll assume we have:

• lib/my_repo.ex which is an Ecto repo called MyRepo.

• lib/queries.sql with SQL queries.

• lib/queries.ex with the following structure:

  import AyeSQL, only: [defqueries: 3]
  
  defqueries(Queries, "queries.sql", repo: MyRepo)

Naming Queries

For naming queries, we add a comment with the keyword -- name: followed by the name of the function e.g the following query would generate the function Queries.get_hostnames/2:

-- name: get_hostnames
SELECT hostname FROM server

Additionally, we could also add documentation for the query by adding a comment with the keyword -- docs: followed by the query's documentation e.g:

-- name: get_hostnames
-- docs: Gets hostnames from the servers.
SELECT hostname FROM server

Important: if the function does not have -- docs: it won't have documentation e.g. @doc false.

Parameters

There are two types of parameters:

• Mandatory: for passing parameters to a query. They start with : e.g. :hostname.
• Optional: for query composability. They start with :_ e.g. :_order_by.

Additionally, any query in a file can be accessed with its name adding : at the front e.g :get_hostnames.

Mandatory Parameters

Let's say we want to get the name of an operative system by architecture:

-- name: get_os_by_architecture
-- docs: Gets operative system's name by a given architecture.
SELECT name
  FROM operative_system
 WHERE architecture = :architecture

The previous query would generate the function Queries.get_os_by_architecture/2 that can be called as:

iex> Queries.get_os_by_architecture(architecture: "AMD64")
{:ok,
  [
    %{name: "Debian Buster"},
    %{name: "Windows 10"},
    ...
  ]
}

Query Composition

Now if we would like to get hostnames by architecture we could compose queries by doing the following:

-- name: get_os_by_architecture
-- docs: Gets operative system's name by a given architecture.
SELECT name
  FROM operative_system
 WHERE architecture = :architecture

-- name: get_hostnames_by_architecture
-- docs: Gets hostnames by architecture.
SELECT hostname
  FROM servers
 WHERE os_name IN ( :get_os_by_architecture )

The previous query would generate the function Queries.get_hostnames_by_architecture/2 that can be called as:

iex> Queries.get_hostnames_by_architecture(architecture: "AMD64")
{:ok,
  [
    %{hostname: "server0"},
    %{hostname: "server1"},
    ...
  ]
}

Optional Fragments

Let's say that now we need to order ascending or descending by hostname by using an optional :_order_by parameter e.g:

-- name: get_os_by_architecture
-- docs: Gets operative system's name by a given architecture.
SELECT name
  FROM operative_system
 WHERE architecture = :architecture

-- name: get_hostnames_by_architecture
-- docs: Gets hostnames by architecture.
SELECT hostname
  FROM servers
 WHERE os_name IN ( :get_os_by_architecture )
 :_order_by

-- name: ascending
ORDER BY hostname ASC

-- name: descending
ORDER BY hostname DESC

The previous query could be called as before:

iex> Queries.get_hostnames_by_architecture(architecture: "AMD64")
{:ok,
  [
    %{hostname: "Barcelona"},
    %{hostname: "Granada"},
    %{hostname: "Madrid"},
    ...
  ]
}

or by order ascending:

iex> params = [architecture: "AMD64", _order_by: :ascending]
iex> Queries.get_hostnames_by_architecture(params)
{:ok,
  [
    %{hostname: "Barcelona"},
    %{hostname: "Madrid"},
    %{hostname: "Granada"},
    ...
  ]
}

or descending:

iex> params = [architecture: "AMD64", _order_by: :descending]
iex> Queries.get_hostnames_by_architecture(params)
{:ok,
  [
    %{hostname: "Zaragoza"},
    %{hostname: "Madrid"},
    %{hostname: "Granada"},
    ...
  ]
}

Important: A query can be called by name e.g. :descending if it's defined in the same SQL file. Otherwise, we need to pass the function instead e.g. Queries.descending/2

iex> params = [architecture: "AMD64", _order_by: &Queries.descending/2]
iex> Queries.get_hostnames_by_architecture(params)
{:ok,
  [
    %{hostname: "Zaragoza"},
    %{hostname: "Madrid"},
    %{hostname: "Granada"},
    ...
  ]
}

IN Statement

Lists in SQL might be tricky. That's why AyeSQL supports a special type for them e.g:

Let's say we have the following query:

-- name: get_os_by_hostname
-- docs: Gets hostnames and OS names given a list of hostnames.
SELECT hostname, os_name
  FROM servers
 WHERE hostname IN (:hostnames)

It is possible to do the following:

iex> params = [hostnames: {:in, ["server0", "server1", "server2"]}]
iex> Server.get_os_by_hostname(params)
{:ok,
  [
    %{hostname: "server0", os_name: "Debian Buster"},
    %{hostname: "server1", avg_ram: "Windows 10"},
    %{hostname: "server2", avg_ram: "Minix 3"}
  ]
}

Subqueries and Subfragments

Subqueries can be composed directly, as show before, or via the :inner tuple e.g. let's say we need to get the adults order by name in ascending order and age in descending order:

-- name: ascending
ASC

-- name: descending
DESC

-- name: by_age
age :order_direction

-- name: by_name
name :order_direction

-- name: get_adults
-- docs: Gets adults.
SELECT name, age
  FROM person
 WHERE age >= 18
ORDER BY :order_by

Then our code in elixir would be:

iex> order_by = [
...>   by_name: [order_direction: :ascending],
...>   by_age: [order_direction: :descending]
...> ]
iex> Queries.get_adults(order_by: {:inner, order_by, ", "})
{:ok,
  [
    %{name: "Alice", age: 42},
    %{name: "Bob", age: 21},
    ...
  ]
}

Note: If you're using this level of composability and it fits your use case, consider using either:

• Ecto
• EEx templates

Installation

AyeSQL is available as a Hex package. To install, add it to your dependencies in your mix.exs file:

def deps do
  [{:ayesql, "~> 1.1"}]
end

If you're going to use any of the provided query runners, then you should add their dependencies as well:

• Add :ecto_sql for AyeSQL.Runner.Ecto (default runner).
• Add :postgrex for AyeSQL.Runner.Postgrex.
• Add duckdbex for AyeSQL.Runner.Duckdbex.
• Add :ecto_sql and :postgrex for running queries using Ecto in a PostgreSQL database.

Author

Alexander de Sousa.

License

AyeSQL is released under the MIT License. See the LICENSE file for further details.