db

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