database/sql: support custom input types / output types, custom conversion

[kardianos]

This is a master tracking issues for various requests to support custom parameter types and custom scan types. This includes may geometry types, slices, structured fields. This issue also tracks issues related to conversion of one type into another type.

• Expand types accepted in driver.TYPE database/sql/driver: Bool, Int32, String, DefaultParameterConverter are exported, but unsetable #18415
• possible inconsistent handling of types in convertAssign database/sql: possible inconsistent handling of types in convertAssign #18101
• support time.Time and arrays database/sql: support time.Time and arrays #17834
• support slices? database/sql/driver: support slices? #16235
• allow drivers to support additional Value types database/sql: allow drivers to support additional Value types #13567

I'd like to flush out a proposal here before go1.8 is released so it can be implemented smoothly in the go1.9 window. I would expect few public API additions. There might be a new interface or two in sql or in sql/driver.

[asaf]

Hey folks,

There's a ref to rana/ora#145 about Oracle (and friends) Stored Procedures OUT params support,

Just for inspiration, here's an example how JDBC statement support these.

Whether statements support this or multi-value types result can also work,
Either way, this is a big issue in the enterprise world,

Thanks.

[kardianos]

@asaf I'd also like to enable OUT params. I would see it being implemented in the public database/sql API by adding Out bool to the NamedArg struct, then pass in a pointer to the value in the Value field. The part I would need to work out is how the driver API works and looks like. Though to be honest, that might be the only thing required as arguments are already passed through. We would almost certainly need to tweak query argument validation (which is another area I'd like to address for go1.9).

[tgulacsi]

If pointers of Out args are allowed to pass through convertAssign, that'd be an easy solution.

[tgulacsi]

Regarding #18415, allowing custom converters would be one option.

My main problem with the current situation is that the driver has an OCINum, that may or may not be represented as an int32 or int64 or float6r, but always can be a string.

If sql could provide the type what the user is Scanning into, then the driver could make a more informative decision about the conversion.

Now it uses float64 as the least wrong.

The same is with a LOB: if the user scans into an io.Reader, then the driver don't have to slurp all the content into memory.

[nullbio]

I'm one of the authors of SQLBoiler (https://github.com/vattle/sqlboiler), we're currently using our own extended types packages to handle this, but it'd be very nice if it was part of the stdlib. What we currently use:

https://github.com/nullbio/null and https://github.com/vattle/sqlboiler/tree/master/types

This allows us to support all types as nullable (not only the ones currently in stdlib), hstore, 1 dimensional Postgres arrays and JSON. Would be very cool to see these added to the stdlib so we could drop support for these ugly dependencies we've had to craft :-).

These are the mappings we currently use if you care to know/if it's of any help or relevance:

https://github.com/vattle/sqlboiler/blob/master/bdb/drivers/mysql.go#L269
https://github.com/vattle/sqlboiler/blob/master/bdb/drivers/postgres.go#L309

Thanks.

[gopherbot]

CL https://golang.org/cl/38533 mentions this issue.

[kardianos]

I believe all of the above issues have been taken care of by allowing drivers to implement driver.NamedValueChecker . There is also a standard way to handle OUTPUT values in go1.9.