Go · MySQL

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
}

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s