The database handle is a pool, not a connection
You write a Go service that responds in milliseconds on your laptop. You deploy it to production, run a load test, and the response times spike. The CPU usage stays flat. The disk is quiet. The database server shows a wall of waiting connections. The bottleneck isn't your business logic. It's how you're managing database connections.
Go's database/sql package provides a clean interface, but it hides a critical detail. The *sql.DB value is not a single connection. It is a connection pool. If you treat it like a single connection, your application will choke under load. If you tune the pool correctly, the database becomes a fast, reliable backing store that scales with your concurrency.
Connection pooling in plain words
Think of a database connection like a phone line to a busy call center. Dialing takes time. The network routes the call, the server authenticates your credentials, and a handshake establishes the session. If you hang up after every question and dial again for the next question, you spend more time on hold than talking.
Connection pooling keeps a set of lines open. When you need to ask a question, you grab an idle line from the pool instead of dialing. When you're done, you put the line back for the next caller. database/sql manages this pool automatically, but you must configure the limits. The pool needs to know how many lines to keep warm and how many to allow during a spike.
Initializing the pool
Here's the standard pattern for creating a database handle. sql.Open validates the data source name and returns a pool. You must configure the pool limits before using it, or you inherit defaults that may not match your workload.
package main
import (
"database/sql"
"time"
)
// NewDB opens a connection pool and configures limits to prevent resource exhaustion.
func NewDB(dsn string) (*sql.DB, error) {
// sql.Open validates the DSN but does not open a connection immediately.
// It returns a pool handle that is safe for concurrent use by multiple goroutines.
db, err := sql.Open("postgres", dsn)
if err != nil {
return nil, err
}
// Cap total connections to match the database server's capacity.
// Too high causes the DB to reject connections; too low causes goroutine thrashing.
db.SetMaxOpenConns(25)
// Keep idle connections alive so the next request doesn't pay the handshake cost.
// Match this to MaxOpenConns for steady workloads to avoid opening and closing churn.
db.SetMaxIdleConns(25)
// Rotate connections periodically to handle network changes and server restarts.
// 5 minutes is a safe default; adjust based on your infrastructure stability.
db.SetConnMaxLifetime(5 * time.Minute)
// Verify the pool works before returning.
// This forces the first connection and catches configuration errors early.
if err := db.Ping(); err != nil {
return nil, err
}
return db, nil
}
The pool settings are a negotiation between your application's concurrency and the database's capacity. MaxOpenConns sets the hard limit. If your app tries to use more connections than this, goroutines block until a connection is released. MaxIdleConns sets the minimum number of connections kept open when the app is quiet. If MaxIdleConns is lower than MaxOpenConns, the pool will close connections when they sit idle, forcing new connections to open when load returns. For steady workloads, setting MaxIdleConns equal to MaxOpenConns eliminates this churn.
SetConnMaxLifetime rotates connections. Network infrastructure like load balancers may drop connections after a certain time. Database servers may restart. If your app holds a connection for hours, it might die silently. Rotating connections ensures you always have fresh links to the database.
Tune the pool, not the code.
Querying with safety
Real-world code involves reading data, handling missing rows, and respecting timeouts. The *sql.DB handle is safe for concurrent use. Create it once and pass it to your handlers. Never open and close the handle per request. That defeats the pool.
package main
import (
"context"
"database/sql"
)
// User represents a row in the users table.
type User struct {
Name string
Email string
}
// GetUser retrieves a user by ID and handles the case where the row doesn't exist.
// Context is always the first parameter to allow cancellation and deadlines.
func GetUser(ctx context.Context, db *sql.DB, id int) (*User, error) {
// Use QueryContext to respect cancellation and deadlines.
// The driver will abort the query if the context is cancelled.
rows, err := db.QueryContext(ctx, "SELECT name, email FROM users WHERE id = $1", id)
if err != nil {
return nil, err
}
// Always close rows to release the connection back to the pool.
// Forgetting this leaks the connection and starves the pool.
defer rows.Close()
if !rows.Next() {
// Check for errors that occurred during iteration.
if err := rows.Err(); err != nil {
return nil, err
}
// Return a sentinel error when no row is found.
return nil, sql.ErrNoRows
}
var u User
if err := rows.Scan(&u.Name, &u.Email); err != nil {
return nil, err
}
return &u, nil
}
The rows.Close() call is critical. When you call db.Query, the pool hands you a connection and keeps it borrowed until you close the rows. If you forget to close the rows, the connection stays stuck. The pool fills up, and new requests block waiting for a connection. The compiler won't catch this. You'll see your pool hit MaxOpenConns and requests start timing out. The runtime might surface sql: connection is already closed if the pool rotates a connection while you're still using it, or you'll just hit a deadlock where all goroutines wait for a connection that's trapped in a forgotten row scan.
Always close your rows. Leaked connections kill production.
Batching and prepared statements
Writing data efficiently requires reducing round-trips. The network is the slow part. Sending one query with ten rows is faster than sending ten queries with one row. Batching inserts groups multiple rows into a single statement.
package main
import (
"context"
"database/sql"
"fmt"
)
// InsertUsers demonstrates batching inserts to reduce round-trips.
// Context is always the first parameter to allow cancellation.
func InsertUsers(ctx context.Context, db *sql.DB, users []User) error {
// Build a batch query with placeholders for multiple rows.
// This reduces network round-trips from N to 1.
query := "INSERT INTO users (name, email) VALUES "
args := make([]interface{}, 0, len(users)*2)
for i, u := range users {
if i > 0 {
query += ", "
}
// Use positional placeholders.
// Each row adds two arguments to the slice.
query += fmt.Sprintf("($%d, $%d)", i*2+1, i*2+2)
args = append(args, u.Name, u.Email)
}
// Execute the batch with a context to respect deadlines.
// The driver sends one packet to the database.
_, err := db.ExecContext(ctx, query, args...)
return err
}
Prepared statements help when you run the same query structure repeatedly with different parameters. db.Prepare sends the query to the server once. The server parses the query, creates an execution plan, and caches it. Subsequent executions skip the parse step. For high-throughput services, this saves CPU on the database side.
Use db.Stmt to cache a prepared statement handle across calls. This avoids re-preparing the statement on every request. The *sql.Stmt is safe for concurrent use. Create it once and reuse it.
Pitfalls and errors
Database code in Go has specific failure modes. Understanding them prevents hard-to-debug production issues.
If you pass a context that expires, the driver cancels the operation. You'll get context deadline exceeded or context canceled. This is expected behavior. Always pass a context with a deadline to database calls. Long-running queries should have a timeout so they don't hold connections indefinitely.
If you use a connection after it's been closed by the pool, you get sql: connection is already closed. This happens when you hold a reference to a *sql.Rows or *sql.Stmt across a pool rotation. The pool closes old connections based on ConnMaxLifetime. If your code keeps a row iterator alive longer than the lifetime, the connection dies. Keep operations short and close resources promptly.
Error handling in Go is verbose by design. The community accepts the boilerplate because it makes the unhappy path visible. Check every error. Wrap errors with fmt.Errorf("operation failed: %w", err) to preserve the chain. Don't swallow errors. If a query fails, the caller needs to know.
Context is plumbing. Run it through every long-lived call site.
When to use what
Database access patterns vary. Choose the right tool based on your workload.
Use sql.Open with tuned pool settings for almost all database access. The pool handles concurrency and reuse automatically.
Use db.Prepare when you execute the same query structure many times with different parameters. The server caches the query plan, saving parse time.
Use batch inserts with multiple value lists when writing many rows. One round-trip beats hundreds of individual inserts.
Use db.Stmt to cache a prepared statement across calls. This avoids re-preparing the statement on every request.
Use raw SQL strings for simple one-off queries. The overhead of preparation isn't worth it for queries that run once.
Use an ORM or query builder when your schema is complex and you need type safety across the application. These tools generate the SQL for you, but you still need to understand the pool underneath.
Use transactions with db.BeginTx when multiple writes must succeed or fail together. Transactions borrow a connection for the duration. Keep transactions short to avoid tying up pool resources.
Conventions and style
Go has strong conventions around database code. Following them makes your code familiar to other Go developers.
context.Context always goes as the first parameter. Functions that take a context should respect cancellation and deadlines. If the context is cancelled, the database operation should stop. This convention allows callers to control timeouts uniformly.
The receiver name for methods is usually one or two letters matching the type. Use (db *Database) Query(...), not (this *Database). This keeps method signatures clean.
Public names start with a capital letter. Private names start lowercase. Export only what other packages need. Keep internal helpers unexported.
Don't pass a *sql.DB by pointer if you already have a pointer. *sql.DB is a pointer type. Passing it by value is cheap and safe. The receiver is already a pointer.
Trust the pool. It's designed for concurrency.