Go SQL error “converting NULL to string is unsupported”

Go is a strongly typed programming language, and many SQL databases can also support unknown values. These values can lead to complications in Go when it encounters unexpected results such as NULL (unknown) for an empty string.

// LookupName returns the username from database ID.
func LookupName(id int) (name string, err error) {
	db := Connect()
	defer db.Close()
	err = db.QueryRow("SELECT username FROM accounts WHERE id=?", id).Scan(&name)
	if err != nil {
		return "", fmt.Errorf("lookup name by id %q: %w", id, err)
	}
	return name, nil
}

sql: Scan error on column index 0, name "username": 
converting NULL to string is unsupported

Instead of saving SQL query values to basic Go types, columns that support NULL or other unknown values should always store their results to the sql package Null types. These include NullBool, NullInt64, NullFloat64, NullTime, NullString and a couple of others.

// LookupName returns the username from database ID.
func LookupName(id int) (name string, err error) {
	db := Connect()
	defer db.Close()
	var usr sql.NullString
	err = db.QueryRow("SELECT username FROM accounts WHERE id=?", id).Scan(&usr)
	if err != nil {
		return "", fmt.Errorf("lookup name by id %q: %w", id, err)
	}
	return usr.String, nil
}