README
¶
postgresdatabase
Use the PostgreSQL database contract through native pgx and pgxpool. Configure
the driver with New(dsn, configure...), register Start, Stop, and Ping with your
application, and wrap it with database.New(driver) for query-builder support.
NewFrom(pool) takes ownership of the exact supplied pool; Stop closes it.
Configure that pool's connection settings and hooks before passing it in.
NewFrom accepts no callbacks because pgxpool limits are fixed at creation.
New callbacks edit a *postgresdatabase.Builder once in order on one fresh
draft before validation. Nil entries panic; expanded nil or empty slices omit
edits. Completed scalar settings detach from retained drafts. MaxIdleConns
remains symmetry-only; use ConnMaxIdleTime to expire idle pgx connections.
Installation and connection
Run go get arkoren.dev/driver/postgres from your application's module directory.
Import arkoren.dev/driver/postgres/postgresdatabase and
arkoren.dev/core/service/database. With your configured PostgreSQL URL in dsn:
driver, err := postgresdatabase.New(dsn, func(connection *postgresdatabase.Builder) {
connection.MaxOpenConns(20)
})
Check err before using the driver. Construction validates settings without
verifying credentials; Start, Ping, or the first query connects. Use verified
TLS for remote production servers and operation contexts with deadlines.
New rejects nonzero pool_min_conns and pool_min_idle_conns: native pgxpool
would start those connections during construction. If you need eager minima,
create the native pool at your application's startup boundary and transfer it
with NewFrom. The native health-check period must be positive. Invalid DSN
messages omit supplied values; errors.As still exposes the native parse cause,
which can contain the original DSN and must not be logged as redacted output.
See the driver guide
for TLS, native bulk copy, and a disposable local server.
Keep protected SQL on one session
Use WithSession when a session lock must protect several transactions or
autocommit statements. All facade operations using the callback context stay on
one physical connection; losing it causes SQL to fail, never reconnect or replay.
The fragment below assumes the configured driver, your report ID id, and a
context.Context named ctx. Import arkoren.dev/foundation/query for raw SQL.
db := database.New(driver)
err := db.WithSession(ctx, func(ctx context.Context) error {
unlock, acquired, err := db.TryLock(ctx, "nightly-report")
if err != nil || !acquired {
return err
}
defer unlock(ctx)
return db.WithTransaction(ctx, func(ctx context.Context) error {
_, err := db.Exec(ctx, query.Raw("UPDATE reports SET ready = true WHERE id = ?", id))
return err
})
})
Driver.WithSession(ctx, func(database.Driver) error) is the raw-driver
equivalent. Sessions do not begin implicit transactions. Nested sessions borrow
the connection but have separate callback lifetimes. A session inside a
transaction stays in that transaction; only WithTransaction adds a savepoint.
Do not use callback drivers or contexts concurrently or after return. Session
handles and cursors expire with database.ErrSessionDone; transaction handles
expire with sql.ErrTxDone. Outstanding cursors close automatically on return.
Canceling an enclosing scope also rejects operations that supply a fresh
context; passing context.Background() does not resume canceled callback work.
Raw Driver.Select replaces the destination slice's previous elements. An empty
result leaves it empty, and a scanning failure can leave a partially read result.
The facade's typed Database.Select uses a fresh result for each call.
Locks and cleanup
Locks are non-reentrant, including within a transaction and across scopes on the
same physical session. A repeated TryLock returns nil, false, nil; Lock
waits until cancellation or release. Transaction locks belong to the outermost
transaction and survive nested rollback or panic. Their returned unlock is a
no-op. Session locks outside transactions end at explicit unlock or the acquiring
callback's return. Outer session locks survive child commits and rollbacks.
The connection remains acquired through transaction finalization and unlock.
Cleanup has a detached five-second budget, joins errors with callback failures,
and discards sessions when acquisition, rollback, or unlock is uncertain. An
error wrapping database.ErrAfterCommit means the outer transaction committed
but cleanup failed: do not retry the transaction. Panics propagate unchanged
after cleanup. Explicit unlock is idempotent, even with an expired context.
Warning: Use a direct PostgreSQL connection or session-mode pooling. PgBouncer transaction pooling can change the server session between commands and breaks managed locks, including locks acquired inside transactions.
Root blocking Lock releases each refused attempt's connection before polling.
A successful root lock still holds one pool slot until unlock, so root queries
need spare capacity. A pinned session waiting on a raw root holder can still
exhaust that spare capacity. For contended protected work, leave the session on
TryLock refusal before retrying. No FIFO lock ordering is promised.
driver.DB() always exposes the root pool, including on callback drivers. Its
operations bypass scopes, transaction routing, and facade telemetry. Raw native
advisory-lock and transaction-control SQL is not managed by these lifetimes.
See the API reference and database guide.
Documentation
¶
Overview ¶
Package postgresdatabase provides a PostgreSQL database.Driver using pgxpool. Construct a driver with New, or transfer an existing pool to NewFrom, then start it, wrap it with database.New, and stop it at application shutdown. New applies Builder policies once in order before validation; nil entries panic and nil or empty slice expansions omit edits. Completed scalar settings detach from retained drafts. NewFrom accepts no policies: set native pool limits before transferring ownership. Stop still closes the supplied pool. To keep construction free of network I/O, New rejects nonzero pool_min_conns and pool_min_idle_conns. Create pools needing eager minima at the application's startup boundary and transfer them with NewFrom. Native health-check periods must be positive. Invalid DSN messages omit supplied values while retaining native parse causes for explicit inspection; unwrapped causes are not redacted.
driver, err := postgresdatabase.New(dsn, func(connection *postgresdatabase.Builder) {
connection.MaxOpenConns(10)
})
if err != nil {
return err
}
db := database.New(driver)
Retained sessions and transactions ¶
Driver.WithSession pins one physical connection without starting a transaction. Every operation on its callback driver uses that connection; connection loss fails subsequent SQL instead of reconnecting or replaying work. Nested sessions borrow it with independently expiring handles and cursors. Driver.WithTransaction starts a transaction on the retained session, or reserves one when called on the root driver. Nested transactions use savepoints, and a session borrowed inside a transaction stays inside it.
Example:
err := driver.WithSession(ctx, func(session database.Driver) error {
return session.WithTransaction(ctx, func(tx database.Driver) error {
_, err := tx.Exec(ctx, "INSERT INTO audit_log(message) VALUES ($1)", "started")
return err
})
})
Callback drivers must not be used concurrently or after return. Ended session calls and cursors fail with database.ErrSessionDone; ended transactions fail with database/sql.ErrTxDone. Native Driver.DB calls always bypass scopes. Canceling an enclosing callback also prevents operations with fresh contexts. Cleanup closes outstanding cursors, finalizes transactions, and releases managed locks before returning the connection. It uses a detached five-second budget and discards uncertain sessions. A cleanup error after a confirmed outer commit wraps database.ErrAfterCommit; do not retry committed work.
Advisory locks and pooling ¶
Driver.Lock and Driver.TryLock use session advisory locks. Locks are not reentrant, including across managed scopes sharing a connection. Fresh locks inside transactions survive all nested rollbacks and panics until the outermost transaction ends; their explicit unlock does nothing. Session locks outside transactions end at explicit unlock or their owning callback's end. An enclosing session's locks survive its child transactions.
Connect directly or use session pooling. PgBouncer transaction pooling cannot preserve these session locks, even when a client connection stays acquired. Root blocking-lock polling releases refused attempts between tries, but every successfully held root lock still consumes a pool slot. Root queries require capacity beyond those held locks. A pinned waiter can still exhaust capacity when mixed with raw root holders that need another connection: try a lock in a short session and leave the session on refusal instead. Raw advisory-lock or transaction-control SQL is outside managed ownership and cleanup guarantees.
Raw Driver.Select replaces previous destination elements; empty results leave the slice empty. Scanning errors can leave the successfully read prefix. The typed Database.Select facade creates a new result rather than reusing a slice.
Index ¶
- Variables
- type Builder
- type BuilderFunc
- type Driver
- func (driver *Driver) DB() *pgxpool.Pool
- func (driver *Driver) Dialect() query.Dialect
- func (driver *Driver) Exec(ctx context.Context, statement string, args ...any) (int64, error)
- func (driver *Driver) Find(ctx context.Context, statement string, dest any, args ...any) error
- func (driver *Driver) Lock(ctx context.Context, name string) (func(context.Context) error, error)
- func (driver *Driver) Ping(ctx context.Context) error
- func (driver *Driver) Query(ctx context.Context, statement string, args ...any) (database.Rows, error)
- func (driver *Driver) Select(ctx context.Context, statement string, dest any, args ...any) error
- func (driver *Driver) Start(ctx context.Context) error
- func (driver *Driver) Stop(ctx context.Context) error
- func (driver *Driver) TryLock(ctx context.Context, name string) (func(context.Context) error, bool, error)
- func (driver *Driver) WithSession(ctx context.Context, fn func(database.Driver) error) (err error)
- func (driver *Driver) WithTransaction(ctx context.Context, fn func(database.Driver) error) (err error)
Constants ¶
This section is empty.
Variables ¶
var ErrInvalidConfig = errors.New("invalid PostgreSQL configuration")
ErrInvalidConfig identifies invalid constructor arguments and native DSN settings. DSN parse diagnostics omit supplied values; the original native cause remains available through errors.As for explicit, non-logging inspection.
Example:
_, err := postgresdatabase.New(" ")
if errors.Is(err, postgresdatabase.ErrInvalidConfig) {
return errors.New("check the database configuration")
}
Functions ¶
This section is empty.
Types ¶
type Builder ¶
type Builder struct {
// contains filtered or unexported fields
}
Builder edits the pool settings used by New. NewFrom has no callbacks because pgxpool limits are fixed when a pool is created. Completed scalar settings detach from retained drafts. Callbacks run directly once in order; nil entries panic, while expanded nil or empty slices omit edits.
Example:
driver, err := postgresdatabase.New(dsn, func(connection *postgresdatabase.Builder) {
connection.MaxOpenConns(10)
})
if err != nil {
return err
}
return driver.Start(ctx)
func (*Builder) ConnMaxIdleTime ¶
ConnMaxIdleTime replaces pgxpool's idle age limit and returns this draft. Zero keeps the native default; New rejects negative durations.
Example:
driver, err := postgresdatabase.New(dsn, func(connection *postgresdatabase.Builder) {
connection.ConnMaxIdleTime(time.Minute)
})
if err != nil {
return err
}
return driver.Start(ctx)
func (*Builder) ConnMaxLifetime ¶
ConnMaxLifetime replaces pgxpool's maximum connection age and returns this draft. Zero preserves the native default; New rejects negative durations.
Example:
driver, err := postgresdatabase.New(dsn, func(connection *postgresdatabase.Builder) {
connection.ConnMaxLifetime(time.Hour)
})
if err != nil {
return err
}
return driver.Start(ctx)
func (*Builder) MaxIdleConns ¶
MaxIdleConns records a symmetry-only setting and returns this draft. pgxpool has no idle-count limit; use ConnMaxIdleTime. New still rejects negatives.
Example:
driver, err := postgresdatabase.New(dsn, func(connection *postgresdatabase.Builder) {
connection.MaxIdleConns(0)
})
if err != nil {
return err
}
return driver.Start(ctx)
func (*Builder) MaxOpenConns ¶
MaxOpenConns replaces pgxpool's MaxConns and returns this draft. Zero keeps the native default; New rejects negatives and clamps positives to MaxInt32.
Example:
driver, err := postgresdatabase.New(dsn, func(connection *postgresdatabase.Builder) {
connection.MaxOpenConns(10)
})
if err != nil {
return err
}
return driver.Start(ctx)
type BuilderFunc ¶
type BuilderFunc = func(*Builder)
BuilderFunc edits a fresh construction draft supplied to New. Policies run synchronously once in order; nil entries panic, and retaining the draft does not let a caller change completed driver settings.
Example:
policy := postgresdatabase.BuilderFunc(func(connection *postgresdatabase.Builder) {
connection.MaxOpenConns(10)
})
driver, err := postgresdatabase.New(dsn, policy)
if err != nil {
return err
}
return driver.Start(ctx)
type Driver ¶
type Driver struct {
// contains filtered or unexported fields
}
Driver implements database.Driver for PostgreSQL on pgx and pgxpool, scanning rows with query.Targets. Root operations acquire from the configured pool; drivers passed to Driver.WithSession and Driver.WithTransaction are callback-scoped handles using one retained physical connection. The root owns the pool and must be stopped after callbacks and cursors finish.
Example:
driver, err := postgresdatabase.New(dsn)
if err != nil {
return err
}
db := database.New(driver)
func New ¶
func New(dsn string, configure ...BuilderFunc) (*Driver, error)
New configures PostgreSQL and returns a Driver.
dsn is the PostgreSQL data source name, as a URL or in key=value form. It is required and has no default; a blank or unparsable dsn returns an error matching ErrInvalidConfig, as does a negative pool limit. DSN parse messages omit supplied values while retaining the native error for errors.As. Policies edit Builder once in order before validation; supplied nil entries panic. Completed scalar settings detach from retained drafts. The pool connects lazily: no network I/O happens until Driver.Start or the first query. Nonzero native pool_min_conns and pool_min_idle_conns are rejected because pgxpool would connect during construction; pool_health_check_period must be positive. For eager minimum connections, create a native pool at application startup and transfer it through NewFrom.
Example:
driver, err := postgresdatabase.New(dsn, func(connection *postgresdatabase.Builder) {
connection.MaxOpenConns(10)
})
if err != nil {
return err
}
return driver.Start(ctx)
func NewFrom ¶
NewFrom wraps a pgx pool the caller created, for example with pgxpool.New. The driver takes ownership: Driver.Stop closes the pool.
There are no configuration callbacks: pgxpool fixes its limits when created, so set them in pgxpool.Config before transferring ownership.
Example:
driver := postgresdatabase.NewFrom(pool) return driver.Stop(ctx)
func (*Driver) DB ¶
DB returns the underlying pgx connection pool, for pgx features the database contract does not cover, such as COPY, LISTEN, and batches. It is always the root pool, also on callback drivers: it bypasses session lifetime guards, transaction routing, and facade telemetry. Do not use it for work that must remain protected by a retained session's lock after connection loss.
Example:
config := driver.DB().Config() maxConnections := config.MaxConns
func (*Driver) Dialect ¶
Dialect reports query.Postgres for roots and callback handles alike. Use it to compile queries with PostgreSQL placeholders and identifier quoting.
Example:
statement, err := query.Raw("SELECT ?", 7).Compile(driver.Dialect())
if err != nil {
return err
}
_, err = driver.Exec(ctx, statement.SQL(), statement.Args()...)
func (*Driver) Exec ¶
Exec executes a statement that modifies data and returns the number of rows it affected. Callback drivers first check their lifetime, then use only their retained connection or transaction. An ended scope cannot acquire from the pool. Arguments use PostgreSQL's numbered placeholders.
Example:
changed, err := driver.Exec(ctx, "UPDATE reports SET ready = true WHERE id = $1", id)
func (*Driver) Find ¶
Find scans the first result row into dest, a pointer to a struct or to a single-column value. When no row matches, the returned error wraps pgx.ErrNoRows, which matches sql.ErrNoRows, and database.ErrNoRows. Its cursor closes before return. Callback lifetime checks prevent a finished handle from querying a returned connection or falling back to the root pool.
Example:
var pid int err := driver.Find(ctx, "SELECT pg_backend_pid()", &pid)
func (*Driver) Lock ¶
Lock waits until the named advisory lock is held or ctx ends. Outside a session, refused attempts return their connections before polling; a successful acquisition retains one pool slot until unlock. Session and transaction calls use their existing connection and never acquire an additional slot.
Locks are not reentrant, including on the same physical session. A transaction owns fresh acquisitions until its outermost end, across nested rollback and panic; its unlock is a no-op. Other callback acquisitions end at unlock or callback return. Use direct or session-pooled PostgreSQL, not transaction-mode PgBouncer, because all managed locks are session-level advisory locks.
Example:
unlock, err := driver.Lock(ctx, "nightly-report")
if err != nil {
return err
}
defer unlock(ctx)
func (*Driver) Ping ¶
Ping verifies connectivity through the root pool or this callback's retained connection. A callback never uses a replacement connection after loss, and an expired callback reports its session or transaction sentinel before any I/O.
Example:
return driver.Ping(ctx)
func (*Driver) Query ¶
func (driver *Driver) Query(ctx context.Context, statement string, args ...any) (database.Rows, error)
Query executes a statement and returns a cursor over its rows. A root cursor retains a pool connection until closed; a callback cursor uses the already pinned connection and is closed automatically when its callback returns. After callback return, Next stops and Err/Scan report the scope's end sentinel. Close remains safe and idempotent after automatic cleanup.
Example:
rows, err := driver.Query(ctx, "SELECT id FROM reports ORDER BY id")
if err != nil {
return err
}
defer rows.Close()
func (*Driver) Select ¶
Select scans every result row into dest, a pointer to a slice of structs, of pointers to structs, or of single-column values. Struct columns follow query.Targets. A successful query replaces existing elements; no rows leaves dest empty. A scan failure can leave a partial result. The cursor closes before return. Invalid destinations fail before SQL; expired callbacks fail before inspecting the destination or accessing a connection.
Example:
var ids []int err := driver.Select(ctx, "SELECT id FROM reports ORDER BY id", &ids)
func (*Driver) Start ¶
Start verifies connectivity so a misconfigured database fails fast. The root pool connects lazily; a callback driver instead checks its retained connection and lifetime without acquiring another pool slot. It is equivalent to Driver.Ping.
Example:
return driver.Start(ctx)
func (*Driver) Stop ¶
Stop closes the connection pool. It waits until every connection in use has been returned, which requires the caller to have closed its cursors and finished its transactions; when ctx ends first, Stop returns ctx.Err() and the pool finishes closing in the background as those connections return. On a callback driver it only checks the scope and context; it never closes the shared pool. Expired callback handles report their session/transaction sentinel.
Example:
return driver.Stop(ctx)
func (*Driver) TryLock ¶
func (driver *Driver) TryLock(ctx context.Context, name string) (func(context.Context) error, bool, error)
TryLock asks once for the named lock without waiting for lock ownership. Pool acquisition and network I/O still obey ctx. An empty name fails with database.ErrEmptyLockName; refusal returns nil, false, nil, including when another managed scope on this physical session owns the canonical key.
The returned unlock is idempotent. Session cleanup uses a detached five-second budget even if the supplied context has ended. An uncertain acquisition or unlock poisons the session so it cannot return to the pool as healthy.
Example:
unlock, acquired, err := driver.TryLock(ctx, "nightly-report")
if err != nil || !acquired {
return err
}
defer unlock(ctx)
func (*Driver) WithSession ¶
WithSession runs fn once on a retained physical connection without beginning a transaction. Transactions started by fn use that same connection, and loss of it never reconnects or replays the callback. Nested sessions borrow the connection but their handles and cursors expire independently when fn returns. Inside a transaction, the borrowed session remains in that transaction. Canceling this callback's context also rejects operations supplied with fresh contexts; an operation cannot resume a canceled enclosing scope.
Callback errors and bounded cleanup errors are joined. A panic is re-raised unchanged after cleanup. Outstanding cursors and owned session locks are closed before the physical connection can return to the pool. Do not use the callback driver concurrently or retain it after return; later calls fail with database.ErrSessionDone. Native Driver.DB access bypasses this scope.
Example:
err := driver.WithSession(ctx, func(session database.Driver) error {
_, err := session.Exec(ctx, "SET application_name = 'migration'")
return err
})
func (*Driver) WithTransaction ¶
func (driver *Driver) WithTransaction(ctx context.Context, fn func(database.Driver) error) (err error)
WithTransaction runs fn in a transaction on one retained physical session. A session callback starts it on its existing connection; a transaction callback starts a savepoint. Nested failure or panic rolls back only nested SQL, while fresh managed locks remain owned until the outermost transaction ends.
Callback errors cause rollback, cancellation prevents commit, and an original panic propagates unchanged after bounded cleanup. The connection remains held through commit or rollback and subsequent session-lock cleanup. A cleanup failure after a confirmed outer commit wraps database.ErrAfterCommit: the data committed, so callers must not retry the transaction. An ambiguous commit or callback failure is never newly marked as a confirmed commit.
Callback drivers and cursors expire on return with sql.ErrTxDone. Do not use them concurrently. Session-lock support requires direct PostgreSQL or session pooling, not PgBouncer transaction pooling. Keep nested transactions shallow: PostgreSQL savepoint overhead increases with many active subtransactions.
Example:
err := driver.WithTransaction(ctx, func(tx database.Driver) error {
if _, err := tx.Lock(ctx, "invoice-numbers"); err != nil {
return err
}
_, err := tx.Exec(ctx, "UPDATE counters SET value = value + 1 WHERE name = $1", "invoice")
return err
})
Source Files