postgres

README

postgres package

Shared Postgres access layer built on pgx.

What this package provides

• connection bootstrap from URL or structured DB config,
• pooled runner abstraction (Runner) for pool and tx paths,
• transaction helpers (WithTx, WithTxRO, WithTxOpts),
• serializable retries (WithSerializable),
• savepoint helper (WithSavepoint),
• SQLSTATE helpers for constraint errors.

Core usage pattern

1. Open one Client on service startup.
2. Use RunnerFromPool() for non-transactional queries.
3. Use WithTx(...) for atomic write flows.
4. Use WithTxRO(...) for consistent read-only multi-query reads.
5. Inside tx callback, use MustRunnerFromContext(txCtx) only in internal layers with strict invariants; on public boundaries, prefer RunnerFromContextOrError(txCtx) and return (value, error).

Interfaces

• TxManager: minimal contract (WithTx, WithTxRO) for higher layers.
• AdvancedTxManager: extended contract with tx options, serializable retry, and savepoints.

Reliability notes

• WithSerializable retries SQLSTATE 40001 and 40P01.
• Transaction cleanup is panic-safe.
• Savepoint rollback/release cleanup errors are preserved and returned.

Tests

• Unit: go test ./postgres
• Integration: go test -tags integration ./postgres
• Race (Docker):

docker run --rm -v "C:/Vortex Services/go-lib/data:/work" golang:1.25.7 sh -c 'cd /work && go mod download && go test -race ./postgres'

Documentation

Index

• Constants
• Variables
• func ContextWithRunner(ctx context.Context, r Runner) context.Context
• func IsForeignKeyViolation(err error) bool
• func IsUniqueViolation(err error) bool
• type AdvancedTxManager
• type Client
  • func Open(ctx context.Context, cfg Config) (*Client, error)
  • func OpenWithDBConfig(ctx context.Context, dbCfg DBConfig) (*Client, error)
  • func (c *Client) Close()
  • func (c *Client) RunnerFromPool() Runner
  • func (c *Client) WithSavepoint(ctx context.Context, fn func(ctx context.Context) error) error
  • func (c *Client) WithSerializable(ctx context.Context, maxRetries int, fn func(ctx context.Context) error) error
  • func (c *Client) WithSerializableRO(ctx context.Context, deferrable bool, fn func(ctx context.Context) error) error
  • func (c *Client) WithTx(ctx context.Context, fn func(ctx context.Context) error) (err error)
  • func (c *Client) WithTxExplicit(ctx context.Context, fn func(run Runner) error) error
  • func (c *Client) WithTxOpts(ctx context.Context, cfg TxConfig, fn func(ctx context.Context) error) (err error)
  • func (c *Client) WithTxRO(ctx context.Context, fn func(ctx context.Context) error) error
  • func (c *Client) WithTxROExplicit(ctx context.Context, fn func(run Runner) error) error
• type Config
• type ConstraintInfo
  • func Constraint(err error) (ConstraintInfo, bool)
• type DBConfig
• type Runner
  • func MustRunnerFromContext(ctx context.Context) Runner
  • func RunnerFromContext(ctx context.Context, fallback *Client) Runner
  • func RunnerFromContextOrError(ctx context.Context) (Runner, error)
• type TxConfig
• type TxManager

Constants

const (
	SQLStateUniqueViolation     = "23505"
	SQLStateForeignKeyViolation = "23503"
	SQLStateNotNullViolation    = "23502"
)

SQLSTATE codes used by this package.

Variables

var ErrRunnerMissingInContext = errors.New("postgres: no Runner in context (outside transaction?)")

Functions

func ContextWithRunner

func ContextWithRunner(ctx context.Context, r Runner) context.Context

ContextWithRunner stores a Runner in the context.

func IsForeignKeyViolation

func IsForeignKeyViolation(err error) bool

func IsUniqueViolation

func IsUniqueViolation(err error) bool

Narrow helper predicates.

Types

type AdvancedTxManager

type AdvancedTxManager interface {
	TxManager
	WithTxOpts(ctx context.Context, cfg TxConfig, fn func(ctx context.Context) error) error
	WithSerializable(ctx context.Context, maxRetries int, fn func(ctx context.Context) error) error
	WithSerializableRO(ctx context.Context, deferrable bool, fn func(ctx context.Context) error) error
	WithSavepoint(ctx context.Context, fn func(ctx context.Context) error) error
}

AdvancedTxManager is an extended contract for advanced scenarios (retryable serializable, savepoints, explicit tx options).

type Client

type Client struct {
	Pool *pgxpool.Pool
}

func Open

func Open(ctx context.Context, cfg Config) (*Client, error)

Open creates a client from high-level Config (URL + pool options).

func OpenWithDBConfig

func OpenWithDBConfig(ctx context.Context, dbCfg DBConfig) (*Client, error)

OpenWithDBConfig creates a client from structured DBConfig (host/port/user/...) and maps pool options to Config.

func (*Client) Close

func (c *Client) Close()

func (*Client) RunnerFromPool

func (c *Client) RunnerFromPool() Runner

RunnerFromPool returns pool-backed Runner (outside transaction).

func (*Client) WithSavepoint

func (c *Client) WithSavepoint(ctx context.Context, fn func(ctx context.Context) error) error

WithSavepoint creates SAVEPOINT when already in tx, otherwise starts regular tx.

func (*Client) WithSerializable

func (c *Client) WithSerializable(ctx context.Context, maxRetries int, fn func(ctx context.Context) error) error

WithSerializable runs SERIALIZABLE tx with retries for 40001/40P01 and ctx awareness.

func (*Client) WithSerializableRO

func (c *Client) WithSerializableRO(ctx context.Context, deferrable bool, fn func(ctx context.Context) error) error

Convenience helper: SERIALIZABLE + ReadOnly + optional DEFERRABLE.

func (*Client) WithTx

func (c *Client) WithTx(ctx context.Context, fn func(ctx context.Context) error) (err error)

WithTx runs panic-safe read-write transaction with default options. Runner is available via RunnerFromContext(ctx) / MustRunnerFromContext(ctx).

func (*Client) WithTxExplicit

func (c *Client) WithTxExplicit(ctx context.Context, fn func(run Runner) error) error

WithTxExplicit is backward-compatible variant with explicit runner callback arg.

func (*Client) WithTxOpts

func (c *Client) WithTxOpts(ctx context.Context, cfg TxConfig, fn func(ctx context.Context) error) (err error)

WithTxOpts runs transaction with options, panic-safe commit/rollback, and optional SET LOCAL timeouts.

func (*Client) WithTxRO

func (c *Client) WithTxRO(ctx context.Context, fn func(ctx context.Context) error) error

WithTxRO runs read-only transaction (for consistent multi-query reads).

func (*Client) WithTxROExplicit

func (c *Client) WithTxROExplicit(ctx context.Context, fn func(run Runner) error) error

WithTxROExplicit is backward-compatible read-only explicit-runner variant.

type Config

type Config struct {
	URL    string            // postgres://user:pass@host:port/dbname?sslmode=disable
	Params map[string]string // extra URL params (override query)

	// Pool options
	MaxConns          int32
	MinConns          int32
	MaxConnLifetime   time.Duration
	MaxConnIdleTime   time.Duration
	HealthCheckPeriod time.Duration
}

Config is a high-level connection config based on URL. Useful for complex DSN cases and backward compatibility.

type ConstraintInfo

type ConstraintInfo struct {
	Code     string // SQLSTATE (e.g. 23505)
	Name     string // constraint name from PG
	Schema   string // optional: pgErr.SchemaName
	Table    string // optional: pgErr.TableName
	Detail   string // optional: pgErr.Detail
	IsUnique bool
}

func Constraint

func Constraint(err error) (ConstraintInfo, bool)

type DBConfig

type DBConfig struct {
	Host            string
	Port            string
	User            string
	Password        string
	DBName          string
	SSLMode         string
	MaxOpenConns    int           // max connections in pool
	MaxIdleConns    int           // minimum warm connections in pool
	ConnMaxLifetime time.Duration // max connection lifetime
	ConnMaxIdleTime time.Duration // max idle lifetime
}

DBConfig is a low-level connection config (host/port/...). It is convenient for apps: load from env and pass to the library.

type Runner

type Runner interface {
	Exec(ctx context.Context, sql string, args ...any) (pgconn.CommandTag, error)
	Query(ctx context.Context, sql string, args ...any) (pgx.Rows, error)
	QueryRow(ctx context.Context, sql string, args ...any) pgx.Row
}

Runner is a shared interface for pool and transaction.

func MustRunnerFromContext

func MustRunnerFromContext(ctx context.Context) Runner

MustRunnerFromContext extracts the Runner or panics.

func RunnerFromContext

func RunnerFromContext(ctx context.Context, fallback *Client) Runner

RunnerFromContext extracts the Runner from the context. Falls back to pool runner if no transaction is active.

func RunnerFromContextOrError

func RunnerFromContextOrError(ctx context.Context) (Runner, error)

type TxConfig

type TxConfig struct {
	Iso        pgx.TxIsoLevel // default: ReadCommitted
	ReadOnly   bool           // default: false
	Deferrable bool           // valid only for SERIALIZABLE; meaningful for read-only

	// Local timeouts for current TX (SET LOCAL ...).
	StatementTimeout         time.Duration // statement timeout
	IdleInTransactionTimeout time.Duration // idle_in_transaction_session_timeout
}

TxConfig contains optional transaction settings.

type TxManager

type TxManager interface {
	WithTx(ctx context.Context, fn func(ctx context.Context) error) error
	WithTxRO(ctx context.Context, fn func(ctx context.Context) error) error
}

TxManager is a minimal transaction management abstraction. Higher layers can depend on this interface instead of *Client.