Golang Insert NULL into sql instead of empty string

Question

I'm trying to insert data into a mysql database using golang. In the case where my value takes on an empty string, I would want to insert a null. How can I adjust the following to insert nulls instead of empty string? Thanks.

_, err := m.Db.Exec(`INSERT INTO 
                         visitor_events
                         (type, 
                          info, 
                          url_path, 
                          visitor_id, 
                          created_at, 
                          domain)
                          VALUES
                          (?, ?, ?, ?, ?, ?)`,
                          m.SaveEventType(ve), ve.EventInfo, m.SaveURLPath(ve.UrlPath), ve.VisitorId, time.Now().UTC(), ve.Domain)

Answer 1

In my code I have a function that converts a string to sql.NullString

func NewNullString(s string) sql.NullString {     if len(s) == 0 {         return sql.NullString{}     }     return sql.NullString{          String: s,          Valid: true,     } } 

Then whenever I am using Exec I wrap my strings that could be NULL in the DB with the NewNullString function.

db.Exec(`   insert into       users first_name, last_name, email       values (?,?,?)`,   firstName,   lastName,   NewNullString(email), ) 

Answer 2

The database/sql package has a NullString type (docs) for just this situation.

Basically just use sql.NullString in place of strings where you want them to be nullable in db.

You could also use a *string in your code to the same effect.

The problem in either case is in mapping to/from a nullable string to a non-nullable string. The empty string is technically a value, so you will almost always have to do something like this if you decide empty string should be translated to nil:

nullableS := &s if s == "" {   nullableS = nil } 

The alternative would be to just use *string instead of string in your models throughout your app.

In databases, I have been taking the approach that empty string and null are equivalent, and just storing empty sting in the db, and making most columns non-nullable.

Answer 3

You can also use NULLIF function in your SQL query.

NULLIF(?, ''): will return NULL instead of an empty string when you try to insert an empty string.

Learn more about NULLIF: link