Database pool

Learn how Boiler-Go manages PostgreSQL connections using pgxpool for optimal performance and reliability

Boiler-Go uses pgxpool to manage PostgreSQL database connections efficiently. The connection pool is initialized once at startup and shared across the application.

Architecture

The database pool is implemented as a singleton with thread-safe access using sync.RWMutex. This ensures:

• Single initialization per application lifecycle
• Safe concurrent access from multiple goroutines
• Clean shutdown with proper resource cleanup

Pool configuration

The pool is configured in internal/db/pool.go:28-36 with production-ready defaults:

poolConfig.MaxConns = 15
poolConfig.MinConns = 2
poolConfig.MaxConnLifetime = 30 * time.Minute
poolConfig.MaxConnIdleTime = 5 * time.Minute
poolConfig.HealthCheckPeriod = 1 * time.Minute

Configuration parameters explained

MaxConns · int · default: "15"

Maximum number of concurrent connections. Prevents database overload.

MinConns · int · default: "2"

Minimum connections kept alive. Reduces latency for incoming requests.

MaxConnLifetime · duration · default: "30m"

Maximum time a connection can be reused before being closed and recreated.

MaxConnIdleTime · duration · default: "5m"

Maximum time an idle connection stays open before being closed.

HealthCheckPeriod · duration · default: "1m"

Interval between automatic health checks on idle connections.

Initialization

The pool is initialized in both cmd/api/main.go and cmd/worker/main.go with a timeout context:

// Initialize database pool with timeout context
dbCtx, dbCancel := context.WithTimeout(ctx, 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()

1. Parse configuration

   The DATABASE_URL from config is parsed into pgxpool.Config

2. Create pool

   A new connection pool is created with the configured parameters

3. Health check

   The pool performs a ping to verify database connectivity

4. Store singleton

   The pool is stored in the package-level variable for global access

If the pool is already initialized, calling Open() again returns an error: "database pool already initialized". This prevents accidental re-initialization.

Usage

Access the pool anywhere in your application using the thread-safe getter:

pool := db.Get()
row := pool.QueryRow(ctx, "SELECT name FROM users WHERE id = $1", userID)

Example query

func GetUser(ctx context.Context, userID int) (*User, error) {
    pool := db.Get()
    
    var user User
    err := pool.QueryRow(ctx,
        "SELECT id, name, email FROM users WHERE id = $1",
        userID,
    ).Scan(&user.ID, &user.Name, &user.Email)
    
    if err != nil {
        return nil, fmt.Errorf("query failed: %w", err)
    }
    
    return &user, nil
}

Shutdown

The pool is automatically closed during graceful shutdown:

defer db.Close()

This ensures:

• All active connections are properly closed
• Resources are released back to the OS
• No connection leaks occur

Always use defer db.Close() immediately after successful initialization to guarantee cleanup even if the application panics.

Error handling

The initialization process includes comprehensive error handling:

Config parsing error

if err != nil {
    return fmt.Errorf("failed to parse database config: %w", err)
}

The pool automatically cleans itself up if the ping fails, preventing resource leaks even during initialization failures.

Best practices

1. Always use context: Pass a context with appropriate timeout to all queries
2. Connection limit: Keep MaxConns aligned with your database’s max_connections setting
3. Monitor metrics: Use pool.Stat() to track connection pool health
4. Avoid connection exhaustion: Ensure all queries complete within reasonable timeframes

// Get pool statistics
stats := pool.Stat()
fmt.Printf("Total connections: %d\n", stats.TotalConns())
fmt.Printf("Idle connections: %d\n", stats.IdleConns())
fmt.Printf("Acquired connections: %d\n", stats.AcquiredConns())