Querying and Changing Data

Purpose and Scope

This page explains the practical workflow for reading rows from a relational database and for executing SQL statements that change data when using Go's database/sql package. The reader-facing split is simple: use query methods when an SQL statement returns data, and use exec methods when it does not. That distinction matters because Query, QueryContext, QueryRow, and QueryRowContext produce row-oriented values that must be scanned, while Exec and ExecContext produce a Result that reports metadata such as an inserted identifier or the number of affected rows.

The implementation boundary behind that workflow is the database/sql driver contract. Application code normally stays in database/sql; driver authors implement database/sql/driver. Values passed by callers are converted into driver-compatible values, context-aware methods are selected when the driver supports them, and returned driver values are scanned into caller destinations. Understanding those layers helps application developers diagnose placeholder, conversion, cancellation, and metadata behavior without depending on a specific database server.

Sources: src/database/sql/convert.go, src/database/sql/ctxutil.go, src/database/sql/driver/driver.go, src/database/sql/driver/types.go

Relevant Source Files

src/database/sql/closemu.go - Defines closingMutex, a specialized read/write lock used to synchronize close-sensitive operations while allowing reentrant reads around database work.
src/database/sql/convert.go - Contains the type-conversion path for Scan and the argument-conversion path used by statement Exec and Query, including named-value validation and driver value checks.
src/database/sql/ctxutil.go - Bridges public context-aware operations to driver interfaces such as ExecerContext, QueryerContext, StmtExecContext, and StmtQueryContext, with fallback behavior for older interfaces.
src/database/sql/driver/driver.go - Defines driver-facing interfaces and expectations for values, connections, context support, query execution, result sets, connection validation, and optional result-set metadata.
src/database/sql/driver/types.go - Defines ValueConverter, Valuer, and built-in converters such as Bool and Int32, which shape how database values and caller arguments are represented.
src/database/sql/internal/sql.go - Provides the internal ScanContext wrapper shared between database/sql and database/sql/driver while keeping driver scan context details opaque to users.

Querying Rows

A query is an SQL operation that returns data to the caller, most commonly a SELECT. For a single expected row, application code uses QueryRow or QueryRowContext; for zero or more rows, it uses Query or QueryContext and iterates through a Rows value. The official Go database documentation emphasizes that returned data is copied into Go variables with Scan. This scanning step is not a decorative convenience: it is where database driver values cross into application-owned Go destinations and where conversion failures become ordinary Go errors.

The single-row workflow is intentionally compact. A call to QueryRow returns a Row placeholder, and the actual outcome is observed when the caller invokes Scan. If the query returns multiple rows, only the first row is scanned for this API shape. If no row is available, callers check for sql.ErrNoRows. For cancellation-aware code, QueryRowContext carries a context.Context through to the lower layers so a timeout, client disconnect, or explicit cancellation can stop the operation before or during driver execution.

The multi-row workflow requires more lifetime management. Query returns Rows, and callers loop with Next, call Scan for each row, close the rows when finished, and check the final iteration error. Although the provided implementation snippets are below the public Rows API, they explain why the loop receives stable Go values rather than arbitrary driver types. The driver package restricts driver Value to nil, int64, float64, bool, []byte, string, time.Time, custom checked values, and optionally cursor-like Rows, giving database/sql a bounded conversion surface.

Sources: src/database/sql/convert.go, src/database/sql/driver/driver.go, src/database/sql/driver/types.go

Scanning and Value Conversion

Scan is the bridge between database output and caller variables. The conversion code is organized around the fact that drivers return a small set of permitted driver.Value types, while application code often wants richer or more specific destinations. The driver/types.go file describes ValueConverter as the common interface for converting values into driver-compatible representations and for helping database/sql convert from driver values to user scan types. This shared contract is why portable application code can scan booleans, numbers, strings, byte slices, times, and nullable wrappers across many drivers.

The conversion layer also handles values traveling in the opposite direction, from application arguments into SQL placeholders. convert.go defines the path that turns arguments passed to Stmt.Exec and Stmt.Query into []driver.NamedValue. That path validates optional names, assigns ordinal positions, consults a driver.NamedValueChecker when available, uses a statement ColumnConverter when present, and falls back to the default parameter converter. A driver can therefore accept custom argument types, reject unsupported values early, or remove per-query options that are not transmitted to the database.

The Valuer interface is the opt-in hook for custom application types. A value implementing driver.Valuer can convert itself to a valid driver Value before execution. The driver documentation notes that errors returned by Value are wrapped by database/sql, allowing callers to use errors.Is after operations such as query and exec calls. This behavior is especially useful for domain types that validate identifiers, encrypted values, JSON wrappers, or nullable values before they are sent over a driver connection.

Sources: src/database/sql/convert.go, src/database/sql/driver/types.go, src/database/sql/internal/sql.go

Executing Changes

An exec operation is for SQL statements that do not return rows to be scanned, such as INSERT, UPDATE, and DELETE. Application code calls DB.Exec, DB.ExecContext, Tx.Exec, Tx.ExecContext, or the corresponding prepared-statement methods. The result is an sql.Result plus an error. When the error is nil, the caller may ask the result for LastInsertId or RowsAffected, depending on what the database and driver support. Those metadata calls should be treated as driver-dependent: some systems do not expose generated IDs, and some statement types do not report affected rows precisely.

The distinction between query and exec also affects resource handling. A query that returns rows must expose a row stream, so the caller is responsible for consuming or closing it. An exec call is complete once the driver returns its Result and error. If an SQL statement might return rows, the Go documentation directs callers to use query methods instead of exec methods. This keeps application code aligned with the driver contract in driver.go, where query methods return Rows and exec methods return Result. Mixing those expectations can produce driver-specific surprises.

Argument placeholders are not standardized by database/sql; they are part of the SQL dialect and driver behavior. Official examples often show ?, while PostgreSQL drivers commonly use placeholders such as $1. The repository conversion path does not rewrite SQL placeholder syntax. Instead, it converts the supplied Go arguments, preserves ordinal and optional name information in driver.NamedValue, and lets the driver interpret the query string. As a result, portable code should parameterize queries but still write placeholder syntax appropriate for the selected driver.

Sources: src/database/sql/convert.go, src/database/sql/driver/driver.go, src/database/sql/driver/types.go

Context-Aware Execution Flow

The context-aware helpers in ctxutil.go show how database/sql adapts to both modern and older drivers. For exec operations, ctxDriverExec first prefers a driver ExecerContext. If that interface is not present, it converts named values to ordinary positional driver.Value arguments, checks whether the context is already done, and then calls the older Execer method. Query execution follows the same pattern through ctxDriverQuery, preferring QueryerContext and falling back to Queryer after conversion and a cancellation check.

Prepared statements use parallel helpers. ctxDriverStmtExec prefers StmtExecContext, while ctxDriverStmtQuery prefers StmtQueryContext. When those are unavailable, they convert named arguments, observe context cancellation before starting work, and call the older statement methods. This fallback design explains an important operational detail: context support is strongest when a driver implements the context-specific interfaces. Without those interfaces, database/sql can avoid starting work after cancellation, but it cannot necessarily interrupt a driver call already in progress.

Preparation and transaction start have similar context bridges. ctxDriverPrepare uses ConnPrepareContext when possible and otherwise closes a prepared statement if the context is canceled immediately after preparation. ctxDriverBegin uses ConnBeginTx for context-aware transaction setup and checks whether requested isolation or read-only options can be honored by a driver that only supports the older Begin method. These helpers are part of the same design: application APIs expose context consistently, while drivers opt into deeper context behavior by implementing newer interfaces.

Sources: src/database/sql/ctxutil.go, src/database/sql/driver/driver.go

API Components Reference

Component	Use in application code	Source-backed behavior
`DB.Query`, `DB.QueryContext`	Execute SQL that returns multiple rows.	Context-aware execution maps to `QueryerContext` when available, otherwise to older `Queryer` after argument conversion.
`DB.QueryRow`, `DB.QueryRowContext`	Execute SQL expected to return at most one row.	Returned data is scanned through the same driver value and conversion model used for row results.
`Rows.Scan` and `Row.Scan`	Copy returned column values into destination pointers.	Conversion is constrained by `driver.Value`, `ValueConverter`, and internal scan context plumbing.
`DB.Exec`, `DB.ExecContext`	Execute a statement that does not return rows.	Context-aware execution maps to `ExecerContext` when available, otherwise to older `Execer` after named values are converted.
`Tx.Exec`, `Tx.ExecContext`	Execute a changing statement as part of a transaction.	Transaction setup may use `ConnBeginTx`; unsupported non-default isolation or read-only options are rejected for older drivers.
`Result.LastInsertId`	Read a generated identifier after an insert when supported.	The driver exec path returns a `driver.Result`; support for specific metadata is driver-dependent.
`Result.RowsAffected`	Read the count of changed rows when supported.	The value comes from the driver result returned by exec execution.
`driver.NamedValue`	Represent an argument with name, ordinal, and value.	Names must be valid for named parameters; fallback conversion rejects names when the driver does not support them.
`driver.Valuer`	Let a custom type produce a driver-compatible value.	`database/sql` wraps `Value` errors so callers can use precise error checks.

This reference is intentionally written from the application API down to the driver contract. In ordinary code, the most important decisions are whether the statement returns rows, whether the operation needs cancellation, and whether repeated execution should use a prepared statement. Under those APIs, database/sql chooses the best available driver method, converts arguments into the driver value set, and exposes either row scanning or result metadata. When troubleshooting, map the symptom to that boundary: scan errors point to destination and conversion rules, placeholder errors point to driver SQL syntax, and cancellation behavior points to driver context support.

Sources: src/database/sql/convert.go, src/database/sql/ctxutil.go, src/database/sql/driver/driver.go, src/database/sql/driver/types.go, src/database/sql/internal/sql.go

Implementation Details and Safety Notes

The closingMutex implementation is not a public database API, but it is relevant to query and exec reliability because database operations must coordinate with close paths. It is described as an RWMutex for synchronizing close, with read locking taking priority over write locking. Reads may starve close, but reads are safely reentrant. That tradeoff fits database handles and statements where active operations should be allowed to protect themselves from concurrent close while avoiding deadlock in nested or repeated read-side paths.

SQL injection safety sits above these internals but depends on using them correctly. The official Go documentation cautions against assembling SQL with formatting functions such as fmt.Sprintf. Instead, write SQL with driver-appropriate placeholders and pass values as separate arguments. Doing so sends those values through the argument conversion path in convert.go, where driver.NamedValueChecker, ColumnConverter, Valuer, and default conversion can validate and normalize them. String-building bypasses that structured path and turns untrusted data into SQL text.

A final useful mental model is that database/sql separates four concerns: the SQL text remains driver-specific, arguments become driver values, returned columns become scan inputs, and context controls the lifetime of operations when supported. That separation lets Go present a stable standard-library API while allowing drivers to differ in placeholder syntax, result metadata support, type checking, cursor behavior, and cancellation depth. For next steps, read the prepared-statements and transactions page when the same SQL runs repeatedly or when multiple changes must commit atomically, and read cancellation guidance before adding timeouts to production database calls.

Sources: src/database/sql/closemu.go, src/database/sql/convert.go, src/database/sql/ctxutil.go, src/database/sql/driver/driver.go, src/database/sql/driver/types.go

Ask a question...