Golang Insert NULL into sql instead of empty string

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)

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),
)

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.

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

If you want to avoid creation N NewNullType functions, you'd better off using pgx & pgtypes along with the Value() func:

https://github.com/jackc/pgtype

https://github.com/jackc/pgtype/blob/master/text.go

example (untested)

type User struct {
    email   pgtype.Text `json:"email"`
    firstName  pgtype.Text `json:"first_name"`
}

func InsertUser(u User) error {
    // --> get SQL values from u
    var err error
    email, err := u.email.Value() // see https://github.com/jackc/pgtype/blob/4db2a33562c6d2d38da9dbe9b8e29f2d4487cc5b/text.go#L174
    if err != nil {
        return err
    }
    firstName, err := d.firstName.Value()
    if err != nil {
        return err
    }
    // ...

    sql := `INSERT INTO users (email, first_name) VALUES ($1, $2)`
    conn, err := pgx.Connect(ctx, "DATABASE_URL")
    defer conn.Close(ctx)
    tx, err := conn.Begin()
    defer tx.Rollback(ctx)
    // --> exec your query using the SQL values your get earlier
    _, err = tx.Exec(ctx, sql, email, firstName)
    // handle error
    }
    err = tx.Commit(ctx)
    // handle error
    return nil
}

Example , add NULL value :

/*Exaples:
ParamReplacingMode = 0  // no replacing params
isql:=InitSql(db,"SELECT * FROM table WHERE price+vat>:Sum and country=:C") // by Oracle
AddParam(isql,":C", "USA")                                                  // the order for AddParams is not bound, you can add params any order
AddParam(isql,":Sum", 130.5)
res,err:= SqlQuery(isql)                                                    //result: db.Query("SELECT * FROM table WHERE price+vat>:Sum and country=:C", 130.5,"USA")
or
ParamReplacingMode = 1  // MySQL  - replacing params to "?"
isql:=InitSql(db,"SELECT * FROM table WHERE price+vat>:Sum and country=:C") // by Oracle convert to Mysql
AddParam(isql,":C", "USA")                                                  // the order for AddParams is not bound, you can add params any order
AddParam(isql,":Sum", 130.5)
res,err:= SqlQuery(isql)                                                    //result: db.Query("SELECT * FROM table WHERE price+vat>? and country=?", 130.5,"USA") //replacing params to "?"
or
ParamReplacingMode = 0 //no replacing params
isql:=InitSql(db,"SELECT * FROM table WHERE price+vat>$1 and country=$2")   // by Postgre
AddParam(isql,"$1", 130.5)
AddParam(isql,"$2", "USA")                                                  // the order for AddParams is not bound, you can add params any order
res,err:= SqlQuery(isql)                                                    //result: db.Query("SELECT * FROM table WHERE price+vat>$1 and country=$2", 130.5,"USA")
or
ParamReplacingMode = 2 // mode Oracle to Postgre, replacing params to <$Number>
isql:=InitSql(db,"SELECT * FROM table WHERE price+vat>:Sum and country=:C") // by Oracle convert to Postgre
AddParam(isql,":C","USA")
AddParam(isql,":Sum",130.5)
res,err:= SqlQuery(isql)                                                    //result: db.Query("SELECT * FROM table WHERE price+vat>$1 and country=$2", 130.5,"USA")
SqlExec() is similar as SqlQuery(), but call db.Exec(...)
Example , add NULL value:
isql:=InitSql(db,"INSERT INTO table (id, name) VALUES (:ID,:Name)")
AddParam(isql, ":ID", 1)
AddParam(isql, ":Name", nil)
res,err:= SqlExec(isql)
*/
type (
TisqlMode int32
TisqlAt   struct {
ParamName string
Pos       int
ParamVal  any
}
Tisql struct {
Sql       string
ResultSql string
DB        *sql.DB
Params    map[string]any
At        []TisqlAt
}
)
const (
Oracle  TisqlMode = iota //0, no replacing params
Mysql                    //1, "SELECT * FROM table WHERE price+vat>:Sum and country=:C" -> db.Query("SELECT * FROM table WHERE price+vat>? and country=?", 130.5,"USA")
Postgre                  //2, "SELECT * FROM table WHERE price+vat>:Sum and country=:C" -> db.Query("SELECT * FROM table WHERE price+vat>$1 and country=$2", 130.5,"USA")
)
func (s TisqlMode) String() string {
switch s {
case Oracle:
return "Oracle" // no replacing params
case Mysql:
return "Mysql"
case Postgre:
return "Postgre"
}
return "unknown"
}
var ParamReplacingMode TisqlMode = -1 //-1 = unknown,  0 = no replacing params,  1 = to MySql,  2 = to Postgre
func indexAt(pStr, pSubStr string, pos int) int { //Index from position
if pos >= len(pStr) {
return -1
}
if pos < 0 {
pos = 0
}
idx := strings.Index(pStr[pos:], pSubStr)
if idx > -1 {
idx += pos
}
return idx
}
func InitSql(db *sql.DB, sql string) *Tisql {
if ParamReplacingMode < 0 { // unknow
_, err := db.Exec("?")
if err != nil {
s := strings.ToLower(fmt.Sprint(err))
if indexAt(s, "mysql", 0) > 0 {
ParamReplacingMode = 1
} else {
ParamReplacingMode = 0
}
}
}
var isql Tisql
isql.Sql = sql
isql.DB = db
isql.Params = make(map[string]any)
return &isql
}
func AddParam(isql *Tisql, pParam string, pValue any) {
isql.Params[pParam] = pValue
}
func paramOrder(isql *Tisql, pCheckParamCount bool) error {
var at TisqlAt
isql.ResultSql = isql.Sql
t := ""
b := strings.ToLower(isql.Sql) + " "
mMode := ParamReplacingMode
var p, p1, p2 int
for name, v := range isql.Params {
p1 = 0
for p1 >= 0 {
p = indexAt(b, strings.ToLower(name), p1)
if p < 0 {
p1 = -1
continue
} else {
p2 = p + len(name)
t = b[p2 : p2+1] //char after param
if indexAt(" :,;!?%$<>^*+-/()[]{}=|'`\"\r\n\t", t, 0) < 0 {
p1 = p + 1
continue
}
p1 = -1
}
}
if p >= 0 {
at.Pos = p
at.ParamVal = v
at.ParamName = name
isql.At = append(isql.At, at)
}
}
if pCheckParamCount && len(isql.At) != len(isql.Params) {
return fmt.Errorf("Different count of params %d / %d", len(isql.At), len(isql.Params))
}
if len(isql.At) > 1 {
sort.Slice(isql.At,
func(i, j int) bool {
return isql.At[i].Pos < isql.At[j].Pos
})
}
mLen := len(isql.Sql)
switch mMode {
case 1: //to Mysql
{
p1, p2, s := 0, 0, ""
for _, at := range isql.At {
p2 = at.Pos
if p2 >= 0 && p2 <= mLen {
if p2 > p1 {
s += isql.Sql[p1:p2] + "?"
}
p1 = p2 + len(at.ParamName)
}
}
if p1 < len(isql.Sql) {
s += isql.Sql[p1:len(isql.Sql)]
}
isql.ResultSql = s
}
case 2: //to Postgre
{
p1, p2, s := 0, 0, ""
for i, at := range isql.At {
p2 = at.Pos
if p2 >= 0 && p2 <= mLen {
if p2 > p1 {
s += isql.Sql[p1:p2] + "$" + fmt.Sprint(i+1)
}
p1 = p2 + len(at.ParamName)
}
}
if p1 < len(isql.Sql) {
s += isql.Sql[p1:len(isql.Sql)]
}
isql.ResultSql = s
}
}
return nil
}
func ParamsStr(isql *Tisql) string {
s := ""
for i, at := range isql.At {
s += "[" + fmt.Sprint(i+1) + ". " + at.ParamName + "=\"" + fmt.Sprint(at.ParamVal) + "\"]"
}
return s
}
func SqlStr(isql *Tisql) string {
s := "SQL:[" + isql.ResultSql + "]"
if len(isql.At) > 0 {
s += " Params:" + ParamsStr(isql)
}
return s
}
func SqlExec(isql *Tisql, opt ...bool) (sql.Result, error) {
checkParamCount := false
if len(opt) > 0 {
checkParamCount = opt[0]
}
err := paramOrder(isql, checkParamCount)
if err != nil {
return nil, err
}
mLen := len(isql.At)
mVal := make([]any, mLen)
for i := range mVal {
mVal[i] = isql.At[i].ParamVal
}
return isql.DB.Exec(isql.ResultSql, mVal...)
}
func SqlQuery(isql *Tisql, opt ...bool) (*sql.Rows, error) {
checkParamCount := false
if len(opt) > 0 {
checkParamCount = opt[0]
}
err := paramOrder(isql, checkParamCount)
if err != nil {
return nil, err
}
mLen := len(isql.At)
mVal := make([]any, mLen)
for i := range mVal {
mVal[i] = isql.At[i].ParamVal
}
return isql.DB.Query(isql.ResultSql, mVal...)
}