Database SQL
What you will learn
By the end of this lesson, you will be able to:
- explain why
*sql.DBis 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.Openper request: pools multiply and connections churn. - Assuming
sql.Openproves connectivity: use a boundedPingContextwhen startup requires it. - Building SQL with client strings: this creates SQL injection risk.
- Forgetting
rows.Closeorrows.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.
The exact generated-ID syntax is database-specific. A database supporting RETURNING may use:
func (r *LessonRepository) Create(ctx context.Context, lesson Lesson) (Lesson, error) {
const query = `
INSERT INTO lessons (title, published, created_at)
VALUES ($1, $2, $3)
RETURNING id`
err := r.database.QueryRowContext(
ctx,
query,
lesson.Title,
lesson.Published,
lesson.CreatedAt,
).Scan(&lesson.ID)
if err != nil {
return Lesson{}, fmt.Errorf("insert lesson: %w", err)
}
return lesson, nil
}
Other databases may use ExecContext followed by LastInsertId when supported. Keep the implementation in the database-specific adapter and test it against the actual database engine, not only a mock.
Summary
*sql.DBis 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.