Database Go and JSON

In the process of using go to develop a Web project, the database read-write operation and JSON format input and output are the two most basic modules, go standard library has helped us do a lot, familiar with Database/sql and Encoding/json these two libraries can help us to develop Web applications more comfortable.

But this article put aside the foundation, only said some in the development encountered some real pain points.

How do I handle null values?

One of the features of Go is zero value, such as zero value of type int, which is 0, string "", and the struct is the zero value of the respective type in each field. As a result, many ORM in go handle null values through the zero value mechanism to get into or out of the library, so the database using ORM operations, how to not explicitly specify, basically do not see a null value. A potentially null varchar field, deposited with "" (An empty string).

This does not discuss the merits of an empty string versus null, but rather shows how to handle null values.

There are several types of SQL standard libraries: ' SQL. nullstring, SQL. NullInt64, SQL. Nullbool, SQL. NullFloat64 ", if you are defining a field type in a struct, use SQL. NullString instead of string, you can deposit null values in the database.

Through the source see, nullstring structure and two methods:

struct {    stringbool Interface {}) Errorvalue () (Driver     . Value, error)

Scan implements the Sql.scanner interface, and value implements the Driver.valuer interface. These two interfaces represent the conversion of data from the go side to the DB end. Scan is called when data is transferred from DB to go, that is, when Select is used (view source code); The value is called when go goes to db. That is to say insert/update time use (view source code).

In two different areas of data conversion, usually with the interface to define, this is the role of the interface, JSON is the same.

With the help of the interface, any implementation of the two interface types, can be converted between the DB and go, the interface to build a bridge.

How do I work with custom types?

As can be thought from the previous section, if you want to save the go custom type to db, you only need to implement scanner and valuer interfaces, just like SQL. Like NullString.

Typical such as nulltime (View source):

struct {time Time  . Time    boolinterface{}) error {    = value. Time. Time)    return  nil}func (NT Nulltime) Value () (Driver. Value, error) {    if !  Nt. Valid {        return  nil, nil    }    return  NT. Time, nil}

To make this type better, add a helper function:

func tonulltime (t time. Time) nulltime {    return nulltime{time:t, Valid:!  T.iszero ()}}

How does a custom type convert to JSON?

If you take a SQL. The type of nullstring, do json. Marshal, the resulting output is this:

{    ...    "column_name": {"String":"Hello?","Valid":true },    ...}//and what we want is:{    ...    "column_name":"Hello?",    ...}//or{    ...    "column_name":NULL,    ...}

We know that the JSON library has two standard interfaces, JSON. Marshaler (view source), JSON. Unmarshaler (view source), if a type implements both interfaces, the JSON is used. When Marshal/unmarshal is called, this type of custom method is called:

Type nullstringstruct{sql. Nullstring}func (v*nullstring) Marshaljson () ([]byte, error) {    ifV.valid {returnJSON. Marshal (v.string)}Else {        returnJSON. Marshal (Nil)}}func (v nullstring) Unmarshaljson (data []byte) Error {varS *string    ifERR: = json. Unmarshal (data, &s); Err! =Nil {returnErr}ifs! =Nil {v.valid=truev.string= *s}Else{v.valid=false    }    returnNil}

Summarize

Go Language interface, plays the role of the bridge, connected to the go and other fields of data conversion, through the implementation of the standard library interface, we can make go data type easily adapt to different data areas.

Reference
http://dennissuratna.com/marshalling-nullable-string-db-value-to-json-in-go/

http://blog.carbonfive.com/2015/07/09/there-will-be-sql/

http://marcesher.com/2014/10/13/go-working-effectively-with-database-nulls/

Database Go and JSON