skip to content

When do you scan a nullable column into sql.NullString, into a *string, or COALESCE it away in SQL?

level: middleimportance: should knowfreq 48%

answer

  1. does the caller need the difference?
  2. zero value versus absent value
  3. one option discards the difference in SQL
  4. value struct, pointer, or a substituted default
  5. Valid flag, nil check, or COALESCE

basics

~20 s

Choose by whether the caller must tell NULL from the zero value. If it must, use sql.NullString for an explicit Valid flag or a *string for a nil check. If not, COALESCE in the query and keep a plain string.

solid answer

~50 s

The decision is about the caller, not the column. `sql.NullString` is a value struct with `String` and `Valid`: no allocation, an explicit flag, and no way to dereference it wrongly — but the struct travels into every layer that touches that field, so each one has to unwrap it. A `*string` destination reads naturally as `if p != nil`, drops straight into structs that already use pointers, and lets `nil` mean NULL end to end, at the cost of one allocation per non-NULL row and the usual nil-dereference risk. `COALESCE(note, '')` in the SQL keeps the Go type plain, but it permanently destroys the difference between NULL and an empty string, so it is only honest when nobody downstream can act on that difference. My default on a legacy table nobody will alter is: model the NULL wherever the domain distinguishes "unset" from "empty", and collapse it in SQL for display-only columns.

go deeper

for a junior

Be able to name the three options and say what each gives you: a Valid flag, a nil check, or a substituted default with no way back. Knowing that COALESCE loses information is the key point here.

for a middle

Explain the mechanics behind the tradeoff: the struct is a value and allocates nothing, the pointer allocates per non-NULL row, and both write NULL back because they implement driver.Valuer.

for a senior

Show a per-column decision rule driven by what callers can act on, and argue for one house convention so readers do not have to check which style a given file uses.

for a principal

Own where nullability is allowed to live. Decide whether database-shaped types may appear in domain structs at all, or whether a mapping layer converts them once at the boundary, and what that costs in code volume.

## The question behind the question All three options read the same row. The choice is about what your Go code is allowed to know afterwards, and who pays for knowing it. Start from one test: **can a caller downstream do something different when the value is NULL than when it is the zero value?** If yes, the NULL has to survive into Go. If no, carrying it is pure ceremony, and ceremony spreads. ## sql.NullString and its family ```go type NullString struct { String string Valid bool } ``` `Valid` is false exactly when the column was NULL, and `String` is then `""`. The type implements both `sql.Scanner` (so it is a legal `Scan` destination) and `driver.Valuer` (so it writes NULL back when invalid), which makes it faithful in both directions. Strengths: it is a plain value, so scanning costs no allocation; the NULL case is impossible to overlook because you have to name `Valid` before you reach `String`; and the same shape exists for other column types — `sql.NullInt64`, `sql.NullInt32`, `sql.NullInt16`, `sql.NullByte`, `sql.NullFloat64`, `sql.NullBool`, `sql.NullTime` — with a generic `sql.Null[T]` in recent Go covering the rest. Weakness: it is a database-shaped type in your domain struct. Every layer that touches the field sees the struct and must unwrap it, and comparisons like `a == b` now compare two fields, which is usually what you want but is easy to get wrong when one side was built by hand with `Valid` left false. ## A pointer destination ```go var note *string err := row.Scan(&note) ``` `Scan` sets the pointer to `nil` for NULL and allocates a fresh value otherwise. Strengths: `nil` is Go's own idiom for absence, so the check is `if note != nil`; the field type stays the ordinary element type; and a nil pointer passed as an argument writes NULL back. Weaknesses: an allocation per non-NULL row, which shows up on a hot path scanning millions of rows; the ever-present possibility of dereferencing nil three layers away from the scan; and the fact that a pointer field in a struct is ambiguous to a reader — it may mean "nullable in the database", "optional in the API", or "large, so shared". ## COALESCE in the query `SELECT COALESCE(note, '') FROM customer` lets you scan into a plain `string`. The Go side becomes boring: no wrapper struct, no pointer, no branch. That is the whole point, and also the whole risk — after this, no code in the process can distinguish a NULL from an empty string. It is the right call for a display-only column where both render as nothing, and the wrong call for anything that feeds a decision, an audit trail, or a write-back path. Note also that the substitute value must be a real value of the column's type, and that wrapping a column in a function can matter for how the database plans the query. ## Deciding on a legacy table The realistic setting is a table declared years ago where half the columns are nullable, most of them by accident, and nobody is going to alter it. The productive move is per column, not per table: - **The domain distinguishes unset from empty** — an approval timestamp, a discount code, a cancellation reason. Model the NULL: `sql.NullTime`, `*string`, or a custom type. Losing it here produces wrong behaviour, not just ugly output. - **The domain cannot distinguish them** — a free-text note, a description shown in a back-office list. `COALESCE` it and keep the plain type. - **You do not yet know** — keep the NULL. Discarding information is easy to do later and impossible to undo per row. One further consideration: consistency beats local optimisation. A codebase that uses `sql.NullString` in some row structs and `*string` in others forces every reader to check which convention this file follows. Pick one for "nullable column" and use it everywhere; reach for the other only when a concrete constraint — an allocation budget, or an existing struct you must fill — argues for it. ## What all three share Whatever you choose, the destination's ability to represent absence must match the **column's** nullability as declared in the schema, not the nullability you have observed in the rows so far. That is the property that decides whether the code survives the first legacy row that carries a NULL.

  • What does a *string scan destination cost that sql.NullString does not?
    One allocation per non-NULL row: `Scan` allocates a fresh value and points the destination at it, whereas `sql.NullString` is a value written in place. On a hot path scanning millions of rows that is measurable; on a request handler reading one row it is noise. The other cost is a nil dereference far from the scan site.
  • The column is NOT NULL in the schema. Is sql.NullString ever still the right destination?
    Usually no — a NOT NULL column should scan into the plain type, and using a nullable wrapper just adds a branch that can never be false. The exception is a query that can itself produce NULL from a non-nullable column, such as an outer join that finds no matching row, or an aggregate over an empty group. There the nullability comes from the query, not the column.
  • You need a nullable column type that has no pre-declared sql.Null* wrapper. What are your options?
    Use the generic `sql.Null[T]` available in recent Go, use a pointer destination, or implement `sql.Scanner` and `driver.Valuer` on your own type so it handles `nil` in both directions. Which one you pick depends on whether the type also needs conversion logic beyond nullability.

saying these in an interview costs you the question

  • Says COALESCE is always safe because empty means missing
  • Treats NullString.String == "" as the NULL test
  • Claims a *string destination never allocates
  • Mixes both conventions in one codebase without a reason
  • Uses a nullable wrapper for a NOT NULL column by default