postgres

postgres

PostgreSQL driver module for rio, built on pgx. Two channels: database/sql (plain or over pgxpool) and native pgx execution.

Getting started

go get github.com/go-rio/postgres

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

users, err := rio.From[User]().Where("age > ?", 18).All(ctx, db)

Open validates the DSN — pgx URL or keyword/value form — but does not connect; ping with db.Unwrap().PingContext(ctx). New wraps an existing *sql.DB and leaves session configuration to the caller.

Error translation

The original *pgconn.PgError stays available through errors.As, including its constraint, table, and detail fields.

Execution paths

rio queries use the same API on all three paths.

standard_conforming_strings

rio's placeholder lexer requires standard_conforming_strings=on, the PostgreSQL default since 9.1. Open, OpenPool, and OpenNative reject an explicit false — set directly, through the options startup parameter, or through PGOPTIONS — and do not inject the setting when omitted. If the server disables it globally, enable it in the DSN:

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

New, NewFromPool, and NewNativeFromPool cannot inspect an existing pool; their callers must enforce this requirement.

pgxpool with database/sql

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

• OpenPool accepts pgxpool pool_* DSN parameters; NewFromPool takes a caller-built pool. PoolOf exposes the pool for Ping, Stat, AcquireFunc, CopyFrom, and LISTEN/NOTIFY.
• Both constructors transfer pool ownership to the rio DB. Closing the DB closes the pool and may block until acquired connections return.
• Configure connection counts on pgxpool, not on db.Unwrap(); the database/sql view keeps no idle connections.

Native pgx execution

OpenNative executes rio queries directly through pgx, preserving rio's SQL rendering, scanning rules, sentinel errors, hooks, and savepoint behavior. Preloads flush each relation layer in one pgx batch round trip. InsertAll streams explicit-key batches — no generated keys to backfill — over COPY.

db, err := postgres.OpenNative(ctx, "postgres://user:pass@localhost:5432/app")

NewNativeFromPool takes ownership of a caller-built pool; closing the DB closes the pool and may block until acquired connections return. db.Unwrap() stays available as a database/sql view for helpers such as migrations and pings, never for pool configuration.

rio's PostgreSQL benchmarks compare both channels.

Query exec mode and PgBouncer

The native path uses pgx's default cache_statement mode. Change it with the default_query_exec_mode DSN parameter or on a caller-built pool config.

On the database/sql paths, rio.WithStmtCache (off by default) adds DB- and transaction-local caches on top of pgx's per-connection cache. Do not use it behind transaction- or statement-mode poolers.

Arrays and JSONB

Tag JSON fields with rio:",json" to store them as jsonb (for example Prefs map[string]any on an Account model, column prefs); a set-based rio.Set{"prefs": v} uses the same mapping.

For PostgreSQL arrays, wrap pgtype in a driver.Valuer and sql.Scanner pair. The wrapper works on all three paths:

var pgMap = pgtype.NewMap()

type Tags []string

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 {
		return nil, err
	}
	return string(b), nil
}

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

For UpdateAll, pass the wrapper type or a rio.Expr, not a bare slice.

JSONB existence operators collide with rio's ? placeholder. Escape each literal question mark as ?? (?| → ??|, ?& → ??&):

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

Contributing

Use Go 1.27 or newer, then run go test ./..., go test -race ./..., and go vet ./... before opening a pull request.

License

MIT