postgres

package module
v0.3.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Jul 10, 2026 License: MIT Imports: 15 Imported by: 0

README

postgres

Doc Go Release Test Report Card License

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

The module is deliberately thin — that is the design, not a first draft. It provides the constructors, eager DSN validation, a precise error translator that maps *pgconn.PgError onto rio's sentinels (SQLSTATE 23505 becomes rio.ErrDuplicateKey, 23503 becomes rio.ErrForeignKeyViolated, the original pgx error kept in the chain for errors.As), and the pgx-native execution channel. Everything that shapes SQL lives 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 — all still there
}

The DSN is handed to pgx untouched: URL form, keyword/value form, and every pgx runtime parameter all work — with one exception, described in the next section. Open validates the DSN but does not connect; ping db.Unwrap() to check connectivity eagerly.

standard_conforming_strings

rio rewrites ? placeholders by lexing your SQL the way a stock PostgreSQL server does: with standard_conforming_strings=on — the default since PostgreSQL 9.1 — a backslash inside a '...' literal is an ordinary character. Turning the setting off makes the server treat backslash as an escape character again, so a literal in your SQL could hide or expose a ? differently on each side and the placeholder count would diverge (rio fails loudly with an arity error rather than sending a misbound query). The setting is therefore not supported. Open keeps the invariant the same way the mysql sibling pins sql_mode:

  • The setting is never mentioned → nothing is injected; the session uses the server's value, which is on unless an operator changed it.
  • The DSN — or the PGOPTIONS environment variable, which pgx also reads — turns it off, either directly (standard_conforming_strings=off) or through the options startup parameter (options=-c standard_conforming_strings=off) → Open returns an error naming the setting.
  • An explicit on is redundant but harmless and passes through.

If your server turns the setting off globally, turn it back on for rio's connections in the DSN (URL form, %20 is a space and %3D is =):

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

or in 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 on all of them, and moving between tiers is a one-line constructor swap.

Tier Constructors What you get
database/sql Open · New The default. database/sql manages connections, so everything in that ecosystem — sqlmock, otelsql wrappers, your own *sql.DB tuning — plugs in unchanged.
pgx pool OpenPool · NewFromPool Same query semantics, pgxpool manages connections: health checks, connection lifetime and idle caps, AfterConnect, Stat() metrics — and PoolOf(db) opens the door to CopyFrom and LISTEN. Measured performance-neutral next to Open; choose it for the pool, not for speed.
pgx native OpenNative · NewNativeFromPool The fastest channel: queries run on pgx directly, no driver.Value boxing. Same SQL, same scanning rules, same errors, same hooks and savepoints — measured on loopback (median of 3): the 100-row read drops from 433 to 124 allocs/op (−71%, −20% bytes), single-row reads 30→18, Insert 19→14, Update 9→6. pgx semantics apply: exec mode comes from the DSN, and TxOf(tx) replaces tx.Unwrap() inside transactions.

The pgx pool tier

OpenPool parses the DSN with pgxpool.ParseConfig — every Open DSN works, plus pgxpool's pool_* parameters (pool_max_conns, pool_min_conns, pool_max_conn_lifetime, pool_max_conn_idle_time, pool_health_check_period) — and applies the same standard_conforming_strings guard:

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 you built from your own pgxpool.Config (tracers, AfterConnect, MinConns, a custom query exec mode). Like New taking over the *sql.DB, both constructors take over the pool's Close: closing the rio handle closes the pool, blocking until acquired connections are returned; a further pool.Close() of your own is a harmless no-op. Keep a pool out of NewFromPool if it must outlive the rio.DB.

Connection counts belong to the pgxpool configuration on this tier — leave SetMaxOpenConns/SetMaxIdleConns off db.Unwrap(). The view keeps 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 and then skips the database/sql layer entirely: rio's rendered SQL goes straight to pgx, and decoded values flow through pgtype's typed scanner interfaces into rio's scan cells with no boxing in between.

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

Everything rio promises holds unchanged — same rendered SQL, scanning rules (NULL handling, overflow checks, []byte copying), sentinel errors, QueryHook events, savepoint choreography, errors.Is(err, context.Canceled) on cancellation. The full integration suite runs twice in CI, once per channel, to keep it that way. Three differences, all loud:

  • tx.Unwrap() returns nil inside transactions — there is no *sql.Tx on this tier. Use postgres.TxOf(tx) for the pgx.Tx. (db.Unwrap() still works: it returns a database/sql view over the same pool for pool-agnostic helpers like pings and migrations; don't tune pooling on it.)
  • rio.WithStmtCache panics at construction — statement caching belongs to pgx's query exec mode here (see below), not to a database/sql layer that no longer exists.
  • Error text can carry pgx prefixes (timeouts, scan errors). The errors.Is/errors.As contracts are identical — only prose differs.

Numbers (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):

shape rio (stdlib) rio (native) hand-written database/sql GORM
read 1 row 30 allocs · 1.3 KB 18 allocs · 1.0 KB 30 allocs · 1.3 KB 82 allocs · 6.5 KB
read 100 rows 433 allocs · 33 KB 124 allocs · 27 KB 532 allocs · 41 KB 1172 allocs · 59 KB
insert 19 allocs 14 allocs 20 allocs 93 allocs
update 9 allocs 6 allocs 7 allocs 93 allocs

For comparison, pgx's own pgx.CollectRows[T] idiom costs ~316 allocs on the 100-row shape — the native channel is faster than the driver's own collection helper, not just faster than database/sql.

Query exec mode and PgBouncer

The native tier uses pgx's own default execution mode, QueryExecModeCacheStatement: statements are prepared and cached per connection automatically. rio does not downgrade it behind your back — choosing OpenNative is choosing pgx, and its default is part of the deal. Change it in the DSN (?default_query_exec_mode=exec, simple_protocol, cache_describe, …) or on your own pgxpool.Config via NewNativeFromPool.

Your setup What to do
Direct connection Nothing. The default (cache_statement) is the fast path.
PgBouncer ≥ 1.21 with max_prepared_statements > 0 Nothing. PgBouncer tracks prepared statements across the multiplexer; the default works.
Older PgBouncer in transaction/statement pooling Add default_query_exec_mode=exec (or simple_protocol) to the DSN. Symptom if you don't: errors like prepared statement "stmtcache_..." does not exist.

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 by itself. (On the database/sql tiers the same situation is handled by rio's WithStmtCache eviction — which evicts and propagates, never retries; both behaviors are documented, they are just each layer's own.)

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

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.

Documentation

Overview

Package postgres connects github.com/go-rio/rio to PostgreSQL through the pgx driver — via its database/sql adapter (Open, OpenPool) or fully natively (OpenNative, the fastest read path; see the README's tier table).

The package is deliberately thin: it constructs a *rio.DB with the built-in rio.Postgres dialect, installs a precise error translator that maps *pgconn.PgError values onto rio's sentinel errors, keeps the connection settings honest about standard_conforming_strings, and adapts pgx to rio's native-channel SPI. All SQL grammar lives in the rio core; this module never shapes a query.

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func New 

func New(db *sql.DB, opts ...rio.Option) *rio.DB

New wraps an existing *sql.DB in a *rio.DB with the Postgres dialect and this package's error translator. Use it when you bring your own pool: a *sql.DB you tuned yourself, or one derived from a pgxpool.Pool via stdlib.OpenDBFromPool.

New performs no connection hygiene — the pool is the caller's; make sure its sessions run with standard_conforming_strings on (the server default since PostgreSQL 9.1), or rio's placeholder rewriting can disagree with the server's lexing (see Open).

Options are applied after the translator, so rio.WithErrorTranslator in opts replaces this package's translation if you need to.

func NewFromPool added in v0.3.0 

func NewFromPool(pool *pgxpool.Pool, opts ...rio.Option) *rio.DB

NewFromPool wraps a caller-built pgxpool.Pool in a *rio.DB, for pools that need a custom pgxpool.Config — tracers, AfterConnect hooks, MinConns, or a non-default query exec mode on the ConnConfig. Everything else matches OpenPool, including the closing contract: like New taking over the *sql.DB's Close, closing the rio.DB closes the pool you passed in (blocking until acquired connections are returned; your own pool.Close() afterwards is a harmless no-op). Keep the pool out of NewFromPool if it must outlive the rio.DB.

NewFromPool performs no connection hygiene — the pool is the caller's; make sure its sessions run with standard_conforming_strings on (the server default since PostgreSQL 9.1), or rio's placeholder rewriting can disagree with the server's lexing (see Open).

func NewNativeFromPool added in v0.3.0 

func NewNativeFromPool(pool *pgxpool.Pool, opts ...rio.Option) *rio.DB

NewNativeFromPool wraps a caller-built pgxpool.Pool in a native-channel *rio.DB, for pools that need a custom pgxpool.Config — tracers, AfterConnect hooks, MinConns, or a non-default QueryExecMode on the ConnConfig. Everything else matches OpenNative, including the closing contract: closing the rio.DB closes the pool you passed in. Keep the pool out of NewNativeFromPool if it must outlive the rio.DB.

NewNativeFromPool performs no connection hygiene — the pool is the caller's; make sure its sessions run with standard_conforming_strings on (the server default since PostgreSQL 9.1), or rio's placeholder rewriting can disagree with the server's lexing (see Open).

func Open 

func Open(dsn string, opts ...rio.Option) (*rio.DB, error)

Open opens a PostgreSQL database via pgx's database/sql adapter and wraps it in a *rio.DB. The DSN is handed to pgx untouched, so both URL form (postgres://user:pass@host:5432/app) and keyword/value form (host=... user=... dbname=...) work, along with every pgx runtime parameter — except one.

rio rewrites ? placeholders by lexing the SQL with standard_conforming_strings on, the server default since PostgreSQL 9.1: a backslash inside a '...' literal is an ordinary character. A session running with the setting off lexes those literals differently — backslash escapes again — so the server could disagree with rio about which ? are placeholders. Open therefore rejects a configuration that turns the setting off, whether spelled as a runtime parameter (standard_conforming_strings=off) or inside the options startup parameter (options=-c standard_conforming_strings=off — including one pgx inherits from the PGOPTIONS environment variable). An explicit on passes through, and when the setting is never mentioned nothing is injected: Open never connects, so it cannot see the server's value. If your server turns the setting off globally, turn it back on for rio's connections in the DSN — the README shows a paste-ready example.

Open validates the DSN eagerly — pgx's database/sql adapter would otherwise surface a malformed DSN on the first query — but it does not connect; ping the underlying pool (db.Unwrap().PingContext) to verify connectivity. Pool tuning also happens on the *sql.DB returned by Unwrap — rio never replaces or configures the connection pool.

func OpenNative added in v0.3.0 

func OpenNative(ctx context.Context, dsn string, opts ...rio.Option) (*rio.DB, error)

OpenNative builds a pgxpool.Pool from the DSN and wraps it in a *rio.DB that executes through pgx natively — no database/sql layer on the query path. Every rio semantic is unchanged: same rendered SQL, same scanning rules, same errors, same hooks, same savepoints. What changes is the cost: the driver.Value boxing tax is gone, so the read path allocates a fraction of the stdlib channel's count (see the README's tier table for measured numbers).

The DSN accepts everything OpenPool's does, including pgxpool's pool_* parameters, and rejects standard_conforming_strings=off exactly as in Open. Query execution mode is pgx's own default (QueryExecModeCacheStatement, automatic per-connection statement caching); tune it through the DSN parameter default_query_exec_mode — behind an old transaction-pooling PgBouncer, set default_query_exec_mode=exec (the README has the matrix). Like the other constructors, OpenNative validates eagerly but does not connect; use PoolOf(db).Ping(ctx) to verify connectivity.

Two public API differences against the stdlib channels, both loud: rio.WithStmtCache panics at construction (statement caching belongs to pgx's exec mode here), and Tx.Unwrap returns nil inside transactions (no *sql.Tx exists) — use TxOf for the pgx.Tx. db.Unwrap() still works: it returns a database/sql view over the same pool for pool-agnostic helpers (pings, migrations); never tune pooling on the view.

Closing: db.Close() closes the view and then the pool, blocking until acquired connections are returned. PoolOf returns the pool for CopyFrom, LISTEN/NOTIFY, Stat, and friends.

func OpenPool added in v0.3.0 

func OpenPool(ctx context.Context, dsn string, opts ...rio.Option) (*rio.DB, error)

OpenPool builds a pgxpool.Pool from the DSN and wraps it in a *rio.DB whose queries run through pgx's database/sql adapter over that pool. Query semantics are exactly Open's — same SQL, same scanning, same errors; what changes is who manages connections. pgxpool brings health checks, connection lifetime and idle caps, the AfterConnect hook, and Stat() metrics; measured against Open the read path is performance-neutral, so the reason to choose OpenPool is pool semantics, not speed.

The DSN accepts everything Open's does plus pgxpool's pool_* parameters (pool_max_conns, pool_min_conns, pool_max_conn_lifetime, pool_max_conn_idle_time, pool_health_check_period). A configuration that turns standard_conforming_strings off is rejected exactly as in Open. Like Open, OpenPool validates eagerly but does not connect — pgxpool connects lazily; use PoolOf(db).Ping(ctx) to verify connectivity.

PoolOf returns the pool behind the *rio.DB: pool statistics, Ping, and pgx-native abilities such as CopyFrom and LISTEN all go through it. For a pool built from your own pgxpool.Config (tracers, AfterConnect, a custom query exec mode), use NewFromPool.

Closing: db.Close() closes both the database/sql view and the pool, blocking until acquired connections are returned (pgxpool.Close semantics); closing the pool again via PoolOf is a harmless no-op. If you close the pool first instead, the view's queries fail with pgxpool's "closed pool" error, and db.Close() remains safe. Do not call SetMaxOpenConns or SetMaxIdleConns on db.Unwrap() — connections belong to the pgxpool configuration, and the view deliberately keeps zero idle database/sql connections (pgx's documented requirement, so an idle view connection never pins a pool connection and starves direct pool users).

func PoolOf added in v0.3.0 

func PoolOf(db *rio.DB) *pgxpool.Pool

PoolOf returns the pgxpool.Pool behind a *rio.DB built by OpenPool, NewFromPool, OpenNative, or NewNativeFromPool, and nil for every other construction (Open and New manage no pool). It is the door to what the pool alone can do: Ping, Stat, AcquireFunc, CopyFrom, LISTEN/NOTIFY.

func TxOf added in v0.3.0 

func TxOf(tx *rio.Tx) pgx.Tx

TxOf returns the pgx.Tx behind a native-channel *rio.Tx — the door to pgx-only abilities inside a transaction (CopyFrom, LISTEN) — and nil for every other construction (on the stdlib channels use tx.Unwrap, which carries the *sql.Tx). Savepoint-nested Tx values share the root transaction's pgx.Tx, exactly as Unwrap shares the *sql.Tx.

Types

This section is empty.

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.