postgres

postgres

PostgreSQL driver module for rio, the Go ORM, built on pgx. Runs through the pgx database/sql adapter by default, or fully natively (OpenNative) for the fastest read path.

It provides constructors, eager DSN validation, the pgx-native execution channel, and an error translator that maps *pgconn.PgError onto rio sentinels, keeping the original pgx error in the chain for errors.As. SQL rendering stays in the rio core.

Install

go get github.com/go-rio/postgres

Usage

db, err := postgres.Open("postgres://user:pass@localhost:5432/app")
if err != nil {
	log.Fatal(err)
}
defer db.Close()

err = rio.Insert(ctx, db, &user) // RETURNING fills the whole row back

if errors.Is(err, rio.ErrDuplicateKey) {
	var pgErr *pgconn.PgError
	errors.As(err, &pgErr) // constraint name, table, detail preserved
}

• The DSN is passed to pgx untouched: URL form, keyword/value form, and every pgx runtime parameter, except standard_conforming_strings=off (below).
• Open validates the DSN without connecting; ping db.Unwrap() to check connectivity eagerly.

standard_conforming_strings

rio rewrites ? placeholders assuming standard_conforming_strings=on (the default since PostgreSQL 9.1), where backslash inside a '...' literal is ordinary. Turned off, backslash becomes an escape character, so rio and the server can disagree on the placeholder count; rio fails with an arity error, not a misbound query. The setting is unsupported, as the mysql sibling pins sql_mode.

Re-enable per connection when the server disables it globally (URL form, %20 is a space, %3D is =):

postgres://user:pass@localhost:5432/app?options=-c%20standard_conforming_strings%3Don

Keyword/value form:

host=localhost dbname=app options='-c standard_conforming_strings=on'

Choosing a constructor

Three tiers, each with a bring-your-own variant. DAO code is identical across tiers; switching is a one-line constructor swap.

The pgx pool tier

OpenPool parses the DSN with pgxpool.ParseConfig: every Open DSN plus pgxpool pool_* parameters (pool_max_conns, pool_min_conns, pool_max_conn_lifetime, pool_max_conn_idle_time, pool_health_check_period). The standard_conforming_strings guard still applies.

db, err := postgres.OpenPool(ctx, "postgres://user:pass@localhost:5432/app?pool_max_conns=10")
if err != nil {
	log.Fatal(err)
}
defer db.Close() // closes the database/sql view, then the pool

pool := postgres.PoolOf(db)    // *pgxpool.Pool: Ping, Stat, CopyFrom, LISTEN
err = pool.Ping(ctx)           // OpenPool validates but never connects

• NewFromPool wraps a pool built from your own pgxpool.Config (tracers, AfterConnect, MinConns, a custom query exec mode).
• Both constructors take over the pool's Close (as New does for *sql.DB): closing the rio handle closes the pool, blocking until acquired connections return; a later pool.Close() is a no-op. Keep a pool out of NewFromPool if it must outlive the rio.DB.
• Connection counts belong to the pgxpool config here; leave SetMaxOpenConns/SetMaxIdleConns off db.Unwrap(). The view holds zero idle database/sql connections (pgx's documented requirement), so an idle view connection never pins a pool connection away from direct pool users.

The native tier

OpenNative builds the same pgxpool as OpenPool, then skips database/sql: rendered SQL goes straight to pgx, and decoded values flow through pgtype's typed scanner interfaces into rio's scan cells with no boxing.

db, err := postgres.OpenNative(ctx, "postgres://user:pass@localhost:5432/app")
if err != nil {
	log.Fatal(err)
}
defer db.Close() // closes the database/sql view, then the pool

pool := postgres.PoolOf(db)      // the pgxpool: Ping, Stat, CopyFrom, LISTEN
err = db.Tx(ctx, func(tx *rio.Tx) error {
	ptx := postgres.TxOf(tx)     // the pgx.Tx behind this transaction
	_ = ptx                      // CopyFrom inside the transaction, etc.
	return nil
})

Contracts hold as on the other tiers: same rendered SQL, scanning rules (NULL handling, overflow checks, []byte copying), sentinel errors, QueryHook events, savepoint choreography, and errors.Is(err, context.Canceled) on cancellation. The full integration suite runs twice in CI, once per channel. Three differences:

Benchmarks (Apple M4, loopback PostgreSQL 17, bench/bench_pg_test.go, median of 3; real networks shrink the latency share, but the allocation savings are CPU-side and stay):

pgx's own pgx.CollectRows[T] costs ~316 allocs on the 100-row shape — the native channel beats even that helper.

Query exec mode and PgBouncer

The native tier uses pgx's default execution mode, QueryExecModeCacheStatement: statements are prepared and cached per connection automatically. rio never downgrades it. Change it in the DSN (?default_query_exec_mode=exec, simple_protocol, cache_describe, …) or on your own pgxpool.Config via NewNativeFromPool.

DDL note: under cache_statement, changing a table's shape invalidates cached plans; pgx detects cached plan must not change result type, invalidates, and retries read queries itself. On the database/sql tiers, rio's WithStmtCache eviction handles the same case — it evicts and propagates, never retries.

On the database/sql tiers behind PgBouncer, keep rio.WithStmtCache off (the default) and apply the same DSN matrix. Against PostgreSQL directly, leave it off too: pgx already caches prepared statements per connection, and stacking database/sql's statement layer on top measured slower in rio's bench suite.

Arrays and JSONB

PostgreSQL's rich column types map through rio with no dialect-specific API.

JSONB. Tag a field rio:",json" and rio (de)serializes it with encoding/json on every write and read — any Go value, no wrapper type and no manual []byte:

type Account struct {
	ID    int64
	Prefs map[string]any `rio:",json"` // jsonb column "prefs"
}

A set-based write goes the same way: rio.Set{"prefs": v} marshals v as JSON, because prefs is a json column.

Arrays. rio binds a field through any driver.Valuer and scans it back through any sql.Scanner, so an array column is a small wrapper type. pgx ships pgtype.Array[T]/FlatArray[T], but they do not implement those two database/sql interfaces directly — pgtype's own Map.SQLScanner doc notes they "need assistance from Map to implement the sql.Scanner interface". So the wrapper delegates (de)serialization to a *pgtype.Map instead of building the {...} literal by hand (element quoting and escaping are easy to get wrong):

import (
	"database/sql/driver"

	"github.com/jackc/pgx/v5/pgtype"
)

var pgMap = pgtype.NewMap()

type Tags []string // maps a text[] column

func (t Tags) Value() (driver.Value, error) {
	b, err := pgMap.Encode(pgtype.TextArrayOID, pgtype.TextFormatCode,
		pgtype.FlatArray[string](t), nil)
	if err != nil || b == nil { // b == nil ⇒ nil/NULL
		return nil, err
	}
	return string(b), nil
}

func (t *Tags) Scan(src any) error {
	return pgMap.SQLScanner((*pgtype.FlatArray[string])(t)).Scan(src)
}

Encode renders Tags{"a", "b,c"} to the literal {a,"b,c"} (pgx quotes the embedded comma); Scan parses it back. Declare the field as usual — a Tags field with a rio:"labels" tag — and it binds and scans on the Open/OpenPool tiers; pgx recognizes the same driver.Valuer/sql.Scanner on OpenNative too.

JSONB operators that contain ?. The existence operators ?, ?|, and ?& collide with rio's ? placeholder. Double each literal ?: rio's rebinder collapses ?? to a single ? and consumes no argument, so

rio.From[Account]().Where("prefs ?? ?", "beta").All(ctx, db)

is sent as prefs ? $1 with one bind ("beta"); ?| and ?& are written ??| and ??&.

Bulk-updating an array column. UpdateAll renders SET col = ?, and the rebinder will not expand a bare slice there (that would emit the malformed SET col = ?, ?). Passing one is a deliberate error:

rio: UpdateAll: column "labels" value is a slice, which SET cannot expand; wrap it in a driver.Valuer (e.g. pq.Array) or use rio.Expr

Wrap the slice in the Valuer type above — rio.Set{"labels": Tags{"a", "b"}} — or pass a rio.Expr for a database-side expression.

The rio family

rio — the ORM · migrate — schema migrations as Go code · sqlite / mysql — the sibling drivers

License

The MIT License (MIT). Please see License File for more information.