Yesql

Yesql is an Elixir library for using SQL.

Rationale

You're writing Elixir You need to write some SQL.

One option is to use Ecto, which provides a sophisticated DSL for generating database queries at runtime. This can be convenient for simple use, but its abstraction only works with the simplest and common database features. Because of this either the abstraction breaks down and we start passing raw strings to Repo.query and fragment, or we will neglect these database features altogether.

So what's the solution? Keep the SQL as SQL. Have one file with your query:

Usage

SELECT *
FROM users
WHERE country_code = :country_code

...and then read that file to turn it into a regular Elixir function at compile time:

defmodule Query do
  use Yesql, driver: Postgrex, conn: MyApp.ConnectionPool

  Yesql.defquery("some/where/select_users_by_country.sql")
end

# A function with the name `users_by_country/1` has been created.
# Let's use it:
iex> Query.users_by_country(country_code: "gbr")
{:ok, [%{name: "Louis", country_code: "gbr"}]}

Alternatively, provide multiple queries inside a file in the following format:

-- name: select_older_cats
-- Important to find old cats
SELECT * FROM cats
WHERE age > :age
ORDER BY age ASC

-- name:insert_cat
INSERT INTO cats (age)
VALUES (:age)

Each section begins with a name declaration, followed by optional documentation that will be compiled into the function docs

defmodule QueryMany do
  use Yesql, driver: Postgrex, conn: MyApp.ConnectionPool

  Yesql.defqueries("some/where/feline_queries.sql")
end

# A function with the name `insert_cat/1` has been created.
# Let's use it:
iex> Query.select_older_cat(age: 14)
{:ok, [%{name: "Felix", age: 15}]}

Documentation is compiled into the macro-generated functions to assist in library usage. Any SQL comments between the name declaration and the start of a SQL block will be treated as documentation and compiled into the resulting Elixir.

Benefits

By keeping the SQL and Elixir separate you get:

• No syntactic surprises. Your database doesn't stick to the SQL standard - none of them do - but Yesql doesn't care. You will never spend time hunting for "the equivalent Ecto syntax". You will never need to fall back to a fragment("some('funky'::SYNTAX)") function.
• Better editor support. Your editor probably already has great SQL support. By keeping the SQL as SQL, you get to use it.
• Team interoperability. DBAs and developers less familiar with Ecto can read and write the SQL you use in your Elixir project.
• Easier performance tuning. Need to EXPLAIN that query plan? It's much easier when your query is ordinary SQL.
• Query reuse. Drop the same SQL files into other projects, because they're just plain ol' SQL. Share them as a submodule.
• Simplicity. This is a very small library, it is easier to understand and review than Ecto and similar.

When Should I Not Use Yesql?

When you need your SQL to work with many different kinds of database at once. If you want one complex query to be transparently translated into different dialects for MySQL, Oracle, Postgres etc., then you genuinely do need an abstraction layer on top of SQL.

Alternatives

We've talked about Ecto, but how does Yesql compare to $OTHER_LIBRARY?

eql

eql is an Erlang library with similar inspiration and goals.

• eql offers no solution for query execution, the library user has to implement this. Yesql offers a friendly API.
• Being an Erlang library eql has to compile the queries at runtime, Yesql does this at compile time so you don't need to write initialisation code and store your queries somewhere.
• eql requires the neotoma PEG compiler plugin, Yesql only uses the Elixir standard library.
• Yesql uses prepared statements so query parameters are sanitised and are only valid in positions that your database will accept parameters. eql functions more like a templating tool so parameters can be used in any position and sanitisation is left up to the user.
• A subjective point, but I believe the Yesql's implementation is simpler than eql's, while offering more features.

Development & Testing

createdb yesql_test
mix deps.get
mix test

Other Languages

Yesql rips off is inspired by Kris Jenkins' Clojure Yesql. Similar libraries can be found for many languages:

Language	Project
C#	JaSql
Clojure	YeSPARQL
Clojure	Yesql
Erlang	eql
Go	DotSql
Go	goyesql
JavaScript	Preql
JavaScript	sqlt
PHP	YepSQL
Python	Anosql
Ruby	yayql

License

Copyright © 2018 Louis Pilfold. All Rights Reserved.