sql

README

sql

Wrapper around github.com/jmoiron/sqlx with the lifecycle contract: typed Config, functional Options, Shutdown(ctx), Healthcheck(ctx), opt-in OpenTelemetry instrumentation, and a transaction Manager that threads tx state through context.Context.

When to use

Any service that needs an SQL database. Currently sql/postgres is the only provided driver helper, but sql.Config{DriverName, DataSource} works with any database/sql-compatible driver — bring your own.

Quickstart

package main

import (
    "context"
    "log"

    "github.com/sergeyslonimsky/core/app"
    "github.com/sergeyslonimsky/core/sql"
    "github.com/sergeyslonimsky/core/sql/postgres"
)

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

    pgCfg := postgres.Config{
        Host: "localhost", Port: "5432",
        User: "app", Password: "secret", Name: "myapp",
    }

    db, err := sql.New(ctx, sql.Config{
        DriverName: pgCfg.Driver(),
        DataSource: pgCfg.DSN(),
    },
        sql.WithOtel(),
        sql.WithMaxOpenConns(25),
        sql.WithConnMaxLifetime(30 * time.Minute),
    )
    if err != nil {
        log.Fatal(err)
    }

    a := app.New()
    a.Add(db)  // registers Shutdown + Healthcheck
}

Configuration

type Config struct {
    DriverName string  // e.g., "postgres"
    DataSource string  // driver-specific DSN
}

For PostgreSQL, use the postgres subpackage:

type postgres.Config struct {
    Host, Port, User, Password, Name, SSLMode string
}

func (c Config) DSN() string     // libpq-format connection string
func (c Config) Driver() string  // "postgres"

Options

• WithLogger(*slog.Logger) — used for lifecycle events. Default: slog.Default().
• WithOtel() — route through otelsqlx.ConnectContext for per-query tracing.
• WithMaxOpenConns(n int) — pool's max open connections (default: unlimited).
• WithMaxIdleConns(n int) — pool's max idle connections (default: 2).
• WithConnMaxLifetime(d time.Duration) — recycle connections after this age.
• WithConnMaxIdleTime(d time.Duration) — close connections idle for this long.

Observability (WithOtel)

Replaces sqlx.ConnectContext with otelsqlx.ConnectContext from github.com/uptrace/opentelemetry-go-extra/otelsqlx. Every *Context query produces a span with the SQL statement (sanitized), driver name, and result/error.

Uses the global tracer/meter providers — call otel.Setup before sql.New and register the provider with app.App.

Lifecycle

*sql.DB implements lifecycle.Resource + lifecycle.Healthchecker. Register with app.App.Add(db).

a.Add(otelProvider)  // first → shuts down last
a.Add(db)            // before redis / kafka producers
a.Add(httpServer)    // last → shuts down first

Shutdown calls *sqlx.DB.Close(). Healthcheck issues PingContext.

Transactions

manager := sql.NewManager(db)

err := manager.WithTx(ctx, func(txCtx context.Context) error {
    q := manager.GetQuerier(txCtx)
    // q is the active *Tx — pass to repositories so they participate in the same transaction
    return userRepo.Update(txCtx, user)
})

Repositories should always call manager.GetQuerier(ctx) — outside WithTx it returns *sql.DB, inside it returns the active Tx. This lets repositories be unaware of whether they are inside a transaction.

Panic safety: if the callback panics, WithTx rolls the transaction back before the panic re-propagates. Callers don't need a manual defer recover.

Generic executors

import "github.com/Masterminds/squirrel"

qb := squirrel.Select("id", "name").From("users").Where(squirrel.Eq{"id": userID})
user, err := sql.Get[User](ctx, manager.GetQuerier(ctx), qb)

Available: Get[T], Select[T], Exec — all accept any Querier.

Extending

sx := db.Unwrap()
// raw *sqlx.DB for NamedExec, batch operations, custom row scans, etc

Owned by the wrapper — never call Close directly; always go through Shutdown.

Testing

import "github.com/DATA-DOG/go-sqlmock"

mockDB, mock, _ := sqlmock.New()
db := sql.NewFromSqlx(sqlx.NewDb(mockDB, "postgres"))
manager := sql.NewManager(db)
// ... set up mock expectations and run repository code

See also

• core/app — register the DB here.
• core/lifecycle — the Resource / Healthchecker contracts.
• core/otel — bootstrap that powers WithOtel.

Documentation

Overview

Package sql wraps github.com/jmoiron/sqlx with the lifecycle contract used across core: typed Config, functional Options, Shutdown / Healthcheck, and an opt-in OpenTelemetry instrumentation pipeline.

The package also exposes a transaction Manager (see manager.go) that threads transaction state through context.Context and a small set of generic squirrel-friendly executors (see executor.go).

Typical usage:

db, err := sql.New(ctx, sql.Config{
    DriverName: pgCfg.Driver(),
    DataSource: pgCfg.DSN(),
},
    sql.WithOtel(),
    sql.WithMaxOpenConns(25),
)
if err != nil { log.Fatal(err) }

a := app.New()
a.Add(db)   // registers Shutdown + Healthcheck

Index

• func Exec(ctx context.Context, q Querier, qb squirrel.Sqlizer) (int64, error)
• func Get[T any](ctx context.Context, q Querier, qb squirrel.Sqlizer) (T, error)
• func Select[T any](ctx context.Context, q Querier, qb squirrel.Sqlizer) ([]T, error)
• type Config
• type DB
  • func New(ctx context.Context, cfg Config, opts ...Option) (*DB, error)
  • func NewFromSqlx(db *sqlx.DB) *DB
  • func (db *DB) BeginTx(ctx context.Context, opts *stdsql.TxOptions) (Tx, error)
  • func (db *DB) ExecContext(ctx context.Context, query string, args ...any) (stdsql.Result, error)
  • func (db *DB) GetContext(ctx context.Context, dest any, query string, args ...any) error
  • func (db *DB) Healthcheck(ctx context.Context) error
  • func (db *DB) SelectContext(ctx context.Context, dest any, query string, args ...any) error
  • func (db *DB) Shutdown(_ context.Context) error
  • func (db *DB) Unwrap() *sqlx.DB
• type DBManager
  • func NewManager(db *DB) *DBManager
  • func (m *DBManager) GetQuerier(ctx context.Context) Querier
  • func (m *DBManager) HasTx(ctx context.Context) bool
  • func (m *DBManager) WithTx(ctx context.Context, callback func(context.Context) error) error
• type Manager
• type Option
  • func WithConnMaxIdleTime(d time.Duration) Option
  • func WithConnMaxLifetime(d time.Duration) Option
  • func WithLogger(l *slog.Logger) Option
  • func WithMaxIdleConns(n int) Option
  • func WithMaxOpenConns(n int) Option
  • func WithOtel() Option
• type Querier
• type Tx

Functions

func Exec

func Exec(ctx context.Context, q Querier, qb squirrel.Sqlizer) (int64, error)

func Get

func Get[T any](ctx context.Context, q Querier, qb squirrel.Sqlizer) (T, error)

func Select

func Select[T any](ctx context.Context, q Querier, qb squirrel.Sqlizer) ([]T, error)

Types

type Config

type Config struct {
	// DriverName is the database/sql driver name (e.g., "postgres", "mysql").
	// Typically obtained from a postgres.Config via .Driver().
	DriverName string

	// DataSource is the driver-specific connection string. Typically
	// obtained from a postgres.Config via .DSN().
	DataSource string
}

Config describes a single SQL database to connect to. Plain fields, no struct tags — consumer apps map their viper keys to fields explicitly inside their own config.NewConfig().

type DB

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

DB wraps a *sqlx.DB and provides Querier semantics, transaction support, and lifecycle conformance. Implements lifecycle.Resource and lifecycle.Healthchecker.

Replaces the v1 DBConn type. The interface that was also called DB has been removed — use *sql.DB directly, or define your own narrow interface in consuming code if you need to mock at the database boundary.

func New

func New(ctx context.Context, cfg Config, opts ...Option) (*DB, error)

New connects to the database, applies pool tuning options, optionally installs OpenTelemetry instrumentation, and verifies the connection.

Replaces the v1 NewDB and NewDBWithOTel constructors — pass WithOtel() for the latter behavior.

Returns an error if the connection fails. Callers should treat this as fatal during startup.

func NewFromSqlx

func NewFromSqlx(db *sqlx.DB) *DB

NewFromSqlx wraps an existing *sqlx.DB. Useful in tests with sqlmock.

The wrapper does not change the underlying *sqlx.DB's pool settings — if pool tuning is needed, configure it on the *sqlx.DB before calling.

func (*DB) BeginTx

func (db *DB) BeginTx(ctx context.Context, opts *stdsql.TxOptions) (Tx, error)

BeginTx starts a transaction. The returned Tx implements Querier so it can be passed to the generic executors (Get, Select, Exec) in this package.

func (*DB) ExecContext

func (db *DB) ExecContext(ctx context.Context, query string, args ...any) (stdsql.Result, error)

ExecContext executes a non-result query (INSERT/UPDATE/DELETE).

func (*DB) GetContext

func (db *DB) GetContext(ctx context.Context, dest any, query string, args ...any) error

GetContext executes the query and scans the single result row into dest. See sqlx.DB.GetContext for semantics.

func (*DB) Healthcheck

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

Healthcheck pings the database. Implements lifecycle.Healthchecker.

Cheap (single PING) and respects ctx via PingContext.

func (*DB) SelectContext

func (db *DB) SelectContext(ctx context.Context, dest any, query string, args ...any) error

SelectContext executes the query and scans every result row into the dest slice. See sqlx.DB.SelectContext for semantics.

func (*DB) Shutdown

func (db *DB) Shutdown(_ context.Context) error

Shutdown closes the underlying *sqlx.DB, releasing the connection pool. Implements lifecycle.Resource.

Idempotent and concurrent-safe: the close runs exactly once; every subsequent call returns the same result.

The ctx parameter is currently ignored — sqlx/database/sql's Close is synchronous and not context-aware. The signature matches lifecycle.Resource for uniform integration with app.App.

func (*DB) Unwrap

func (db *DB) Unwrap() *sqlx.DB

Unwrap returns the underlying *sqlx.DB. Use for operations not covered by Querier (raw NamedExec, batch operations, custom row scans, etc).

Owned by the wrapper — do not Close directly; always go through Shutdown.

type DBManager

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

DBManager is the default Manager implementation backed by a *DB.

func NewManager

func NewManager(db *DB) *DBManager

NewManager wraps a *DB in a transaction Manager.

func (*DBManager) GetQuerier

func (m *DBManager) GetQuerier(ctx context.Context) Querier

GetQuerier — see Manager.GetQuerier.

func (*DBManager) HasTx

func (m *DBManager) HasTx(ctx context.Context) bool

HasTx — see Manager.HasTx.

func (*DBManager) WithTx

func (m *DBManager) WithTx(ctx context.Context, callback func(context.Context) error) error

WithTx — see Manager.WithTx.

Panic safety: if callback panics, the transaction is rolled back before the panic propagates. This prevents leaked transactions that would otherwise linger until the database's server-side idle timeout.

type Manager

type Manager interface {
	// WithTx runs callback inside a single transaction. The callback's ctx
	// carries the transaction; subsequent GetQuerier(ctx) calls return the
	// transaction's Querier. On error, the transaction is rolled back and
	// the callback's error is returned. On success, the transaction is
	// committed.
	WithTx(ctx context.Context, callback func(context.Context) error) error

	// GetQuerier returns the active transaction's Querier if ctx was
	// produced by WithTx; otherwise returns the manager's underlying *DB.
	GetQuerier(ctx context.Context) Querier

	// HasTx reports whether ctx already carries a WithTx-controlled
	// transaction. Leaf primitives that MUST run inside a caller-owned
	// transaction (e.g. a ledger writer that pairs a balance update with an
	// audit insert) can use it to fail fast instead of silently auto-
	// committing each statement.
	HasTx(ctx context.Context) bool
}

Manager is the transaction orchestration interface used by repositories that want to participate in WithTx-controlled transactions without owning the transaction lifetime themselves.

Repositories should call manager.GetQuerier(ctx) on every query — the returned Querier is either the active transaction (if WithTx wrapped the call chain) or the underlying *DB.

type Option

type Option func(*options)

Option configures a new DB.

func WithConnMaxIdleTime

func WithConnMaxIdleTime(d time.Duration) Option

WithConnMaxIdleTime sets the maximum amount of time a connection may be idle before it is closed. Default: 0 (no idle limit). Use to recycle connections that haven't been used recently (e.g., 5 minutes).

func WithConnMaxLifetime

func WithConnMaxLifetime(d time.Duration) Option

WithConnMaxLifetime sets the maximum amount of time a connection may be reused. Default: 0 (connections reused forever). Use a finite value (e.g., 30 minutes) to encourage healthy rotation behind load balancers.

func WithLogger

func WithLogger(l *slog.Logger) Option

WithLogger attaches a *slog.Logger used for lifecycle events. Defaults to slog.Default() when omitted.

func WithMaxIdleConns

func WithMaxIdleConns(n int) Option

WithMaxIdleConns sets the maximum number of idle connections kept in the pool. Default: database/sql's default (2). Should be ≤ MaxOpenConns.

func WithMaxOpenConns

func WithMaxOpenConns(n int) Option

WithMaxOpenConns sets the maximum number of open connections to the database. Default: database/sql's default (0 = unlimited). Recommended to set explicitly in production (typical values: 10-50).

func WithOtel

func WithOtel() Option

WithOtel enables OpenTelemetry instrumentation by routing the connection through otelsqlx, which produces a span per query with attributes for the SQL statement, driver name, and result/error status.

Uses the global tracer/meter providers — call otel.Setup and register the provider with app.App before constructing the DB so the providers are non-noop.

type Querier

type Querier interface {
	GetContext(ctx context.Context, dest any, query string, args ...any) error
	SelectContext(ctx context.Context, dest any, query string, args ...any) error
	ExecContext(ctx context.Context, query string, args ...any) (stdsql.Result, error)
}

Querier is the minimal query interface implemented by both *DB and Tx. Generic executors (Get, Select, Exec in executor.go) accept a Querier so they work uniformly inside or outside a transaction.

type Tx

type Tx interface {
	Querier
	Commit() error
	Rollback() error
}