# 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](https://hexdocs.pm/swaf_queries/0.2.0/api-reference.html)
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.

* `SWAFQueries` **does 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.

* 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](https://github.com/Kalli/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:
  ```sql
  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:
  ```sql
  -- /* 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](https://www.openmymind.net/Elixir-Without-Ecto/)
* [Yesql Github page](https://github.com/lpil/yesql)
* [AyeSQL Github page](https://github.com/alexdesousa/ayesql)
* [SQLYac](https://github.com/Kalli/sqlyac)

* Few days ago I came across a post on [Elixir
  forum](https://elixirforum.com/t/sqlkit-execute-raw-sql-in-strings-or-sql-files-get-maps-and-structs-back/73794)
  which presented yet another alternative developed recently,
  [SqlKit](https://github.com/tylerbarker/sql_kit) that is worth
  looking at.