Generate types with methods that can be used as interfaces

[tschaub]

I'm using sqlc together with a package that generates an http router based on an OpenAPI spec. I find that I need to do a lot of mapping between sqlc generated types and types for the http responses. This is ok if my queries are relatively simple, but I've found that with joins I end up with a lot of structs that share field types but have dedicated types. If these structs instead had methods whose signatures matched, I could create interfaces that these types implement and use these interfaces in the functions that generate API responses.

For example, say I've got a jobs table and a deliveries table.

CREATE TABLE jobs (
    id UUID PRIMARY KEY,
    status TEXT NOT NULL,
    created TIMESTAMP WITH TIME ZONE NOT NULL,
    updated TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE TABLE deliveries (
    id UUID PRIMARY KEY,
    job_id UUID UNIQUE NOT NULL REFERENCES jobs(id) ON DELETE CASCADE,
    status TEXT NOT NULL,
    created TIMESTAMP WITH TIME ZONE NOT NULL,
    updated TIMESTAMP WITH TIME ZONE NOT NULL
);

And here are two example queries to get a single job and to list all jobs:

-- name: GetJob :one
SELECT jobs.*, deliveries.id AS delivery_id
FROM jobs
LEFT JOIN deliveries ON jobs.id = deliveries.job_id
WHERE jobs.id = $1;

-- name: ListJobs :many
SELECT jobs.*, deliveries.id AS delivery_id
FROM jobs
LEFT JOIN deliveries ON jobs.id = deliveries.job_id;

I end up with two structs whose fields match:

type GetJobRow struct {
    ID             uuid.UUID `json:"id"`
    Status         string    `json:"status"`
    Created        time.Time `json:"created"`
    Updated        time.Time `json:"updated"`
    DeliveryID     uuid.UUID `json:"delivery_id"`
}

type ListJobsRow struct {
    ID             uuid.UUID `json:"id"`
    Status         string    `json:"status"`
    Created        time.Time `json:"created"`
    Updated        time.Time `json:"updated"`
    DeliveryID     uuid.UUID `json:"delivery_id"`
}

If these structs had methods for each of the fields, I could create a single JobWithDeliveryID interface and use that in the signature for my function that generates values for my API responses. Without that interface, I need to create two functions that map the types above to the same type for the API response.

Doing this twice is not so bad, but I've found that the types proliferate pretty quickly, while the number of corresponding interfaces I would have to create is relatively low.

[kyleconroy]

This is similar to #112

Adding getter methods to all returned structs would be a big change, but we could put it behind an emit_getter_methods flag. We'd need to take special precautions so that the generated methods wouldn't conflict with field names on the struct.

Another approach would be an option to tell sqlc that both these queries should return the same struct. There are a few ways we could do this, but one would be to improve view support (#193)

CREATE TEMP VIEW delivery_jobs AS
  SELECT jobs.*, deliveries.id AS delivery_id
  FROM jobs
  LEFT JOIN deliveries ON jobs.id = deliveries.job_id;

-- name: GetJob :one
SELECT *
FROM delivery_jobs
WHERE id = $1;

-- name: ListJobs :many
SELECT * FROM delivery_jobs;

The temporary view would never be created; instead we'd take the body of the CREATE VIEW statement and insert it into the compiled query.

[tschaub]

I like the idea of the idea of the temporary view representing the common type for these two queries. Feels like a lot of effort to get in place.

I understand that the getter methods would also be a big change. It could offer a lot of flexibility though - being able to create interfaces that represent sets of columns shared between multiple rows.

I was imagining that the generated struct fields would be unexported and that the getter methods would take the uppercased names. People authoring other packages only need to get and not set these values, right? I know this is a huge breaking change, but it seems like the work to implement it and the upgrade work for users is fairly straightforward.

[tschaub]

I see that @Cyberax suggested the same in #387.

[tooolbox]

Maybe this helps, but if you have two structs with matching fields, you can do a type conversion between them. The json tags can vary as long as the name, type and sequence of fields is the same.

This is covered in the Go Spec: https://golang.org/ref/spec#Conversions

Playground: https://play.golang.org/p/zGxwL2yDlKU

You still have to "map the types above to the same type for the API response" but it seems a lot less onerous to do t1(t2).

[johanbrandhorst]

This should probably be clsoed as a dupe of #387. If there has been a change in concensus that issue can be reopened.

[jamesleeht]

I think this is still quite an important feature - quite common for SQL queries to return the exact same fields and it won't always be a deliveries.* situation.

@tooolbox solution to do Golang conversions works, but it feels a bit dirty.

I wonder if it's possible to do some kind of query annotation to indicate that the same struct should be scanned?

[kyleconroy]

Closing as a duplicate of #387.

If you'd like these getter methods, please use a separate tool to generate them from the sqlc generated code.