SQL

Brings an extensible SQL parser and sigil to Elixir, confidently write SQL with automatic parameterized queries.

• Lower the barrier for DBAs to contribute in your codebase, without having to translate SQL to Ecto.Query.
• Composable queries, no need for you to remember, when to start with select or from.
• Interpolation-al queries, don't fiddle with fragments and ?.

Examples

iex(1)> email = "john@example.com"
"john@example.com"
iex(2)> select = ~SQL"select id, email"
"select id, email"
iex(3)> ~SQL[from users] |> ~SQL[where email = #{email}] |> select
"select id, email from users where email = \"john@example.com\""
iex(4)> sql = ~SQL[from users where email = #{email} select id, email]
"select id, email from users where email = \"john@example.com\""
iex(5)> to_sql(sql)
{"select id, email from users where email = $0", ["john@example.com"]}
iex(6)> to_string(sql)
"select id, email from users where email = $0"
iex(7)> inspect(sql)
"select id, email from users where email = \"john@example.com\""

Leverage the Enumerable protocol in your repository

  defmodule MyApp.Repo do
    use Ecto.Repo, otp_app: :myapp, adapter: Ecto.Adapters.Postgres
    use SQL, adapter: SQL.Adapters.Postgres

    defimpl Enumerable, for: SQL do
      def count(_enumerable) do
        {:error, __MODULE__}
      end
      def member?(_enumerable, _element) do
        {:error, __MODULE__}
      end
      def reduce(%SQL{} = enumerable, _acc, _fun) do
        {sql, params} = SQL.to_sql(enumerable)
        result = __MODULE__.query!(sql, params)
        {:done, Enum.map(result.rows, &Map.new(Enum.zip(result.columns, &1)))}
      end
      def slice(_enumerable) do
        {:error, __MODULE__}
      end
    end
  end

  iex(1)> Enum.map(~SQL[from users select *], &IO.inspect/1)
  %{"id" => 1, "email" => "john@example.com"}
  %{"id" => 2, "email" => "jane@example.com"}
  [%{"id" => 1, "email" => "john@example.com"}, %{"id" => 2, "email" => "jane@example.com"}]

Installation

If available in Hex, the package can be installed by adding sql to your list of dependencies in mix.exs:

def deps do
  [
    {:sql, "~> 0.2.0"}
  ]
end

Documentation can be generated with ExDoc and published on HexDocs. Once published, the docs can be found at https://hexdocs.pm/sql.