Crate postgresql

Expand description

An async PostgreSQL client with a macro-only, compile-time checked query API.

§Features

Compile-time checked queries — query! and query_as! connect to the database at build time (when DATABASE_URL is set) and verify the statement, its parameters and its result shape against your Rust types.
Async, tokio-only — one background task per connection; no busy polling and no async runtime dependency beyond tokio.
Connection pooling — bounded pool with idle/lifetime limits and connections returned automatically on drop.
Ergonomic row mapping — #[derive(FromRow)], tuples, and scalars.
JSON structs — #[derive(Json)] maps a serde type straight to a json / jsonb column, with no serde_json::Value in between.
Iterator array binding — bind UNNEST / = ANY arrays from a lazy iterator with no intermediate Vec.
TLS — rustls with the stdx crypto_rustls provider (TLS 1.3).
COPY — binary COPY ... FROM STDIN / TO STDOUT for bulk loads.
Scripts — execute_script runs multi-statement SQL (migrations, SET scripts, VACUUM) via the simple query protocol.
LISTEN / NOTIFY — Connection::notifications streams notifications from the server.
Cancellation — an abandoned query is drained in the background and, if it is still running two seconds later, cancelled with a CancelRequest.
Zero-copy accessors — Row::get_str / Row::get_bytes borrow from the received frame.
Transactions without lifetimes — Connection, Pool, PooledConnection and Transaction all implement Executor, so generic code works with any of them.

§Quick start

use postgresql::{query, query_as, FromRow};

#[derive(FromRow, Debug)]
struct User {
    id: i32,
    name: String,
    email: Option<String>,
}

// Compile-time checked when DATABASE_URL is set at build time.
let users: Vec<User> = query_as!(User,
    "SELECT id, name, email FROM users WHERE age >= $1",
    18)
    .fetch_all(&pool)
    .await?;

let count: i64 = query_as!(i64, "SELECT count(*)::int8 FROM users")
    .fetch_one(&pool)
    .await?;

let affected: u64 = query!("UPDATE users SET active = true WHERE age > $1", 18)
    .execute(&pool)
    .await?;

§Connecting

use postgresql::{Connection, Pool};

let conn = Connection::connect("postgres://user:pass@localhost/app").await?;
let pool = Pool::connect("postgres://user:pass@localhost/app?pool_size=16", None).await?;

Only postgres:// and postgresql:// URLs are accepted. Supported query parameters are sslmode, sslrootcert, connect_timeout (seconds), application_name, options, and the pool settings pool_size, min_connections, acquire_timeout, idle_timeout and max_lifetime (seconds). timezone sets the IANA zone used for zone-less timestamp/date/time values (default UTC), and statement_cache_size bounds the prepared-statement cache (0 disables it, for transaction-mode poolers). max_message_len bounds the largest backend message the client will buffer, in bytes (default 128 MiB); max_scram_iterations caps the SCRAM iteration count the server may request (default 2,000,000). Unknown parameters are rejected so typos surface immediately.

connect_timeout bounds the whole connection attempt: the TCP connect, the TLS handshake and authentication. A server that accepts TCP and then stalls cannot hang Connection::connect.

§TLS is required

This client always connects over TLS; plaintext connections are not possible. sslmode=disable, prefer and allow are rejected with a configuration error. sslmode selects the verification level:

`sslmode`	Behaviour
`require` (default)	TLS, certificate not verified (like libpq’s `require`)
`verify-ca`	TLS, certificate chain verified, hostname not checked
`verify-full`	TLS, chain and hostname verified

TLS uses rustls with the stdx crypto_rustls provider (TLS 1.3 only).

§Transactions

use postgresql::query;
let txn = pool.begin().await?;
query!("INSERT INTO accounts (id, balance) VALUES ($1, $2)", 1i32, 100i64)
    .execute(&txn)
    .await?;
txn.commit().await?;

// Dropping without committing rolls back and returns the connection.

§Generic code over any executor

Executor is a sealed marker implemented by Connection, PooledConnection, Pool and Transaction. Queries are always run through the macros, so generic helpers look like this:

async fn count_users<E: postgresql::Executor>(db: &E, min_age: i32)
    -> Result<i64, postgresql::Error>
{
    query_as!(i64, "SELECT count(*)::int8 FROM users WHERE age >= $1", min_age)
        .fetch_one(db)
        .await
}

§Iterator array binding (`UNNEST` / `= ANY`)

array() wraps a lazy iterator as a PostgreSQL array without allocating an intermediate Vec:

use postgresql::query;
query!(
    "INSERT INTO invitations (id, name)
     SELECT * FROM unnest($1::int4[], $2::text[])",
    postgresql::array(invitations.iter().map(|i| i.0)),
    postgresql::array(invitations.iter().map(|i| i.1.as_str())),
)
.execute(&db)
.await?;

Vec<T> and &[T] bind as arrays directly; array(iter) is for lazy iterators and is single-use.

§Bulk loads (`COPY`)

copy_in! and copy_out! check the COPY statement against the target type at build time (by resolving the relation in the catalog) and stream binary rows. #[derive(ToRow)] encodes a struct’s fields in declaration order.

use postgresql::copy_in;
let copy = copy_in!(conn, (i32, String), "COPY users (id, name) FROM STDIN BINARY").await?;
for i in 0..1_000_000i32 {
    copy.write_row(&(i, format!("user-{i}"))).await?;
}
let copied = copy.finish().await?;

Only COPY ... FROM STDIN BINARY and COPY ... TO STDOUT BINARY are supported.

§Scripts, notifications and cancellation

use postgresql::{ScriptExecutor, query};

// Multi-statement scripts (unchecked, parameterless).
conn.execute_script("CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT)").await?;

// LISTEN / NOTIFY.
conn.execute_script("LISTEN jobs").await?;
let mut notifications = conn.notifications();
query!("NOTIFY jobs, 'go'").execute(&conn).await?;
let n = notifications.recv().await.unwrap();

// Dropping a row stream drains the result and, if the query is still
// running after a short delay, cancels it server-side.

§Supported types

Rust type	PostgreSQL type
`bool`	`bool`
`i16`, `i32`, `i64`	`int2`, `int4`, `int8`
`f32`, `f64`	`float4`, `float8`
`String` / `&str`	`text`, `varchar`, `bpchar`, `name`
`Vec<u8>` / `&[u8]`	`bytea`
`uuid::Uuid`	`uuid`
`time::DateTime`	`timestamptz`, `timestamp`, `date`, `time`, `timetz`
`serde_json::Value`	`json`, `jsonb`
`#[derive(Json)]` types, `Json<T>`	`json`, `jsonb`
`ipnetwork::IpNetwork`	`inet`, `cidr`
`Interval`	`interval`
`Option<T>`	`NULL` if `None`
`Vec<T>`, `&[T]`, `Array`	arrays

numeric / decimal is not supported yet. Cast it in SQL to a supported type (amount::text for exact values, amount::float8 for approximate ones) or store it as int8 / f64.

Decoding a column into a Rust type it cannot represent (say an int4 column into bool) is an error, not a reinterpretation of the bytes.

§Limits and unsupported values

Arrays must be one-dimensional; a multi-dimensional array is a decode error.
PostgreSQL’s infinity / -infinity date and timestamp values are rejected with a decode error: they are not a real instant and cannot be represented as a [time::DateTime].
Temporal values are bounded by time::DateTime’s range, years −9999 to 9999, even though PostgreSQL can represent dates from 4713 BC to 294276 AD. A value outside that range is a decode error. time and timetz values must fall within a single day (24:00:00 is rejected).
time and timetz are decode-only. They decode to a time::DateTime whose civil fields are the time of day (on the Unix epoch day); there is no encoder for them (use Timestamp / Date to write temporal values).
A single backend message, and a single binary COPY row, is bounded by max_message_len (128 MiB by default). A larger message or row is a decode error rather than unbounded buffering.
COPY ... FROM STDIN and COPY ... TO STDOUT can only be run through copy_in! / copy_out!. Running one through query! / query_as! or execute_script is an error; the client leaves COPY mode and keeps the connection usable.
SCRAM authentication is capped at max_scram_iterations iterations (2,000,000 by default); the server’s PBKDF2 work runs on the blocking pool, so it never stalls an async worker. The password is prepared with SASLprep before key derivation.
Not yet supported: SCRAM channel binding (SCRAM-SHA-256-PLUS), multi-host connection URLs, the numeric / decimal type, and streaming replication (CopyBothResponse).
Connection::notifications delivers notifications through a bounded broadcast channel; a subscriber that falls behind misses the oldest notifications instead of buffering them without bound.

The examples below use a fictional schema, so they are marked ignore (the query! macro checks the SQL against a live database at build time). The same code paths are exercised by the crate’s integration tests.

§JSON

The preferred way to map a Rust type to a json / jsonb column is #[derive(Json)], together with serde’s Serialize and Deserialize:

use postgresql::{FromRow, Json, query, query_as};

#[derive(serde::Serialize, serde::Deserialize, Json, Debug)]
struct User {
    name: String,
    admin: bool,
}

#[derive(FromRow, Debug)]
struct Something {
    id: i32,
    name: String,
    user: User,
}

let rows: Vec<Something> = query_as!(Something, "SELECT id, name, user FROM something")
    .fetch_all(&pool)
    .await?;
query!("UPDATE something SET user = $1", something.user)
    .execute(&pool)
    .await?;

#[derive(Json)] makes the type usable directly as a field, a parameter, a tuple element, or a whole result column, and its jsonb type is checked at compile time like any other. Option<T> gives a nullable column and Vec<T> a jsonb[].

A fully automatic mapping for any Serialize / Deserialize type is not possible: a blanket impl<T: DeserializeOwned> FromSql for T would overlap the impls for i32, String and every other built-in type, so the mapping has to be opted into. Two forms are provided:

#[derive(Json)] — preferred; see above.
[Json<T>] — for cases the derive cannot cover: a type that already derives FromRow (deriving Json too would define FromRow and RowShape twice), or a value with no nameable type, such as HashMap<String, i32>:

use postgresql::{FromRow, Json, query};

#[derive(FromRow)]
struct Something {
    id: i32,
    attributes: Json<HashMap<String, i32>>,
}

query!("UPDATE something SET attributes = $1", something.attributes)
    .execute(&pool)
    .await?;

Both forms encode as jsonb (with the usual version byte) and accept both json and jsonb columns on decode. Writing to an actual json (not jsonb) column needs the same SQL cast as serde_json::Value ($1::jsonb::json).

§Compile-time checking

When DATABASE_URL is set at build time and STDX_POSTGRESQL_CHECK is not false, the query and COPY macros connect to the database and check the statement. query! / query_as! verify the SQL, the number and types of parameters, and the result columns (names, types, nullability) against the target type. copy_in! / copy_out! verify the COPY column list against the target type by resolving the relation in pg_catalog. A mismatch is a compile error. When the SQL is not a string literal, or checking is disabled, the code compiles and is checked at runtime instead.

§Authentication

SCRAM-SHA-256 is used for password authentication; MD5 is rejected. The server’s SCRAM challenge is validated (bounded iteration count and salt, nonce, server signature), so a hostile endpoint cannot make the client do unbounded work during login.

Modules§

types: PostgreSQL type identifiers and wire formats.

Macros§

copy_in: Builds a compile-time checked binary COPY ... FROM STDIN.
copy_out: Builds a compile-time checked binary COPY ... TO STDOUT.
query: Builds an untyped query. See the crate documentation.
query_as: Builds a query decoded into a named type. See the crate documentation.

Structs§

Array: A single-use adapter that binds an iterator as a PostgreSQL array.
CancelToken: A token that cancels the query currently running on a connection.
Column: Metadata for one result column.
ColumnSpec: One expected result column.
Columns: The column metadata shared by every row of one result set.
Config: A parsed postgres:// connection URL.
Connection: A live connection to one PostgreSQL server.
CopyIn: A writer for a binary COPY ... FROM STDIN decoded from T.
CopyOut: A reader for a binary COPY ... TO STDOUT decoded into T.
Date: Encodes a time::DateTime as a date (taken in UTC).
Interval: A PostgreSQL interval: an exact count of months and days plus a microsecond time component.
Json: A value stored as a PostgreSQL json / jsonb column.
Notification: A server notification from LISTEN / NOTIFY.
Pool: A pool of reusable connections.
PoolConfig: Connection pool tuning.
PooledConnection: A connection checked out of a Pool.
Query: A prepared query returning untyped crate::Rows.
QueryAs: A prepared query decoded into T.
Row: One result row.
RowStream: A stream of rows from a query.
Timestamp: Encodes a time::DateTime as a zone-less timestamp.
Transaction: An open transaction.

Enums§

Error: The error type returned by every fallible operation in this crate.
IsNull: Whether an encoded value is SQL NULL.
SslMode: How the client verifies the server certificate.

Traits§

Executor: Anything a query can run against: a connection, a pooled connection, a pool, or a transaction.
FromCopyRow: A value that can be decoded from one binary COPY row.
FromRow: A type that can be built from a Row.
FromSql: A value that can be decoded from the PostgreSQL binary format.
RowShape: A type whose result columns can be checked at compile time.
ScriptExecutor: Runs multi-statement SQL scripts via the simple query protocol.
StaticType: A value with a statically known PostgreSQL type OID.
ToRow: A value that can be written as one binary COPY row.
ToSql: A value that can be bound as a query parameter in the binary format.

Functions§

array: Binds a lazy iterator as a one-dimensional PostgreSQL array.
encode_copy_row: Encodes one binary-format COPY row: an Int16 field count followed by each field as an Int32 length (or -1 for NULL) and its bytes.

Derive Macros§

FromRow: Derives [postgresql::FromRow] for a struct with named fields.
Json: Derives json / jsonb encoding and decoding for a type.
ToRow: Derives [postgresql::ToRow] for a struct with named fields.