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)

score 85 · Accepted Answer · answered Oct 26 '16 at 17:15

85

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

answered Oct 26 '16 at 17:15

jmaloney

11,580
2
36
29

1

Why don't you make your `NewNullString(..)` function return `interface{}`, so that if `s` is not an empty string, you could return it without wrapping into `sql.NullString`? – slomek Oct 27 '16 at 05:52
8

@slomek I would find it confusing if a function named `NewNullString` did not return a type called NullString. – jmaloney Oct 28 '16 at 01:02
I would rename this function then, as returning sth other than an actual Null value seems confusing now. Don't take me wrong, this is after all the correct answer, I'm just wondering ;) – slomek Oct 28 '16 at 17:00
@jmaloney It is an old question and kind of not in scope of question but, you should warn users to add ability to handle NullStrings while reading back using query. I ran into this issue today while trying to `Scan` `Null` value into a `string`. – praveent May 02 '20 at 13:04
I think the name `NewNullableString` is more perfect for the function – princebillyGK Feb 09 '22 at 08:24

captncraig · Answer 2 · 2016-10-26T15:57:53.707

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.

Is there a way to cast a string to NullString? – user2694306 Oct 26 '16 at 15:49 — user2694306, Oct 26 '16 at 15:49

score 4 · Answer 3 · answered Dec 23 '21 at 11:50

4

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

answered Dec 23 '21 at 11:50

Manassé

991
1
7
6

score 0 · Answer 4 · answered Jun 14 '22 at 15:13

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
}

Not sure that library is useful when OP is using MySQL. – Carl Winbäck Jul 28 '23 at 10:30 — Carl Winbäck, Jul 28 '23 at 10:30
You are right, sorry I overlooked the issue – Big_Boulard Jul 28 '23 at 14:22 — Big_Boulard, Jul 28 '23 at 14:22

Jan Tungli · Answer 5 · 2023-06-20T09:27:53.700

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...)
}

Golang Insert NULL into sql instead of empty string

5 Answers5

example (untested)

Linked