ExoSQL

Build Status

Not everything described here is working yet

Universal SQL engine for Elixir.

This library implements the SQL engine to perform queries on user provided databases using a simple interface based on Foreign Data Wrappers from PostgreSQL.

This allows to use SQL on your own data and virtual tables.

For example it includes a CSV reader and an HTTP client, so that you can do queries as:

SELECT url, status_code
  FROM urls
  INNER JOIN request
  ON urls.url = request.url

Installation

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

def deps do
  [
    {:exosql, "~> 0.2"}
  ]
end

Features

Check the tests for current features available.

Variables

Variables can be passed as a dictionary at __vars__ inside the context, and referenced as $name at the SQL expression. This may change in the future to streamline it more with standard SQL (no need for $).

INNER JOIN

Because some columns may need to be autogenerated depending on the query, if you want to access those columns you may need to use INNER JOINS. This way the planner asks for those specific column values.

For example:

SELECT * FROM request

does not know to which URL you want to access, but:

SELECT * FROM request WHERE url = 'http://serverboards.io'

knows the URL and can get the data.

Then same way, on INNER JOINS this can be used to access to auto generated data:

SELECT url, status_code
  FROM urls
  INNER JOIN request
  ON urls.url = request.url

Included extractors

ExoSQL has been developed with the idea of connecting to Serverboards services, and as such it does not provide more than some test extractors:

Creating new ones is a very straightforward process. The HTTP example can be followed.

Using ExoSQL

There is no formal documentation yet, but you can check the esql_test.exs file to get an idea of how to use ExoSQL.

Example:

context = %{
  "A" => {ExoSQL.Csv, path: "test/data/csv/"},
  "B" => {ExoSQL.HTTP, []}
}
{:ok, result} = ExoSQL.query("
  SELECT urls.url, request.status_code
    FROM urls
   INNER JOIN request
      ON urls.url = request.url
", context)
%ExoSQL.Result{
  columns: [{"A", "urls", "url"}, {"B", "request", "status_code"}],
  rows: [
    ["https://serverboards.io/e404", 404],
    ["http://www.facebook.com", 302],
    ["https://serverboards.io", 200],
    ["http://www.serverboards.io", 301],
    ["http://www.google.com", 302]
  ]}

A Simple extractor can be:

defmodule MyExtractor do
  def schema(_config), do: {:ok, ["week"]}
  def schema(_config, "week"), do: {:ok, %{ columns: ["id", "nr", "name", "weekend"] }}
  def execute(_config, "week", _quals, _columns) do
    {:ok, %{
      columns: ["id", "nr", "name", "weekend"],
      rows: [
        [1, 0, "Sunday", true],
        [2, 1, "Monday", false],
        [3, 2, "Tuesday", false],
        [4, 3, "Wednesday", false],
        [5, 4, "Thursday", false],
        [6, 5, "Friday", false],
        [7, 6, "Saturday", true],
      ]
    }}
  end
end

And then a simple query:

{:ok, res} = ExoSQL.query("SELECT * FROM week WHERE weekend", %{ "A" => {MyExtractor, []}})               
ExoSQL.format_result(res)
A.week.id A.week.nr A.week.name A.week.weekend
1 0 Sunday true
7 6 Saturday true

Related libraries

There are other implemetnations of this very same idea:

If you know any other, please ping me and I will add it here.

I develop ExoSQL as I needed an elixir solution for an existing project, and to learn how to create an SQL engine. ExoSQL is currently used in Serverboards KPI.

Known BUGS