Go database/sql fails to scan a NULL column, and stores empty strings instead of NULL
converting NULL to string is unsupportedShort answer
database/sql hands a NULL to Scan as nil, and a plain *string cannot hold it, so Scan returns an error. Scan into sql.NullString (or sql.Null[string] on Go 1.22+) and check Valid. For writes, pass a NullString with Valid set to false, or a plain nil, so the database receives NULL instead of an empty string.
Fix it fast
1Find out which cause you have
Find which column is nullable. A column that allows NULL is the only one that can trigger this, and the error names its index and name.
DESCRIBE customer; -- MySQL -- or in psql: \d customer2Match what you see, then jump to its fix
What does the error look like?
converting NULL to string is unsupportedunsupported Scan, storing driver.Value type <nil> into type *string
Cause 1 of 2
A nullable column is scanned into a plain string
1Check
The Scanner interface documentation lists nil as the value a driver passes for NULL. A *string destination has no way to represent it.
grep -n "Scan(&" *.go2Fix
Scan into sql.NullString and read String only when Valid is true. From Go 1.22 the generic sql.Null[string] does the same.
var s sql.NullString
err := db.QueryRow("SELECT name FROM customer WHERE id = ?", id).Scan(&s)
if err != nil {
return err
}
name := "Valued Customer"
if s.Valid {
name = s.String
}
// Go 1.22+
var g sql.Null[string]
// g.V holds the value, g.Valid says whether it was NULL3Verify
Run the query on a row that holds NULL and a row that holds text; both scan without error.
go test ./...Confirmed by go.dev
Cause 2 of 2
An empty string is written where NULL was intended
1Check
A Go string has no NULL state, so an empty string is sent as an empty string. NullString implements driver.Valuer: with Valid false it is sent as NULL.
SELECT COUNT(*) FROM customer WHERE phone IS NULL;2Fix
Build a NullString from the input, valid only when the string is not empty, and pass that as the argument.
phone := sql.NullString{String: input, Valid: input != ""}
_, err := db.Exec("INSERT INTO customer (name, phone) VALUES (?, ?)", name, phone)
// PostgreSQL with lib/pq or pgx uses $1, $2 instead of ?3Verify
Insert a row with an empty input and check that the column is NULL.
SELECT id, phone IS NULL FROM customer ORDER BY id DESC LIMIT 1;Confirmed by pkg.go.dev
How do I stop it coming back?
- Make columns NOT NULL with a default when NULL has no meaning, so Go code can use plain strings.
- Use the Null types in the struct fields that map to nullable columns.
- Remember that ? is for MySQL; Postgres drivers use $1, $2.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Back end
Knex runs out of connections and the query waits until it times out
Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?
Every query needs a free connection from Knex's pool.
3 causes, each with a fixBack end
TypeORM connects but creates no tables or columns
A new column is missing, with no error at startup
TypeORM does not create tables by itself.
3 causes, each with a fixBack end
CodeIgniter 4: try/catch does not catch a failed insert in the model
DatabaseException
CodeIgniter throws a DatabaseException on a failed query only when DBDebug is true, and inside a transaction it does not throw unless you ask.
3 causes, each with a fixBack end
CodeIgniter 4 where() builds a different condition than you meant
A SQL syntax error that appears only when a value contains a quote character
The query builder joins every where() with AND and every orWhere() with OR, with no brackets.
3 causes, each with a fixBack end
Call to undefined function mysql_pconnect() after a PHP upgrade
Call to undefined function mysql_connect()
The old mysql_* functions belonged to the ext/mysql extension, which PHP removed in version 7.0.
2 causes, each with a fixBack end
CodeIgniter: Call to undefined method on the result object when using order_by() after get_where()
Call to undefined method CI_DB_mysql_result::order_by()
get_where() runs the query at once and returns a result object, not the query builder.
3 causes, each with a fix