Databases in Go

Almost every backend service talks to a database, and Go’s approach is characteristically low-level and explicit: the standard database/sql package gives you a thin, driver-agnostic API over SQL, and expects you to understand what is happening underneath. There is one concept that, if you get it wrong, causes the most common and most painful Go-database production incident — so we lead with it.

A *sql.DB Is a Pool, Not a Connection

When you call sql.Open("pgx", dsn), you do not get a database connection. You get a *sql.DB, which is a pool that manages a set of connections for you — opening them lazily, handing them out when you run a query, taking them back when you are done, and reusing them. Three consequences follow, and they are the heart of using Go with databases correctly:

• Open it once, share it everywhere. A *sql.DB is safe for concurrent use by many goroutines. You create one at startup and pass that single handle to everything — all your request-handler goroutines (Lesson 8) share it, and the pool sorts out who gets which underlying connection. Opening a new *sql.DB per request is the classic catastrophic mistake: each one spins up its own pool, and you exhaust the database’s connection limit in seconds under load. One *sql.DB for the whole process, full stop.

• Size the pool deliberately. The pool has knobs, and the defaults are not always right for production:

  • SetMaxOpenConns(n) — the hard cap on open connections. This is the critical one: your database server allows a finite number of connections (PostgreSQL commonly ~100). If you run N instances (pods) each with a pool of size M, then N × M must stay under the server’s limit — otherwise your own fleet DDoSes the database. Size M accordingly.
  • SetMaxIdleConns(n) — how many idle connections to keep ready (avoids the cost of reopening under steady load).
  • SetConnMaxLifetime(d) — recycle a connection after a while. Important so connections don’t live forever (they can go stale, and after a database failover you want old connections retired and reopened to the new primary).

• The real limit is the database, not Go. Goroutines are cheap (Lesson 4), but database connections are not — they are the scarce resource. This is exactly why the worker-pool pattern (Lesson 6) exists: you may have 10,000 goroutines handling requests, but they funnel through a pool of maybe 20 connections, and the pool makes them wait their turn. The pool is your concurrency limiter against the database.

Querying Correctly

With the pool understood, the query API is small but has a few rules you must follow or you will leak connections.

Always use the Context variants. Every query method has a ...Context form — QueryContext, QueryRowContext, ExecContext — and you should use them exclusively, passing the request’s context (Lesson 6):

row := db.QueryRowContext(ctx, "SELECT name, email FROM users WHERE id = $1", id)
var u User
if err := row.Scan(&u.Name, &u.Email); err != nil {
    if errors.Is(err, sql.ErrNoRows) {
        return User{}, ErrNotFound       // no row is a normal case, not a crash
    }
    return User{}, fmt.Errorf("get user %d: %w", id, err)
}

Passing ctx means a cancelled request (client gone, or timeout fired) releases its database connection promptly instead of holding it while running a query nobody wants. The plain db.Query ignores cancellation — don’t use it.

Always close rows, and check the error after the loop. For multi-row queries, the returned *sql.Rows holds a connection until you close it. Forget to close it and that connection never returns to the pool — do this a few thousand times and the pool is empty and every request hangs. The correct shape:

rows, err := db.QueryContext(ctx, "SELECT id, name FROM orders WHERE user_id = $1", uid)
if err != nil {
    return nil, err
}
defer rows.Close()                    // ALWAYS — returns the connection to the pool
var orders []Order
for rows.Next() {
    var o Order
    if err := rows.Scan(&o.ID, &o.Name); err != nil {
        return nil, err
    }
    orders = append(orders, o)
}
return orders, rows.Err()             // check for an error that ended the loop early

defer rows.Close() right after the query, and rows.Err() after the loop (because rows.Next() returning false can mean “done” or “error,” and only rows.Err() tells you which).

Two correctness essentials:

• sql.ErrNoRows is normal. A QueryRow that matches nothing returns this sentinel from Scan. It is an expected “not found,” not a failure — check it with errors.Is(err, sql.ErrNoRows) and map it to your own ErrNotFound / a 404, not a 500.
• Parameterise every query. Use placeholders ($1, $2 for PostgreSQL; ? for MySQL) and pass values as arguments — the driver handles them safely. Never build SQL by string-concatenating user input; that is SQL injection, the oldest and still one of the most damaging vulnerabilities. (The Security course covers why in depth; in Go the fix is simply: always parameterise.)

Transactions, Migrations, and the ORM Question

Transactions follow the same context-first pattern: tx, err := db.BeginTx(ctx, nil), run your statements on tx, then tx.Commit() — and crucially defer tx.Rollback() right after beginning, so any early return rolls back automatically (a rollback after a commit is a harmless no-op). This guarantees you never leave a transaction dangling on an error path.

Migrations — schema changes — are not part of database/sql, and you should manage them explicitly with a tool like golang-migrate or goose: versioned, ordered .sql files applied in sequence, checked into the repo. Run them as a deliberate step in your deploy, not implicitly at app startup where two instances starting at once could race.

Do you need an ORM? Usually not. Go culture leans toward less magic (Lesson 1), and the options reflect that:

• The standard database/sql with hand-written SQL is perfectly viable and maximally explicit.
• sqlx adds light conveniences (scanning rows straight into structs) over database/sql without hiding the SQL.
• sqlc generates type-safe Go code from your SQL queries at build time — you write SQL, it writes the Go. Very popular because you keep full control of the SQL and gain type safety.
• Full ORMs (like GORM) exist and some teams like them, but they bring the usual ORM costs — hidden queries, N+1 surprises, a query language on top of SQL — that run against Go’s explicit grain. Reach for one only if you specifically want it.

The Go default is: understand the pool, write clear parameterised SQL, use context and close your rows, and let a thin layer (or nothing) sit between you and the database. That explicitness is more typing than an ORM, and far fewer surprises in production.

Pitfalls to Avoid

1. Opening a *sql.DB per request. The single worst mistake — each opens its own pool and you exhaust the DB’s connection limit instantly. Open one at startup, share it across all goroutines.
2. Not sizing SetMaxOpenConns for your fleet. N instances × pool size can blow past the database’s max connections, and your own service takes the DB down. Set the pool so the total stays safely under the server limit.
3. Leaking connections by not closing rows. Forgetting defer rows.Close() holds a connection out of the pool permanently; enough leaks and every request hangs. Close rows always, and check rows.Err() after the loop.
4. Using non-context query methods. db.Query ignores cancellation, so a timed-out or abandoned request keeps its connection busy. Always use QueryContext/ExecContext/QueryRowContext with the request context (Lesson 6).
5. String-concatenating SQL. Building queries with fmt.Sprintf and user input is SQL injection. Always use placeholders ($1/?) and pass arguments — the driver escapes them safely.
6. Treating sql.ErrNoRows as a failure. Returning a 500 when a lookup simply found nothing. It’s a normal “not found” — check errors.Is(err, sql.ErrNoRows) and map it to your ErrNotFound/404.
7. Running migrations implicitly at startup. Two instances booting together can race the schema change. Make migrations an explicit, ordered deploy step with a dedicated tool.

Quick Recap

• A *sql.DB is a connection POOL, not a connection. Open it once at startup and share the single handle across all request goroutines (it’s concurrency-safe). Opening one per request exhausts the DB’s connection limit instantly.
• Size the pool: SetMaxOpenConns so that instances × pool size stays under the server’s max; set SetMaxIdleConns and SetConnMaxLifetime (recycle connections, survive failovers). The DB connections — not goroutines — are the scarce resource (hence worker pools, Lesson 6).
• Query correctly: always the ...Context variants (cancellation frees the connection); always defer rows.Close() and check rows.Err(); treat sql.ErrNoRows as a normal not-found; parameterise every query ($1/?) — never concatenate SQL.
• Transactions: BeginTx(ctx, ...) + defer tx.Rollback() + Commit(). Migrations: explicit, versioned, a deploy step. ORM: usually unnecessary — standard database/sql, sqlx, or sqlc fit Go’s explicit style; heavy ORMs run against the grain.

Next lesson: taking this service to production — graceful shutdown, config, structured logging, and one tiny static container.