Right, let’s settle this. You’ve got your sql.DB object, and you’ve probably seen sql.Conn lurking in the docs. You might be thinking, “Why do I need two different things to talk to the database? Isn’t one enough?” The designers, in their infinite wisdom, decided no. And for once, I’m with them. It’s not just redundancy; these two serve fundamentally different masters.

Think of sql.DB not as a single database connection, but as the manager of a whole pool of them. It’s your highly efficient, slightly cynical office manager. You, the developer, hand a task (a query) to the manager (sql.DB), and it figures out the best idle worker (a connection) in the pool to handle it. When the worker is done, it goes back to the pool to wait for the next job. This is phenomenally efficient because creating a new TCP connection and database session from scratch is brutally expensive. The pool avoids that overhead.

But sometimes, you need to talk to one specific worker for a whole series of tasks. You need a dedicated line. That’s what sql.Conn is for: a single, dedicated database connection, checked out from the pool for you to use exclusively.

When to Steal a Connection from the Pool

You don’t grab a sql.Conn for your everyday SELECT * FROM users. That’s like renting a dedicated fiber line to check your email. You use it for specialized operations that require state to be maintained on a single connection.

The classic, non-negotiable use case is transactions. You simply cannot have a transaction span multiple connections; the entire point is that you’re doing a set of operations in an isolated session. The sql.DB.BeginTx() method handles this for you under the hood—it grabs a connection, starts the transaction, and ensures all queries in that Tx object use that same connection.

Another big one is using temporary tables. These little scamps are session-specific. If you create one on connection A, it will vanish the moment that connection is returned to the pool and reset, and you certainly can’t see it from connection B.

// This is a recipe for pain and confusion.
db.Exec("CREATE TEMPORARY TABLE my_temp_table (id SERIAL, data TEXT)") // Uses Conn A from pool
rows, err := db.Query("SELECT * FROM my_temp_table")                   // Probably uses Conn B from pool -> ERROR!

The correct way, using a dedicated connection:

// Get a single connection from the pool
conn, err := db.Conn(ctx)
if err != nil {
    log.Fatal(err)
}
defer conn.Close() // Seriously, don't forget this. You're not leasing a connection, you're stealing it.

// Now all operations on `conn` use the same underlying connection
_, err = conn.ExecContext(ctx, "CREATE TEMPORARY TABLE my_temp_table (id SERIAL, data TEXT)")
if err != nil {
    log.Fatal(err)
}

// This is guaranteed to work because we're still on the same connection
rows, err := conn.QueryContext(ctx, "SELECT * FROM my_temp_table")
if err != nil {
    log.Fatal(err)
}
defer rows.Close()

The Pitfalls of Hoarding Connections

Here’s the catch, and it’s a big one: when you call db.Conn(), you’re literally taking a connection out of the pool. It’s gone. No one else can use it until you call Close() on it. If you do this and forget to close it (or your context gets cancelled), you’ve just sprung a leak. Your pool slowly shrinks, application performance degrades, and eventually new requests will hang waiting for a connection that’s never coming back.

This is why you always use defer conn.Close() immediately after checking the error, and you always pass a context with a timeout to every operation. If your operation gets cancelled, the defer will still run and release the connection back to the pool. The sql package is good, but it’s not clairvoyant. It can’t force you to not be careless.

So Why Isn’t Everything a sql.Conn?

Performance, pure and simple. A connection pool allows thousands of lightweight goroutines to share a much smaller number of expensive connections. Most queries are atomic, independent operations. Holding a connection hostage for a single query is wildly inefficient. The pool allows the database to handle a high level of concurrency without drowning in a sea of simultaneous connections.

Use the pool (sql.DB) for 99% of your work. It’s safe, efficient, and managed for you. Only reach for the individual connection (sql.Conn) when you have a specific, technical requirement that forces your hand. And when you do, treat it like a borrowed sports car: return it in pristine condition, and on time.