postgres

package module
v1.2.0 Latest Latest
Warning

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

 Go to latest Published: Jul 23, 2026 License: MIT Imports: 12 Imported by: 0

README

go-postgres 🐘

Resilient PostgreSQL wiring for Go in one call — a sensible pgxpool, connect timeout, and health check by default, plus a transaction helper that retries serialization failures automatically.

go-postgres wraps jackc/pgx/v5 (pgxpool) so a service stops re-deriving the same pool sizes, timeouts, and retry policy in every codebase. New returns a pool that has already Pinged the server, so a successful call means you are ready to serve. RunInTx handles the retry loop that serializable workloads need.

📦 Install

go get github.com/Bugs5382/go-postgres

🚀 Usage

New parses the DSN, applies resilient defaults, and verifies readiness with a Ping. Pool() hands you the full pgx command API.

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

var now time.Time
db.Pool().QueryRow(ctx, "SELECT now()").Scan(&now)

🔁 Serialization-failure retries

RunInTx runs your function inside a transaction and retries the whole thing on serialization_failure (SQLSTATE 40001) and deadlock_detected (40P01) with capped exponential backoff — the retry loop every SERIALIZABLE workload needs. Make the function idempotent; it may run more than once.

err := db.RunInTx(ctx, func(tx pgx.Tx) error {
	var balance int
	if err := tx.QueryRow(ctx, "SELECT balance FROM accounts WHERE id = $1", id).Scan(&balance); err != nil {
		return err
	}
	_, err := tx.Exec(ctx, "UPDATE accounts SET balance = $1 WHERE id = $2", balance-100, id)
	return err
}, postgres.WithIsolation(pgx.Serializable))

Any other error — including one your function returns — aborts immediately without a retry. Retryable(err) reports whether an error is one of the two transient codes.

🧩 Querier — a pgx-free query seam

Tx, Rows, Row, CommandTag, and ErrNoRows re-export the matching pgx (and pgconn) types, and Querier is the minimal Exec/Query/QueryRow interface both a pool and a transaction satisfy. Build a store or repository against Querier instead of *pgxpool.Pool or Tx, and it runs unchanged whether it is handed the pool (DB.Querier) or a transaction (DB.RunInTxQuerier, the Querier form of RunInTx) — and it can depend on go-postgres alone, with no direct import of github.com/jackc/pgx/v5.

func accountBalance(ctx context.Context, q postgres.Querier, id int) (int, error) {
	var balance int
	err := q.QueryRow(ctx, "SELECT balance FROM accounts WHERE id = $1", id).Scan(&balance)
	if errors.Is(err, postgres.ErrNoRows) {
		return 0, fmt.Errorf("account %d not found", id)
	}
	return balance, err
}

// Outside a transaction:
balance, err := accountBalance(ctx, db.Querier(), id)

// Inside one -- RunInTxQuerier shares RunInTx's retry loop and TxOptions:
err = db.RunInTxQuerier(ctx, func(q postgres.Querier) error {
	balance, err := accountBalance(ctx, q, id)
	if err != nil {
		return err
	}
	_, err = q.Exec(ctx, "UPDATE accounts SET balance = $1 WHERE id = $2", balance-100, id)
	return err
}, postgres.WithIsolation(pgx.Serializable))

A hand-written fake satisfying Querier needs no pgx import either, so a unit test for accountBalance above never touches a real database.

🎛 Options

Every knob is a functional option, and every one has a resilient default.

db, _ := postgres.New(ctx, dsn,
	postgres.WithMaxConns(50),
	postgres.WithMinConns(2),
	postgres.WithMaxConnLifetime(time.Hour),
	postgres.WithMaxConnIdleTime(30*time.Minute),
	postgres.WithHealthCheckPeriod(time.Minute),
	postgres.WithConnectTimeout(5*time.Second),
	postgres.WithTxRetry(10, 5*time.Millisecond, time.Second),
)

Per-transaction options override the defaults for a single call: WithTxOptions, WithIsolation, WithAttempts, and WithBackoff.

💓 Health

Healthy is a cheap Ping — wire it straight into a readiness or liveness probe.

if !db.Healthy(ctx) {
	// fail the probe
}

🧱 Migrations

Migrate and MigrateWithTable run a directory of plain SQL migrations with golang-migrate -- no pool required, so they can run before New (for example, at startup or from an init container). The directory holds paired *.up.sql/*.down.sql files; ErrNoChange counts as success.

if err := postgres.Migrate(dsn, "./migrations"); err != nil {
	log.Fatal(err)
}

MigrateWithTable records applied versions in a table other than the default schema_migrations, so a service's own migrations coexist in the same database as an embedded library's migrations, each tracking its own versions independently:

err := postgres.MigrateWithTable(dsn, "./migrations", "app_migrations")

📊 OpenTelemetry

The optional otel subpackage installs an otelpgx query tracer through the WithTracer seam, so the core carries no OpenTelemetry dependency. Configure the global TracerProvider (for example with go-otel) first.

import otelpg "github.com/Bugs5382/go-postgres/otel"

db, _ := postgres.New(ctx, dsn, otelpg.WithTracing()) // span per query

The same subpackage's InstrumentMigrate wraps a migration run with a span, a duration histogram, a count, and structured, trace-correlated logs (via go-log):

err := otelpg.InstrumentMigrate(ctx, "app", func() error {
	return postgres.Migrate(dsn, "./migrations")
})

🛠 Develop

task build    # go build ./...
task test     # go test ./...
task ci       # build + vet + race tests + linters
task license  # verify MIT headers (golic)

Integration tests run against a live server and are excluded from the default build:

POSTGRES_DSN=postgres://user:pass@localhost:5432/acme task test-integration

⚖️ License

MIT © 2026 Shane

Documentation

Overview

Package postgres wraps github.com/jackc/pgx/v5 (pgxpool) with resilient defaults so a service gets a correctly configured PostgreSQL connection pool from a single New call: sensible pool sizes, a connect timeout, a health-check period, and a startup Ping that verifies readiness. Functional options tune the pool, timeouts, and an optional pgx QueryTracer. RunInTx runs a function inside a transaction and retries automatically on serialization failures (SQLSTATE 40001) and deadlocks (40P01) with capped exponential backoff. The returned DB exposes the underlying *pgxpool.Pool for the full pgx API.

Tx, Rows, Row, CommandTag, and ErrNoRows re-export the matching pgx (and pgconn) types, and Querier is the minimal Exec/Query/QueryRow interface both a pool and a transaction satisfy. Building a store or repository against Querier -- reached through DB.Querier (outside a transaction) or RunInTxQuerier (inside one) -- lets that code depend on this package alone, with no direct import of github.com/jackc/pgx/v5.

Index

Examples

Constants

This section is empty.

Variables

View Source 
var ErrNoRows = pgx.ErrNoRows

ErrNoRows is returned by QueryRow's Row.Scan when a query matched no rows. It is pgx.ErrNoRows, re-exported so a caller can check it with errors.Is without importing github.com/jackc/pgx/v5 directly.

Functions

func Migrate added in v1.1.0 

func Migrate(dsn, migrationsDir string) error

Migrate runs every pending "up" migration in migrationsDir against dsn and returns once the schema is current. dsn is a standard PostgreSQL connection string, for example "postgres://user:pass@localhost:5432/acme". ErrNoChange (there was nothing to migrate) is treated as success, not an error.

Example

Migrate applies every pending "up" migration in a directory of paired *.up.sql/*.down.sql files before a service starts serving traffic. 

package main

import (
	"log"

	postgres "github.com/Bugs5382/go-postgres"
)

func main() {
	if err := postgres.Migrate("postgres://user:pass@localhost:5432/acme", "./migrations"); err != nil {
		log.Fatal(err)
	}
}

func MigrateWithTable added in v1.1.0 

func MigrateWithTable(dsn, migrationsDir, table string) error

MigrateWithTable is Migrate, but records applied versions in table instead of golang-migrate's default "schema_migrations". Use it when two independent sets of migrations run against the same database -- for example, a service's own schema alongside an embedded library's -- so each set tracks its own versions and neither skips the other's migrations. An empty table falls back to the default.

Example

MigrateWithTable records applied versions in a table other than the default "schema_migrations", so a service's own migrations coexist in the same database as an embedded library's, each tracking its own versions. 

package main

import (
	"log"

	postgres "github.com/Bugs5382/go-postgres"
)

func main() {
	err := postgres.MigrateWithTable("postgres://user:pass@localhost:5432/acme", "./migrations", "app_migrations")
	if err != nil {
		log.Fatal(err)
	}
}

func Retryable 

func Retryable(err error) bool

Retryable reports whether err is a transient PostgreSQL failure that RunInTx retries: a serialization failure (SQLSTATE 40001) or a deadlock (40P01). It unwraps the error chain, so a wrapped *pgconn.PgError is still recognized.

Types

type CommandTag added in v1.2.0 

type CommandTag = pgconn.CommandTag

CommandTag is the tag Querier.Exec returns on success. It is an alias for pgconn.CommandTag, re-exported so a caller can name it without importing github.com/jackc/pgx/v5/pgconn directly.

type DB 

type DB struct {
	// contains filtered or unexported fields
}

DB is a thin, resilient wrapper over a pgx connection pool. Build one with New; use Pool to reach the full pgx API, RunInTx to run a transaction with automatic serialization-failure retries, Healthy for a liveness probe, and Close to release the pool. A DB is safe for concurrent use by multiple goroutines.

func New 

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

New parses the DSN, builds a pgx connection pool with the resilient defaults for anything left unset, and verifies readiness with a Ping before returning. The DSN accepts either a keyword/value string or a URL, for example "postgres://user:pass@localhost:5432/acme". On a failed Ping the pool is closed and the error is returned, so a returned DB is always usable. The ctx bounds both the pool creation and the readiness Ping.

Example

Connect to a server with the resilient defaults, then query through the underlying pgx pool. 

package main

import (
	"context"
	"log"
	"time"

	postgres "github.com/Bugs5382/go-postgres"
)

func main() {
	ctx := context.Background()

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

	var now time.Time
	if err := db.Pool().QueryRow(ctx, "SELECT now()").Scan(&now); err != nil {
		log.Fatal(err)
	}
}
Example (Options)

Tune the pool and the transaction retry policy through functional options. 

package main

import (
	"context"
	"log"
	"time"

	postgres "github.com/Bugs5382/go-postgres"
)

func main() {
	ctx := context.Background()

	db, err := postgres.New(ctx,
		"postgres://user:pass@localhost:5432/acme",
		postgres.WithMaxConns(50),
		postgres.WithConnectTimeout(5*time.Second),
		postgres.WithHealthCheckPeriod(time.Minute),
		postgres.WithTxRetry(10, 5*time.Millisecond, time.Second),
	)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
}

func (*DB) Close 

func (db *DB) Close()

Close releases the connection pool and waits for in-flight queries to finish. It is safe to call once; further use of the DB after Close returns errors from pgx.

func (*DB) Healthy 

func (db *DB) Healthy(ctx context.Context) bool

Healthy reports whether the server answers a Ping within ctx. It is cheap and suitable for readiness and liveness probes.

func (*DB) Ping 

func (db *DB) Ping(ctx context.Context) error

Ping verifies a round-trip to the server within ctx, acquiring and releasing a pooled connection. It returns the underlying error on failure.

func (*DB) Pool 

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

Pool returns the underlying pgx pool so callers can use the full pgx API (Query, Exec, CopyFrom, LISTEN/NOTIFY, and so on). The returned value is owned by this DB; do not Close it directly -- use DB.Close.

func (*DB) Querier added in v1.2.0 

func (db *DB) Querier() Querier

Querier returns the pool as a Querier, so a store or repository built against the Querier seam can query outside a transaction without naming *pgxpool.Pool (or, transitively, importing github.com/jackc/pgx/v5).

Example

Build a store against Querier instead of *pgxpool.Pool, and reach it either through DB.Querier (outside a transaction) or RunInTxQuerier (inside one). Neither the store nor this example needs to import github.com/jackc/pgx/v5. 

package main

import (
	"context"
	"errors"
	"fmt"
	"log"

	postgres "github.com/Bugs5382/go-postgres"
)

func main() {
	ctx := context.Background()

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

	if _, err := accountBalance(ctx, db.Querier(), 1); err != nil {
		log.Fatal(err)
	}
}

// accountBalance is a store method built against postgres.Querier, so it runs
// unchanged whether q is a plain pool (DB.Querier) or a transaction
// (RunInTxQuerier's argument).
func accountBalance(ctx context.Context, q postgres.Querier, id int) (int, error) {
	var balance int
	err := q.QueryRow(ctx, "SELECT balance FROM accounts WHERE id = $1", id).Scan(&balance)
	if errors.Is(err, postgres.ErrNoRows) {
		return 0, fmt.Errorf("account %d not found", id)
	}
	return balance, err
}

func (*DB) RunInTx 

func (db *DB) RunInTx(ctx context.Context, fn func(pgx.Tx) error, opts ...TxOption) error

RunInTx runs fn inside a transaction and commits it. If fn or the commit fails with a serialization failure (SQLSTATE 40001) or a deadlock (40P01), the whole transaction is retried from the top with capped exponential backoff, up to the configured attempt limit. Any other error -- including an application error returned by fn -- aborts immediately without a retry. fn must be idempotent across attempts: it may run more than once, and it must not commit or roll back the transaction itself. The default policy comes from WithTxRetry; per-call TxOptions override it and set the pgx transaction options (for example Serializable isolation, where retries are expected under contention).

Example

RunInTx retries automatically on serialization failures, which are expected under Serializable isolation. fn must be safe to run more than once. 

package main

import (
	"context"
	"log"

	postgres "github.com/Bugs5382/go-postgres"
	"github.com/jackc/pgx/v5"
)

func main() {
	ctx := context.Background()

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

	err = db.RunInTx(ctx, func(tx pgx.Tx) error {
		var balance int
		if err := tx.QueryRow(ctx, "SELECT balance FROM accounts WHERE id = $1", 1).Scan(&balance); err != nil {
			return err
		}
		_, err := tx.Exec(ctx, "UPDATE accounts SET balance = $1 WHERE id = $2", balance-100, 1)
		return err
	}, postgres.WithIsolation(pgx.Serializable))
	if err != nil {
		log.Fatal(err)
	}
}

func (*DB) RunInTxQuerier added in v1.2.0 

func (db *DB) RunInTxQuerier(ctx context.Context, fn func(Querier) error, opts ...TxOption) error

RunInTxQuerier is RunInTx, but hands fn the transaction as a Querier instead of a Tx, so callers can build against the Querier seam (for example a repository or store interface shared with code that also runs outside a transaction via DB.Querier) without naming Tx or importing github.com/jackc/pgx/v5. It delegates to RunInTx, so it shares the same serialization-failure/deadlock retry loop and the same TxOptions.

Example

RunInTxQuerier is RunInTx, but hands the callback a Querier instead of a pgx.Tx, so the same fn can share a store's Querier-based methods with code that also runs outside a transaction (see ExampleDB_Querier). 

package main

import (
	"context"
	"errors"
	"fmt"
	"log"

	postgres "github.com/Bugs5382/go-postgres"
	"github.com/jackc/pgx/v5"
)

// accountBalance is a store method built against postgres.Querier, so it runs
// unchanged whether q is a plain pool (DB.Querier) or a transaction
// (RunInTxQuerier's argument).
func accountBalance(ctx context.Context, q postgres.Querier, id int) (int, error) {
	var balance int
	err := q.QueryRow(ctx, "SELECT balance FROM accounts WHERE id = $1", id).Scan(&balance)
	if errors.Is(err, postgres.ErrNoRows) {
		return 0, fmt.Errorf("account %d not found", id)
	}
	return balance, err
}

func main() {
	ctx := context.Background()

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

	err = db.RunInTxQuerier(ctx, func(q postgres.Querier) error {
		balance, err := accountBalance(ctx, q, 1)
		if err != nil {
			return err
		}
		_, err = q.Exec(ctx, "UPDATE accounts SET balance = $1 WHERE id = $2", balance-100, 1)
		return err
	}, postgres.WithIsolation(pgx.Serializable))
	if err != nil {
		log.Fatal(err)
	}
}

type Option 

type Option func(*config)

Option configures a DB. Options are applied in order by New; a later Option wins over an earlier one.

func WithConnectTimeout 

func WithConnectTimeout(d time.Duration) Option

WithConnectTimeout bounds how long a single connection attempt may take. A non-positive duration keeps the default.

func WithHealthCheckPeriod 

func WithHealthCheckPeriod(d time.Duration) Option

WithHealthCheckPeriod sets how often the pool checks the health of idle connections. A non-positive duration keeps the default.

func WithMaxConnIdleTime 

func WithMaxConnIdleTime(d time.Duration) Option

WithMaxConnIdleTime caps how long a connection may sit idle before it is closed. A non-positive duration keeps the default.

func WithMaxConnLifetime 

func WithMaxConnLifetime(d time.Duration) Option

WithMaxConnLifetime caps how long a connection may live before it is retired and replaced. A non-positive duration keeps the default.

func WithMaxConns 

func WithMaxConns(n int32) Option

WithMaxConns sets the maximum number of connections in the pool. A non-positive size is ignored, keeping the resilient default.

func WithMinConns 

func WithMinConns(n int32) Option

WithMinConns sets the minimum number of idle connections the pool keeps warm. A negative value is ignored; zero is a valid setting (no warm minimum).

func WithTracer 

func WithTracer(t pgx.QueryTracer) Option

WithTracer installs a pgx QueryTracer on every connection, enabling query tracing without pulling a telemetry library into the core. For OpenTelemetry, use the otel subpackage, which supplies a tracer through this seam. A nil tracer is ignored.

func WithTxRetry 

func WithTxRetry(attempts int, base, max time.Duration) Option

WithTxRetry sets the default transaction retry policy RunInTx applies: attempts is the maximum number of tries (not extra retries), and base/max bound the exponential backoff between them. attempts below 1 and non-positive backoff bounds are ignored, keeping the defaults. A per-call TxOption (WithAttempts, WithBackoff) overrides this.

type Querier added in v1.2.0 

type Querier interface {
	// Exec runs sql (INSERT/UPDATE/DELETE/DDL, or any statement that returns
	// no rows) and reports the resulting command tag.
	Exec(ctx context.Context, sql string, args ...any) (CommandTag, error)

	// Query runs sql and returns the resulting rows. The caller must Close
	// (or fully read to completion) the returned Rows.
	Query(ctx context.Context, sql string, args ...any) (Rows, error)

	// QueryRow runs sql and returns a Row wrapping at most one result row.
	// Any error from the query itself surfaces from the returned Row's Scan,
	// as ErrNoRows when there was no matching row.
	QueryRow(ctx context.Context, sql string, args ...any) Row
}

Querier is the minimal query surface a store or repository should depend on instead of *pgxpool.Pool or Tx directly: Exec, Query, and QueryRow. Both a pool (DB.Querier) and a transaction (Tx, the argument RunInTx's callback receives) satisfy it, so the same code runs standalone or inside a transaction, and a unit test can supply a hand-written fake with no pgx dependency at all.

type Row added in v1.2.0 

type Row = pgx.Row

Row is a single-row pgx result, returned by Querier.QueryRow. It is an alias for pgx.Row, re-exported so a caller can name it without importing github.com/jackc/pgx/v5 directly.

type Rows added in v1.2.0 

type Rows = pgx.Rows

Rows is a pgx result set, returned by Querier.Query. It is an alias for pgx.Rows, re-exported so a caller can name it without importing github.com/jackc/pgx/v5 directly.

type Tx added in v1.2.0 

type Tx = pgx.Tx

Tx is a pgx transaction, the type RunInTx's callback receives. It is an alias for pgx.Tx, re-exported so a caller can name it without importing github.com/jackc/pgx/v5 directly.

type TxOption 

type TxOption func(*txConfig)

TxOption configures a single RunInTx call. Options are applied in order; a later Option wins over an earlier one.

func WithAttempts 

func WithAttempts(n int) TxOption

WithAttempts overrides the maximum number of tries for this call. A value below 1 is ignored, keeping the DB default.

func WithBackoff 

func WithBackoff(base, max time.Duration) TxOption

WithBackoff overrides the exponential backoff bounds for this call. Non-positive bounds are ignored, keeping the DB default.

func WithIsolation 

func WithIsolation(level pgx.TxIsoLevel) TxOption

WithIsolation sets only the transaction isolation level for this call, leaving the other pgx transaction options unchanged.

func WithTxOptions 

func WithTxOptions(o pgx.TxOptions) TxOption

WithTxOptions sets the pgx transaction options (isolation level, access mode, deferrable) for this call. Use it to run under Serializable isolation, where serialization failures -- which RunInTx retries -- are expected under contention.

Source Files

View all Source files

Directories

Path Synopsis
Package otel adds optional OpenTelemetry query tracing to a go-postgres DB.
 Package otel adds optional OpenTelemetry query tracing to a go-postgres DB.

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.