SWAFQueries

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

Installation

The package can be installed by adding swaf_queries to your list of dependencies in mix.exs:

 defp deps do
    [
      # {:swaf_views, path: "../swaf_queries"},
      {:swaf_queries, "~> 0.2"}
    ]
  end

See Hexdocs for a full documentation.

Features

• Define as many queries as needed per .sql file. All the queries in a file will be compiled into Elixir functions and will belong to a module named after the name of the .sql file. For the moment sub-directories are not taken into account but this is on the TODO list.

• Queries get compiled from within the REPL without need to leave IEx and explicitly recompile with mix compile or iex -S mix with the exception for new files (see the documentation).

• Elixir documentation is generated out of the SQL comments for the module and for the generated functions. Use mix docs in the project.

• Parameters of generated functions are the parameters of the query as specified in SQL.

• SWAFQueries works with MySQL and MariaDB through the MyXQL driver and uses all the goodies provided by Ecto. Integration with Postgrex is on the TODO list.

• SWAFQueriesdoes not parse, interpret nor optimize the SQL code.

SWAFQueries is based on Ecto which are fantastic pieces of code that implement many goodies. The main ones:

• Prepared statement caching for better performance and security

• Automated transaction management

Todo

Interested to give a hand? Welcome, be my guest and contact me.

Known bugs to be corrected

• If there is a comment inside a multi-line request, this comment is taken as a documentation comment while it should be ignored

Features

• P2: add some helper functions like

  • last_inserted_id() which returns the last ID (calls query("SELECT LAST_INSERT_ID() as id") under the hood).

• P1: manage multi query queries like

  Begin
    query_1
    query_n
  Commit

• P1: Create comments that will not be interpreted as @doc@moduledoc comments. For example

  -- ** this comment will be totally ignored wherever it is located

• P0: add the stream: hint for defq:

• P0: manage directory hierarchies so that if the file user.sql is located in sql/auth/user.sql, queries will belong to the module Auth.User.

• P0: implement additional hints

  • bang variants of hints (really necessary ?). raw!:, map!:, stream!:, result!: would return the results directly on success or raise an error otherwise rather than returning result tuples.
  • all: would generate all four raw:, map:, stream:, result:

• P0: Write a blog and improve documentation

• P0: See how to avoid creation of Repo.ex. Probably need to include it in Queries.ex ?

• P1: Add support for Postgrex and possibly to some other drivers. Needs to be thought throughout as for the moment the code is really tied with MyXQL driver (positional parameters, returned results, ...)

• P1: Add an URL to access the DB rather than all the parameters.

• P2: Parameterize query functions with maps and/or keyword lists (really useful?). For example: my_query(%{name: "marco", lastname: "polo"}) or my_query([name: "marco", surname: "polo"])

• P1: Add a compile(sql, {m, f, a}, hints \\ []) function which will compile sql, an SQL string, and create a function f with arity a in the module m for use cases not covered by SWAFQueries.

• P2 : See SQLYac and see if it might be possible to create mix tasks on the same model that could be used from the command line. For example:

  mix sql sql/user.sql get_user_by_id 42

• P1: Global comments to comment out (and ignore) whole parts of code (for now all comments are used as documentation. For example:

  -- /* This is the start of a global comment
  -- defq: some_query
  SOME SQL STATEMENTS
  -- */

  All what is between -- /* and -- */ is ignored.

  Possibly add a single line comments introduced with -- //.

• P2: In function documentation, generate the code of the query as it is formatted in the SQL file

• P2: -- no_moduledoc:: do not generate any module doc, even if there are global SQL comments

• P2: -- no_doc: do not generate function documentation, even if there are query level SQL comments

Motivation

My personal reason is that I don't like Ecto DSL. Please, understand me. Ecto is a great piece of code and SWAFQueries relies on it, but it's just too much for my needs. I'm not (any more) a professional developer and I'm working on one, maybe two side projects per year that require a database. Every time I try Ecto, I feel like I need to re-learn a new language, which means new syntax, debugging, etc. while the only thing I actually need to do is to send a SELECT or an INSERT to a database. Yet, I'm definitely not some kind of pro-pure-SQL extremist and I love having help from my development stack like connection management, security, optimization.

There are some other good and more general reasons to use SQL directly:

• While the developers may be proficient in Elixir, it might not be the case with the DBA: it might not be simple to optimize a query written in Ecto DSL

• When working on large projects, same requests may be required by different parts of the system not all written in Elixir.

• SQL has excellent tooling and dedicated IDE that require to use SQL

• One argument I've often read about Ecto is that it abstracts database access and, thus, it allows easy swap from one SQL dialect to another one. I'm not really sure such use cases happen often in real life.

All that put together, I started to look around how the others were doing, to write some macros that inject helpers and it finished with SWAFQueries.

Sources of inspiration and alternatives

• Elixir Without Ecto

• Yesql Github page

• AyeSQL Github page

• SQLYac

• Few days ago I came across a post on Elixir forum which presented yet another alternative developed recently, SqlKit that is worth looking at.