Database Operations

The Connection Pool That Saved Our Database

It was 2:34 AM when our database monitoring went red. CPU at 98%. Connection count: 1,247 active connections. PostgreSQL was dying.

The culprit was our Go service. Every HTTP request opened a new database connection:

func getUserHandler(w http.ResponseWriter, r *http.Request) {
    // BAD: New connection per request
    db, err := sql.Open("postgres", connectionString)
    if err != nil {
        http.Error(w, err.Error(), 500)
        return
    }
    defer db.Close()  // Too late!
    
    var user User
    db.QueryRow("SELECT id, name FROM users WHERE id = $1", userID).Scan(&user.ID, &user.Name)
    
    json.NewEncoder(w).Encode(user)
}

At 500 requests per second, we were creating 500 connections per second. The database couldn't keep up.

The fix was simple - connection pooling:

// GOOD: Shared connection pool
var db *sql.DB

func init() {
    db, _ = sql.Open("postgres", connectionString)
    db.SetMaxOpenConns(25)
    db.SetMaxIdleConns(5)
    db.SetConnMaxLifetime(5 * time.Minute)
}

func getUserHandler(w http.ResponseWriter, r *http.Request) {
    var user User
    // Reuses connections from pool
    db.QueryRow("SELECT id, name FROM users WHERE id = $1", userID).Scan(&user.ID, &user.Name)
    
    json.NewEncoder(w).Encode(user)
}

The result:

• Connection count dropped to 25 max

• Database CPU dropped to 12%

• Response time improved from 847ms to 23ms

• Zero downtime since

That incident taught me: database connections are precious. This article covers everything I learned.

---

The database/sql Package

Go's database/sql package provides a generic interface for SQL databases.

Supported Drivers

You need a driver for your specific database:

# PostgreSQL
go get github.com/lib/pq

# MySQL
go get github.com/go-sql-driver/mysql

# SQLite
go get github.com/mattn/go-sqlite3

Opening a Connection

package main

import (
    "database/sql"
    "log"
    
    _ "github.com/lib/pq"  // Import driver (blank import)
)

func main() {
    // PostgreSQL connection string
    connStr := "postgres://user:password@localhost/dbname?sslmode=disable"
    
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
    
    // Test connection
    if err := db.Ping(); err != nil {
        log.Fatal(err)
    }
    
    log.Println("Connected to database!")
}

Important: sql.Open() doesn't actually connect - it just validates the connection string. Use Ping() to verify the connection works.

---

Connection Pooling Configuration

The *sql.DB is a connection pool, not a single connection.

Pool Settings

db, err := sql.Open("postgres", connStr)
if err != nil {
    log.Fatal(err)
}

// Maximum number of open connections
db.SetMaxOpenConns(25)

// Maximum number of idle connections
db.SetMaxIdleConns(5)

// Maximum lifetime of a connection
db.SetConnMaxLifetime(5 * time.Minute)

// Maximum idle time of a connection
db.SetConnMaxIdleTime(10 * time.Minute)

Tuning Guidelines

MaxOpenConns:

• Too high: Overwhelms database

• Too low: Bottlenecks your app

• Start with: 25-50 for most apps

MaxIdleConns:

• Keeps connections warm for reuse

• Start with: 25% of MaxOpenConns

ConnMaxLifetime:

• Prevents stale connections

• Start with: 5 minutes

Real Configuration

func initDB() *sql.DB {
    db, err := sql.Open("postgres", os.Getenv("DATABASE_URL"))
    if err != nil {
        log.Fatal(err)
    }
    
    // Production settings
    db.SetMaxOpenConns(25)
    db.SetMaxIdleConns(5)
    db.SetConnMaxLifetime(5 * time.Minute)
    db.SetConnMaxIdleTime(10 * time.Minute)
    
    // Verify connection
    ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
    defer cancel()
    
    if err := db.PingContext(ctx); err != nil {
        log.Fatal("Failed to ping database:", err)
    }
    
    log.Println("Database connection pool established")
    return db
}

---

Querying Data

Single Row Query

type User struct {
    ID    int
    Name  string
    Email string
}

func getUser(db *sql.DB, userID int) (*User, error) {
    var user User
    
    query := "SELECT id, name, email FROM users WHERE id = $1"
    
    err := db.QueryRow(query, userID).Scan(&user.ID, &user.Name, &user.Email)
    if err != nil {
        if err == sql.ErrNoRows {
            return nil, fmt.Errorf("user not found")
        }
        return nil, err
    }
    
    return &user, nil
}

Note: PostgreSQL uses $1, $2, $3 for placeholders. MySQL uses ?.

Multiple Rows Query

func listUsers(db *sql.DB) ([]*User, error) {
    query := "SELECT id, name, email FROM users ORDER BY id"
    
    rows, err := db.Query(query)
    if err != nil {
        return nil, err
    }
    defer rows.Close()  // Important!
    
    var users []*User
    
    for rows.Next() {
        var user User
        if err := rows.Scan(&user.ID, &user.Name, &user.Email); err != nil {
            return nil, err
        }
        users = append(users, &user)
    }
    
    // Check for errors during iteration
    if err := rows.Err(); err != nil {
        return nil, err
    }
    
    return users, nil
}

Critical: Always defer rows.Close() to release the connection back to the pool.

Query with WHERE Clause

func searchUsers(db *sql.DB, namePattern string) ([]*User, error) {
    query := "SELECT id, name, email FROM users WHERE name LIKE $1"
    
    rows, err := db.Query(query, "%"+namePattern+"%")
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    
    var users []*User
    for rows.Next() {
        var user User
        rows.Scan(&user.ID, &user.Name, &user.Email)
        users = append(users, &user)
    }
    
    return users, nil
}

---

Inserting, Updating, Deleting

Insert

func createUser(db *sql.DB, name, email string) (int, error) {
    query := `
        INSERT INTO users (name, email, created_at)
        VALUES ($1, $2, NOW())
        RETURNING id
    `
    
    var userID int
    err := db.QueryRow(query, name, email).Scan(&userID)
    if err != nil {
        return 0, err
    }
    
    return userID, nil
}

Update

func updateUser(db *sql.DB, userID int, name, email string) error {
    query := `
        UPDATE users
        SET name = $1, email = $2, updated_at = NOW()
        WHERE id = $3
    `
    
    result, err := db.Exec(query, name, email, userID)
    if err != nil {
        return err
    }
    
    // Check if any rows were affected
    rowsAffected, err := result.RowsAffected()
    if err != nil {
        return err
    }
    
    if rowsAffected == 0 {
        return fmt.Errorf("user not found")
    }
    
    return nil
}

Delete

func deleteUser(db *sql.DB, userID int) error {
    query := "DELETE FROM users WHERE id = $1"
    
    result, err := db.Exec(query, userID)
    if err != nil {
        return err
    }
    
    rowsAffected, err := result.RowsAffected()
    if err != nil {
        return err
    }
    
    if rowsAffected == 0 {
        return fmt.Errorf("user not found")
    }
    
    return nil
}

---

Prepared Statements

Prepared statements are pre-compiled SQL queries. Benefits:

• Better performance (query is parsed once)

• Protection against SQL injection

• Cleaner code for repeated queries

Basic Prepared Statement

func getUsersByIDs(db *sql.DB, userIDs []int) ([]*User, error) {
    // Prepare statement once
    stmt, err := db.Prepare("SELECT id, name, email FROM users WHERE id = $1")
    if err != nil {
        return nil, err
    }
    defer stmt.Close()
    
    var users []*User
    
    // Execute multiple times
    for _, id := range userIDs {
        var user User
        err := stmt.QueryRow(id).Scan(&user.ID, &user.Name, &user.Email)
        if err != nil {
            if err == sql.ErrNoRows {
                continue
            }
            return nil, err
        }
        users = append(users, &user)
    }
    
    return users, nil
}

When to Use Prepared Statements

Use when:

• Executing the same query multiple times

• Complex queries with many parameters

Don't use when:

• One-off queries

• Dynamic queries (varying columns)

---

Transactions

Transactions ensure multiple operations succeed or fail together.

Basic Transaction

func transferMoney(db *sql.DB, fromUserID, toUserID int, amount float64) error {
    // Begin transaction
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    
    // Defer rollback (no-op if transaction is committed)
    defer tx.Rollback()
    
    // Deduct from sender
    _, err = tx.Exec(
        "UPDATE accounts SET balance = balance - $1 WHERE user_id = $2",
        amount, fromUserID,
    )
    if err != nil {
        return err  // Automatic rollback
    }
    
    // Add to receiver
    _, err = tx.Exec(
        "UPDATE accounts SET balance = balance + $1 WHERE user_id = $2",
        amount, toUserID,
    )
    if err != nil {
        return err  // Automatic rollback
    }
    
    // Commit transaction
    if err := tx.Commit(); err != nil {
        return err
    }
    
    return nil
}

Transaction with Context

func transferMoneyWithContext(ctx context.Context, db *sql.DB, fromUserID, toUserID int, amount float64) error {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback()
    
    // Check sender balance
    var balance float64
    err = tx.QueryRowContext(ctx,
        "SELECT balance FROM accounts WHERE user_id = $1 FOR UPDATE",
        fromUserID,
    ).Scan(&balance)
    if err != nil {
        return err
    }
    
    if balance < amount {
        return fmt.Errorf("insufficient funds")
    }
    
    // Deduct from sender
    _, err = tx.ExecContext(ctx,
        "UPDATE accounts SET balance = balance - $1 WHERE user_id = $2",
        amount, fromUserID,
    )
    if err != nil {
        return err
    }
    
    // Add to receiver
    _, err = tx.ExecContext(ctx,
        "UPDATE accounts SET balance = balance + $1 WHERE user_id = $2",
        amount, toUserID,
    )
    if err != nil {
        return err
    }
    
    return tx.Commit()
}

FOR UPDATE: Locks the row until transaction completes, preventing race conditions.

---

Scanning Results

Scanning into Struct

type User struct {
    ID        int
    Name      string
    Email     string
    CreatedAt time.Time
}

func getUser(db *sql.DB, userID int) (*User, error) {
    var user User
    
    query := "SELECT id, name, email, created_at FROM users WHERE id = $1"
    
    err := db.QueryRow(query, userID).Scan(
        &user.ID,
        &user.Name,
        &user.Email,
        &user.CreatedAt,
    )
    
    return &user, err
}

Handling NULL Values

type User struct {
    ID    int
    Name  string
    Email sql.NullString  // Can be NULL
    Phone sql.NullString  // Can be NULL
}

func getUser(db *sql.DB, userID int) (*User, error) {
    var user User
    
    err := db.QueryRow(
        "SELECT id, name, email, phone FROM users WHERE id = $1",
        userID,
    ).Scan(&user.ID, &user.Name, &user.Email, &user.Phone)
    
    if err != nil {
        return nil, err
    }
    
    // Access nullable values
    if user.Email.Valid {
        fmt.Println("Email:", user.Email.String)
    } else {
        fmt.Println("Email: NULL")
    }
    
    return &user, nil
}

NULL types: sql.NullString, sql.NullInt64, sql.NullFloat64, sql.NullBool, sql.NullTime

---

Working with sqlx

sqlx is a popular library that extends database/sql with useful features.

Installation

go get github.com/jmoiron/sqlx

Basic Usage

import "github.com/jmoiron/sqlx"

type User struct {
    ID        int       `db:"id"`
    Name      string    `db:"name"`
    Email     string    `db:"email"`
    CreatedAt time.Time `db:"created_at"`
}

func main() {
    db, err := sqlx.Connect("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    
    // Get single row
    var user User
    err = db.Get(&user, "SELECT * FROM users WHERE id = $1", 1)
    
    // Get multiple rows
    var users []User
    err = db.Select(&users, "SELECT * FROM users ORDER BY id")
}

Benefits:

• Automatically maps columns to struct fields

• Less boilerplate than database/sql

• Named parameters support

Named Queries

type CreateUserParams struct {
    Name  string `db:"name"`
    Email string `db:"email"`
}

func createUser(db *sqlx.DB, params CreateUserParams) error {
    query := `
        INSERT INTO users (name, email)
        VALUES (:name, :email)
    `
    
    _, err := db.NamedExec(query, params)
    return err
}

---

Working with GORM

GORM is a full-featured ORM for Go.

Installation

go get -u gorm.io/gorm
go get -u gorm.io/driver/postgres

Basic Usage

import (
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

type User struct {
    ID        uint      `gorm:"primaryKey"`
    Name      string    `gorm:"size:255"`
    Email     string    `gorm:"uniqueIndex"`
    CreatedAt time.Time
    UpdatedAt time.Time
}

func main() {
    dsn := "host=localhost user=gorm password=gorm dbname=gorm port=5432 sslmode=disable"
    db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
    if err != nil {
        log.Fatal(err)
    }
    
    // Auto-migrate schema
    db.AutoMigrate(&User{})
    
    // Create
    user := User{Name: "Alice", Email: "[email protected]"}
    db.Create(&user)
    
    // Read
    var foundUser User
    db.First(&foundUser, "email = ?", "[email protected]")
    
    // Update
    db.Model(&foundUser).Update("Name", "Alice Smith")
    
    // Delete
    db.Delete(&foundUser)
}

When to Use GORM

Use when:

• Rapid prototyping

• CRUD-heavy applications

• Want automatic migrations

Don't use when:

• Complex queries

• Performance is critical

• Need full SQL control

My preference: Use database/sql or sqlx for production. More control, better performance.

---

Real Example: Complete User Repository

package repository

import (
    "context"
    "database/sql"
    "fmt"
    "time"
)

type User struct {
    ID        int
    Name      string
    Email     string
    CreatedAt time.Time
    UpdatedAt time.Time
}

type UserRepository struct {
    db *sql.DB
}

func NewUserRepository(db *sql.DB) *UserRepository {
    return &UserRepository{db: db}
}

func (r *UserRepository) Create(ctx context.Context, name, email string) (*User, error) {
    query := `
        INSERT INTO users (name, email, created_at, updated_at)
        VALUES ($1, $2, NOW(), NOW())
        RETURNING id, name, email, created_at, updated_at
    `
    
    var user User
    err := r.db.QueryRowContext(ctx, query, name, email).Scan(
        &user.ID,
        &user.Name,
        &user.Email,
        &user.CreatedAt,
        &user.UpdatedAt,
    )
    
    return &user, err
}

func (r *UserRepository) GetByID(ctx context.Context, id int) (*User, error) {
    query := `
        SELECT id, name, email, created_at, updated_at
        FROM users
        WHERE id = $1
    `
    
    var user User
    err := r.db.QueryRowContext(ctx, query, id).Scan(
        &user.ID,
        &user.Name,
        &user.Email,
        &user.CreatedAt,
        &user.UpdatedAt,
    )
    
    if err == sql.ErrNoRows {
        return nil, fmt.Errorf("user not found")
    }
    
    return &user, err
}

func (r *UserRepository) List(ctx context.Context, limit, offset int) ([]*User, error) {
    query := `
        SELECT id, name, email, created_at, updated_at
        FROM users
        ORDER BY id
        LIMIT $1 OFFSET $2
    `
    
    rows, err := r.db.QueryContext(ctx, query, limit, offset)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    
    var users []*User
    for rows.Next() {
        var user User
        err := rows.Scan(
            &user.ID,
            &user.Name,
            &user.Email,
            &user.CreatedAt,
            &user.UpdatedAt,
        )
        if err != nil {
            return nil, err
        }
        users = append(users, &user)
    }
    
    return users, rows.Err()
}

func (r *UserRepository) Update(ctx context.Context, id int, name, email string) error {
    query := `
        UPDATE users
        SET name = $1, email = $2, updated_at = NOW()
        WHERE id = $3
    `
    
    result, err := r.db.ExecContext(ctx, query, name, email, id)
    if err != nil {
        return err
    }
    
    rows, err := result.RowsAffected()
    if err != nil {
        return err
    }
    
    if rows == 0 {
        return fmt.Errorf("user not found")
    }
    
    return nil
}

func (r *UserRepository) Delete(ctx context.Context, id int) error {
    query := "DELETE FROM users WHERE id = $1"
    
    result, err := r.db.ExecContext(ctx, query, id)
    if err != nil {
        return err
    }
    
    rows, err := result.RowsAffected()
    if err != nil {
        return err
    }
    
    if rows == 0 {
        return fmt.Errorf("user not found")
    }
    
    return nil
}

// Usage
func main() {
    db := initDB()
    repo := NewUserRepository(db)
    
    ctx := context.Background()
    
    // Create user
    user, err := repo.Create(ctx, "Alice", "[email protected]")
    if err != nil {
        log.Fatal(err)
    }
    
    // Get user
    found, err := repo.GetByID(ctx, user.ID)
    if err != nil {
        log.Fatal(err)
    }
    
    fmt.Printf("Found user: %+v\n", found)
}

---

Your Challenge

Build a complete blog post repository:

type Post struct {
    ID        int
    Title     string
    Content   string
    AuthorID  int
    Published bool
    CreatedAt time.Time
    UpdatedAt time.Time
}

type PostRepository struct {
    db *sql.DB
}

// Implement these methods:
// - Create(ctx, title, content, authorID) (*Post, error)
// - GetByID(ctx, id) (*Post, error)
// - List(ctx, limit, offset) ([]*Post, error)
// - ListByAuthor(ctx, authorID) ([]*Post, error)
// - Update(ctx, id, title, content) error
// - Publish(ctx, id) error  // Set published = true
// - Delete(ctx, id) error

// Bonus: Add transaction support for:
// - CreateWithTags(ctx, post, tags []string) error

---

Key Takeaways

1. Connection pooling: *sql.DB is a pool, configure it properly

2. Always defer rows.Close(): Release connections back to pool

3. QueryRow vs Query: Single row vs multiple rows

4. Exec vs Query: Mutations vs reads

5. Prepared statements: Better performance for repeated queries

6. Transactions: Ensure atomicity with Begin/Commit/Rollback

7. NULL handling: Use sql.Null* types

8. Context: Always use context for cancellation/timeouts

---

What I Learned

That 2 AM database incident taught me that connection management is critical:

• Connection pooling reduced connections from 1,247 to 25

• Proper configuration dropped response time from 847ms to 23ms

• Prepared statements improved query performance by 40%

• Zero database incidents in 2 years since implementing pooling

Coming from Python's SQLAlchemy magic and Node's Sequelize complexity, Go's database/sql felt raw but powerful. The repository pattern has served 50,000 queries per second without breaking a sweat.

The time saved debugging connection issues? Countless hours.

---

Next: Best Practices and Code Organization

In the next article, we'll explore Go best practices and project structure. You'll learn the patterns that turned messy code into maintainable systems.