Connection Pooling & the pgx Driver

PostgreSQL creates a separate OS process for every active connection. Poorly managed connections will rapidly exhaust server memory.

Essential Connection Pool Settings

1. SetMaxOpenConns(25): Limits total active connections. Prevents client spikes from crashing Postgres.
2. SetMaxIdleConns(25): Number of warm connections kept ready in the pool.
3. SetConnMaxLifetime(5 * time.Minute): Recycles stale connections to prevent leaked state and handle cloud DNS rotations.

Code Reference (main.go)

// Fundamentals learning: Connecting to and interacting with PostgreSQL.
// To run this file, you must have local Postgres running (e.g., brew services start postgresql).
//
// 1. First download the pgx driver:
//    go get github.com/jackc/pgx/v5/stdlib
// 2. Run the code:
//    go run basics_learning/3_postgres_db/main.go

package main

import (
    "context"
    "database/sql"
    "fmt"
    "log"
    "os"
    "time"

    // The pgx stdlib driver must be registered anonymously.
    // This tells Go's standard "database/sql" library how to talk to PostgreSQL.
    _ "github.com/jackc/pgx/v5/stdlib"
)

type Product struct {
    ID    int
    Name  string
    Price float64
}

func main() {
    // 1. Connection string (DSN). Adjust credentials to match your local setup.
    // Format: postgres://username:password@host:port/database_name?sslmode=disable
    connStr := os.Getenv("DATABASE_URL")
    if connStr == "" {
        connStr = "postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable"
    }

    // 2. Establish connection pool (sql.Open does not actually establish a connection yet,
    // it just prepares the database pool structure).
    db, err := sql.Open("pgx", connStr)
    if err != nil {
        log.Fatalf("Failed to open database config: %v", err)
    }
    defer db.Close() // Ensure the connection pool is closed on exit

    // 3. Configure Connection Pool Settings (CRITICAL for production/startup performance)
    db.SetMaxOpenConns(25)                 // Max number of open connections to the database
    db.SetMaxIdleConns(25)                 // Max number of connections in the idle connection pool
    db.SetConnMaxLifetime(5 * time.Minute) // Max amount of time a connection may be reused

    // 4. Ping the database to establish a real connection and verify credentials.
    ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
    defer cancel()

    err = db.PingContext(ctx)
    if err != nil {
        log.Fatalf("Could not connect to Postgres! Is it running? Error: %v\n\nTips:\n1. If Postgres is not running, run: brew services start postgresql\n2. Make sure username/password match your database setup.\n", err)
    }
    log.Println("Successfully connected to PostgreSQL database!")

    // 5. Create a temporary table for demo
    createTableSQL := `
    CREATE TABLE IF NOT EXISTS products (
        id SERIAL PRIMARY KEY,
        name TEXT NOT NULL,
        price NUMERIC(10, 2) NOT NULL
    );`
    _, err = db.ExecContext(ctx, createTableSQL)
    if err != nil {
        log.Fatalf("Failed to create table: %v", err)
    }
    log.Println("Table 'products' verified.")

    // 6. Insert a Product using parametrized inputs (PREVENTS SQL injection!)
    // Note: Postgres uses '$1', '$2', '$3' placeholders, NOT '?' or '%s'.
    insertSQL := `INSERT INTO products (name, price) VALUES ($1, $2) RETURNING id;`
    var newID int

    // QueryRowContext is used when we expect exactly one row returned (e.g. RETURNING id)
    err = db.QueryRowContext(ctx, insertSQL, "Go Programming Guide Book", 49.99).Scan(&newID)
    if err != nil {
        log.Fatalf("Failed to insert record: %v", err)
    }
    log.Printf("Inserted product successfully! Assigned ID: %d\n", newID)

    // 7. Query a single row
    selectOneSQL := `SELECT id, name, price FROM products WHERE id = $1;`
    var p Product
    err = db.QueryRowContext(ctx, selectOneSQL, newID).Scan(&p.ID, &p.Name, &p.Price)
    if err != nil {
        log.Fatalf("Failed to retrieve single product: %v", err)
    }
    log.Printf("Queried Single Product -> ID: %d, Name: %s, Price: $%.2f\n", p.ID, p.Name, p.Price)

    // 8. Query multiple rows
    selectManySQL := `SELECT id, name, price FROM products LIMIT 5;`
    // QueryContext returns a rows iterator
    rows, err := db.QueryContext(ctx, selectManySQL)
    if err != nil {
        log.Fatalf("Failed to query multiple products: %v", err)
    }
    defer rows.Close() // ALWAYS close rows to avoid connection pool leaks!

    log.Println("Listing up to 5 products:")
    for rows.Next() {
        var item Product
        // Scan matches column values to struct pointers by order of SELECT columns
        err := rows.Scan(&item.ID, &item.Name, &item.Price)
        if err != nil {
            log.Fatalf("Failed to scan row: %v", err)
        }
        fmt.Printf(" - ID: %d, Name: %q, Price: $%.2f\n", item.ID, item.Name, item.Price)
    }

    // Always check rows.Err() after loop finished.
    // If a network connection error happened mid-stream, rows.Next() returns false,
    // and rows.Err() will contain the actual connection error.
    if err = rows.Err(); err != nil {
        log.Fatalf("Error iterating rows: %v", err)
    }
}