DOCSEARCHbyAlgolia
Submit Docs

Postgrex Documentation

repository·master·Indexed 22 days ago

https://github.com/elixir-ecto/postgrex

A PostgreSQL driver for Elixir that utilizes the PostgreSQL binary protocol for efficient data encoding and decoding. It supports transactions, prepared queries, and custom type extensions via the Postgrex.Extension behaviour. The library provides built-in support for various PostgreSQL types including ranges, multiranges, network addresses, and geometric types, and includes a Notifications API for PostgreSQL's LISTEN/NOTIFY mechanism.

Tokens
4.7K
Snippets
5
Records
30
Agent score
78%

What's inside Postgrex

  1. Handle OID types in queries

    master

    Because Postgrex uses the PostgreSQL binary protocol, OID types (like regclass) are expected as integers rather than strings. If you pass a string to a parameter expecting an OID, the query will fail.

    You have three ways to handle this:

    1. Explicit Cast: Cast the parameter in the SQL string (e.g., $1::text::regclass).
    2. Pre-determine OID: Query for the OID once, store it, and use the integer in subsequent queries (most efficient).
    3. Pass Integer: Pass the integer OID directly.
    # Option 1: Explicit cast (works with binary protocol)
query("select nextval($1::text::regclass)", ["some_sequence"])

# Option 2: Determine OID once and reuse (most efficient)
%{rows: [{sequence_oid}]} = query("select $1::text::regclass", ["some_sequence"])
query("select nextval($1)", [sequence_oid])

  2. Quickstart: Connect to PostgreSQL and run queries

    master

    To use Postgrex, start a connection using Postgrex.start_link/1 with your database credentials. You can then execute queries using Postgrex.query!/3 (which raises on error) or Postgrex.query/3.

    Results are returned as %Postgrex.Result{} structs containing information about the command, columns, and rows.

  3. Extend Postgrex with custom types

    master

    You can extend Postgrex's encoding/decoding capabilities by defining custom type modules using Postgrex.Types.define/3.

    1. Define your extensions (e.g., using modules from the Postgrex.Extensions namespace).
    2. Call Postgrex.Types.define/3 in a dedicated file (outside of any module or function) to build your custom type module.
    3. Pass your custom type module to Postgrex.start_link/1 via the types: option.

    Important: Postgrex.Types.define/3 must be called at the top level of a file so it executes during compilation.

  4. Configure Postgrex for PgBouncer

    master

    If you are using an older version of PgBouncer (prior to 1.21.0) with transaction or statement pooling, named prepared statements may cause errors because the bouncer might route requests to different backend processes.

    To resolve this, force Postgrex to use unnamed prepared queries by setting prepare: :unnamed in Postgrex.start_link/1.

    Postgrex.start_link(prepare: :unnamed)

  5. Implement a custom PostgreSQL type extension

    master

    To support custom PostgreSQL types, implement the Postgrex.Extension behaviour. An extension defines how Elixir values are encoded to and decoded from PostgreSQL.

    Key requirements for implementation:

    • matching/1: Returns a list of attributes (like type: "ltree") that identify which PostgreSQL types this extension handles.
    • format/1: Specifies if the type uses :binary or :text format.
    • encode/1: Returns a quoted expression (macro) that converts an Elixir value into iodata(). The first 4 bytes of the iodata() must be the byte size of the remaining data as a signed 32-bit big-endian integer.
    • decode/1: Returns a quoted expression (macro) using binary pattern matching to decode the data. It must account for the 4-byte signed 32-bit big-endian length header.
    • init/1: Used for initialization; returns a state passed to other callbacks.
    defmodule MyApp.LTree do
  @behaviour Postgrex.Extension

  def init(opts) do
    Keyword.get(opts, :decode_copy, :copy)
  end

  def matching(_state), do: [type: "ltree"]

  def format(_state), do: :text

  def encode(_state) do
    quote do
      bin when is_binary(bin) ->
        [<<byte_size(bin)::signed-size(32)>> | bin]
    end
  end

  def decode(:reference) do
    quote do
      <<len::signed-size(32), bin::binary-size(len)>> ->
        bin
    end
  end

  def decode(:copy) do
    quote do
      <<len::signed-size(32), bin::binary-size(len)>> ->
        :binary.copy(bin)
    end
  end
end

  6. Use Postgrex.Stream for streaming query results

    master

    The Postgrex.Stream struct is returned from stream commands and implements the Enumerable protocol. This allows you to iterate over large query results using standard Elixir Enumerable functions like Enum.reduce/3.

    Internally, Postgrex.Stream wraps a DBConnection.Stream or DBConnection.PrepareStream to manage the data flow from PostgreSQL. Note that certain Enumerable operations like member?/2, count/1, and slice/3 are not supported and will return {:error, Postgrex.Stream}.

  7. Configure JSON support

    master

    Postgrex uses the Jason library for JSON support by default. To enable it, add :jason to your dependencies:

    {:jason, "~> 1.0"}

    To use a different JSON library, configure the :json_library option in your application configuration. Note: You must recompile Postgrex after changing this setting by cleaning the build:

    mix deps.clean postgrex --build
    config :postgrex, :json_library, SomeOtherLib

  8. Represent PostgreSQL geometric types

    master

    Postgrex provides several structs for PostgreSQL geometric types:

    • Postgrex.Point: Represents a point with x and y (floats).
    • Postgrex.Polygon: Represents a polygon with a list of vertices (Postgrex.Point.t()).
    • Postgrex.Line: Represents a line using the formula a*x + b*y + c = 0 with fields a, b, and c (floats).
    • Postgrex.LineSegment: Represents an lseg with point1 and point2 (Postgrex.Point.t()).
    • Postgrex.Box: Represents a box with upper_right and bottom_left (Postgrex.Point.t()).
    • Postgrex.Path: Represents a path with points ([Postgrex.Point.t()]) and an open boolean.
    • Postgrex.Circle: Represents a circle with a center (Postgrex.Point.t()) and a radius (number).

  9. Handle channel name casing in Postgrex.Notifications

    master

    PostgreSQL notification channels have specific casing rules. Postgrex.Notifications wraps channel names in quotes when listening. This means casing must be handled carefully to ensure matches.

    To ensure notifications are received, follow one of these two rules:

    1. Exact Casing: If you wrap the channel name in quotes when sending a notification (e.g., NOTIFY "fooBar", 'msg';), ensure the channel name has the exact same casing when listening.
    2. Lowercase: If you do NOT wrap the channel name in quotes when sending (e.g., NOTIFY foobar, 'msg';), PostgreSQL treats it as lowercase. In this case, you must provide the lowercased name when calling listen/3.

  10. Copy data to the database using Postgrex.Stream

    master

    The Postgrex.Stream implementation of the Collectable protocol allows you to use Stream.into/2 to copy data into the database.

    Important Requirements:

    • Data can only be copied to the database inside a transaction.
    • The data being copied must be in iodata format. If the data is not iodata, an ArgumentError will be raised with the message: expected iodata to copy to database, got: <data>.

    When using into/2, the stream will execute the necessary COPY commands to move the data into the database efficiently.

  11. Use Postgrex.Notifications for pub/sub

    master

    Postgrex.Notifications provides an API for PostgreSQL's LISTEN/NOTIFY mechanism. To use it, you must first start the notification process in your supervision tree, then use listen/3 to subscribe to specific channels.

    When a notification is broadcast on a channel, the subscribing process receives a message in the format: {:notification, notification_pid, listen_ref, channel, message}.

    Important: Handling Race Conditions There is a race condition between starting to listen and notifications being issued. To maintain data consistency, follow this three-step approach:

    1. Subscribe to the channel.
    2. Obtain the current state of the data.
    3. Handle incoming notifications.

    This approach should also be used when dealing with auto-reconnects.

  12. Enable support for infinite timestamps in Postgrex

    master

    By default, Postgrex raises an ArgumentError if it encounters PostgreSQL infinity timestamps (inf or -inf) when decoding timestamptz types. To support these values, you must define a custom types module using Postgrex.Types.define/3 with the allow_infinite_timestamps: true option, and then configure your database connection to use this module.

    When enabled, PostgreSQL infinity values are decoded as :inf or "-inf" in Elixir.