169 lines
4.2 KiB
Go
169 lines
4.2 KiB
Go
// Package database provides a standardized PostgreSQL/CockroachDB connection pool.
|
|
//
|
|
// This package wraps sqlx to provide:
|
|
// - Connection pool management with sensible defaults
|
|
// - Health checks for liveness/readiness probes
|
|
// - Context-aware query execution
|
|
//
|
|
// Usage:
|
|
//
|
|
// // Connect with configuration from pkg/config
|
|
// dbCfg := config.ReadDatabaseConfig()
|
|
// pool, err := database.Connect(ctx, dbCfg.URL, database.Options{
|
|
// MaxOpenConns: dbCfg.MaxOpenConns,
|
|
// MaxIdleConns: dbCfg.MaxIdleConns,
|
|
// ConnMaxLifetime: dbCfg.ConnMaxLifetime,
|
|
// })
|
|
// if err != nil {
|
|
// log.Fatal("failed to connect to database", "error", err)
|
|
// }
|
|
// defer pool.Close()
|
|
//
|
|
// // Use pool.DB for queries
|
|
// var users []User
|
|
// err = pool.DB.SelectContext(ctx, &users, "SELECT * FROM users")
|
|
package database
|
|
|
|
import (
|
|
"context"
|
|
"fmt"
|
|
"net/url"
|
|
"time"
|
|
|
|
"github.com/jmoiron/sqlx"
|
|
_ "github.com/lib/pq" // PostgreSQL/CockroachDB driver
|
|
)
|
|
|
|
// Pool wraps a sqlx.DB with additional lifecycle management.
|
|
type Pool struct {
|
|
// DB is the underlying sqlx database connection pool.
|
|
// Use this for all query operations.
|
|
DB *sqlx.DB
|
|
|
|
// URL is the connection URL (redacted for logging).
|
|
URL string
|
|
}
|
|
|
|
// Options configures the database connection pool.
|
|
type Options struct {
|
|
// MaxOpenConns sets the maximum number of open connections.
|
|
// Default: 25
|
|
MaxOpenConns int
|
|
|
|
// MaxIdleConns sets the maximum number of idle connections.
|
|
// Default: 5
|
|
MaxIdleConns int
|
|
|
|
// ConnMaxLifetime sets the maximum lifetime of a connection.
|
|
// Default: 5 minutes
|
|
ConnMaxLifetime time.Duration
|
|
|
|
// ConnMaxIdleTime sets the maximum idle time for a connection.
|
|
// Default: 0 (no limit)
|
|
ConnMaxIdleTime time.Duration
|
|
}
|
|
|
|
// Connect establishes a connection pool to the database.
|
|
// The URL should be a PostgreSQL connection string:
|
|
//
|
|
// postgres://user:pass@host:port/dbname?sslmode=disable
|
|
func Connect(ctx context.Context, url string, opts Options) (*Pool, error) {
|
|
if url == "" {
|
|
return nil, fmt.Errorf("database URL is required")
|
|
}
|
|
|
|
// Apply defaults
|
|
if opts.MaxOpenConns == 0 {
|
|
opts.MaxOpenConns = 25
|
|
}
|
|
if opts.MaxIdleConns == 0 {
|
|
opts.MaxIdleConns = 5
|
|
}
|
|
if opts.ConnMaxLifetime == 0 {
|
|
opts.ConnMaxLifetime = 5 * time.Minute
|
|
}
|
|
|
|
db, err := sqlx.ConnectContext(ctx, "postgres", url)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("failed to connect to database: %w", err)
|
|
}
|
|
|
|
// Configure pool
|
|
db.SetMaxOpenConns(opts.MaxOpenConns)
|
|
db.SetMaxIdleConns(opts.MaxIdleConns)
|
|
db.SetConnMaxLifetime(opts.ConnMaxLifetime)
|
|
if opts.ConnMaxIdleTime > 0 {
|
|
db.SetConnMaxIdleTime(opts.ConnMaxIdleTime)
|
|
}
|
|
|
|
// Verify connection
|
|
if err := db.PingContext(ctx); err != nil {
|
|
db.Close()
|
|
return nil, fmt.Errorf("failed to ping database: %w", err)
|
|
}
|
|
|
|
return &Pool{
|
|
DB: db,
|
|
URL: redactURL(url),
|
|
}, nil
|
|
}
|
|
|
|
// MustConnect is like Connect but panics on error.
|
|
// Useful in main() for fail-fast initialization.
|
|
func MustConnect(ctx context.Context, url string, opts Options) *Pool {
|
|
pool, err := Connect(ctx, url, opts)
|
|
if err != nil {
|
|
panic(fmt.Sprintf("failed to connect to database: %v", err))
|
|
}
|
|
return pool
|
|
}
|
|
|
|
// Close closes the database connection pool.
|
|
func (p *Pool) Close() error {
|
|
if p.DB != nil {
|
|
return p.DB.Close()
|
|
}
|
|
return nil
|
|
}
|
|
|
|
// Ping verifies the database connection is alive.
|
|
// Use this for health checks.
|
|
func (p *Pool) Ping(ctx context.Context) error {
|
|
return p.DB.PingContext(ctx)
|
|
}
|
|
|
|
// Stats returns connection pool statistics.
|
|
func (p *Pool) Stats() Stats {
|
|
s := p.DB.Stats()
|
|
return Stats{
|
|
MaxOpenConnections: s.MaxOpenConnections,
|
|
OpenConnections: s.OpenConnections,
|
|
InUse: s.InUse,
|
|
Idle: s.Idle,
|
|
WaitCount: s.WaitCount,
|
|
WaitDuration: s.WaitDuration,
|
|
}
|
|
}
|
|
|
|
// Stats holds connection pool statistics.
|
|
type Stats struct {
|
|
MaxOpenConnections int
|
|
OpenConnections int
|
|
InUse int
|
|
Idle int
|
|
WaitCount int64
|
|
WaitDuration time.Duration
|
|
}
|
|
|
|
// redactURL removes password from database URL for safe logging.
|
|
func redactURL(rawURL string) string {
|
|
u, err := url.Parse(rawURL)
|
|
if err != nil {
|
|
return "[invalid-url]"
|
|
}
|
|
if u.User != nil {
|
|
u.User = url.UserPassword(u.User.Username(), "****")
|
|
}
|
|
return u.String()
|
|
}
|