epgsql

Erlang PostgreSQL Database Client

Asynchronous fork of https://github.com/wg/epgsql
It passes all tests from original driver except 3 timeout tests.
Backward compatibility is preserved by module pgsql.
Difference highlights (see CHANGES for full list):
+ internal queue of client requests, so you don't need to wait for response to send next request
+ results can be delivered as regular erlang messages, either complete or row by row
+ single process to hold driver state and receive socket data
+ execute several prepared statements as a batch
+ bind timestamps in erlang:now() format


* Known problems

Timeout supplied at connect time works as socket connect timeout not query timeout.
SSL performance degrades if driver process has large inbox (thousands of messages).


* Connect

{ok, C} = pgsql:connect(Host, [Username], [Password], Opts).

Host - host to connect to.
Username - username to connect as, defaults to $USER.
Password - optional password to authenticate with.
Opts - property list of extra options. Supported properties:

+ {database, String}
+ {port, Integer}
+ {ssl, Atom} true | false | required
+ {ssl_opts, List} see ssl application docs in OTP
+ {timeout, Integer} milliseconds, defaults to 5000
+ {async, Pid} see Server Notifications section

{ok, C} = pgsql:connect("localhost", "username", [{database, "test_db"}]).
ok = pgsql:close(C).

The timeout parameter will trigger an {error, timeout} result when the
socket fails to connect within Timeout milliseconds.

Asynchronous connect example (applies to ipgsql too):

{ok, C} = apgsql:start_link(),
Ref = apgsql:connect(C, "localhost", "username", [{database, "test_db"}]),
receive
{C, Ref, connected} ->
{ok, C};
{C, Ref, Error = {error, _}} ->
Error;
{'EXIT', C, _Reason} ->
{error, closed}
end.


* Simple Query

{ok, Columns, Rows} = pgsql:squery(C, "select ...").
{ok, Count} = pgsql:squery(C, "update ...").
{ok, Count, Columns, Rows} = pgsql:squery(C, "insert ... returning ...").

{error, Error} = pgsql:squery(C, "invalid SQL").

Columns - list of column records, see pgsql.hrl for definition.
Rows - list of tuples, one for each row.
Count - integer count of rows inserted/updated/etc

The simple query protocol returns all columns as text (Erlang binaries)
and does not support binding parameters.

apgsql:squery returns result as a single message:

Ref = apgsql:squery(C, Sql),
receive
{C, Ref, Res} -> Res
end.
Res has same fomat as return value of pgsql:squery.

ipgsql:squery returns result incrementally for each query inside Sql and
for each row:

Ref = ipgsql:squery(C, Sql),
receive
{C, Ref, {columns, Columns}} ->
%% columns description
Columns;
{C, Ref, {data, Row}} ->
%% single data row
Row;
{C, Ref, {error, _E} = Error} ->
Error;
{C, Ref, {complete, {_Type, Count}}} ->
%% execution of one insert/update/delete has finished
{ok, Count}; % affected rows count
{C, Ref, {complete, _Type}} ->
%% execution of one select has finished
ok;
{C, Ref, done} ->
%% execution of all queries from Sql has finished
done;
end.


* Extended Query

{ok, Columns, Rows} = pgsql:equery(C, "select ...", [Parameters]).
{ok, Count} = pgsql:equery(C, "update ...", [Parameters]).
{ok, Count, Columns, Rows} = pgsql:equery(C, "insert ... returning ...", [Parameters]).

{error, Error} = pgsql:equery(C, "invalid SQL", [Parameters]).

Parameters - optional list of values to be bound to $1, $2, $3, etc.

The extended query protocol combines parse, bind, and execute using
the unnamed prepared statement and portal. A "select" statement returns
{ok, Columns, Rows}, "insert/update/delete" returns {ok, Count} or
{ok, Count, Columns, Rows} when a "returning" clause is present. When
an error occurs, all statements result in {error, #error{}}.

PostgreSQL's binary format is used to return integers as Erlang
integers, floats as floats, bytea/text/varchar columns as binaries,
bools as true/false, etc. For details see pgsql_binary.erl and the
Data Representation section below.

* Parse/Bind/Execute

{ok, Statement} = pgsql:parse(C, [StatementName], Sql, [ParameterTypes]).

StatementName - optional, reusable, name for the prepared statement.
ParameterTypes - optional list of PostgreSQL types for each parameter.

For valid type names see pgsql_types.erl.

ok = pgsql:bind(C, Statement, [PortalName], ParameterValues).

PortalName - optional name for the result portal.

{ok | partial, Rows} = pgsql:execute(C, Statement, [PortalName], [MaxRows]).
{ok, Count} = pgsql:execute(C, Statement, [PortalName]).
{ok, Count, Rows} = pgsql:execute(C, Statement, [PortalName]).

PortalName - optional portal name used in bind/4.
MaxRows - maximum number of rows to return (0 for all rows).

execute returns {partial, Rows} when more rows are available.

ok = pgsql:close(C, Statement).
ok = pgsql:close(C, statement | portal, Name).
ok = pgsql:sync(C).

All functions return {error, Error} when an error occurs.

* Data Representation

null = null
bool = true | false
char = $A | binary
intX = 1
floatX = 1.0
date = {Year, Month, Day}
time = {Hour, Minute, Second.Microsecond}
timetz = {time, Timezone}
timestamp = {date, time}
timestamptz = {date, time}
interval = {time, Days, Months}
text = <<"a">>
varchar = <<"a">>
bytea = <<1, 2>>
array = [1, 2, 3]

record = {int2, time, text, ...} (decode only)

timestamp and timestamptz parameters can take erlang:now() format {MegaSeconds, Seconds, MicroSeconds}

* Errors

Errors originating from the PostgreSQL backend are returned as {error, #error{}},
see pgsql.hrl for the record definition. epgsql functions may also return
{error, What} where What is one of the following:

{unsupported_auth_method, Method} - required auth method is unsupported
timeout - request timed out
closed - connection was closed
sync_required - error occured and pgsql:sync must be called

* Server Notifications

PostgreSQL may deliver two types of asynchronous message: "notices" in response
to notice and warning messages generated by the server, and "notifications" which
are generated by the LISTEN/NOTIFY mechanism.

Passing the {async, Pid} option to pgsql:connect will result in these async
messages being sent to the specified process, otherwise they will be dropped.

Message formats:

{pgsql, Connection, {notification, Channel, Pid, Payload}}

Connection - connection the notification occured on

Channel - channel the notification occured on
Pid - database session pid that sent notification
Payload - optional payload, only available from PostgreSQL >= 9.0

{pgsql, Connection, {notice, Error}}

Connection - connection the notice occured on
Error - an #error{} record, see pgsql.hrl