Porta

Porta is an extension of Ecto (and, optionally, Phoenix) which provides an alternative, more SQL-oriented way of doing database stuff. Its most notable feature is Composable SQL. An example:

priv/queries/banned_users_by_occupation.sql

select u.* from users u
where u.banned = true and u.occupation = _occupation_
order by u.age

priv/queries/users_bought_product_after.sql

select u.id from users u
order by u.name
left join orders o on u.id = o.user_id
where o.inserted_at > _after_
join products p on p.id = o.product_id
where p.name = _product_

lib/app/repo.ex

defmodule App.Repo do
  use Ecto.Repo
  use Porta.Repo
end

banned_cheerleaders_who_bought_salmon_after_article_50_was_invoked =
  App.Repo.run! [
    banned_users_by_occupation: %{occupation: "cheerleader"},
    users_bought_product_after: %{product: "salmon", after: ~N"2017-03-29 12:30:00"}
  ]

This merges the two queries and runs something equivalent to:

select u.* from users u
left join orders o on u.id = o.user_id
join products p on p.id = o.product_id
where u.banned = true and u.occupation = $1
  and o.inserted_at > $3 and p.name = $2
order by u.age, u.name

With the positional arguments ["cheerleader", "salmon", ~N"2017-03-29 12:30:00"].

Caveats

The merger above is less intelligent than it seems.

It first splits each query wherever a line starts with a clause keyword such as left join, where and order by. Because of this, the newlines in the examples above are required. After that, it replaces all named parameters with positional parameters (or subqueries, or raw SQL). Then it groups and sorts all clauses on their type, maintaining the order of clauses of the same type. Finally, clauses of the same type are merged together if necessary.

While this approach supports all SQL dialects and allows for more expressive queries than most typical ORMs, there are a few problems:

• Each table has to be aliased the same way across all merged queries. I think this can always be achieved through subquerying, but I may be wrong.
• Some clause types do not have a simple, natural merge strategy. In the case of select clauses, this means we currently discard everything but the first one.

Subqueries

Whenever a keyword list is used in place of an argument, it is assumed to be a subquery:

users_who_bought_user_9s_favorite_product_after_carnaval_in_the_year_1979 =
  Repo.run! users_bought_product_after: %{
    product: [favorite_product: %{user_id: 9}],
    after: [carnaval: %{year: 1979}]
  }

priv/queries/favorite_product.sql

select p.name from products p
join orders o on p.id = o.product_id
join users u on u.id = o.user_id
where u.id = _user_id_
group by p.id
order by count(o.*)
limit 1

priv/queries/carnaval.sql

select (_year_::int || '-11-11)::date

Raw SQL

(don't put user input in raw parameters or you will get hacked and robbed and lynched)

To insert raw SQL in the place of a named parameter, use a {:raw, param} tuple. This can be used to dynamically specify columns and table names:

priv/queries/select_user_column.sql

select _column_ from users

Repo.run! select_user_column: %{
  column: {:raw, "name"}
}

Ad-Hoc Queries

A few SQL queries are included in Porta by default. An example is the select query, which works as if you put select _columns_ from _from_ _alias_ in priv/queries/select.sql. These queries can be composed as well:

Repo.run! [
  union_all: %{
    left: [
      select: %{
        columns: {:raw, "name"},
        from: [banned_users_by_occupation: %{occupation: "guru"}],
        alias: {:raw, "gurus"}
      }
    ],
    right: [
      select: %{
        columns: {:raw, "name"},
        from: {:raw, "users"},
        alias: {:raw, "u"}
      },
      where: %{clause: {:raw, "u.age < 19"}}
    ]
  },
  order: %{by: {:raw, "name"}}
]

While the union and union_all default queries are very useful, it usually makes more sense to put select subqueries in their own file under priv/queries.

A feature that should see even rarer use is the ability to replace the atoms in the query list with strings:

Repo.run! [{"select _i_ - 5", %{i: 6}}]

This is equivalent to putting select _i_ - 5 in select_i_minus_5.sql and running:

Repo.run! select_i_minus_5: %{i: 6}

SQL Files as Migrations

I'll write up documentation for this and the other Mix tasks someday.

Installation

Most of Porta's features require a somewhat recent version of Ecto. Some require Phoenix. Other than that:

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