db_connection

v0.2.1

db_connection v0.2.1 DBConnection behaviour

A behaviour module for implementing efficient database connection client processes, pools and transactions.

DBConnection handles callbacks differently to most behaviours. Some callbacks will be called in the calling process, with the state copied to and from the calling process. This is useful when the data for a request is large and means that a calling process can interact with a socket directly.

A side effect of this is that query handling can be written in a simple blocking fashion, while the connection process itself will remain responsive to OTP messages and can enqueue and cancel queued requests.

If a request or series of requests takes too long to handle in the client process a timeout will trigger and the socket can be cleanly disconnected by the connection process.

If a calling process waits too long to start its request it will timeout and its request will be cancelled. This prevents requests building up when the database can not keep up.

If no requests are received for a period of time the connection will trigger an idle timeout and the database can be pinged to keep the connection alive.

Should the connection be lost, attempts will be made to reconnect with (configurable) exponential random backoff to reconnect. All state is lost when a connection disconnects but the process is reused.

The DBConnection.Query protocol provide utility functions so that queries can be prepared or encoded and results decoding without blocking the connection or pool.

By default the DBConnection provides a single connection. However the :pool option can be set to use a pool of connections. If a pool is used the module must be passed as an option - unless inside a run/3 or transaction/3 fun and using the run/transaction connection reference (t).

Summary

Types

conn()
params()
query()
result()
t()

Run or transaction connection reference

Functions

child_spec(conn_mod, opts, child_opts \\ [])

Create a supervisor child specification for a pool of connections

close(conn, query, opts \\ [])

Close a prepared query on a database connection and return {:ok, result} on success or {:error, exception} on error

close!(conn, query, opts \\ [])

Close a prepared query on a database connection and return the result. Raises an exception on error

execute(conn, query, params, opts)

Execute a prepared query with a database connection and return {:ok, result} on success or {:error, exception} if there was an error

execute!(conn, query, params, opts \\ [])

Execute a prepared query with a database connection and return the result. Raises an exception on error

execute_close(conn, query, params, opts \\ [])

Execute a prepared query and close it with a database connection and return {:ok, result} on success or {:error, exception} if there was an error

execute_close!(conn, query, params, opts \\ [])

Execute a prepared query and close it with a database connection and return the result. Raises an exception on error

prepare(conn, query, opts \\ [])

Prepare a query with a database connection for later execution and returns {:ok, query} on success or {:error, exception} if there was an error

prepare!(conn, query, opts)

Prepare a query with a database connection and return the prepared query. An exception is raised on error

prepare_execute(conn, query, params, opts \\ [])

Prepare a query and execute it with a database connection and return both the preprared query and the result, {:ok, query, result} on success or {:error, exception} if there was an error

prepare_execute!(conn, query, params, opts \\ [])

Prepare a query and execute it with a database connection and return both the prepared query and result. An exception is raised on error

query(conn, query, params, opts \\ [])

Run a query with a database connection and returns {:ok, result} on success or {:error, exception} if there was an error

query!(conn, query, params, opts \\ [])

Run a query with a database connection and return the result. An exception is raised on error

rollback(conn, err)

Rollback a transaction, does not return

run(conn, fun, opts)

Acquire a lock on a connection and run a series of requests on it. The result of the fun is return inside an :ok tuple: {:ok result}

start_link(conn_mod, opts)

Start and link to a database connection process

transaction(conn, fun, opts)

Acquire a lock on a connection and run a series of requests inside a tranction. The result of the transaction fun is return inside an :ok tuple: {:ok result}

Macros

__using__()

Use DBConnection to set the behaviour and include default implementations for handle_prepare/3 (no-op), handle_execute_close/4 (forwards to handle_execute/4 and handle_close/3) and handle_close/3 (no-op). handle_info/2 is also implemented as a no-op

Types 

conn :: GenSever.server | t
params :: any
query :: any
result :: any
t :: %DBConnection{pool_mod: module, pool_ref: any, conn_mod: any, conn_ref: reference}

Run or transaction connection reference.

Functions

child_spec(conn_mod, opts, child_opts \\ [])

Specs

child_spec(module, opts :: Keyword.t, child_opts :: Keyword.t) :: Supervisor.Spec.spec

Create a supervisor child specification for a pool of connections.

See Supervisor.Spec for child options (child_opts).

close(conn, query, opts \\ [])

Specs

close(conn, query, opts :: Keyword.t) ::
  {:ok, result} |
  {:error, Exception.t}

Close a prepared query on a database connection and return {:ok, result} on success or {:error, exception} on error.

This function should be used to free resources held by the connection process and/or the database server.

Options

  • :pool_timeout - The maximum time to wait for a reply when making a synchronous call to the pool (default: 5_000)
  • :queue - Whether to block waiting in an internal queue for the connection’s state (boolean, default: true)
  • :timeout - The maximum time that the caller is allowed the to hold the connection’s state (ignored when using a run/transaction connection, default: 15_000)
  • :log - A function to log information about a call, either a 1-arity fun, {module, function, args} with DBConnection.LogEntry.t prepended to args or nil. See DBConnection.LogEntry (default: nil)

The pool and connection module may support other options. All options are passed to handle_close/3.

See prepare/3.

close!(conn, query, opts \\ [])

Specs

close!(conn, query, opts :: Keyword.t) :: result

Close a prepared query on a database connection and return the result. Raises an exception on error.

See close/3.

execute(conn, query, params, opts)

Specs

execute(conn, query, params, opts :: Keyword.t) ::
  {:ok, result} |
  {:error, Exception.t}

Execute a prepared query with a database connection and return {:ok, result} on success or {:error, exception} if there was an error.

If the query is not prepared on the connection an attempt may be made to prepare it and then execute again.

Options

  • :pool_timeout - The maximum time to wait for a reply when making a synchronous call to the pool (default: 5_000)
  • :queue - Whether to block waiting in an internal queue for the connection’s state (boolean, default: true)
  • :timeout - The maximum time that the caller is allowed the to hold the connection’s state (ignored when using a run/transaction connection, default: 15_000)
  • :log - A function to log information about a call, either a 1-arity fun, {module, function, args} with DBConnection.LogEntry.t prepended to args or nil. See DBConnection.LogEntry (default: nil)

The pool and connection module may support other options. All options are passed to handle_execute/4.

See prepare/3.

execute!(conn, query, params, opts \\ [])

Specs

execute!(conn, query, params, opts :: Keyword.t) :: result

Execute a prepared query with a database connection and return the result. Raises an exception on error.

See execute/4

execute_close(conn, query, params, opts \\ [])

Specs

execute_close(conn, query, params, opts :: Keyword.t) ::
  {:ok, result} |
  {:error, Exception.t}

Execute a prepared query and close it with a database connection and return {:ok, result} on success or {:error, exception} if there was an error.

All options are passed to handle_execute_close/4.

See execute/4 and close/3.

execute_close!(conn, query, params, opts \\ [])

Specs

execute_close!(conn, query, params, opts :: Keyword.t) :: result

Execute a prepared query and close it with a database connection and return the result. Raises an exception on error.

See execute_close/4

prepare(conn, query, opts \\ [])

Specs

prepare(conn, query, opts :: Keyword.t) ::
  {:ok, query} |
  {:error, Exception.t}

Prepare a query with a database connection for later execution and returns {:ok, query} on success or {:error, exception} if there was an error.

The returned query can then be passed to execute/3 and/or close/3

Options

  • :pool_timeout - The maximum time to wait for a reply when making a synchronous call to the pool (default: 5_000)
  • :queue - Whether to block waiting in an internal queue for the connection’s state (boolean, default: true)
  • :timeout - The maximum time that the caller is allowed the to hold the connection’s state (ignored when using a run/transaction connection, default: 15_000)
  • :log - A function to log information about a call, either a 1-arity fun, {module, function, args} with DBConnection.LogEntry.t prepended to args or nil. See DBConnection.LogEntry (default: nil)

The pool and connection module may support other options. All options are passed to handle_prepare/3.

Example

{ok, query}   = DBConnection.prepare(pid, "SELECT id FROM table")
{:ok, result} = DBConnection.execute(pid, query, [])
:ok           = DBConnection.close(pid, query)
prepare!(conn, query, opts)

Specs

prepare!(conn, query, opts :: Keyword.t) :: query

Prepare a query with a database connection and return the prepared query. An exception is raised on error.

See prepare/3.

prepare_execute(conn, query, params, opts \\ [])

Specs

prepare_execute(conn, query, params, Keyword.t) ::
  {:ok, {query, result}} |
  {:error, Exception.t}

Prepare a query and execute it with a database connection and return both the preprared query and the result, {:ok, query, result} on success or {:error, exception} if there was an error.

This function is different to query/4 because the query is also returned, whereas the query is closed with query/4.

The returned query can be passed to execute/4, execute_close/4, and/or close/3

Options

  • :pool_timeout - The maximum time to wait for a reply when making a synchronous call to the pool (default: 5_000)
  • :queue - Whether to block waiting in an internal queue for the connection’s state (boolean, default: true)
  • :timeout - The maximum time that the caller is allowed the to hold the connection’s state (ignored when using a run/transaction connection, default: 15_000)
  • :log - A function to log information about a call, either a 1-arity fun, {module, function, args} with DBConnection.LogEntry.t prepended to args or nil. See DBConnection.LogEntry (default: nil)

Example

{ok, query, result} = DBConnection.prepare_execute(pid, “SELECT id FROM table WHERE id=$1”, [1]) {:ok, result2} = DBConnection.execute(pid, query, [2]) :ok = DBConnection.close(pid, query)

prepare_execute!(conn, query, params, opts \\ [])

Prepare a query and execute it with a database connection and return both the prepared query and result. An exception is raised on error.

See prepare_execute/4.

query(conn, query, params, opts \\ [])

Specs

query(conn, query, params, opts :: Keyword.t) ::
  {:ok, result} |
  {:error, Exception.t}

Run a query with a database connection and returns {:ok, result} on success or {:error, exception} if there was an error.

Options

  • :pool_timeout - The maximum time to wait for a reply when making a synchronous call to the pool (default: 5_000)
  • :queue - Whether to block waiting in an internal queue for the connection’s state (boolean, default: true)
  • :timeout - The maximum time that the caller is allowed the to hold the connection’s state (ignored when using a run/transaction connection, default: 15_000)
  • :log - A function to log information about a call, either a 1-arity fun, {module, function, args} with DBConnection.LogEntry.t prepended to args or nil. See DBConnection.LogEntry (default: nil)

The pool and connection module may support other options. All options are passed to handle_prepare/3 and handle_execute_close/4.

Example

{:ok, _} = DBConnection.query(pid, "SELECT id FROM table", [], [])
query!(conn, query, params, opts \\ [])

Specs

query!(conn, query, params, opts :: Keyword.t) :: result

Run a query with a database connection and return the result. An exception is raised on error.

See query/3.

rollback(conn, err)

Specs

rollback(t, reason :: any) :: no_return

Rollback a transaction, does not return.

Aborts the current transaction fun. If inside transaction/3 bubbles up to the top level.

Example

{:error, :bar} = DBConnection.transaction(pid, fn(conn) ->
  DBConnection.rollback(conn, :bar)
  IO.puts "never reaches here!"
end)
run(conn, fun, opts)

Specs

run(conn, (t -> result), opts :: Keyword.t) :: result when result: var

Acquire a lock on a connection and run a series of requests on it. The result of the fun is return inside an :ok tuple: {:ok result}.

To use the locked connection call the request with the connection reference passed as the single argument to the fun. If the connection disconnects all future calls using that connection reference will fail.

run/3 and transaction/3 can be nested multiple times but a transaction/3 call inside another transaction/3 will be treated the same as run/3.

Options

  • :pool_timeout - The maximum time to wait for a reply when making a synchronous call to the pool (default: 5_000)
  • :queue - Whether to block waiting in an internal queue for the connection’s state (boolean, default: true)
  • :timeout - The maximum time that the caller is allowed the to hold the connection’s state (default: 15_000)

The pool may support other options.

Example

{:ok, res} = DBConnection.run(pid, fn(conn) ->
  res = DBConnection.query!(conn, "SELECT id FROM table", [])
  res
end)
start_link(conn_mod, opts)

Specs

start_link(module, opts :: Keyword.t) :: GenServer.on_start

Start and link to a database connection process.

Options

  • :pool - The DBConnection.Pool module to use, (default: DBConnection.Connection)
  • :idle_timeout - The idle timeout to ping the database (default: 15_000)
  • :backoff_min - The minimum backoff interval (default: 200)
  • :backoff_max - The maximum backoff interval (default: 15_000)
  • :backoff_type - The backoff strategy, :stop for no backoff and to stop, :exp for exponential, :rand for random and :rand_exp for random exponential (default: :rand_exp)
  • :after_connect - A function to run on connect using run/3, either a 1-arity fun, {module, function, args} with DBConnection.t prepended to args or nil (default: nil)

Example

{:ok, pid} = DBConnection.start_link(mod, [idle_timeout: 5_000])
transaction(conn, fun, opts)

Specs

transaction(conn, (conn -> result), opts :: Keyword.t) ::
  {:ok, result} |
  {:error, reason :: any} when result: var

Acquire a lock on a connection and run a series of requests inside a tranction. The result of the transaction fun is return inside an :ok tuple: {:ok result}.

To use the locked connection call the request with the connection reference passed as the single argument to the fun. If the connection disconnects all future calls using that connection reference will fail.

run/3 and transaction/3 can be nested multiple times. If a transaction is rolled back or a nested transaction fun raises the transaction is marked as failed. Any calls inside a failed transaction (except rollback/2) will raise until the outer transaction call returns. All running transaction/3 calls will return {:error, :rollback} if the transaction failed or connection closed and rollback/2 is not called for that transaction/3.

Options

  • :pool_timeout - The maximum time to wait for a reply when making a synchronous call to the pool (default: 5_000)
  • :queue - Whether to block waiting in an internal queue for the connection’s state (boolean, default: true)
  • :timeout - The maximum time that the caller is allowed the to hold the connection’s state (default: 15_000)
  • :log - A function to log information about begin, commit and rollback calls made as part of the transaction, either a 1-arity fun, {module, function, args} with DBConnection.LogEntry.t prepended to args or nil. See DBConnection.LogEntry (default: nil)

The pool and connection module may support other options. All options are passed to handle_begin/2, handle_commit/2 and handle_rollback/2.

Example

{:ok, res} = DBConnection.transaction(pid, fn(conn) ->
  res = DBConnection.query!(conn, "SELECT id FROM table", [])
  res
end)

Macros

__using__()

Use DBConnection to set the behaviour and include default implementations for handle_prepare/3 (no-op), handle_execute_close/4 (forwards to handle_execute/4 and handle_close/3) and handle_close/3 (no-op). handle_info/2 is also implemented as a no-op.