GopherForgeA WORKBENCH FOR GO

Around Go: SQL services · 3 MIN READ

SQL rows: close, check errors and bound the result

Write a cancellation-aware query loop without losing iteration failures.

A successful QueryContext does not mean every row will decode successfully. Network and server errors can occur during iteration, and Scan can fail on a type or null mismatch. Decide whether partial results are meaningful; do not silently return a successful incomplete list.

SQL rows: close, check errors and bound the result · A MAP OF THE IDEA

  1. Authorize and bind values
  2. Run a bounded query
  3. Scan owned rows
  4. Check final iteration error

Useful when

  • Write a cancellation-aware query loop without losing iteration failures.
  • Check Query, Scan and final iteration errors as separate phases. Close owned rows, authorize scope and cap returned work before reporting a successful result.

Watch out

  • A parameterized tenant value still requires authorization, and LIMIT is not a universal bound on query execution cost.

Implementation & operation cost: Choose the policy from the actual contract, then verify its failure path in the target environment.

01

Bound a tenant-scoped query

This PostgreSQL helper returns at most one hundred identifiers, uses a parameter for the tenant value and returns no successful partial result on failure. A real endpoint must derive tenant from authorization, not merely trust a request parameter.

go
func noteIDs(ctx context.Context, db *sql.DB, tenant string) ([]int64, error) {
	rows, err := db.QueryContext(ctx,
		`SELECT id FROM notes WHERE tenant_id = $1 ORDER BY id LIMIT 100`, tenant)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var ids []int64
	for rows.Next() {
		var id int64
		if err := rows.Scan(&id); err != nil {
			return nil, err
		}
		ids = append(ids, id)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return ids, nil
}
Read the code

The helper imports context and database/sql and assumes a PostgreSQL-compatible driver and a notes table with an integer id. Close releases resources on early return; Err checks failure after Next becomes false. For a query whose close result affects correctness, handle that result explicitly too.

02

Handle one row and null deliberately

QueryRowContext defers its error until Scan. Distinguish sql.ErrNoRows from an infrastructure or conversion failure. Use nullable types or pointers when the schema permits null; a zero int is not automatically a missing value. Keep parameter placeholders consistent with the chosen driver.

03

Parameters bind values, not SQL grammar

The $1 parameter protects a value position. It does not let a caller supply an arbitrary table name or ORDER BY expression safely. Choose identifiers and sort directions from a fixed application allowlist, and bind all untrusted values separately.

04

Separate buffering from streaming

A LIMIT bounds this helper’s row count, not the byte size of arbitrary columns or total work examined by the plan. For exports, stream bounded records and carry cancellation to the cursor. Test a failure after several rows and define the response protocol once output has started.

TAKE THIS WITH YOU

Check Query, Scan and final iteration errors as separate phases. Close owned rows, authorize scope and cap returned work before reporting a successful result.

Check your understanding

A query yields three records and then the driver reports a network error. Which result is correct for an all-or-nothing helper?

Go to the source

Progress stays in this browser. No account needed.