tessl/golang-github-com-jackc-pgx-v5

pgx is a pure Go driver and toolkit for PostgreSQL providing a native high-performance interface with PostgreSQL-specific features plus a database/sql compatibility adapter.

Overview

Eval results

Files

database/sql Adapter (stdlib)

Name: tessl/golang-github-com-jackc-pgx-v5
Author: tessl

The stdlib package enables pgx to be used as a database/sql compatible driver. This allows using the standard database/sql interface while benefiting from pgx's PostgreSQL-specific features where needed.

Import

import "github.com/jackc/pgx/v5/stdlib"
import "database/sql"

Usage via sql.Open

The pgx driver is registered under the name "pgx":

// URL format
db, err := sql.Open("pgx", "postgres://user:pass@localhost:5432/mydb?sslmode=disable")

// Keyword/value format
db, err := sql.Open("pgx", "host=localhost port=5432 user=pgx password=secret dbname=mydb sslmode=disable")

Usage via stdlib.OpenDB

func OpenDB(config pgx.ConnConfig, opts ...OptionOpenDB) *sql.DB

Open a *sql.DB from a pgx.ConnConfig. Allows setting tracers and other pgx-specific options:

config, _ := pgx.ParseConfig(os.Getenv("DATABASE_URL"))
config.Tracer = &tracelog.TraceLog{Logger: myLogger, LogLevel: tracelog.LogLevelInfo}

db := stdlib.OpenDB(*config)

Usage via stdlib.OpenDBFromPool

func OpenDBFromPool(pool *pgxpool.Pool, opts ...OptionOpenDB) *sql.DB

Open a *sql.DB from an existing pgxpool.Pool. Sets db.SetMaxIdleConns(0) automatically to avoid starving the pool.

pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
db := stdlib.OpenDBFromPool(pool)
// Note: closing db does NOT close the pool

Connectors

func GetConnector(config pgx.ConnConfig, opts ...OptionOpenDB) driver.Connector
func GetPoolConnector(pool *pgxpool.Pool, opts ...OptionOpenDB) driver.Connector

Used with sql.OpenDB(connector).

RegisterConnConfig / UnregisterConnConfig

func RegisterConnConfig(c *pgx.ConnConfig) string
func UnregisterConnConfig(connStr string)

connConfig, _ := pgx.ParseConfig(os.Getenv("DATABASE_URL"))
connConfig.Tracer = &tracelog.TraceLog{Logger: myLogger, LogLevel: tracelog.LogLevelInfo}
connStr := stdlib.RegisterConnConfig(connConfig)
db, _ := sql.Open("pgx", connStr)
// When done:
defer stdlib.UnregisterConnConfig(connStr)

OptionOpenDB

type OptionOpenDB func(*connector)

Options for configuring the driver:

func OptionBeforeConnect(bc func(context.Context, *pgx.ConnConfig) error) OptionOpenDB
func OptionAfterConnect(ac func(context.Context, *pgx.Conn) error) OptionOpenDB
func OptionResetSession(rs func(context.Context, *pgx.Conn) error) OptionOpenDB
func OptionShouldPing(f func(context.Context, ShouldPingParams) bool) OptionOpenDB

OptionBeforeConnect — called before each new connection. Receives a shallow copy of ConnConfig.
OptionAfterConnect — called after each new connection is established.
OptionResetSession — called when a connection is reused. Return driver.ErrBadConn to discard.
OptionShouldPing — control whether to ping before reusing. Default: ping when idle > 1s.

type ShouldPingParams struct {
    Conn         *pgx.Conn
    IdleDuration time.Duration
}

GetDefaultDriver

func GetDefaultDriver() driver.Driver

Returns the pgx *Driver registered as "pgx" in database/sql.

Accessing pgx.Conn from database/sql

Use (*sql.Conn).Raw() to access the underlying *pgx.Conn:

conn, err := db.Conn(ctx)
if err != nil {
    return err
}
defer conn.Close()

err = conn.Raw(func(driverConn any) error {
    pgxConn := driverConn.(*stdlib.Conn).Conn() // *pgx.Conn
    // Use pgx-specific features
    _, err := pgxConn.CopyFrom(ctx,
        pgx.Identifier{"users"},
        []string{"name", "email"},
        pgx.CopyFromRows(rows))
    return err
})

PostgreSQL-Specific Types with database/sql

Use pgtype.Map.SQLScanner for complex types (arrays, ranges, etc.):

m := pgtype.NewMap()
var a []int64
err := db.QueryRow("SELECT '{1,2,3}'::bigint[]").Scan(m.SQLScanner(&a))

Conn (driver.Conn implementation)

type Conn struct{ /* unexported */ }

func (c *Conn) Begin() (driver.Tx, error)
func (c *Conn) BeginTx(ctx context.Context, opts driver.TxOptions) (driver.Tx, error)
func (c *Conn) Close() error
func (c *Conn) Conn() *pgx.Conn
func (c *Conn) ExecContext(ctx context.Context, query string, argsV []driver.NamedValue) (driver.Result, error)
func (c *Conn) Ping(ctx context.Context) error
func (c *Conn) Prepare(query string) (driver.Stmt, error)
func (c *Conn) PrepareContext(ctx context.Context, query string) (driver.Stmt, error)
func (c *Conn) QueryContext(ctx context.Context, query string, argsV []driver.NamedValue) (driver.Rows, error)
func (c *Conn) ResetSession(ctx context.Context) error
func (c *Conn) CheckNamedValue(*driver.NamedValue) error

Driver

type Driver struct{ /* unexported */ }

func (d *Driver) Open(name string) (driver.Conn, error)
func (d *Driver) OpenConnector(name string) (driver.Connector, error)

Rows (driver.Rows implementation)

type Rows struct{ /* unexported */ }

func (r *Rows) Close() error
func (r *Rows) Columns() []string
func (r *Rows) Next(dest []driver.Value) error
func (r *Rows) ColumnTypeDatabaseTypeName(index int) string
func (r *Rows) ColumnTypeLength(index int) (int64, bool)
func (r *Rows) ColumnTypePrecisionScale(index int) (precision, scale int64, ok bool)
func (r *Rows) ColumnTypeScanType(index int) reflect.Type

Stmt (driver.Stmt implementation)

type Stmt struct{ /* unexported */ }

func (s *Stmt) Close() error
func (s *Stmt) NumInput() int
func (s *Stmt) Exec(argsV []driver.Value) (driver.Result, error)
func (s *Stmt) ExecContext(ctx context.Context, argsV []driver.NamedValue) (driver.Result, error)
func (s *Stmt) Query(argsV []driver.Value) (driver.Rows, error)
func (s *Stmt) QueryContext(ctx context.Context, argsV []driver.NamedValue) (driver.Rows, error)

RandomizeHostOrderFunc

func RandomizeHostOrderFunc(ctx context.Context, connConfig *pgx.ConnConfig) error

A BeforeConnect hook that randomizes host order for multi-master databases (e.g., CockroachDB). Use with db.SetConnMaxLifetime for periodic rebalancing.

db := stdlib.OpenDB(*config,
    stdlib.OptionBeforeConnect(stdlib.RandomizeHostOrderFunc))
db.SetConnMaxLifetime(30 * time.Minute)

Notes

pgx uses $1, $2, etc. positional parameters, not named parameters.
The pgx driver is NOT compatible with database/sql named parameters.
When using OpenDBFromPool, db.SetMaxIdleConns(0) is called automatically; do not override.
Do not set db.SetMaxOpenConns on the SQL DB when backed by a pgxpool, as pool already manages max connections.

Install with Tessl CLI

npx tessl i tessl/golang-github-com-jackc-pgx-v5@5.8.0