Backend Development

Database SQL

Use database/sql safely with connection pools, contexts, parameters, row cleanup, and explicit mappings.

What you will learn

By the end of this lesson, you will be able to:

  • explain why *sql.DB is a pool handle rather than one connection;
  • open and verify a database dependency;
  • execute parameterized statements and scan rows;
  • close rows and inspect iteration errors;
  • map storage failures into useful application errors.

Mental model: sql.DB manages a pool

*sql.DB is a concurrency-safe handle that manages a pool of database connections. Opening the handle may not establish a connection immediately.

Create it once during application wiring, configure it, share it, and close it during process shutdown.

database, err := sql.Open(driverName, dataSourceName)
if err != nil {
    return nil, fmt.Errorf("open database handle: %w", err)
}

database.SetMaxOpenConns(20)
database.SetMaxIdleConns(10)
database.SetConnMaxLifetime(30 * time.Minute)
database.SetConnMaxIdleTime(5 * time.Minute)

Pool settings depend on database capacity, application replicas, workload, and driver behaviour. More connections are not automatically faster.

Verify startup connectivity

ctx, cancel := context.WithTimeout(parent, 5*time.Second)
defer cancel()

if err := database.PingContext(ctx); err != nil {
    database.Close()
    return nil, fmt.Errorf("ping database: %w", err)
}

The program must import and register an appropriate database driver. Drivers are third-party dependencies; choose and configure one from its maintained documentation.

Parameterize every value

result, err := database.ExecContext(
    ctx,
    `UPDATE lessons SET title = ? WHERE id = ?`,
    title,
    id,
)

The placeholder syntax varies by database and driver. Some use ?; PostgreSQL drivers commonly use $1, $2, and so on.

Never concatenate untrusted values into SQL:

// Unsafe: input becomes SQL syntax.
query := "SELECT id FROM users WHERE email = '" + email + "'"

Parameters separate SQL code from data. Identifiers such as table names usually cannot be parameterized; choose them from a fixed allow-list rather than accepting arbitrary client input.

Query one row

func (r *LessonRepository) Find(ctx context.Context, id string) (Lesson, error) {
    const query = `
        SELECT id, title, published, created_at
        FROM lessons
        WHERE id = ?`

    var lesson Lesson
    err := r.database.QueryRowContext(ctx, query, id).Scan(
        &lesson.ID,
        &lesson.Title,
        &lesson.Published,
        &lesson.CreatedAt,
    )
    if errors.Is(err, sql.ErrNoRows) {
        return Lesson{}, ErrLessonNotFound
    }
    if err != nil {
        return Lesson{}, fmt.Errorf("find lesson %q: %w", id, err)
    }
    return lesson, nil
}

QueryRowContext defers query errors until Scan. Handle sql.ErrNoRows separately when absence has domain meaning.

Query several rows

rows, err := database.QueryContext(ctx, query, published)
if err != nil {
    return nil, fmt.Errorf("query lessons: %w", err)
}
defer rows.Close()

lessons := make([]Lesson, 0)
for rows.Next() {
    var lesson Lesson
    if err := rows.Scan(&lesson.ID, &lesson.Title); err != nil {
        return nil, fmt.Errorf("scan lesson: %w", err)
    }
    lessons = append(lessons, lesson)
}
if err := rows.Err(); err != nil {
    return nil, fmt.Errorf("iterate lessons: %w", err)
}
return lessons, nil

Closing rows releases the connection back to the pool. Always check rows.Err() because iteration can stop due to an error after QueryContext succeeds.

Nullable database values

If a column can be SQL NULL, scan into a suitable nullable representation such as sql.NullString, a pointer, or a driver-supported nullable type. Convert that storage representation explicitly into the domain model.

Do not make every domain field nullable simply because a legacy schema permits NULL.

Context and timeouts

Pass the request or job context through repository methods. A driver and database must cooperate for cancellation to interrupt work promptly, so also configure database-side statement timeouts where appropriate.

Do not create a fresh background context inside the repository; it disconnects the query from caller cancellation.

Repository boundaries

Repositories should translate expected storage outcomes into domain-level errors while preserving unexpected errors for diagnostics.

Keep SQL details, driver constraint codes, and row shapes out of HTTP handlers. Keep HTTP status codes out of repositories.

Common mistakes

  • Calling sql.Open per request: pools multiply and connections churn.
  • Assuming sql.Open proves connectivity: use a bounded PingContext when startup requires it.
  • Building SQL with client strings: this creates SQL injection risk.
  • Forgetting rows.Close or rows.Err: connections leak or late failures disappear.
  • Using SELECT *: column-order changes can break scans and fetch unnecessary data.
  • Tuning the pool without database-wide limits: replicas can collectively exhaust the server.

Practice

Implement repository methods to create, find, and list lessons. Use contexts, parameterized values, explicit columns, pagination, sql.ErrNoRows mapping, and complete row cleanup.

Summary

  • *sql.DB is a shared connection-pool handle.
  • Parameterize data values and allow-list dynamic identifiers.
  • Use context-aware operations and explicit query timeouts.
  • Close row iterators and inspect rows.Err().
  • Translate expected persistence outcomes at the repository boundary.

Official reference: Accessing relational databases.

Ready to wrap up?

Mark this complete when you can explain the main idea and finish the practice task.

Copyright © 2026