SWAFQueries (swaf_queries v0.2.0)

SWAFQueries is a thin layer on top of Ecto and MyXQL driver that compiles raw SQL files into Elixir functions.

Overview

When used in a source file with a call to the __using__ macro, SWAFQueries searches for .sql files in a specific directory. Each .sql file is a regular SQL file with some meta information (contained in SQL comments, e.g.: lines stating with --) so that SWAFQueries will be able to create and compile every query present in the file into one or several Elixir function. All functions will belong to a module named after the name of the .sql file. Comments on top of the file will be treated as module's documentation. Comments before each query will be treated as function's documentation.

Quick start

Add the dependencies

To your application's mix.exs file add the following dependencies:

defp deps do
[
  {:swaf_queries,  "~> 0.1"},
  {:myxql, "~> 0.8.0"},
  {:ecto_sql, "~> 3.0"},,
  {:ex_doc, "~> 0.34", only: :dev, runtime: false},
]
end

:ex_doc is optional but as SWAFQueries generates Elixir documentation from SQL comments, it can be pretty useful.

Configure the database connection

First of all, we need a running MariaDB server. For development and tests (and only for those purposes), I use the following snippet to get a server up and running within seconds:

DBIMG="mariadb:10.10-jammy"
DB_NAME="my_app"
docker run -i --rm --name ${DB_NAME}_db \
    -v "$(pwd)/${DB_NAME}_data:/var/lib/mysql/" \
    -p "3306:3306" \
    -e MARIADB_DATABASE=${DB_NAME}_db \
    -e MARIADB_USER=${DB_NAME}_user \
    -e MARIADB_PASSWORD=${DB_NAME}_pass \
    -e MARIADB_ROOT_PASSWORD=${DB_NAME}_root_password \
    -d ${DBIMG}

Then use mycli to access the database in raw SQL (but any client will do the work):

mycli \
  -h 127.0.0.1 \
  -u ${DB_NAME}_user --password=${DB_NAME}_pass \
  ${DB_NAME}_db

Now that we have a running database server, edit the file config.exs and add the following parameters. These are the usual Ecto configuration parameters:

config :my_app, MyApp.Repo,
  adapter: Ecto.Adapters.MyXQL,
  database: "my_app_db",
  username: "my_app_user",
  password: "my_app_pass",
  hostname: "127.0.0.1",
  pool_size: 2,
  port: 3306

Create a repo module

As SWAFQueries uses Ecto.Repo, we need to define a repo.ex file which will simply contain the following:

defmodule MyApp.Repo do
    use Ecto.Repo,
    otp_app: :my_app,
    adapter: Ecto.Adapters.MyXQL
end

Create a queries module

MyApp.Queries will be the root module for all the sub-modules with the query functions. SWAFQueries will also inject in MyApp.Queries some utility functions that are used internally but that may also be used by the application.

defmodule MyApp.Queries do
  use SWAFQueries,
    sql_dir: "sql",
    repo_module: MyApp.Repo    
end

Both parameters are mandatory:

  • sql_dir: defines where the .sql files are located. As defined above, it will be in the root directory of the project;
  • repo_module: is the name of the module we have just define in the previous section.

Start the connection in the supervision tree

With Ecto, starting the connection means starting the repo module. In file lib/my_app/application.ex, add:

def start(_type, _args) do
  children = [
    MyApp.Repo      # <<< This line
  ]

Add SQL files to the SQL directory

Provided that you have a user table with a column names id, create the file sql/user.sql containing the following SQL code:

--- Queries to manage informations in the `user` table.

-- defq: get_user_by_id map: raw:
-- Searches the database for a user with `id` equal to `:id`
SELECT * FROM user WHERE id = :id

-- defq: get_users map: raw:
SELECT * FROM user

Compile and execute

Compile and run the REPL with iex -S mix. You'll the following output:

Erlang/OTP 27 [erts-15.2] [source] [64-bit] [smp:12:12] [ds:12:12:10] [async-threads:1] [jit:ns]
[...]
-- Counted 2 SQL files to compile from directory 'sql'
  -- Compiling queries from file 'sql/user.sql'
    -- Generating function 'get_users/0'
    -- Generating function 'get_users_m/0'
    -- Generating function 'get_user_by_id_m/1'
Generated test_app app
Interactive Elixir (1.18.1) - press Ctrl+C to exit (type h() ENTER for help)

You can now try the following:

iex(2)> MyApp.Queries.User.get_users_m
{:ok,
 [
   %{
     "created_at" => ~U[2025-10-11 10:32:32Z],
     "email" => "pierre@email.com",
     "id" => 1,
     "name" => "pierre",
     "updated_at" => ~U[2025-10-11 10:32:32Z]
   },
   %{
     "created_at" => ~U[2025-10-11 10:32:32Z],
     "email" => "paul@email.com",
     "id" => 2,
     "name" => "paul",
     "updated_at" => ~U[2025-10-11 10:32:32Z]
   }
 ]}

or

iex(3)> h MyApp.Queries.User.get_users_m

                             def get_users_m()                                

Returns a list of maps which represent records.

## SQL query sent to the server

     SELECT * FROM user

## Returns

  • {:ok, []} in case of no rows.
  • {:ok, list_of_maps} where list_of_maps is a list where every element is
    a map representing a record from the database. Keys of the maps are
    strings.
  • {:error, reason} in case of error.

Format of the SQL files

Even if the .sql files are regular SQL files, they need to respect a simple format to be understood by SWAFQueries. They basically have two sections:

  • The first section is whatever number of SQL comment lines with markdown text. All those comments will be gathered and added to the module's documentation. This section can be empty. From the above example, this section corresponds to:

    --- Queries to manage informations in the `user` table.

  • The second section is a list of one or more query definitions. Each definition may have two or three parts:

    • One line SQL comment starting with -- defq:. This line defines the base name of the functions to be created for the query that will be defined after. The defq: keyword may be followed by some hints telling SWAFQueries what kind of function to define. We'll see the details below. This line is obviously mandatory. From the above example, this part corresponds to:

      -- defq: get_user_by_id map: raw:

      The detailed format for this line is described below

    • The second part is optional. It may contain any number of SQL comment lines with markdown text. All those comments will be gathered and added to the function's documentation. From the above example, this part corresponds to:

      -- Searches the database for a user with `id` equal to `:id`

    • Finally comes the SQL code for the query. The SQL code can be split on any number of lines but must contain only one SQL statement (See the discussion in the section dedicated to transactions). This part is obviously mandatory. From the above example, this part corresponds to:

      SELECT * FROM user WHERE id = :id

Few things to notice:

  • Comments are regular Elixir documentation strings except that each line has to be prefixed with -- to also be a valid SQL comment. For example, a multi-line documentation with a list will look like this:

    -- This comment will be put in a documentation string.
--
-- This is the second paragraph of the documentation. Let's start make a list:
-- * first item
-- * second item

  • The only exception to SQL validity is the part id = :id. id is the name of a column, but :id is an identifier that will be used by SWAFQueries to create the parameter list of the function.

Query functions generation

Kind of generated functions

As stated previously, each query is introduced by a line with the defq: keyword which may be followed by some hints telling SWAFQueries what kind of function to define. The kind of function is basically what it returns and in which format. Hints are "commutable", meaning that one can declare multiple hints for the same query. In this case multiple functions will be generated. The hits are:

  • without any hint (--defq: <query_name>) or with the raw: hint (--defq: <query_name> raw:) SWAFQueries will generate the function named <query_name>(...) (well discuss the arity in the next section). This function will return the row result of the execution of the query:

    • {:ok, %MyXQL.Result{ ... }} on success
    • {:error, %MyXQL.Error{ ... }} on error

  • the map: hint (--defq: <query_name> map:) will make SWAFQueries generate the function named <query_name>(...)_m (notice the _m) that returns the result of the query as a list of maps, each map being a record from the database:

    • {:ok, []} in case of no record.
    • {:ok, list_of_maps} where list_of_maps is a list where every element is a map representing a record from the database. Keys of the maps are strings.
    • {:error, reason} in case of error.

    The map: hint is obviously useful for read-type queries with SELECT.

    For example:

    iex(12)> MyApp.Queries.User.get_users_m()
{:ok
 [
   %{
     "email" => "pierre@email.com",
     "id" => 1,
     "name" => "pierre",
     },
   %{
     "email" => "bjoorn@email.com",
     "id" => 2,
     "name" => "bjoorn",
   }
 ]
}

  • the stream: hint will make SWAFQueries generate the function named <query_name>(...)_s that returns the result of the query as a stream. The behaviour is the same as for the map: hint. stream: is intended for read-type queries with a huge amount of data returned.

    Notice that stream: is not implemented yet. If you use it or if you use is_read:, a warning will be generated during query compilation.

  • the result: hint (--defq: <query_name> result:) will make SWAFQueries generate the function named <query_name>(...)_r which returns a selected subset of information contained in the row query result. This hint is indented to be used with write-type queries like DELETE, INSERT or UPDATE. Returned values are:

    • {:ok, %{last_insert_id: id, num_rows: nb}} where:
      • last_insert_id is the ID of the last inserted element
      • num_rows is the number of rows affected by the query
    • {:error, %{message: message, statement: statement}} where:
      • message is the SQL error message
      • statement is the SQL statement which had been sent to the server
      • errcode is the error code returned by the database server

One can declare several hints for one function, which makes SWAFQueries generate all corresponding functions. For example, the definition

-- defq: get_users map: result: raw:

will generate the following functions:

get_users(...)
get_users_m(...)
get_users_r(...)

Arity of the generated functions

The arity of the generated functions is dependant on the SQL query. Basically, every parameter used in a query, will be considered as a parameter to the functions.

Here are some examples:

  • SELECT * FROM user: no parameter used here. The generated functions will have arity equal to zero.

    For example: get_users().

  • SELECT * FROM user WHERE id = :id : one parameter used, :id. The generated functions will have arity equal to one;

    For example: get_user_by_id(id).

  • SELECT * FROM user WHERE id = :id AND age > :age: two parameters used, :id and :age. The generated functions will have arity equal to two.

    For example: get_user_by_id_and_age(id, age).

  • SELECT user.name, address.town FROM user, address WHERE user.id = :id AND address.user_id = :id: one parameter used twice. Even if the parameter :id is used twice, the generated functions will have arity equal to one.

    For example: get_user_name_and_town_by_id(id).

Remember that Elixir is your friend in terms of documentation, either in the REPL or if you generate it with mix docs.

Transactions and multi-statement calls

At the moment, it is not possible to define transactions in a .sql file as this will generate multiple statements in a single call which the MyXQL driver does not allow. There a connection option, multiple_statements: which can be set to true but its purpose is limited and it does not work in our situation.

A query definition like:

-- defq: create_user_and_address result:
START TRANSACTION;
INSERT INTO user (name) VALUES (:name);
SET @last_user_id = LAST_INSERT_ID();
INSERT INTO address (user_id, town) VALUES (@last_user_id, :town);
COMMIT;

will compile and a the function create_user_and_address/2 will be create, but it will produce an error (output reformatted for readability):

iex(5)> MyApp.Queries.User.create_user_and_address_r("maurycy", "Paris")
{:error,
  %{
     message: "You have an error in your SQL syntax; check the manual that corresponds to 
               your MariaDB server version for the right syntax to use near: 
               'INSERT INTO user (name) VALUES ( ? ); SET @last_user_id = LAST_INSERT_ID(); I...' at line 1",
     statement: " START TRANSACTION; INSERT INTO user (name) VALUES ( ? ); 
                  SET @last_user_id = LAST_INSERT_ID(); 
                  INSERT INTO address (user_id, town) VALUES (@last_user_id,  ? ); COMMIT;",
     errcode: 1064
 }}

The actual solution is to use the transaction/2 function injected in the root module of the application (MyApp.Queries):

# for example in queries.ex file but it could be anywhere else
alias MyApp.Queries.User

def add_user(name, email, town) do
  transaction(fn ->
    with 
      {:ok, res0} <- User.insert_user_r(name, email),
      {:ok, _} <-User.insert_user_address_r(res0.last_insert_id, town) do
        {:ok, res0}
      end
  end, [timeout: 2_000])
end

Then, the call to MyAppApp.Queries.add_user("maurycy", "maurycy@email", "Paris") the first time will generate the output

{:ok, %{last_insert_id: 69, num_rows: 1}}

But a second call (and provided that there is a uniqueness constraint on the email) will produce the following error (which hopefully is expected):

{:error,
 %{
   message: "Duplicate entry 'maurycy@email' for key 'email'",
   statement: " INSERT INTO user (name, email) VALUES ( ? ,  ? )",
   errcode: 1062
}}

See the documentation of transaction/2 for more details.

Functions injected in queries root module

SWAFQueries injects a handful of functions to the root module it is called from (e.g. MyApp.Queries). They are all documented if you run mix docs. Quickly:

  • query(sql_request, values): executes the SQL request with the parameters from the values list. Returns

    • {:ok, %MyXQL.Result{ ... }} on success
    • {:error, %MyXQL.Error{ ... }} on error

  • query!(sql_request, values): same as query/2 but returns %MyXQL.Result{ ... } or raises an error on invalid queries.

  • transaction(fun, opts \ []): executes the function fun/0 in a transaction or rollbacks it upon error

  • rollback(value): Provokes a rollback of the current transaction and returns value

  • to_map(rows, cols): Zips a list of names of columns with a list of list of rows into a list of maps.

SQL code recompilation

Working in the REPL and recompiling the modified files of a project from within the REPL is really comfortable. SWAFQueries tries to make it possible by setting the @external_resource "<file.sql>" attribute when creating the corresponding module. However, for a newly created .sql file, the compiler has no way to know that this file exists before its first compilation. The rule of thumbs is:

  • If a .sql file that had already been compiled is modified, a recompile in the REPL will recompile it
  • If it's a newly created file, we need either to exit the repl and re-enter or to touch another .sql so that all .sql files will get recompiled.