How do I handle null values in MySQL in when marshaling to JSON

Question

I'm just making my own backend with Labstack Echo for fun and I'm not sure how to handle the null values from MySQL. I'm using NullString but when I want to return the JSON I can't return the same struct I scanned in because email and courseId are not strings, they are NullString.

[
  {
    "uid": "1",
    "name": "Yumiko",
    "email": {
      "String": "",
      "Valid": false
    },
    "cid": {
      "String": "",
      "Valid": false
    }
  }
]

Do I have to make another struct to fill in and return after null checking all the columns?

This is how I'm doing it right now, removed all the err checks to reduce size

func GetUsers(c echo.Context) error {
  rows, err := model.DB.Query("SELECT * FROM users")
  var a []model.User
  u := new(model.User)
  columns, err := rows.Columns()

  values := make([]sql.RawBytes, len(columns))
  scanArgs := make([]interface{}, len(values))
  for i := range values {
    scanArgs[i] = &values[I]
  }

  for rows.Next() {
    err = rows.Scan(scanArgs...)
    err = rows.Scan(&u.UserId, &u.Name, &u.Email, &u.CourseId)
    a = append(a, *u)
  }

  return c.JSON(http.StatusOK, a)
}

And User is

type User struct {
  UserId   int            `json:"uid" xml:"uid" form:"uid" query:"uid"`
  Name     string         `json:"name" xml:"name" form:"name" query:"name"`
  Email    sql.NullString `json:"email" xml:"email" form:"email" query:"email"`
  CourseId sql.NullString `json:"cid" xml:"cid" form:"cid" query:"cid"`
}

I want to improve and implement the best practices thanks, also considering scrapping all this and going with gorm.

You could do something like this: https://stackoverflow.com/a/51961778/965900 — mkopriva, Apr 23 '20 at 07:51
Does this answer your question? https://stackoverflow.com/q/51961358/13860 — Jonathan Hall, Apr 23 '20 at 07:51
"also considering scrapping all this and going with gorm" GORM will have the exact same problem, since it uses the exact same sql backend (plus, since it's an ORM, a million others, to boot, if you ask me) — Jonathan Hall, Apr 23 '20 at 07:56
@Nik3348 the use of `scanArgs` and the associated `values` and `columns` seems the be completely pointless and resource-stealing, is it here by copy-paste mistake? or is that actually in your code and you actually make use of it in some way or another? because if not then you *need* to remove it. In addition to that allocating the element instance outside of the loop rather than inside it seems backwards, if that's being done with the goal to improve performance I would be interested to see the benchmarks that prove its efficacy. — mkopriva, Apr 23 '20 at 08:06
@mkopriva ah I'll remove it thank you, I actually copied it the example from https://github.com/go-sql-driver/mysql/wiki/Examples#rawbytes — Nik3348, Apr 23 '20 at 08:09
@Nik3348 I see, it is a valid approach when you don't have a concrete struct into whose fields you would scan the columns, i.e. only when you don't know the column types for some reason. But when you have something concrete into which you can scan the row then that approach becomes unnecessary. — mkopriva, Apr 23 '20 at 08:14
@Nik3348 as an alternative to `sql.NullString` you could use `*string` as the field type, this should correctly handle both json nulls and sql nulls, however you'll have to nil-check and dereference the pointer everywhere in your code where you need to use the pointed-to string. Another alternative, if null and empty string are synonymous for that specific piece of data, is to use `NULLIF` and `COALESCE` in the sql queries, or declare a custom non-struct based string type that implements the scanner and valuer interfaces. — mkopriva, Apr 23 '20 at 08:22
Ah that's perfect actually I'm going to use *string cause when if I use sql.NullString and insert into the db I get the problem you answered here: https://stackoverflow.com/questions/43304365/cannot-unmarshal-string-into-go-struct-field-article-article-type-of-type-models But I'll try a few ways and see what's best, thank you!! — Nik3348, Apr 23 '20 at 08:44

How do I handle null values in MySQL in when marshaling to JSON

0 Answers0