plamb/pg_contrivance

A layer on top of postgrex for exploiting the power of Postgresql and SQL.

PgContrivance

This is a layer of useful tooling on top of postgrex. For a high-level database wrapper look to Ecto or alternatively at Moebius which does a good job of creating a direct, easy-to-use SQL dsl. PgContrivance looks to exploit Postgresql's capabilities without abstracting the SQL away, in fact, it pretty much revels in the glory of plain old SQL strings and looks to exploit Postgresql specific functionality.

Much of PgContrivance was thought up as an addition to Moebius but I kept thinking that it would be best as a standalone library that was only dependent on Postgrex and useable standalone or in an Ecto app too.

Functionality:

• execute sql queries with params
• execute sql queries with params with transaction
• bulk insert
• named parameters in query strings
• work with Geo
• eex sql templates
• sql from files
• much more...

Warning/Versions

This is very much in development and highly dependent on postgrex. PgContrivance uses Postgrex v0.11 but as functionality evolves and changes there, it may cause breaking changes here. As time goes on, I will try to keep a feature matrix of what versions work with what version of Postgrex.

Right now I'm using a version scheme like postgrex-major.postgrex-minor-contrivance-version. i.e. 0.11.1. This is very much subject to change.

Installation

The package is not in Hex yet, it can be used by accessing it from github:

1. Add pg_contrivance to your list of dependencies in mix.exs:

   def deps do [{:pg_contrivance, github: "plamb/pg_contrivance"}, {:postgrex, "> 0.11"}, {:poolboy, "> 1.5"}] end

2. Ensure pg_contrivance is started before your application:

   def application do [applications: [:poolboy, :postgrex, :pg_contrivance]] end

Configuration

You'll need to have a configuration block for the database connection.

    config :pg_contrivance, MyApplication.MyDb
      connection: [database: "contrived", pool_mod: DBConnection.Poolboy]

Withing the connection key you can specify any of the normal Postgrex connection options.

[Note: You will need to specify the pool_mod-this will become a default soon].

Basic Usage

The api utilizes a %SqlCommand{} struct to make usage a bit more Elixir like (and will be quite familiar to Moebius users) that allow us to pipeline commands and results.

sql "SELECT name, email FROM USERS"
|> query
|> to_list

query "SELECT name, email FROM USERS"
|> to_list

sql "SELECT name, email FROM users WHERE username = $1"
|> params ["bob@acme.com"]
|> query
|> to_list

With named parameter conversion:

sql("SELECT name, email FROM users WHERE username = :username")
|> params(%{username: "bob@acme.com"})
|> query
|> to_list

Very Low-level API

At it's most basic PgContrivance is a VERY thin wrapper around Postgrex.query, query! and transaction. All of the low-level functions take a sql string, a list of params and optionally Postgrex options (:pool_timeout, :queue, :timeout, :decode_mapper, :pool)

PgContrivance.Postgres.query "SELECT name, email FROM USERS", []

See the docs for the return types. query/3 returns the same its Postgrex counterpart: {:ok, %Postgrex.Result{}} or {:error, %Postgrex.Error{}}. query!/3 returns %Postgrex.Result{} or raises raises Postgrex.Error if there was an error.

Transaction incorporates a rollback mechanism if there is an error but is called just like query/3:

PgContrivance.Postgres.transaction "UPDATE users SET email='bob@acme.com'", []

Acknowledgements

Initially PgContrivance steals/copies and liberally imitates concepts from Moebius, particularly the Moebius.Runner and bulk-insert code (thanks John Atten). If it wasn't for Rob's (both Conery and Sullivan) and Johnny Winn I would have never even thought about pursing my own thoughts of how I wanted a library to work or even started to code it. Their "I can do anything" attitude is quite infectious.