The connection pool that isn't a connection
You are building a service that needs to read and write user data. In other languages, you grab a connection object, pass it to your handlers, and hope it survives concurrent requests. Go gives you database/sql. The package name is a historical artifact that trips up beginners. It does not represent a single TCP connection to your database. It is a thread-safe connection pool manager. The actual protocol speaking happens through a driver you register separately. This split is intentional. The standard library defines the contract, and the community ships the implementations.
Think of *sql.DB as a front desk at a busy hotel. Guests arrive and ask for a room. The front desk hands them a key from a rack. When the guest checks out, they return the key. The front desk never leaves the lobby. It just manages the inventory. If the rack is empty, the front desk calls the maintenance team to build a new room, up to a configured limit. Your code talks to the front desk, not the rooms.
How the interface actually works
Here is the minimal pattern to open a pool, verify it works, and fetch a single row.
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/lib/pq" // registers the postgres driver under the name "postgres"
)
func main() {
// creates the pool manager; does not open a TCP connection yet
db, err := sql.Open("postgres", "host=localhost user=postgres password=secret dbname=mydb sslmode=disable")
if err != nil {
log.Fatal(err)
}
defer db.Close() // releases all idle connections when the program exits
// borrows a connection, sends a health check, and returns it to the pool
if err := db.Ping(); err != nil {
log.Fatal(err)
}
// executes the query on a pooled connection and returns a single row
var id int
var name string
err = db.QueryRow("SELECT id, name FROM users WHERE id = $1", 1).Scan(&id, &name)
if err != nil {
log.Fatal(err)
}
fmt.Printf("Found: %d %s\n", id, name)
}
The blank import _ "github.com/lib/pq" is a Go convention. It forces the package to load so its init function runs. That init function calls sql.Register, which maps the string "postgres" to the driver implementation. You never instantiate the driver directly. You only reference it by name. The underscore tells the compiler you intentionally ignore the package's exported symbols. You only want the side effect of registration.
When sql.Open runs, it validates the driver name and stores the connection string. It does not touch the network. The first actual database call, like Ping or QueryRow, triggers the pool to borrow or create a connection. QueryRow returns a *sql.Row that holds the result set. Calling Scan reads the columns into your variables. The connection returns to the pool automatically after Scan finishes.
The compiler rejects the program with cannot use id (type int) as *int in argument if you forget the address-of operator & in Scan. Scan needs pointers so it can write the database values into your memory. Pass a value instead of a pointer and the compiler stops you before runtime.
*sql.DB is safe for concurrent use. Create it once, typically at startup, and pass the same instance to every handler or worker. Do not open and close it per request. The pool handles concurrency for you.
Connection pooling is not optional. Reuse the *sql.DB instance or your application will exhaust file descriptors.
Scanning results and handling database nulls
Databases store nulls. Go does not. The database/sql package bridges this gap with special types like sql.NullInt64 and sql.NullString. These types wrap a value and a boolean flag that indicates whether the database returned a null.
Here is how you scan a nullable column safely.
// GetUserWithNullableEmail fetches a user where the email field might be null
func GetUserWithNullableEmail(db *sql.DB, id int) (User, error) {
var u User
// uses sql.NullString to capture database nulls without panicking
var email sql.NullString
err := db.QueryRow("SELECT id, name, email FROM users WHERE id = $1", id).Scan(&u.ID, &u.Name, &email)
if err != nil {
return User{}, err
}
// checks the Valid flag before accessing the underlying string value
if email.Valid {
u.Email = email.String
}
return u, nil
}
The Valid field prevents you from dereferencing a null value. If the database returns null, Valid is false and String is empty. You can also implement the sql.Scanner interface on your own types to handle custom parsing. The interface requires a single method: Scan(value interface{}) error. The driver calls it automatically during Scan.
Go conventions favor explicit struct fields over magic reflection. Define your types clearly. Implement Scanner only when the conversion logic is complex enough to justify the interface.
Real-world usage: context and transactions
Production code rarely runs a single query in isolation. You need cancellation support, deadlines, and atomic writes. Go solves this by threading context.Context through the query methods and providing transaction objects that wrap the pool.
Here is how you fetch a record with a timeout.
// FetchUser retrieves a user by ID with a cancellation deadline
func FetchUser(ctx context.Context, db *sql.DB, id int) (User, error) {
var u User
// passes the context to the driver so it can cancel long-running queries
err := db.QueryRowContext(ctx, "SELECT id, name FROM users WHERE id = $1", id).Scan(&u.ID, &u.Name)
if err != nil {
return User{}, err
}
return u, nil
}
The context.Context parameter always goes first by convention. The driver uses it to monitor deadlines. If the context expires, the driver interrupts the query and returns an error. Your application never hangs waiting for a slow database. Functions that accept a context should respect cancellation and deadlines. Pass it down to every long-lived call site.
Transactions require a slightly different flow. You start a transaction, execute statements on the transaction object, and commit or roll back. The pattern uses a deferred rollback that gets overwritten on success.
// TransferCredits moves funds between two accounts atomically
func TransferCredits(ctx context.Context, db *sql.DB, fromID, toID int, amount float64) error {
// begins a transaction that borrows a dedicated connection
tx, err := db.BeginTx(ctx, nil)
if err != nil {
return err
}
// ensures the transaction rolls back if the function returns early
defer tx.Rollback()
// executes the debit on the transaction's isolated connection
_, err = tx.ExecContext(ctx, "UPDATE accounts SET balance = balance - $1 WHERE id = $2", amount, fromID)
if err != nil {
return err
}
// executes the credit on the same connection to maintain consistency
_, err = tx.ExecContext(ctx, "UPDATE accounts SET balance = balance + $1 WHERE id = $2", amount, toID)
if err != nil {
return err
}
// commits the changes and releases the connection back to the pool
return tx.Commit()
}
The defer tx.Rollback() pattern is standard Go. If Commit succeeds, Rollback becomes a no-op. If any step fails, the deferred call cleans up the transaction. You never leave a transaction hanging.
The database/sql package expects you to handle errors explicitly. The community accepts the if err != nil boilerplate because it forces you to acknowledge failure paths. Wrap errors with fmt.Errorf("fetch user: %w", err) to preserve the stack trace and add context.
Error handling is explicit by design. Write the checks or the bug hides in production.
Connection lifecycle and pool tuning
The default pool settings work for small services. Under load, you need to adjust three parameters. SetMaxOpenConns limits the total number of connections to the database. SetMaxIdleConns limits how many connections sit unused in the pool. SetConnMaxLifetime forces connections to close and reopen after a duration, which prevents stale connections from being killed by network timeouts or database restarts.
Here is how you configure the pool at startup.
// ConfigureDB sets up the connection pool with production-safe limits
func ConfigureDB(db *sql.DB) {
// caps total connections to prevent overwhelming the database server
db.SetMaxOpenConns(25)
// keeps idle connections alive to avoid handshake latency on spikes
db.SetMaxIdleConns(10)
// rotates connections every 30 minutes to bypass firewall idle timeouts
db.SetConnMaxLifetime(30 * time.Minute)
}
Setting MaxOpenConns too low creates a bottleneck. Goroutines block waiting for an available connection. Setting it too high overwhelms the database server with too many concurrent TCP sockets. Match it to your database configuration and your goroutine count. The MaxIdleConns setting should usually match or slightly exceed your expected baseline concurrency. Idle connections stay open and ready, saving the cost of TLS handshakes and authentication.
The pool tracks connection health automatically. If a borrowed connection dies mid-query, database/sql marks it as bad, discards it, and retries the query on a fresh connection. You do not need to catch network errors and retry manually. The retry happens transparently, up to one attempt.
Pool management is a shared responsibility. Tune the limits or the database becomes the bottleneck.
Where things go wrong
The API is simple, but the runtime behavior catches developers off guard. The most common issue is leaking row iterators. When you call db.Query (plural), it returns a *sql.Rows that must be closed. Forgetting to close it keeps a connection pinned to the pool until the garbage collector runs. In high-throughput services, that drains the pool and causes timeouts. Always call defer rows.Close() immediately after Query.
Driver placeholders differ across databases. PostgreSQL uses $1, $2. MySQL uses ?. SQLite uses ? or $1. The database/sql package does not translate them. You write the placeholder syntax your driver expects. Mixing them up triggers a runtime error from the driver, not the Go compiler.
The compiler rejects the program with rows.Close() called twice if you accidentally defer close on a row that already closed. The runtime panics with sql: Rows are closed if you try to iterate after closing. Check the error before calling Next().
Another trap is passing *sql.DB to a function that expects a single connection. The package does not expose a way to grab a raw connection for manual management. You work with the pool. If you need connection-level state, use session variables or temporary tables, not manual connection checkout.
Receiver naming follows Go conventions. Use one or two letters matching the type: (db *sql.DB), not (this *sql.DB) or (self *sql.DB). The community reads code faster when receivers are short and predictable.
The worst goroutine bug is the one that never logs. Always attach a context with a deadline to database calls. Watch the pool metrics. Close your rows.
When to reach for database/sql
Go gives you several ways to talk to databases. Pick the right tool for the workload.
Use database/sql when you need full control over queries, transactions, and connection pooling without the overhead of an abstraction layer. Use an ORM like GORM or Ent when your domain model maps cleanly to tables and you want rapid CRUD generation. Use a raw driver directly when you need driver-specific features like copy commands or binary protocol access that the standard interface does not expose. Use a query builder like Squirrel when you want programmatic SQL generation with type safety but still need to manage connections manually.
The standard library interface is the baseline. Build abstractions on top of it only when the boilerplate actually hurts.