Go: What are database/sql best practices in Go?

1import (
2    "database/sql"
3    "time"
4)
5
6// 1. Connection pool configuration
7db, err := sql.Open("postgres", dsn)
8if err != nil {
9    log.Fatal(err)
10}
11db.SetMaxOpenConns(25)                // Maximum connections
12db.SetMaxIdleConns(10)                // Maximum idle connections
13db.SetConnMaxLifetime(5 * time.Minute) // Maximum lifetime
14db.SetConnMaxIdleTime(1 * time.Minute) // Maximum idle time
15
16// 2. Connection check
17ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
18defer cancel()
19if err := db.PingContext(ctx); err != nil {
20    log.Fatal(err)
21}
22
23// 3. QueryRow with context
24var user User
25err = db.QueryRowContext(ctx, "SELECT * FROM users WHERE id = $1", id).
26    Scan(&user.ID, &user.Name, &user.Email)
27if err != nil {
28    if err == sql.ErrNoRows {
29        return nil, ErrNotFound
30    }
31    return nil, fmt.Errorf("query user: %w", err)
32}
33
34// 4. Prepared statements for repeated queries
35prep, err := db.PrepareContext(ctx, "SELECT * FROM users WHERE email = $1")
36if err != nil {
37    log.Fatal(err)
38}
39defer prep.Close()
40
41// 5. Transactions
42tx, err := db.BeginTx(ctx, nil)
43if err != nil {
44    log.Fatal(err)
45}
46defer tx.Rollback()  // Rollback on error
47
48_, err = tx.ExecContext(ctx, "UPDATE accounts SET balance = balance - $1 WHERE id = $2", amount, fromID)
49if err != nil {
50    return err
51}
52
53_, err = tx.ExecContext(ctx, "UPDATE accounts SET balance = balance + $1 WHERE id = $2", amount, toID)
54if err != nil {
55    return err
56}
57
58return tx.Commit()
59
60// 6. Rows.Close() for multiple queries
61rows, err := db.QueryContext(ctx, "SELECT * FROM users")
62if err != nil {
63    return err
64}
65defer rows.Close()
66
67for rows.Next() {
68    var user User
69    if err := rows.Scan(&user.ID, &user.Name); err != nil {
70        return err
71    }
72    process(user)
73}
74return rows.Err()  // Check errors after iteration