Database

PostgreSQL connection pool management with pgx

The db package provides a global connection pool for PostgreSQL using the high-performance pgx/v5 driver. It manages connection lifecycle, pooling configuration, and provides thread-safe access to database connections.

Functions

Open

Initializes the database connection pool with optimized settings.

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

ctx · context.Context

Context for timeout control during connection establishment

cfg · *config.Config

Configuration containing the database URL

Returns: error - Error if initialization fails

Pool configuration:

• MaxConns: 15 connections
• MinConns: 2 connections
• MaxConnLifetime: 30 minutes
• MaxConnIdleTime: 5 minutes
• HealthCheckPeriod: 1 minute

Behavior:

• Returns error if pool is already initialized
• Parses the database URL from configuration
• Creates connection pool with optimized settings
• Pings database to verify connectivity
• Cleans up pool on ping failure

Get

Returns the global connection pool instance.

func Get() *pgxpool.Pool

Returns: *pgxpool.Pool - The connection pool instance

Thread-safe: Uses read lock for concurrent access

Close

Closes the connection pool and cleans up all connections.

func Close()

Thread-safe: Uses write lock to ensure safe cleanup

Types

DBTX

Interface for database operations, compatible with both *pgxpool.Pool and pgx.Tx for transaction support.

type DBTX interface {
    Exec(context.Context, string, ...interface{}) (pgconn.CommandTag, error)
    Query(context.Context, string, ...interface{}) (pgx.Rows, error)
    QueryRow(context.Context, string, ...interface{}) pgx.Row
}

Queries

Generated by sqlc for type-safe database queries.

type Queries struct {
    db DBTX
}

New

Creates a new Queries instance for executing type-safe SQL operations.

func New(db DBTX) *Queries

db · DBTX

Database connection or transaction

Returns: *Queries - Query executor instance

WithTx

Creates a new Queries instance scoped to a transaction.

func (q *Queries) WithTx(tx pgx.Tx) *Queries

tx · pgx.Tx

Transaction to use for queries

Returns: *Queries - Transaction-scoped query executor

Usage

Initialize database pool

import (
    "context"
    "time"
    "boiler-go/internal/config"
    "boiler-go/internal/db"
    "boiler-go/pkg/logger"
)

func main() {
    cfg := config.Load(logger.New())
    
    // Initialize with timeout
    ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
    defer cancel()
    
    if err := db.Open(ctx, cfg); err != nil {
        log.Fatal().Err(err).Msg("failed to initialize database")
    }
    defer db.Close()
    
    // Database is ready to use
}

Execute queries

// Get the pool
pool := db.Get()

// Execute a query
var count int
err := pool.QueryRow(ctx, "SELECT COUNT(*) FROM users").Scan(&count)
if err != nil {
    log.Error().Err(err).Msg("query failed")
}

Use with sqlc-generated queries

// Create queries instance
queries := db.New(db.Get())

// Execute type-safe queries
user, err := queries.GetUser(ctx, userID)
if err != nil {
    log.Error().Err(err).Msg("failed to get user")
}

Transaction example

pool := db.Get()

// Begin transaction
tx, err := pool.Begin(ctx)
if err != nil {
    return err
}
defer tx.Rollback(ctx)

// Use queries with transaction
queries := db.New(pool).WithTx(tx)

// Execute operations
if err := queries.CreateUser(ctx, params); err != nil {
    return err
}

if err := queries.UpdateProfile(ctx, profileParams); err != nil {
    return err
}

// Commit transaction
if err := tx.Commit(ctx); err != nil {
    return err
}

Example from API server

// From cmd/api/main.go
func main() {
    cfg := config.Load(logger.New())
    logg := newLogger(cfg, "logs/api.log")
    
    // Initialize database pool with timeout context
    dbCtx, dbCancel := context.WithTimeout(context.Background(), 10*time.Second)
    defer dbCancel()
    if err := db.Open(dbCtx, cfg); err != nil {
        logg.Fatal().Err(err).Msg("failed to initialize database")
    }
    logg.Info().Msg("database connected")
    defer db.Close()
    
    // Pass pool to router for handlers
    router := handler.NewRouter(logg, cfg, db.Get(), rdb, schedulerClient)
}

Thread safety

All package functions are thread-safe:

• Open() uses write lock to prevent concurrent initialization
• Get() uses read lock for safe concurrent access
• Close() uses write lock for safe cleanup

The underlying pgxpool.Pool is also fully thread-safe and designed for concurrent use.