Support hydrating specific Go types with function outputs

[rishi-kulkarni]

What do you want to change?

One common use case I have a bit of trouble with is storing a blob of unstructured data as JSON in SQLite. I can use a column type override to scan this JSON into a Go struct and get the benefits of static typing in my application code.

Recently SQLite released the JSONB datatype, which is stored as a BLOB. To pull it out as JSON, you utilize the json_extract function with some JMESpath expression, like this:

create table if not exists test (data blob);

insert into test values(jsonb('{"a":2}'));

select json_extract(data, '$') from test;

As far as I can tell, there's no way to tell SQLC to stick the output of this function into a predefined struct, and it instead just comes out as interface{}. If there's no current way to specify this right now, it would be great to have some support in the future.

What database engines need to be changed?

PostgreSQL, MySQL, SQLite

What programming language backends need to be changed?

Go, Python, Kotlin

[kyleconroy]

Yeah, this has been a common ask. We haven't found a good solution yet. One idea was to apply the type parameters proposal (#2800) to column outputs. The open question is how to specify the Go type to use inside a comment string, as we've only supported those in YAML before.

[rishi-kulkarni]

Yeah, after mulling over this for a few days, I decided it's probably more "in the spirit" of sqlc to make a view and then specify the Go type in the config.

On the other hand, there's something to be said for being able to specify the Go type next to the schema (in a sql comment? It might be challenging to support this across the wide range of migration tools you support) rather than in the config file. The yaml is great for overriding types, but this sort of thing (specifying special types per column) can get quite dense.