skip to content

NULLs and Custom Scanners

4 roadmaps4 questions

A NULL column will not land in a string, so Scan fails until you use sql.NullString, a pointer, or your own Scanner. driver.Valuer is the same contract pointing the other way.

part ofGo (Golang)overview, primer and where to startread it →
on this pageshow

explore

questions

4

Why does scanning a SQL NULL into a Go string variable fail in database/sql, and what do you scan into instead?

level: juniorimportance: must knowfreq 70%

answer

  1. the zero value is not absence
  2. the driver reports NULL as nil
  3. one destination carries a bool alongside
  4. the other destination can simply be nil
  5. sql.NullString.Valid, or a *string target

basics

~20 s

A Go string has no value that means absent, so Scan returns an error saying converting NULL to string is unsupported. Declare the variable as sql.NullString or as *string, then check Valid or check for nil before using it.

solid answer

~50 s

SQL NULL means "no value", and a Go `string` cannot represent that: `""` is an ordinary value a column can also hold. When the driver reports NULL, `database/sql` hands `Scan` an untyped `nil`, and converting that into a `*string` destination fails with an error reading `converting NULL to string is unsupported` — no panic and no silent zero value. The two standard destinations are `sql.NullString`, a struct of `String string` plus `Valid bool` that implements `sql.Scanner`, and a pointer destination such as `var email *string` passed as `&email`, which `Scan` sets to `nil` for NULL and to a freshly allocated string otherwise. Both work in the other direction too: an invalid `sql.NullString` or a nil `*string` is written back as NULL. If the caller genuinely does not need to tell NULL from the empty string, `COALESCE` the column in the query instead and keep the plain Go type.

code

go · 16 lines
go
var (
	status sql.NullString // Valid is false when the column was NULL
	note   *string        // nil when the column was NULL
)

// row is a *sql.Row for one legacy customer.
if err := row.Scan(&status, &note); err != nil {
	return err
}

if status.Valid {
	fmt.Println("status:", status.String)
}
if note != nil {
	fmt.Println("note:", *note)
}

go deeper

for a junior

Be ready to say plainly that a Go string cannot hold "no value", that Scan returns an error rather than storing "", and to name both fixes: sql.NullString with its Valid field, or a *string that comes back nil.

for a middle

Explain the mechanics: the driver reports NULL as an untyped nil, and only Scanner implementations, pointer-to-pointer, *any, *[]byte and *sql.RawBytes destinations accept it. Show that the same types write NULL back.

for a senior

Show how you stop it reaching production: derive nullability from the schema or ColumnType.Nullable rather than from observed rows, and decide per column whether NULL is modelled in Go or collapsed in SQL.

for a principal

Own the convention. Decide whether nullable columns are represented once in a shared row-mapping layer or ad hoc per query, and how much of a legacy schema's nullability you are willing to carry into every downstream type.

## What NULL is by the time it reaches Go In SQL, a column value may be NULL — a marker meaning "there is no value here". Go's basic types have no such state. A `string` is always some string, and the closest candidate, `""`, is a perfectly ordinary value that the column can also legitimately hold. An `int64` is always some integer, and `0` is likewise a real value. So the moment a nullable column meets a plain Go variable, something has to give. ## What Scan actually does `Scan` takes `...any`, and each argument must be a **pointer to** the destination variable. Internally it inspects the value the driver produced for that column and the concrete type of the destination, and converts one into the other. Drivers report a NULL column as an untyped `nil`. For a `nil` source, `database/sql` accepts only a small set of destinations: - anything implementing `sql.Scanner` (which is how the `sql.Null*` types work), - a pointer-to-pointer such as `**string`, which it sets to `nil`, - `*any`, `*[]byte` and `*sql.RawBytes`, each of which becomes `nil`. Everything else — `*string`, `*int64`, `*bool`, `*time.Time` — produces an error whose core text is `converting NULL to string is unsupported`, wrapped with the column index and name. Note what does **not** happen: it does not panic, and it does not quietly store the zero value. It returns an error, and code that ignores `Scan`'s error turns this into a wrong-looking row instead of a loud one. ## Fix one: the sql.Null* family `sql.NullString` is a two-field struct: - `String string` — the value, meaningful only when `Valid` is true; - `Valid bool` — false exactly when the column was NULL. When `Valid` is false, `String` is `""`, so you must branch on `Valid`, never on the value. The same shape exists for the other column types: `sql.NullInt64`, `sql.NullInt32`, `sql.NullInt16`, `sql.NullByte`, `sql.NullFloat64`, `sql.NullBool` and `sql.NullTime`, and recent Go adds a generic `sql.Null[T]` with fields `V T` and `Valid bool` so you are not limited to the pre-declared set. Each of these implements `sql.Scanner`, so it is a legal `Scan` destination, and `driver.Valuer`, so passing one as a query argument writes NULL when `Valid` is false. ## Fix two: a pointer destination Declare the variable as a pointer and pass its address: ```go var email *string err := row.Scan(&email) ``` For NULL, `Scan` zeroes the pointer, so `email == nil`. For a non-NULL value it allocates a fresh `string` and points at it. The check is the idiomatic `if email != nil`, there is no extra struct in your model, and a nil pointer passed as an argument is written back as NULL. The costs are an allocation per non-NULL row and the ordinary risk of dereferencing a nil pointer somewhere downstream. ## Fix three: remove the NULL in SQL If the caller truly cannot act on the difference — a display-only description, say — `SELECT COALESCE(note, '') FROM ...` lets you keep a plain `string` destination. This is a deliberate loss of information: after it, no Go code can tell a NULL from an empty string. Make that choice consciously rather than by accident. ## Where this bites in practice The classic failure is an internal back-office page over a long-lived table whose columns were declared nullable years ago. Every row anyone has looked at has a value, the code scans into plain `string` fields, and it works for a year — until the one legacy row that predates the column is opened and the handler returns an error. Nothing changed in the code; the data finally exercised the schema. The defence is to treat nullability as a property of the **column**, not of the rows you happen to have seen. Read the DDL, or ask the result set: `rows.ColumnTypes()` returns `[]*sql.ColumnType`, and `ColumnType.Nullable()` returns `(nullable, ok bool)` — where `ok` is false when the driver cannot report it, which is itself worth knowing before you trust the answer. Anywhere the schema says nullable, the destination must be able to hold "no value". ## Writing NULL back The symmetry matters: `sql.NullString{}` (with `Valid` false) and a nil `*string` both become NULL as query arguments, whereas `sql.NullString{String: "", Valid: true}` and a pointer to `""` both insert an empty string. Using the same type in both directions is what keeps a round trip faithful.

  • How do you write a NULL back into that column from Go?
    Pass a destination that can express absence. An `sql.NullString` with `Valid` false, or a nil `*string`, both become NULL as a query argument, because both types implement `driver.Valuer` and return `nil` for the missing case. A `sql.NullString{String: "", Valid: true}` or a pointer to `""` inserts an empty string instead — the same distinction as on the read side.
  • How do you find out which columns of an existing table can actually be NULL?
    Read the schema — the DDL is the authority. At runtime you can also ask the result set: `rows.ColumnTypes()` gives `[]*sql.ColumnType`, and `ColumnType.Nullable()` reports `(nullable, ok bool)`. Treat `ok == false` as "the driver does not know" rather than "not nullable". Never infer nullability from the rows you happen to have seen; one legacy row is enough to break the assumption.
  • If Scan returns that error, why is it still dangerous to ignore it?
    Because the destination is left holding whatever it held before — typically the previous row's value in a loop, or the zero value. Ignoring the error turns a loud, specific failure into a plausible-looking wrong row that will be believed. `Scan`'s error is the only signal that the conversion did not happen.

saying these in an interview costs you the question

  • Claims a NULL column just scans as the empty string
  • Says Scan panics on NULL rather than returning an error
  • Checks NullString.String == "" instead of Valid
  • Assumes a column is not nullable because no NULL has appeared yet
  • Ignores Scan's error and reads the destination anyway
open as a page

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

level: middleimportance: should knowfreq 48%

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.

open as a page

How do you implement sql.Scanner and driver.Valuer so a custom type round-trips a nullable column?

level: middleimportance: should knowfreq 42%

basics

~20 s

Implement Scan(src any) error on a pointer receiver, type-switching over the driver's values and treating a nil src as NULL. Implement Value() (driver.Value, error) on a value receiver, returning nil for NULL or one of driver.Value's types otherwise.

open as a page

What does scanning a column into sql.RawBytes give you, and what rules must you follow to use it safely?

level: seniorimportance: nice to knowfreq 25%

basics

~20 s

sql.RawBytes aliases memory owned by the driver, so Scan skips the copy a *[]byte or *string destination makes. The bytes stay valid only until the next Next, Scan or Close call, must not be mutated, and are nil for NULL.

open as a page