@spec child_spec([start_option()]) :: :supervisor.child_spec()

Returns a supervisor child specification for a DBConnection pool.

@spec close!(conn(), Postgrex.Query.t(), [option()]) :: :ok

Closes an (extended) prepared query and returns :ok or raises Postgrex.Error if there was an error. See close/3.

@spec close(conn(), Postgrex.Query.t(), [option()]) :: :ok | {:error, Exception.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

Options

• :queue - Whether to wait for connection in a queue (default: true);
• :timeout - Close request timeout (default: 15000);
• :mode - set to :savepoint to use a savepoint to rollback to before the close on error, otherwise set to :transaction (default: :transaction);

examples

Examples

query = Postgrex.prepare!(conn, "", "CREATE TABLE posts (id serial, title text)")
Postgrex.close(conn, query)

@spec execute!(conn(), Postgrex.Query.t(), list(), [execute_option()]) ::
  Postgrex.Result.t()

Runs an (extended) prepared query and returns the result or raises Postgrex.Error if there was an error. See execute/4.

@spec execute(conn(), Postgrex.Query.t(), list(), [execute_option()]) ::
  {:ok, Postgrex.Query.t(), Postgrex.Result.t()} | {:error, Postgrex.Error.t()}

Runs an (extended) prepared query.

It returns the result as {:ok, %Postgrex.Query{}, %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.

options

Options

• :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);
• :mode - set to :savepoint to use a savepoint to rollback to before the execute on error, otherwise set to :transaction (default: :transaction);

examples

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%"])

@spec parameters(conn(), [option]) :: %{required(binary()) => binary()}
when option: {:timeout, timeout()}

Returns a cached map of connection parameters.

options

Options

• :timeout - Call timeout (default: 15000)

@spec prepare!(conn(), iodata(), iodata(), [option()]) :: Postgrex.Query.t()

Prepares an (extended) query and returns the prepared query or raises Postgrex.Error if there was an error. See prepare/4.

@spec prepare(conn(), iodata(), iodata(), [option()]) ::
  {:ok, Postgrex.Query.t()} | {:error, Exception.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

Options

• :queue - Whether to wait for connection in a queue (default: true);
• :timeout - Prepare request timeout (default: 15000);
• :mode - set to :savepoint to use a savepoint to rollback to before the prepare on error, otherwise set to :transaction (default: :transaction);

examples

Examples

Postgrex.prepare(conn, "", "CREATE TABLE posts (id serial, title text)")

@spec prepare_execute!(conn(), iodata(), iodata(), list(), [execute_option()]) ::
  {Postgrex.Query.t(), Postgrex.Result.t()}

Prepares and runs a query and returns the result or raises Postgrex.Error if there was an error. See prepare_execute/5.

@spec prepare_execute(conn(), iodata(), iodata(), list(), [execute_option()]) ::
  {:ok, Postgrex.Query.t(), Postgrex.Result.t()} | {:error, Postgrex.Error.t()}

Prepares and executes a query in a single step.

It returns the result as {:ok, %Postgrex.Query{}, %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.

options

Options

• :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);
• :mode - set to :savepoint to use a savepoint to rollback to before the execute on error, otherwise set to :transaction (default: :transaction);

examples

Examples

Postgrex.prepare_execute(conn, "", "SELECT id FROM posts WHERE title like $1", ["%my%"])

@spec query!(conn(), iodata(), list(), [execute_option()]) :: Postgrex.Result.t()

Runs an (extended) query and returns the result or raises Postgrex.Error if there was an error. See query/3.

@spec query(conn(), iodata(), list(), [execute_option()]) ::
  {:ok, Postgrex.Result.t()} | {:error, Exception.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

Options

• :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);
• :mode - set to :savepoint to use a savepoint to rollback to before the query on error, otherwise set to :transaction (default: :transaction);
• :cache_statement - Caches the query with the given name

examples

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", [])

@spec rollback(DBConnection.t(), reason :: any()) :: no_return()

Rollback a transaction, does not return.

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

example

Example

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

@spec start_link([start_option()]) ::
  {:ok, pid()} | {:error, Postgrex.Error.t() | term()}

Start the connection process and connect to postgres.

options

Options

Postgrex provides multiple ways to connect to the server, listed in order of precedence below:

• :hostname - Server hostname (default: PGHOST env variable, then localhost);
• :port - Server port (default: PGPORT env variable, then 5432);
• :endpoints - A list of endpoints (host and port pairs, with an optional extra_opts keyword list); Postgrex will try each endpoint in order, one by one, until the connection succeeds; The syntax is [{host1, port1},{host2, port2},{host3, port3}] or [{host1, port1, extra_opt1: value},{host2, port2, extra_opt2: value}}]; This option takes precedence over :hostname+:port;
• :socket_dir - Connect to PostgreSQL via UNIX sockets in the given directory; The socket name is derived based on the port. This is the preferred method for configuring sockets and it takes precedence over the hostname. If you are connecting to a socket outside of the PostgreSQL convention, use :socket instead;
• :socket - Connect to PostgreSQL via UNIX sockets in the given path. This option takes precedence over the :hostname, :endpoints and :socket_dir;

Once a server is specified, you can configure the connection with the following:

• :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);

• :ping_timeout - Socket receive timeout when idle in milliseconds (defaults to :timeout value);

• :idle_interval - Ping connections after a period of inactivity in milliseconds. Defaults to 1000ms;

• :ssl - Set to true if ssl should be used (default: false);

• :ssl_opts - A list of ssl options, see the tls_client_option from the ssl docs;

• :socket_options - Options to be given to the underlying socket (applies to both TCP and UNIX sockets);

• :target_server_type - Allows opening connections to a server in the given replica mode. The allowed values are :any, :primary and :secondary (default: :any). If this option is used together with endpoints, we will traverse all endpoints until we find an endpoint matching the server type;

• :disconnect_on_error_codes - List of error code atoms that when encountered will disconnect the connection. This is useful when using Postgrex against systems that support failover, which when it occurs will emit certain error codes e.g. :read_only_sql_transaction (default: []);

• :show_sensitive_data_on_connection_error - By default, Postgrex hides all information during connection errors to avoid leaking credentials or other sensitive information. You can set this option if you wish to see complete errors and stacktraces during connection errors;

The following options controls the pool and other Postgrex features:

• :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 to :naive (default: :strict);

• :pool - The pool module to use, defaults to DBConnection.ConnectionPool. See the pool documentation for more options. The default :pool_size for the default pool is 1. If you set a different pool, this option must be included with all requests contacting the pool;

• :types - The types module to use, see Postgrex.Types.define/3, this option is only required when using custom encoding or decoding (default: Postgrex.DefaultTypes);

• :search_path - A list of strings used to set the search path for the connection. This is useful when, for instance, an extension like citext is installed in a separate schema. If that schema is not in the connection's search path, Postgrex might not be able to recognize the extension's data type. When this option is nil, the search path is not modified. (default: nil). See the PostgreSQL docs for more details.

Postgrex uses the DBConnection library and supports all DBConnection options like :idle, :after_connect etc. See DBConnection.start_link/2 for more information.

examples

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>}

Connect to postgres instance through a unix domain socket

iex> {:ok, pid} = Postgrex.start_link(socket_dir: "/tmp", database: "postgres")
{:ok, #PID<0.69.0>}

ssl-client-authentication

SSL client authentication

When connecting to Postgres or CockroachDB instances over SSL it is idiomatic to use certificate authentication. Config files do not allowing passing functions, so use the init callback of the Ecto supervisor.

In your Repository configuration:

config :app, App.Repo,
  ssl: String.to_existing_atom(System.get_env("DB_SSL_ENABLED", "true")),
  verify_ssl: true

And in App.Repo, set your :ssl_opts:

def init(_type, config) do
  config =
    if config[:verify_ssl] do
      Keyword.put(config, :ssl_opts, my_ssl_opts(config[:hostname]))
    else
      config
    end

  {:ok, config}
end

def my_ssl_opts(server) do
  [
    verify: :verify_peer,
    cacertfile: System.get_env("DB_CA_CERT_FILE"),
    server_name_indication: String.to_charlist(server),
    customize_hostname_check: [match_fun: :public_key.pkix_verify_hostname_match_fun(:https)],
    depth: 3
  ]
end

pgbouncer

PgBouncer

When using PgBouncer with transaction or statement pooling named prepared queries can not be used because the bouncer may route requests from the same postgrex connection to different PostgreSQL backend processes and discards named queries after the transactions closes. To force unnamed prepared queries set the :prepare option to :unnamed.

handling-failover

Handling failover

Some services, such as AWS Aurora, support failovers. The 2 options endpoints and target_server_type can be used together to achieve a faster fail-over.

Imagine an AWS Aurora cluster named "test" with 2 instances. Use the following options minimize downtime by ensuring that Postgrex connects to the new primary instance as soon as possible.

{:ok, pid} = Postgrex.start_link(
  endpoints: [
    {"test.cluster-xyz.eu-west-1.rds.amazonaws.com", 5432},
    {"test.cluster-ro-xyz.eu-west-1.rds.amazonaws.com", 5432}
  ],
  target_server_type: :primary,
  (...)
)

In the event of a fail-over, Postgrex gets first disconnected from what used to be the primary instance. The primary instance will then reboot and turn into a secondary instance. Meanwhile, one of the secondary instances will have turned into the new primary instance. However, the DNS entry of the primary endpoint provided by AWS can take some time to get updated. That is why it can be faster to let Postgrex iterate over all the instances of the cluster to find the new primary instance instead of waiting for the DNS update.

If the cluster does not have DNS-backed primary and secondary endpoints (like the ones provided by AWS Aurora) or if the cluster is made of more than 2 instances, the hostname (and port) of all of the individual instances can be specified in the endpoints list:

endpoints: [
  {"test-instance-1.xyz.eu-west-1.rds.amazonaws.com", 5432},
  {"test-instance-2.xyz.eu-west-1.rds.amazonaws.com", 5432},
  (...),
  {"test-instance-N.xyz.eu-west-1.rds.amazonaws.com", 5432}
]

failover-with-ssl-support

Failover with SSL support

As specified in Erlang :ssl.connect, host verification using :public_key.pkix_verify_hostname_match_fun(:https) requires that the ssl_opt server_name_indication is set, and for this reason, the aforementioned endpoints list can become a three element tuple as:

endpoints: [
  {
    "test-instance-1.xyz.eu-west-1.rds.amazonaws.com",
    5432,
    [ssl: [server_name_indication: String.to_charlist("test-instance-1.xyz.eu-west-1.rds.amazonaws.com")]]
  },
  (...),
  {
    "test-instance-2.xyz.eu-west-1.rds.amazonaws.com",
    5432,
    [ssl: [server_name_indication: String.to_charlist("test-instance-2.xyz.eu-west-1.rds.amazonaws.com")]]
  }
]

@spec stream(DBConnection.t(), iodata() | Postgrex.Query.t(), list(), [option]) ::
  Postgrex.Stream.t()
when option: execute_option() | {:max_rows, pos_integer()}

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

Options

• :max_rows - Maximum numbers of rows in a result (default to 500)
• :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

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)

@spec transaction(conn(), (DBConnection.t() -> result), [option()]) ::
  {: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

Options

• :queue - Whether to wait for connection in a queue (default: true);
• :timeout - Transaction timeout (default: 15000);
• :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 :mode will be used for all requests inside the transaction function.

example

Example

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