CtrlK
BlogDocsLog inGet started
Tessl Logo

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.

—

Pending
Overview
Eval results
Files

common-patterns.mddocs/

Common Patterns

Frequently used patterns for everyday pgx usage. These patterns cover 80% of typical use cases.

Pattern 1: Simple Query with Single Row

var name string
var email string
err := pool.QueryRow(ctx, "SELECT name, email FROM users WHERE id = $1", userID).Scan(&name, &email)
if errors.Is(err, pgx.ErrNoRows) {
    // user not found
}

Pattern 2: Query Multiple Rows into Struct Slice

type User struct {
    ID    int64  `db:"id"`
    Name  string `db:"name"`
    Email string `db:"email"`
}

rows, err := pool.Query(ctx, "SELECT id, name, email FROM users WHERE active = $1", true)
if err != nil {
    return err
}
users, err := pgx.CollectRows(rows, pgx.RowToStructByName[User])
if err != nil {
    return err
}
// users is []User

Pattern 3: Execute INSERT/UPDATE/DELETE

tag, err := pool.Exec(ctx, "DELETE FROM users WHERE id = $1", userID)
if err != nil {
    return err
}
fmt.Printf("Deleted %d rows\n", tag.RowsAffected())

Pattern 4: Transaction with Auto-Commit/Rollback

err := pgx.BeginFunc(ctx, pool, func(tx pgx.Tx) error {
    _, err := tx.Exec(ctx, "UPDATE accounts SET balance = balance - $1 WHERE id = $2", 100, fromAccount)
    if err != nil {
        return err // automatic rollback
    }
    
    _, err = tx.Exec(ctx, "UPDATE accounts SET balance = balance + $1 WHERE id = $2", 100, toAccount)
    if err != nil {
        return err // automatic rollback
    }
    
    return nil // automatic commit
})

Pattern 5: Bulk Insert with CopyFrom

rows := [][]any{
    {"Alice", "alice@example.com"},
    {"Bob", "bob@example.com"},
    {"Charlie", "charlie@example.com"},
}

count, err := pool.CopyFrom(
    ctx,
    pgx.Identifier{"users"},
    []string{"name", "email"},
    pgx.CopyFromRows(rows),
)
fmt.Printf("Copied %d rows\n", count)

Pattern 6: Batch Multiple Queries

batch := &pgx.Batch{}

var user1Name, user2Name string

batch.Queue("SELECT name FROM users WHERE id = $1", 1).QueryRow(func(row pgx.Row) error {
    return row.Scan(&user1Name)
})

batch.Queue("SELECT name FROM users WHERE id = $1", 2).QueryRow(func(row pgx.Row) error {
    return row.Scan(&user2Name)
})

batch.Queue("UPDATE stats SET last_access = now()")

br := pool.SendBatch(ctx, batch)
err := br.Close()
if err != nil {
    return err
}
// user1Name and user2Name are now populated

Pattern 7: Query with Named Arguments

rows, err := pool.Query(ctx,
    "SELECT * FROM orders WHERE user_id = @user_id AND status = @status",
    pgx.NamedArgs{
        "user_id": 42,
        "status":  "pending",
    })

Pattern 8: Scan into Map

rows, err := pool.Query(ctx, "SELECT id, name, email FROM users LIMIT 10")
if err != nil {
    return err
}
userMaps, err := pgx.CollectRows(rows, pgx.RowToMap)
// userMaps is []map[string]any

Pattern 9: Stream Large Result Set

For very large result sets where loading all rows into memory isn't practical:

rows, err := pool.Query(ctx, "SELECT id, data FROM large_table")
if err != nil {
    return err
}
defer rows.Close()

for rows.Next() {
    var id int64
    var data string
    if err := rows.Scan(&id, &data); err != nil {
        return err
    }
    // process one row at a time
    processRow(id, data)
}

return rows.Err()

Pattern 10: Handle NULL Values

Option A: Use pgtype nullable wrappers

type User struct {
    ID    int64
    Name  string
    Email pgtype.Text // can be NULL
}

rows, err := pool.Query(ctx, "SELECT id, name, email FROM users")
users, err := pgx.CollectRows(rows, pgx.RowToStructByPos[User])

for _, u := range users {
    if u.Email.Valid {
        fmt.Println("Email:", u.Email.String)
    } else {
        fmt.Println("Email: NULL")
    }
}

Option B: Use pointers

type User struct {
    ID    int64
    Name  string
    Email *string // nil if NULL
}

rows, err := pool.Query(ctx, "SELECT id, name, email FROM users")
users, err := pgx.CollectRows(rows, pgx.RowToStructByPos[User])

for _, u := range users {
    if u.Email != nil {
        fmt.Println("Email:", *u.Email)
    } else {
        fmt.Println("Email: NULL")
    }
}

Option C: Use zeronull types (zero value = NULL)

import "github.com/jackc/pgx/v5/pgtype/zeronull"

type User struct {
    ID    int64
    Name  string
    Email zeronull.Text // "" is stored/scanned as NULL
}

rows, err := pool.Query(ctx, "SELECT id, name, email FROM users")
users, err := pgx.CollectRows(rows, pgx.RowToStructByPos[User])

Pattern 11: Work with Arrays

import "github.com/jackc/pgx/v5/pgtype"

// Query array
var tags pgtype.FlatArray[string]
err := pool.QueryRow(ctx, "SELECT tags FROM articles WHERE id = $1", articleID).Scan(&tags)

// Insert array
newTags := pgtype.FlatArray[string]{"golang", "postgresql", "database"}
_, err = pool.Exec(ctx, "INSERT INTO articles (title, tags) VALUES ($1, $2)", title, newTags)

Pattern 12: Work with JSON/JSONB

type Metadata struct {
    Author  string   `json:"author"`
    Tags    []string `json:"tags"`
    Version int      `json:"version"`
}

// Query JSONB
var meta Metadata
err := pool.QueryRow(ctx, "SELECT metadata FROM documents WHERE id = $1", docID).Scan(&meta)

// Insert JSONB
newMeta := Metadata{
    Author:  "Alice",
    Tags:    []string{"draft", "review"},
    Version: 1,
}
_, err = pool.Exec(ctx, "INSERT INTO documents (title, metadata) VALUES ($1, $2)", title, newMeta)

Pattern 13: Acquire Connection from Pool for Multiple Operations

conn, err := pool.Acquire(ctx)
if err != nil {
    return err
}
defer conn.Release()

// Use conn for multiple operations
_, err = conn.Exec(ctx, "SET work_mem = '256MB'")
rows, err := conn.Query(ctx, "SELECT * FROM large_table ORDER BY created_at")
// ... process rows

Or use AcquireFunc for auto-release:

err := pool.AcquireFunc(ctx, func(conn *pgxpool.Conn) error {
    _, err := conn.Exec(ctx, "SET work_mem = '256MB'")
    if err != nil {
        return err
    }
    rows, err := conn.Query(ctx, "SELECT * FROM large_table ORDER BY created_at")
    // ... process rows
    return nil
})

Pattern 14: Custom Type Registration

// Load and register custom enum type
t, err := conn.LoadType(ctx, "my_status_enum")
if err != nil {
    return err
}
conn.TypeMap().RegisterType(t)

// Load array type
arrType, err := conn.LoadType(ctx, "_my_status_enum")
conn.TypeMap().RegisterType(arrType)

// Now you can use the type
var status string
err = conn.QueryRow(ctx, "SELECT status FROM orders WHERE id = $1", orderID).Scan(&status)

Pattern 15: Prepared Statements (Manual)

Normally pgx auto-prepares. For explicit control:

sd, err := conn.Prepare(ctx, "get_user", "SELECT id, name FROM users WHERE email = $1")
if err != nil {
    return err
}

// Use prepared statement (by name)
var id int64
var name string
err = conn.QueryRow(ctx, "get_user", "alice@example.com").Scan(&id, &name)

// Deallocate when done
conn.Deallocate(ctx, "get_user")

Pattern 16: LISTEN/NOTIFY

_, err := conn.Exec(ctx, "LISTEN my_channel")
if err != nil {
    return err
}

for {
    notification, err := conn.WaitForNotification(ctx)
    if err != nil {
        return err
    }
    fmt.Printf("Received: %s on channel %s\n", notification.Payload, notification.Channel)
}

Pattern 17: Logging All Queries

import "github.com/jackc/pgx/v5/tracelog"

type myLogger struct{}

func (l *myLogger) Log(ctx context.Context, level tracelog.LogLevel, msg string, data map[string]any) {
    log.Printf("[%s] %s %v", level, msg, data)
}

config, _ := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))
config.ConnConfig.Tracer = &tracelog.TraceLog{
    Logger:   &myLogger{},
    LogLevel: tracelog.LogLevelInfo,
}
pool, err := pgxpool.NewWithConfig(ctx, config)

Pattern 18: Connection with TLS

import "crypto/tls"

config, _ := pgx.ParseConfig(connString)
config.TLSConfig = &tls.Config{
    InsecureSkipVerify: false,
    ServerName:         "myserver.example.com",
}
conn, err := pgx.ConnectConfig(ctx, config)

Pattern 19: Test Against Multiple Query Modes

import "github.com/jackc/pgx/v5/pgxtest"

func TestMyQuery(t *testing.T) {
    ctr := pgxtest.DefaultConnTestRunner()
    pgxtest.RunWithQueryExecModes(context.Background(), t, ctr, nil,
        func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
            // Test runs once per query execution mode
            var count int
            err := conn.QueryRow(ctx, "SELECT COUNT(*) FROM users").Scan(&count)
            assert.NoError(t, err)
            assert.Greater(t, count, 0)
        })
}

Pattern 20: Use database/sql with pgx-specific features

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

db, err := sql.Open("pgx", connString)

// Get underlying pgx connection for pgx-specific features
sqlConn, err := db.Conn(ctx)
defer sqlConn.Close()

err = sqlConn.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
})

Install with Tessl CLI

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

docs

batch.md

common-patterns.md

connection-pool.md

copy.md

database-sql.md

direct-connection.md

index.md

querying.md

testing.md

tracing.md

transactions.md

tile.json