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.
Support loading from multiple files or glob patterns.
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
Multi-file support
Syntax
Installation

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

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

Multi-file Support

For larger projects, you can organize your SQL queries across multiple files and load them all at once. AyeSQL supports both explicit file lists and glob patterns.

Loading Multiple Files

You can pass a list of file paths to defqueries:

defmodule MyApp.Queries do
  use AyeSQL, repo: MyApp.Repo

  defqueries([
    "sql/users.sql",
    "sql/posts.sql",
    "sql/comments.sql"
  ])
end

Using Glob Patterns

Or use glob patterns to automatically load all matching files:

defmodule MyApp.Queries do
  use AyeSQL, repo: MyApp.Repo

  # Load all .sql files in the sql/ directory and subdirectories
  defqueries("sql/**/*.sql")
end

This is particularly useful for organizing queries by domain or feature:

sql/
├── orders/
│   ├── reads.sql
│   └── writes.sql
├── clients/
│   └── clients.sql
└── payments/
    └── payments.sql

Multi-file Behavior

When using multiple files:

Alphabetical ordering: Files are processed in alphabetical order by their full path, ensuring deterministic and predictable behavior.
Unique query names: All query names must be unique across all loaded files. If duplicate names are found, a compile-time error will be raised with details about which files contain the duplicates.
Cross-file composition: Queries can reference other queries from any loaded file using the :query_name syntax, enabling composition across your entire query library.
Recompilation tracking: Each file is registered as an @external_resource, so your module will automatically recompile when any SQL file changes.

Example with the `defqueries/3` Macro

The standalone macro also supports multi-file loading:

import AyeSQL, only: [defqueries: 3]

# List of files
defqueries(Queries, ["sql/users.sql", "sql/posts.sql"], repo: MyRepo)

# Glob pattern
defqueries(Queries, "sql/**/*.sql", repo: MyRepo)

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.

Next Page → Why Raw SQL