Postgrex v0.13.0 Postgrex
PostgreSQL driver for Elixir.
This module handles the connection to Postgres, providing support for queries, transactions, connection backoff, logging, pooling and more.
Note that the notifications API (pub/sub) supported by Postgres is
handled by Postgrex.Notifications
. Hence, to use this feature,
you need to start a separate (notifications) connection.
Summary
Functions
Returns a supervisor child specification for a DBConnection pool
Closes an (extended) prepared query and returns :ok
or
{:error, %Postgrex.Error{}}
if there was an error. Closing a query releases
any resources held by postgresql for a prepared query with that name. See
Postgrex.Query
for the query data
Closes an (extended) prepared query and returns :ok
or raises
Postgrex.Error
if there was an error. See close/3
Runs an (extended) prepared query and returns the result as
{:ok, %Postgrex.Result{}}
or {:error, %Postgrex.Error{}}
if there was an
error. Parameters are given as part of the prepared query, %Postgrex.Query{}
.
See the README for information on how Postgrex encodes and decodes Elixir
values by default. See Postgrex.Query
for the query data and
Postgrex.Result
for the result data
Runs an (extended) prepared query and returns the result or raises
Postgrex.Error
if there was an error. See execute/4
Returns a cached map of connection parameters
Prepares an (extended) query and returns the result as
{:ok, %Postgrex.Query{}}
or {:error, %Postgrex.Error{}}
if there was an
error. Parameters can be set in the query as $1
embedded in the query
string. To execute the query call execute/4
. To close the prepared query
call close/3
. See Postgrex.Query
for the query data
Prepares an (extended) query and returns the prepared query or raises
Postgrex.Error
if there was an error. See prepare/4
Runs an (extended) query and returns the result as {:ok, %Postgrex.Result{}}
or {:error, %Postgrex.Error{}}
if there was a database error. Parameters can
be set in the query as $1
embedded in the query string. Parameters are given
as a list of elixir values. See the README for information on how Postgrex
encodes and decodes Elixir values by default. See Postgrex.Result
for the
result data
Runs an (extended) query and returns the result or raises Postgrex.Error
if
there was an error. See query/3
Rollback a transaction, does not return
Start the connection process and connect to postgres
Returns a stream for a query on a connection
Acquire a lock on a connection and run a series of requests inside a
transaction. The result of the transaction fun is return inside an :ok
tuple: {:ok, result}
Types
A connection process name, pid or reference.
A connection reference is used when making multiple requests to the same
connection, see transaction/3
.
Functions
Returns a supervisor child specification for a DBConnection pool.
close(conn, Postgrex.Query.t, Keyword.t) :: :ok | {:error, Postgrex.Error.t}
Closes an (extended) prepared query and returns :ok
or
{:error, %Postgrex.Error{}}
if there was an error. Closing a query releases
any resources held by postgresql for a prepared query with that name. See
Postgrex.Query
for the query data.
This function may still raise an exception if there is an issue with types
(ArgumentError
), connection (DBConnection.ConnectionError
), ownership
(DBConnection.OwnershipError
) or other error (RuntimeError
).
Options
:pool_timeout
- Time to wait in the queue for the connection (default:5000
):queue
- Whether to wait for connection in a queue (default:true
);:timeout
- Close request timeout (default:15000
);:pool
- The pool module to use, must match that set onstart_link/1
, seeDBConnection
:mode
- set to:savepoint
to use a savepoint to rollback to before the close on error, otherwise set to:transaction
(default::transaction
);
Examples
query = Postgrex.prepare!(conn, "", "CREATE TABLE posts (id serial, title text)")
Postgrex.close(conn, query)
Closes an (extended) prepared query and returns :ok
or raises
Postgrex.Error
if there was an error. See close/3
.
execute(conn, Postgrex.Query.t, list, Keyword.t) :: {:ok, Postgrex.Result.t} | {:error, Postgrex.Error.t}
Runs an (extended) prepared query and returns the result as
{:ok, %Postgrex.Result{}}
or {:error, %Postgrex.Error{}}
if there was an
error. Parameters are given as part of the prepared query, %Postgrex.Query{}
.
See the README for information on how Postgrex encodes and decodes Elixir
values by default. See Postgrex.Query
for the query data and
Postgrex.Result
for the result data.
This function may still raise an exception if there is an issue with types
(ArgumentError
), connection (DBConnection.ConnectionError
), ownership
(DBConnection.OwnershipError
) or other error (RuntimeError
).
Options
:pool_timeout
- Time to wait in the queue for the connection (default:5000
):queue
- Whether to wait for connection in a queue (default:true
);:timeout
- Execute request timeout (default:15000
);:decode_mapper
- Fun to map each row in the result to a term after decoding, (default:fn x -> x end
);:pool
- The pool module to use, must match that set onstart_link/1
, seeDBConnection
:mode
- set to:savepoint
to use a savepoint to rollback to before the execute on error, otherwise set to:transaction
(default::transaction
);
Examples
query = Postgrex.prepare!(conn, "", "CREATE TABLE posts (id serial, title text)")
Postgrex.execute(conn, query, [])
query = Postgrex.prepare!(conn, "", "SELECT id FROM posts WHERE title like $1")
Postgrex.execute(conn, query, ["%my%"])
execute!(conn, Postgrex.Query.t, list, Keyword.t) :: Postgrex.Result.t
Runs an (extended) prepared query and returns the result or raises
Postgrex.Error
if there was an error. See execute/4
.
Returns a cached map of connection parameters.
Options
:pool_timeout
- Call timeout (default:5000
):pool
- The pool module to use, must match that set onstart_link/1
, seeDBConnection
prepare(conn, iodata, iodata, Keyword.t) :: {:ok, Postgrex.Query.t} | {:error, Postgrex.Error.t}
Prepares an (extended) query and returns the result as
{:ok, %Postgrex.Query{}}
or {:error, %Postgrex.Error{}}
if there was an
error. Parameters can be set in the query as $1
embedded in the query
string. To execute the query call execute/4
. To close the prepared query
call close/3
. See Postgrex.Query
for the query data.
This function may still raise an exception if there is an issue with types
(ArgumentError
), connection (DBConnection.ConnectionError
), ownership
(DBConnection.OwnershipError
) or other error (RuntimeError
).
Options
:pool_timeout
- Time to wait in the queue for the connection (default:5000
):queue
- Whether to wait for connection in a queue (default:true
);:timeout
- Prepare request timeout (default:15000
);:pool
- The pool module to use, must match that set onstart_link/1
, seeDBConnection
:mode
- set to:savepoint
to use a savepoint to rollback to before the prepare on error, otherwise set to:transaction
(default::transaction
);
Examples
Postgrex.prepare(conn, "", "CREATE TABLE posts (id serial, title text)")
prepare!(conn, iodata, iodata, Keyword.t) :: Postgrex.Query.t
Prepares an (extended) query and returns the prepared query or raises
Postgrex.Error
if there was an error. See prepare/4
.
query(conn, iodata, list, Keyword.t) :: {:ok, Postgrex.Result.t} | {:error, Postgrex.Error.t}
Runs an (extended) query and returns the result as {:ok, %Postgrex.Result{}}
or {:error, %Postgrex.Error{}}
if there was a database error. Parameters can
be set in the query as $1
embedded in the query string. Parameters are given
as a list of elixir values. See the README for information on how Postgrex
encodes and decodes Elixir values by default. See Postgrex.Result
for the
result data.
This function may still raise an exception if there is an issue with types
(ArgumentError
), connection (DBConnection.ConnectionError
), ownership
(DBConnection.OwnershipError
) or other error (RuntimeError
).
Options
:pool_timeout
- Time to wait in the queue for the connection (default:5000
):queue
- Whether to wait for connection in a queue (default:true
);:timeout
- Query request timeout (default:15000
);:decode_mapper
- Fun to map each row in the result to a term after decoding, (default:fn x -> x end
);:pool
- The pool module to use, must match that set onstart_link/1
, seeDBConnection
:mode
- set to:savepoint
to use a savepoint to rollback to before the query on error, otherwise set to:transaction
(default::transaction
);
Examples
Postgrex.query(conn, "CREATE TABLE posts (id serial, title text)", [])
Postgrex.query(conn, "INSERT INTO posts (title) VALUES ('my title')", [])
Postgrex.query(conn, "SELECT title FROM posts", [])
Postgrex.query(conn, "SELECT id FROM posts WHERE title like $1", ["%my%"])
Postgrex.query(conn, "COPY posts TO STDOUT", [])
query!(conn, iodata, list, Keyword.t) :: Postgrex.Result.t
Runs an (extended) query and returns the result or raises Postgrex.Error
if
there was an error. See query/3
.
Rollback a transaction, does not return.
Aborts the current transaction fun. If inside multiple transaction/3
functions, bubbles up to the top level.
Example
{:error, :oops} = Postgrex.transaction(pid, fn(conn) ->
DBConnection.rollback(conn, :bar)
IO.puts "never reaches here!"
end)
Start the connection process and connect to postgres.
Options
:hostname
- Server hostname (default: PGHOST env variable, then localhost);:port
- Server port (default: PGPORT env variable, then 5432);:database
- Database (default: PGDATABASE env variable; otherwise required);:username
- Username (default: PGUSER env variable, then USER env var);:password
- User password (default: PGPASSWORD env variable);:parameters
- Keyword list of connection parameters;:timeout
- Socket receive timeout when idle in milliseconds (default:15000
);:connect_timeout
- Socket connect timeout in milliseconds (defaults to:timeout
value);:handshake_timeout
- Connection handshake timeout in milliseconds (defaults to:timeout
value);:ssl
- Set totrue
if ssl should be used (default:false
);:ssl_opts
- A list of ssl options, see ssl docs;:socket_options
- Options to be given to the underlying socket;:prepare
- How to prepare queries, either:named
to use named queries or:unnamed
to force unnamed queries (default::named
);:transactions
- Set to:strict
to error on unexpected transaction state, otherwise set tonaive
(default::naive
);:pool
- The pool module to use, seeDBConnection
for pool dependent options, this option must be included with all requests contacting the pool if notDBConnection.Connection
(default:DBConnection.Connection
);:types
- The types module to use, seePostgrex.TypeModule
, this option is only required when using custom encoding or decoding (default:Postgrex.DefaultTypes
);
Postgrex
uses the DBConnection
framework and supports all DBConnection
options like :idle
, :after_connect
etc.
See DBConnection.start_link/2
for more information.
Examples
iex> {:ok, pid} = Postgrex.start_link(database: "postgres")
{:ok, #PID<0.69.0>}
Run a query after connection has been established:
iex> {:ok, pid} = Postgrex.start_link(after_connect: &Postgrex.query!(&1, "SET TIME ZONE 'UTC';", []))
{:ok, #PID<0.69.0>}
stream(DBConnection.t, iodata | Postgrex.Query.t, list, Keyword.t) :: Postgrex.Stream.t
Returns a stream for a query on a connection.
Stream consumes memory in chunks of at most max_rows
rows (see Options).
This is useful for processing large datasets.
A stream must be wrapped in a transaction and may be used as an Enumerable
or a Collectable
.
When used as an Enumerable
with a COPY .. TO STDOUT
SQL query no other
queries or streams can be interspersed until the copy has finished. Otherwise
it is possible to intersperse enumerable streams and queries.
When used as a Collectable
the values are passed as copy data with the
query. No other queries or streams can be interspersed until the copy has
finished. If the query is not copying to the database the copy data will still
be sent but is silently discarded.
Options
:max_rows
- Maximum numbers of rows in a result (default to500
):decode_mapper
- Fun to map each row in the result to a term after decoding, (default:fn x -> x end
);:mode
- set to:savepoint
to use a savepoint to rollback to before an execute on error, otherwise set to:transaction
(default::transaction
);
Examples
Postgrex.transaction(pid, fn(conn) ->
query = Postgrex.prepare!(conn, "", "COPY posts TO STDOUT")
stream = Postgrex.stream(conn, query, [])
result_to_iodata = fn(%Postgrex.Result{rows: rows}) -> rows end
Enum.into(stream, File.stream!("posts"), result_to_iodata)
end)
Postgrex.transaction(pid, fn(conn) ->
stream = Postgrex.stream(conn, "COPY posts FROM STDIN", [])
Enum.into(File.stream!("posts"), stream)
end)
transaction(conn, (DBConnection.t -> result), Keyword.t) :: {:ok, result} | {:error, any} when result: var
Acquire a lock on a connection and run a series of requests inside a
transaction. 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.
rollback/2
rolls back the transaction and causes the function to
return {:error, reason}
.
transaction/3
can be nested multiple times if the connection
reference is used to start a nested transaction. The top level
transaction function is the actual transaction.
Options
:pool_timeout
- Time to wait in the queue for the connection (default:5000
):queue
- Whether to wait for connection in a queue (default:true
);:timeout
- Transaction timeout (default:15000
);:pool
- The pool module to use, must match that set onstart_link/1
, seeDBConnection
;:mode
- Set to:savepoint
to use savepoints instead of an SQL transaction, otherwise set to:transaction
(default::transaction
);
The :timeout
is for the duration of the transaction and all nested
transactions and requests. This timeout overrides timeouts set by internal
transactions and requests. The :pool
and :mode
will be used for all
requests inside the transaction function.
Example
{:ok, res} = Postgrex.transaction(pid, fn(conn) ->
Postgrex.query!(conn, "SELECT title FROM posts", [])
end)