go sql

Go SQL error “converting NULL to string is unsupported”

Reading time of 233 words
1 minute
Reading time of 233 words ~ 1 minute


If you find this content useful, consider buying me a coffee
Or help me out by engaging with any advertisers that you find interesting

Go is a strongly typed programming language, and many SQL databases also support unknown values. These unknowns can lead to complications in Go. Such as in this example, when a NULL value is in use for an empty string, it throws the unhelpful error converting NULL to string is unsupported.

 1import (
 2    "fmt"
 3)
 4
 5// LookupName returns the username from database ID.
 6func LookupName(id int) (string, error) {
 7    db := Connect()
 8    defer db.Close()
 9    var name string
10    err := db.QueryRow("SELECT username FROM accounts WHERE id=?", id).Scan(&name)
11    if err != nil {
12        return "", fmt.Errorf("lookup name by id %q: %w", id, err)
13    }
14    return name, nil
15}
sql: Scan error on column index 0, name "username":
converting NULL to string is unsupported

Instead of saving SQL query values as basic Go types, database table columns that support NULL or other unknowns should save their values to the database.sql Null types. These include NullString, NullBool, NullInt64, NullFloat64, and NullTime.

 1import (
 2    "database/sql"
 3    "fmt"
 4)
 5
 6// LookupName returns the username from database ID.
 7func LookupName(id int) (string, error) {
 8    db := Connect()
 9    defer db.Close()
10    var name sql.NullString
11    err := db.QueryRow("SELECT username FROM accounts WHERE id=?", id).Scan(&name)
12    if err != nil {
13        return "", fmt.Errorf("lookup name by id %q: %w", id, err)
14    }
15    return name.String, nil
16}

Written by Ben Garrett

If you find this content useful, consider buying me a coffee
Or help me out by engaging with any advertisers that you find interesting