Postgres Sigil

A library to improve the ergonomics of working with Postgrex. It can be thought of as a middle ground between Ecto and ayesql in that the goal is to write queries in plain SQL but within Elixir source files, not separately. The syntax is heavily inspired by the Scala library doobie.

Writing queries

Basic selects

Use the ~q sigil to construct queries. Variables can be safely interpolated into the query and will be replaced with $1, $2 etc positional parameters before being sent to Postgres.

~q"SELECT * FROM users WHERE id = #{id}" |> to_tuple()
# result: {"SELECT * FROM users WHERE id = $1", [1245]}

Fragments

Queries can be interpolated into other queries which allows you to re-use fragments.

recently_seen = ~q"last_seen >= NOW() - INTERVAL '1 day'"
~q"SELECT * FROM users WHERE #{recently_seen}" |> to_tuple()
# result: {"SELECT * FROM users WHERE last_seen >= NOW() - INTERVAL '1 day'", []}

Inserts and updates

Interpolating a call to values() will result in the value being enclosed in brackets and prefixed with VALUES.

Note you cannot directly insert maps because they do not have a defined order.

user = %{name: "Tom", email: "[email protected]"}
~q"INSERT INTO users (name, email) #{values(user.name, user.email)}" |> to_tuple()
# result: {"INSERT INTO users (name, email) VALUES ($1, $2)", ["Tom", "[email protected]"]}

The main benefit this syntax offers is that if you pass a list to values it'll generate the correct SQL for a batch insert operation:

~q"INSERT INTO users (name, email, address1) #{values([
  {"A", "[email protected]", "123 fake street"},
  {"B", "[email protected]", "234 fake street"}
])}" |> to_tuple()

# result: {
#  "INSERT INTO users (name, email, address1) VALUES ($1, $2, $3), ($4, $5, $6)",
#  ["A", "[email protected]", "123 fake street", "B", "[email protected]", "234 fake street"]
#}

Dynamic columns

Column names can be interpolated by wrapping the interpolation in col()

~q"SELECT #{col("name")} FROM users" |> to_tuple()
# result: {"SELECT \"name\" FROM users", []}

Unsafe interpolation

If you're really up to no good then you can wrap interpolations in unsafe() which will result in the value being directly placed into the query with no escaping. This should only be used if you're fully aware of the security implications.

~q"SELECT #{unsafe("name")} FROM users"
# result: {"SELECT name FROM users", []}

Running queries

You can run the queries either with Ecto or directly with Postgrex.

~q"SELECT * FROM users" |> PostgresSigil.Ecto.query!(MyApp.Repo) # ecto
~q"SELECT * FROM users" |> PostgresSigil.Postgrex.query!(:pid) # postgrex

Explaining queries

Both the Ecto and Postgrex integrations provide explain_to_file! that will run the query with EXPLAIN ANALYZE and write the result to a file named explain.json.

This can then be pasted into https://explain.dalibo.com/ for analysis.

Handling results

PostgresSigil.Results defines a number of functions to make it easier to process the results that Postgrex returns.