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)

jmaloney · Accepted Answer · 2016-10-26 17:15:05Z

100

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, 2016 at 17:15

jmaloney

12.4k3 gold badges39 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

slomek Over a year ago

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?

jmaloney Over a year ago

@slomek I would find it confusing if a function named NewNullString did not return a type called NullString.

slomek Over a year ago

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

praveent Over a year ago

@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.

Prince Billy Graham Over a year ago

I think the name NewNullableString is more perfect for the function

captncraig · Accepted Answer · 2016-10-26 15:57:53Z

22

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.

edited Oct 26, 2016 at 15:57

answered Oct 26, 2016 at 15:46

captncraig

23.3k18 gold badges116 silver badges154 bronze badges

1 Comment

user2694306 Over a year ago

Is there a way to cast a string to NullString?

Manassé · Accepted Answer · 2021-12-23 11:50:15Z

5

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, 2021 at 11:50

Manassé

1,0311 gold badge9 silver badges6 bronze badges

Comments

Big_Boulard · Accepted Answer · 2022-06-14 15:13:52Z

0

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
}

answered Jun 14, 2022 at 15:13

Big_Boulard

1,3851 gold badge19 silver badges42 bronze badges

2 Comments

Carl Winbäck Over a year ago

Not sure that library is useful when OP is using MySQL.

Big_Boulard Over a year ago

You are right, sorry I overlooked the issue

Jan Tungli · Accepted Answer · 2023-06-20 09:27:53Z

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

Collectives™ on Stack Overflow

Golang Insert NULL into sql instead of empty string

5 Answers 5

5 Comments

1 Comment

Comments

example (untested)

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

1 Comment

Comments

example (untested)

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related