The Wrong Way of Querying: A Go Story

Context

I was handed an incomplete internal tool built with Go + SQLite. I ended up taking it all the way to production and later migrating the data layer to PostgreSQL because SQLite couldn't reliably handle concurrent reads/writes for our workload (we even saw corruption of the "truth source").

This post is about a production debugging lesson I learned the hard way—something a modern LLM might catch quickly, but real systems still have a way of teaching you humility.

The Symptom

Everything looked fine for the first few days. Then on day 3 or 4:

• Every operation started failing
• Postgres started complaining: too many clients
• Even after tuning MaxOpenConnections, connections were getting consumed way too easily

That's when I realized: this wasn't "Postgres being strict". This was my code leaking/holding connections.

Root Cause #1: Nested query while iterating Rows()

The Mistake

Running additional ORM queries inside a for rows.Next() loop.

Why it's bad

• db.Raw(...).Rows() typically holds a DB connection until you close rows
• If you execute other queries inside that loop, the "outer" query's connection is still busy
• Under load (many rows), you open more connections while the old one stays pinned → pool exhaustion → too many clients

Bad: Nested query pattern

// BAD: outer rows stays open while inner queries run.
// This can multiply concurrent connections.
func BadNestedQueries(db *gorm.DB) error {
    rows, err := db.Raw(`SELECT id FROM users`).Rows()
    if err != nil {
        return err
    }
    defer rows.Close()

    for rows.Next() {
        var userID int
        if err := rows.Scan(&userID); err != nil {
            return err
        }

        // Inner query while outer rows is still open (nested query).
        var orders []Order
        if err := db.Where("user_id = ?", userID).Find(&orders).Error; err != nil {
            return err
        }

        // ...more work...
    }
    return nil
}

Good: Two-phase approach (read → close → query/process)

// GOOD: read IDs first, close rows, then do follow-up queries.
// Outer connection is released early.
func GoodTwoPhase(db *gorm.DB) error {
    rows, err := db.Raw(`SELECT id FROM users`).Rows()
    if err != nil {
        return err
    }

    var userIDs []int
    for rows.Next() {
        var id int
        if err := rows.Scan(&id); err != nil {
            _ = rows.Close()
            return err
        }
        userIDs = append(userIDs, id)
    }
    if err := rows.Close(); err != nil {
        return err
    }

    // Now it's safe to run additional queries.
    for _, userID := range userIDs {
        var orders []Order
        if err := db.Where("user_id = ?", userID).Find(&orders).Error; err != nil {
            return err
        }
        // ...process orders...
    }
    return nil
}

Root Cause #2: Forgetting rows.Close()

The Mistake

Not closing rows at all.

Why it's bad

• Rows() returns a cursor that often keeps a connection checked out
• If you don't close it, the connection may not return to the pool promptly (or ever)
• Over time: slow leak → pool exhaustion → outage

Bad: Leaks cursor/connection

// BAD: rows is never closed.
func BadMissingClose(db *gorm.DB) error {
    rows, err := db.Raw(`SELECT id FROM users`).Rows()
    if err != nil {
        return err
    }

    for rows.Next() {
        var id int
        if err := rows.Scan(&id); err != nil {
            return err
        }
    }
    return nil // rows.Close() never called
}

Good: Always close

// GOOD: always close rows.
// Use defer + handle early return paths.
func GoodAlwaysClose(db *gorm.DB) error {
    rows, err := db.Raw(`SELECT id FROM users`).Rows()
    if err != nil {
        return err
    }
    defer rows.Close()

    for rows.Next() {
        var id int
        if err := rows.Scan(&id); err != nil {
            return err
        }
    }
    return rows.Err()
}

Practical Checklist

What I now do by default:

• ✓Put defer rows.Close() right after Rows()
• ✓Don't do nested queries inside for rows.Next()
• ✓Prefer "two-phase": fetch IDs/keys → close rows → do follow-up work
• ✓Return rows.Err() after iteration
• ✓Keep DB work inside loops minimal; batch when possible