Stage 3 · Build

Databases & Persistence

database/sql

Use sql.DB, QueryContext, ExecContext, prepared statements, scanning rows, and nullable columns.

8 min readBuilding Backend ApplicationsBuild

sql.DB Connection

sql.DB is a pool of connections, not a single connection. You never open or close individual connections. The pool handles connection reuse, health checks, and retry logic. Configure pool size to match your database capacity.

Goopening-a-database-connection.go

22 linesLn 1, Col 1Go

sql.Open does not establish a connection. It configures the pool. Call Ping to verify the database is reachable. The pool settings control concurrency and resource usage.

Querying Rows

Use QueryContext for SELECT queries that return multiple rows. Always defer rows.Close() to release the connection back to the pool. Iterate with rows.Next() and scan results into variables.

Goquerying-multiple-rows.go

24 linesLn 1, Col 1Go

rows.Close() releases the connection. rows.Err() checks for errors during iteration. Always check rows.Err() after the loop.

Executing Commands

Use ExecContext for INSERT, UPDATE, and DELETE statements. It returns a Result object with the number of rows affected and the last insert ID.

Goinsert-and-update-operations.go

27 linesLn 1, Col 1Go

Use RETURNING id to get the generated UUID without a separate query. RowsAffected confirms the operation affected at least one row.

Prepared Statements

Prepared statements parse the SQL once and execute it multiple times with different parameters. They improve performance for repeated queries and prevent SQL injection.

Gousing-prepared-statements.go

17 linesLn 1, Col 1Go

Prepare parses the SQL. QueryRowContext executes it with parameters. Close releases the prepared statement. For single-use queries, skip Prepare and use QueryContext directly.

Scanning Rows

Scan maps column values to Go variables. The types must match: VARCHAR to string, INT to int, DECIMAL to float64 or decimal.Decimal, TIMESTAMP to time.Time. Mismatches cause runtime errors.

Goscanning-into-structs-and-maps.go

18 linesLn 1, Col 1Go

For dynamic queries where you do not know the columns at compile time, scan into a map using Columns() to get column names and generic interface pointers.

Nullable Columns

Database columns can be NULL. Go zero values do not distinguish between NULL and empty string. Use sql.NullString, sql.NullInt64, sql.NullTime for nullable columns.

Gohandling-nullable-columns.go

26 linesLn 1, Col 1Go

sql.NullString has Valid bool and String string. If Valid is true, String contains the value. If false, the column is NULL. Check Valid before using the value.

Use COALESCE for default values

Instead of checking NullString in Go, use COALESCE in SQL: SELECT COALESCE(bio, '') FROM users. This returns empty string for NULL, letting you scan directly into a string.

Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.