The Error
You run a query, call rows.Scan(), and Go throws this at you:
sql: Scan error on column index 2, name "description": converting NULL to string is an invalid operation
Or one of its siblings:
sql: Scan error on column index 1, name "age": converting NULL to int64 is an invalid operation
sql: Scan error on column index 3, name "active": converting NULL to bool is an invalid operation
The query ran fine. No syntax error, no network blip. The crash happens purely at scan time, when one of the returned columns holds a SQL NULL.
Root Cause
Go's primitive types — string, int, bool, float64 — have no concept of "nothing". They always hold a value. When database/sql tries to copy a NULL from the database into one of them, it has nowhere to put it and returns an error.
A few situations trigger this reliably:
- The column is nullable in the schema (
VARCHAR NULL,INT NULL) but you scan into a barestringorint. - A
LEFT JOINreturnsNULLfor unmatched rows on the right side. - A column has no default value and some rows were inserted without specifying it.
- Someone ran an
ALTER TABLE ... MODIFY COLUMN ... NULLin production but the Go code wasn't updated to match.
How to Reproduce
type User struct {
ID int
Name string
Description string // nullable in DB
}
row := db.QueryRow("SELECT id, name, description FROM users WHERE id = ?", 1)
var u User
err := row.Scan(&u.ID, &u.Name, &u.Description) // crashes if description IS NULL
if err != nil {
log.Fatal(err) // sql: Scan error on column index 2, name "description": converting NULL to string is an invalid operation
}
Solution 1: Use sql.Null* Types (Recommended)
The database/sql package ships with nullable wrappers built for exactly this. Swap out any primitive that maps to a nullable column:
sql.NullStringsql.NullInt64/sql.NullInt32/sql.NullInt16sql.NullFloat64sql.NullBoolsql.NullTime
import "database/sql"
type User struct {
ID int
Name string
Description sql.NullString // can hold NULL
Age sql.NullInt64
}
row := db.QueryRow("SELECT id, name, description, age FROM users WHERE id = ?", 1)
var u User
err := row.Scan(&u.ID, &u.Name, &u.Description, &u.Age)
if err != nil {
log.Fatal(err)
}
// Always check .Valid before reading the value
if u.Description.Valid {
fmt.Println("Description:", u.Description.String)
} else {
fmt.Println("Description is NULL")
}
Each sql.Null* type carries two fields: .Valid (was it non-NULL?) and the actual value (.String, .Int64, and so on). No extra dependencies, no third-party library required.
Solution 2: Scan Into a Pointer
Don't want to touch your struct? Scan into a pointer instead. A nil pointer is a perfectly valid scan target for NULL:
var description *string
var age *int64
err := row.Scan(&id, &name, &description, &age)
if err != nil {
log.Fatal(err)
}
if description != nil {
fmt.Println(*description)
}
Reach for this when you want a surgical fix — one function, one query — without restructuring anything else. For table-wide changes, Solution 1 scales better.
Solution 3: Use COALESCE in SQL to Avoid NULL at the Source
Sometimes the cleanest fix is upstream. If NULL and an empty string (or zero) carry identical meaning in your app, rewrite the query to substitute a default before the data ever reaches Go:
SELECT id, name, COALESCE(description, '') AS description, COALESCE(age, 0) AS age
FROM users
WHERE id = ?;
var description string // safe — COALESCE guarantees a non-NULL value
var age int64
err := row.Scan(&id, &name, &description, &age)
Skip this when the distinction between NULL and zero actually matters — for example, a score of 0 vs. a score that was never entered.
Dealing with Multiple Nullable Columns
Tables with six or eight nullable columns get messy fast. One clean pattern: define a dedicated scan struct that mirrors the raw SQL result, then map it to your domain struct afterward.
type userRow struct {
ID int
Name string
Description sql.NullString
Age sql.NullInt64
Score sql.NullFloat64
Active sql.NullBool
CreatedAt sql.NullTime
}
func scanUser(row *sql.Row) (*User, error) {
var r userRow
err := row.Scan(&r.ID, &r.Name, &r.Description, &r.Age, &r.Score, &r.Active, &r.CreatedAt)
if err != nil {
return nil, err
}
u := &User{
ID: r.ID,
Name: r.Name,
}
if r.Description.Valid {
u.Description = r.Description.String
}
if r.Age.Valid {
u.Age = int(r.Age.Int64)
}
return u, nil
}
The scan struct absorbs all the NULL-handling noise. Your User domain struct stays clean.
Finding Which Columns Are Nullable
Not sure which columns can produce NULL? Ask the database directly:
-- MySQL / MariaDB
SELECT COLUMN_NAME, IS_NULLABLE, DATA_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'users';
-- PostgreSQL
SELECT column_name, is_nullable, data_type
FROM information_schema.columns
WHERE table_name = 'users';
Any row showing IS_NULLABLE = YES needs a nullable scan target on the Go side. Audit this output before writing scan code for an unfamiliar table.
Verification
Once you've applied a fix, test it against an actual NULL row — not just rows with data:
-- Insert a test row with NULL
INSERT INTO users (id, name, description) VALUES (999, 'Test', NULL);
row := db.QueryRow("SELECT id, name, description FROM users WHERE id = ?", 999)
var id int
var name string
var description sql.NullString
err := row.Scan(&id, &name, &description)
if err != nil {
log.Fatalf("Still broken: %v", err)
}
fmt.Printf("ID: %d, Name: %s, Description valid: %v\n", id, name, description.Valid)
// Output: ID: 999, Name: Test, Description valid: false
No error plus Valid: false — that's your confirmation the fix actually works.
Lessons Learned
- Check schema nullability before writing scan targets. If the column allows
NULL, your Go type must too. - LEFT JOINs almost always produce NULLs. Every column from the right-side table is a candidate for a nullable type.
- Pick the right tool for the job: use
sql.Null*types when NULL and zero carry different meanings; use pointers for quick local fixes; useCOALESCEwhen they're interchangeable. - Seed your test data with NULLs. A test suite that only inserts fully-populated rows will never catch this class of bug — until production does.