Custom data type with database/sql

[Paul Smith]

I'd like to add a custom data type that is supported by my database backend to the driver package for that database, but I'm not understanding where and how to hook it in.

Specifically, PostgreSQL supports a data type called 'hstore' (as an extension, not built-in), which is key-value structure, which would fit naturally with Go's map[string]string. I'd like to modify github.com/lib/pq package to support a) a user passing in map[string]string to .Exec and .Query{,Row} as a positional arg, and b) a user getting a map[string]string back from a SELECT query upon a .Scan of Row/Rows.

There is a serialized string representation of hstore that is easy enough to encode/decode, and it is easy to detect that a column returned in a SELECT is of type hstore. The problems, where to insert these steps in the lib/pq package. It seems that database/sql enforces only a certain set of types as values for query/exec args, and for .Scan'ing: []byte, string, int64, float64, bool, and time.Time, so passing in a map[string]string is an error (convert.go:37). It seems like the Value/ValueConverter interfaces are designed for this purpose, but there are no examples of how to implement them in an external package that registers a db driver.

If possible, I'd like to avoid a separate package if possible that just encodes/decodes hstores and puts the burden on the user to call them when using database/sql -- it would be ideal if the conversion could happen automatically "behind the scenes", in lib/pq.

--

http://www.pauladamsmith.com/ · @paulsmith · (312) 492-4668

[Kamil Kisiel]

I think a solution is to implement the database/sql Scanner and Valuer interfaces on an Hstore type which is an alias for map[string]string.

There's a few types in database/sql that do this already which you can use as an example. I think basically on the driver side you'd have it return hstore values as either string or []byte, and then the Scan() function of your Hstore type would know how to convert that in to the map.