RFC: allow transparent storage and retrieval of JSON datatypes

Problem Usage of JSON types in a SQL database is both a common use pattern, and very difficult to use with both sqlx and database.sql. types.JSONText is a very helpful type for transparent passthrough of values to consuming clients, but adding built in support this in sqlx reduces boilerplate reading and writing these rows.

Example:

// Lead represents a single potential customer for XYZ service
type Lead struct {
    ID int64 `db:"id"`
    /* ... */
    // AnalyticsAttrs a JSON object of attributes applied to help categorize, score, etc this lead
    AnalyticsAttrs types.JSONText `db:"analytics_attrs"`
}

Querying:

var lead Lead
_ = db.QueryRowx("...").StructScan(&lead)

var attrs map[string]interface{}
_ = json.Unmarshal(lead.AnalyticsAttrs, &attrs)

// do something with attrs

Inserting is equally annoying. Note that I am not suggesting this is necessarily a good design, only trying to give a reasonable example familiar to hopefully many.

Current workaround:

Use the code written above, everywhere you use a JSON column
Implement a custom type implementing Scanner / Valuer interface

Problems with 1: It works, however, it is very cumbersome. When every other type of object can automatically marshal unmarshal into the database, why can't json objects? They are built into most databases as well as first class supported in go.

Problems with 2: It works, however it is also cumbersome. For custom type CustomX, you need to write custom types for map[string]CustomX, []CustomX, etc. Implementing driver.Valuer and sql.Scanner is (I would argue) advanced usage. Implementers need to write all 4, JSONMarhsal, JSONUnmarshal, Value, and Scan for these types.

Not being satisfied with it works I believe is the whole reason sqlx was created. database.sql works but it doesn't work well enough.

Proposed solution: Use struct tag options (similar to the ",omitempty" ",string" options in the json package) to allow a type to mark itself as stored using json. The database backing storage can be anything supporting Scan() or Value() into []byte or string. This allows any type that implements json.Marshaler to be stored in the database to be stored, and any type that implements json.Unmarshaler to be read from the database.

type Lead struct {
    // ...
    Tags                        []string               `db:"tags,json"`
    IntAttrs                    map[string]int         `db:"int_attrs,json"`
    JSONAttrs                   map[string]interface{} `db:"json_attrs,json"`
    CustomXSupportsJSONMarshal CustomX                `db:"custom_x,json"`
    CustomXs                    []CustomX              `db:"custom_xs,json"`
}

A similar strategy could be applied for xml, gzip, etc.

I believe this would be a breaking change, since I believe it could break structs tagged with a "," in their column name.

Thoughts? Comments?

jmoiron / sqlx

RFC: allow transparent storage and retrieval of JSON datatypes #362