go-database

Go Database Patterns

Database access is where most Go services spend their complexity budget. Get connection management, transactions, and query patterns right.

Detailed reference material, loaded on demand:

• references/query-patterns.md — full query/scan/rows patterns, null handling, N+1 avoidance, connection-leak examples.
• references/tooling.md — repository pattern implementation, sqlc annotated queries, migration tooling and rules.

Read a reference file only when the summary below is not enough.

1. Connection Management

Configure the pool explicitly — the default is unbounded connections:

func OpenDB(dsn string) (*sql.DB, error) {
    db, err := sql.Open("postgres", dsn)
    if err != nil {
        return nil, fmt.Errorf("open db: %w", err)
    }

    db.SetMaxOpenConns(25)
    db.SetMaxIdleConns(10)
    db.SetConnMaxLifetime(5 * time.Minute)
    db.SetConnMaxIdleTime(1 * time.Minute)

    if err := db.PingContext(context.Background()); err != nil {
        return nil, fmt.Errorf("ping db: %w", err)
    }

    return db, nil
}

Setting	Guideline
MaxOpenConns	Match your DB's max connections / number of app instances
MaxIdleConns	40-50% of MaxOpenConns
ConnMaxLifetime	5-10 minutes (prevents stale connections behind load balancers)
ConnMaxIdleTime	1-2 minutes

2. Query Rules

1. Parameterized queries only — string concatenation into SQL is an injection vulnerability, no exceptions.
2. Always pass context — use the *Context variants (QueryContext, QueryRowContext, ExecContext) so queries respect cancellation and timeouts.
3. defer rows.Close() immediately after the error check, and check rows.Err() after the iteration loop.
4. Handle sql.ErrNoRows explicitly with errors.Is, mapping it to a domain error like ErrUserNotFound.

var user User
err := db.QueryRowContext(ctx,
    "SELECT id, name, email FROM users WHERE id = $1", id,
).Scan(&user.ID, &user.Name, &user.Email)

if errors.Is(err, sql.ErrNoRows) {
    return nil, ErrUserNotFound
}
if err != nil {
    return nil, fmt.Errorf("get user %s: %w", id, err)
}