db

README

Database Package

This package provides a unified database interface that works with both MySQL and PostgreSQL databases, including PostgreSQL 17. It handles connection management, pooling, and query execution.

Features

• Unified interface for MySQL and PostgreSQL (all versions)
• Comprehensive PostgreSQL connection options for compatibility with all versions
• Connection pooling with configurable parameters
• Context-aware query execution with timeout support
• Transaction support
• Proper error handling

PostgreSQL Version Compatibility

This package is designed to be compatible with all PostgreSQL versions, including:

• PostgreSQL 10+
• PostgreSQL 14+
• PostgreSQL 15+
• PostgreSQL 16+
• PostgreSQL 17

The connection string builder automatically adapts to specific PostgreSQL version requirements.

Configuration Options

Basic Configuration

Configure the database connection using the Config struct:

cfg := db.Config{
    Type:            "mysql", // or "postgres"
    Host:            "localhost",
    Port:            3306,
    User:            "user",
    Password:        "password",
    Name:            "dbname",
    MaxOpenConns:    25,
    MaxIdleConns:    5,
    ConnMaxLifetime: 5 * time.Minute,
    ConnMaxIdleTime: 5 * time.Minute,
}

PostgreSQL-Specific Options

For PostgreSQL databases, additional options are available:

cfg := db.Config{
    Type:            "postgres",
    Host:            "localhost",
    Port:            5432,
    User:            "user",
    Password:        "password",
    Name:            "dbname",
    
    // PostgreSQL-specific options
    SSLMode:          db.SSLPrefer,                   // SSL mode (disable, prefer, require, verify-ca, verify-full)
    SSLCert:          "/path/to/client-cert.pem",     // Client certificate file
    SSLKey:           "/path/to/client-key.pem",      // Client key file
    SSLRootCert:      "/path/to/root-cert.pem",       // Root certificate file
    ApplicationName:  "myapp",                        // Application name for pg_stat_activity
    ConnectTimeout:   10,                             // Connection timeout in seconds
    TargetSessionAttrs: "any",                        // For load balancing (any, read-write, read-only, primary, standby)
    
    // Additional connection parameters
    Options: map[string]string{
        "client_encoding": "UTF8",
        "timezone":        "UTC",
    },
    
    // Connection pool settings
    MaxOpenConns:    25,
    MaxIdleConns:    5,
    ConnMaxLifetime: 5 * time.Minute,
    ConnMaxIdleTime: 5 * time.Minute,
}

JSON Configuration

When using JSON configuration files, the PostgreSQL options are specified as follows:

{
  "id": "postgres17",
  "type": "postgres",
  "host": "postgres17",
  "port": 5432,
  "name": "mydb",
  "user": "postgres",
  "password": "password",
  "ssl_mode": "prefer",
  "application_name": "myapp",
  "connect_timeout": 15,
  "target_session_attrs": "any",
  "options": {
    "application_name": "myapp",
    "client_encoding": "UTF8"
  },
  "max_open_conns": 25,
  "max_idle_conns": 5,
  "conn_max_lifetime_seconds": 300,
  "conn_max_idle_time_seconds": 60
}

Usage Examples

Connecting to the Database

// Create a new database instance
database, err := db.NewDatabase(cfg)
if err != nil {
    log.Fatalf("Failed to create database instance: %v", err)
}

// Connect to the database
if err := database.Connect(); err != nil {
    log.Fatalf("Failed to connect to database: %v", err)
}
defer database.Close()

Executing Queries

// Context with timeout
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()

// Execute a query that returns rows
rows, err := database.Query(ctx, "SELECT id, name FROM users WHERE age > $1", 18)
if err != nil {
    log.Fatalf("Query failed: %v", err)
}
defer rows.Close()

// Process rows
for rows.Next() {
    var id int
    var name string
    if err := rows.Scan(&id, &name); err != nil {
        log.Printf("Failed to scan row: %v", err)
        continue
    }
    fmt.Printf("User: %d - %s\n", id, name)
}

if err = rows.Err(); err != nil {
    log.Printf("Error during row iteration: %v", err)
}

Using the Database Manager

// Create a database manager
manager := db.NewDBManager()

// Load configuration from JSON
configJSON, err := ioutil.ReadFile("config.json")
if err != nil {
    log.Fatalf("Failed to read config file: %v", err)
}

if err := manager.LoadConfig(configJSON); err != nil {
    log.Fatalf("Failed to load database config: %v", err)
}

// Connect to all databases
if err := manager.Connect(); err != nil {
    log.Fatalf("Failed to connect to databases: %v", err)
}
defer manager.CloseAll()

// Get a specific database connection
postgres17, err := manager.GetDatabase("postgres17")
if err != nil {
    log.Fatalf("Failed to get database: %v", err)
}

// Use the database
// ...

PostgreSQL 17 Support

This package fully supports PostgreSQL 17 by:

1. Using connection string parameters compatible with PostgreSQL 17
2. Supporting all PostgreSQL 17 connection options including TLS/SSL modes
3. Properly handling connection pool management
4. Working with both older and newer versions of PostgreSQL on the same codebase

Documentation

Index

• Variables
• type Config
  • func (c *Config) SetDefaults()
• type Database
  • func NewDatabase(config Config) (Database, error)
• type DatabaseConnectionConfig
• type Manager
  • func NewDBManager() *Manager
  • func (m *Manager) Close(id string) error
  • func (m *Manager) CloseAll() error
  • func (m *Manager) Connect() error
  • func (m *Manager) GetConnectedDatabases() []string
  • func (m *Manager) GetDatabase(id string) (Database, error)
  • func (m *Manager) GetDatabaseConfig(id string) (DatabaseConnectionConfig, error)
  • func (m *Manager) GetDatabaseType(id string) (string, error)
  • func (m *Manager) ListDatabases() []string
  • func (m *Manager) LoadConfig(configJSON []byte) error
• type MultiDBConfig
• type PostgresSSLMode

Variables

var (
	ErrNotFound       = errors.New("record not found")
	ErrAlreadyExists  = errors.New("record already exists")
	ErrInvalidInput   = errors.New("invalid input")
	ErrNotImplemented = errors.New("not implemented")
	ErrNoDatabase     = errors.New("no database connection")
)

Common database errors

Types

type Config

type Config struct {
	Type     string
	Host     string
	Port     int
	User     string
	Password string
	Name     string

	// Additional PostgreSQL specific options
	SSLMode            PostgresSSLMode
	SSLCert            string
	SSLKey             string
	SSLRootCert        string
	ApplicationName    string
	ConnectTimeout     int               // in seconds
	QueryTimeout       int               // in seconds, default is 30 seconds
	TargetSessionAttrs string            // for PostgreSQL 10+
	Options            map[string]string // Extra connection options

	// Connection pool settings
	MaxOpenConns    int
	MaxIdleConns    int
	ConnMaxLifetime time.Duration
	ConnMaxIdleTime time.Duration
}

Config represents database connection configuration

func (*Config) SetDefaults

func (c *Config) SetDefaults()

SetDefaults sets default values for the configuration if they are not set

type Database

type Database interface {
	// Core database operations
	Query(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
	QueryRow(ctx context.Context, query string, args ...interface{}) *sql.Row
	Exec(ctx context.Context, query string, args ...interface{}) (sql.Result, error)

	// Transaction support
	BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error)

	// Connection management
	Connect() error
	Close() error
	Ping(ctx context.Context) error

	// Metadata
	DriverName() string
	ConnectionString() string
	QueryTimeout() int

	// DB object access (for specific DB operations)
	DB() *sql.DB
}

Database represents a generic database interface

func NewDatabase

func NewDatabase(config Config) (Database, error)

NewDatabase creates a new database connection based on the provided configuration

type DatabaseConnectionConfig

type DatabaseConnectionConfig struct {
	ID       string `json:"id"`   // Unique identifier for this connection
	Type     string `json:"type"` // mysql or postgres
	Host     string `json:"host"`
	Port     int    `json:"port"`
	User     string `json:"user"`
	Password string `json:"password"`
	Name     string `json:"name"`

	// PostgreSQL specific options
	SSLMode            string            `json:"ssl_mode,omitempty"`
	SSLCert            string            `json:"ssl_cert,omitempty"`
	SSLKey             string            `json:"ssl_key,omitempty"`
	SSLRootCert        string            `json:"ssl_root_cert,omitempty"`
	ApplicationName    string            `json:"application_name,omitempty"`
	ConnectTimeout     int               `json:"connect_timeout,omitempty"`
	QueryTimeout       int               `json:"query_timeout,omitempty"` // in seconds
	TargetSessionAttrs string            `json:"target_session_attrs,omitempty"`
	Options            map[string]string `json:"options,omitempty"`

	// Connection pool settings
	MaxOpenConns    int `json:"max_open_conns,omitempty"`
	MaxIdleConns    int `json:"max_idle_conns,omitempty"`
	ConnMaxLifetime int `json:"conn_max_lifetime_seconds,omitempty"`  // in seconds
	ConnMaxIdleTime int `json:"conn_max_idle_time_seconds,omitempty"` // in seconds
}

DatabaseConnectionConfig represents a single database connection configuration

type Manager

type Manager struct {
	// contains filtered or unexported fields
}

Manager manages multiple database connections

func NewDBManager

func NewDBManager() *Manager

NewDBManager creates a new database manager

func (*Manager) Close

func (m *Manager) Close(id string) error

Close closes a specific database connection

func (*Manager) CloseAll

func (m *Manager) CloseAll() error

CloseAll closes all database connections

func (*Manager) Connect

func (m *Manager) Connect() error

Connect establishes connections to all configured databases

func (*Manager) GetConnectedDatabases

func (m *Manager) GetConnectedDatabases() []string

GetConnectedDatabases returns a list of all connected databases

func (*Manager) GetDatabase

func (m *Manager) GetDatabase(id string) (Database, error)

GetDatabase retrieves a database connection by ID

func (*Manager) GetDatabaseConfig

func (m *Manager) GetDatabaseConfig(id string) (DatabaseConnectionConfig, error)

GetDatabaseConfig returns the configuration for a specific database

func (*Manager) GetDatabaseType

func (m *Manager) GetDatabaseType(id string) (string, error)

GetDatabaseType returns the type of a database by its ID

func (*Manager) ListDatabases

func (m *Manager) ListDatabases() []string

ListDatabases returns a list of all configured databases

func (*Manager) LoadConfig

func (m *Manager) LoadConfig(configJSON []byte) error

LoadConfig loads database configurations from JSON

type MultiDBConfig

type MultiDBConfig struct {
	Connections []DatabaseConnectionConfig `json:"connections"`
}

MultiDBConfig represents the configuration for multiple database connections

type PostgresSSLMode

type PostgresSSLMode string

PostgresSSLMode defines the SSL mode for PostgreSQL connections

const (
	SSLDisable    PostgresSSLMode = "disable"
	SSLRequire    PostgresSSLMode = "require"
	SSLVerifyCA   PostgresSSLMode = "verify-ca"
	SSLVerifyFull PostgresSSLMode = "verify-full"
	SSLPrefer     PostgresSSLMode = "prefer"
)

SSLMode constants for PostgreSQL