Embedding struct?

[farwydi]

Hello!

Input: (PostgreSQL)

-- name: PreFetchContentsByGroup :many
SELECT c.*, array_agg(g.id)::INT[] AS Ids
FROM contents c
         INNER JOIN content_group_links cgl ON c.id = cgl.content_id
         INNER JOIN groups g ON cgl.group_id = g.id
WHERE g.id = ANY ($1::INT[])
GROUP BY c.id;

const preFetchContentsByGroup = `-- name: PreFetchContentsByGroup :many
SELECT c.id, c.name, c.type, c.file, c.category, c.description, c.priority, c.enable, c.sys_1, c.sys_2, c.text_field_1, c.text_field_2, c.text_field_3, c.text_field_4, c.name_i18n, c.protected, c.uidx, c.name_i, c.description_i, c.text_field_1_i, c.text_field_2_i, c.text_field_3_i, c.text_field_4_i, c.file_i, array_agg(g.id)::INT[] AS Ids
FROM contents c
         INNER JOIN content_group_links cgl ON c.id = cgl.content_id
         INNER JOIN groups g ON cgl.group_id = g.id
WHERE g.id = ANY ($1::INT[])
GROUP BY c.id
`

Output:

type PreFetchContentsByGroupRow struct {
	ID           int32
	Name         sql.NullString
	Type         string
	File         uuid.UUID
	Category     sql.NullInt32
	Description  sql.NullString
	Priority     int16
	Enable       bool
	Sys1         sql.NullString
	Sys2         sql.NullString
	TextField1   sql.NullString
	TextField2   sql.NullString
	TextField3   sql.NullString
	TextField4   sql.NullString
	NameI18n     sql.NullInt32
	Protected    bool
	Uidx         uuid.UUID
	NameI        sql.NullString
	DescriptionI sql.NullString
	TextField1I  sql.NullString
	TextField2I  sql.NullString
	TextField3I  sql.NullString
	TextField4I  sql.NullString
	FileI        sql.NullString
	Ids          []int32
}

Is it possible to do so ...

type PreFetchContentsByGroupRow struct {
	Content
	Ids          []int32
}

Can this be?

[kyleconroy]

Thank you for the feature request. I agree that the PreFetchContentsByGroupRow generated struct isn't very useful for this given query. However, right now I don't have any plans on returning nested structures from queries.

If it is added in the future, it will probably work as you've outlined. c.* expressions would be scanned into separate structs on the return row.

For others reading this issue, feel free to post your own queries that could be helped by embedding structs in the return result (or just hit the 👍 button)

[inconshreveable]

Here's a proposed spec for modified star expansion in generated Go code to satisfy this feature request:

Stating this as three simple rules:

• If the only returned value of a query is table_name.* then there is no behavior change.
• Otherwise, each occurrence of table_name.* is expanded to its model struct in the result row struct.
• As a special case, * now expands to table_1.*, table_2.*, table_3.* for each table in the query

Here are a set of examples illustrating the proposed behavior changes:

• SELECT * FROM table_a -> remains unchanged
• SELECT table_a.* FROM table_a -> remains unchanged
• SELECT table_a.* FROM table_a, table_b -> remains unchanged
• SELECT table_a.*, LENGTH(table_a.foo) FROM table_a -> now outputs a row struct with an embedded model for table_a as well as any other columns selected:

type Row struct {
  TableA TableA
  Col_2  int
}

• SELECT table_a.*, table_b.foo FROM table_a, table_b -> this functions like the previous example outputting a row struct of:

type Row struct {
  TableA TableA
  Foo    int
}

• SELECT table_a.*, table_b.* FROM table_a, table_b -> now outputs a row struct with embedded models for both table_a and table_b:

type Row struct {
  TableA TableA
  TableB TableB
}

• SELECT * FROM table_a, table_b -> this is equivalent to the previous example. the * is expanded to table_a.*, table_b.*:

type Row struct {
  TableA TableA
  TableB TableB
}

[kyleconroy]

Thanks for writing up a spec. I want to have an agreed upon design before any code is written.

As designed, this is a breaking change. The final spec should be opt-in, either in the configuration file or on a per-query basis.

This proposal doesn't play well with CTEs and other nested queries. Given this query:

WITH foobar as (
    SELECT foo.*, bar.* FROM foo, bar
), subquery as (
    SELECT foobar.*, foobar.* FROM foobar
)
SELECT subquery.* FROM subquery

would we expect the output to be?

type Foobar struct {
  Foo Foo
  Bar Bar
}

type Subquery struct {
  Foobar Foobar
  Foobar_1 Foobar
}

type Row struct {
  Subquery Subquery
}

My proposal is that we introduce a sqlc function that explicitly signals that a star expansion should be embedded.

-- name: FooLength :many
SELECT sqlc.embed(foo.*), LENGTH(foo.bar) as bar_len FROM foo;
-- name: FooBaz :many
SELECT sqlc.embed(foo.*), bar.baz FROM foo, bar;
-- name: FooBar :many
SELECT sqlc.embed(foo.*), sqlc.embed(bar.*) FROM foo, bar;

// FooLength return value
type Row struct {
  Foo Foo
  BarLen int32 
}

// FooBaz return value
type Row struct {
  Foo Foo
  Baz int
}

// FooBar return value
type Row struct {
  Foo Foo
  Bar Bar
}

Note that in this proposal, the behavior for SELECT * FROM foo, bar; would not change.

In the future, I'd like to support the same feature using composite types, which is part of the SQL standard. Sadly, the lib/pq driver and database/sql packages don't support composite types because they don't use the binary protocol for PostgreSQL. It's possible that we should wait on implementing this feature and instead focus on adding support for pgx. With composite type support, the queries would be much cleaner:

-- name: FooLength :many
SELECT foo, LENGTH(foo.bar) as bar_len FROM foo;
-- name: FooBaz :many
SELECT foo, bar.baz FROM foo, bar;
-- name: FooBar :many
SELECT foo, bar FROM foo, bar;

Let me play around with pgx composite type support.

[inconshreveable]

Just to follow up here we've run into another situation where this would be beneficial. It's a standard many-to-many relationship where we want to pull out a list of objects and all of objects that they're related to via a join table. Given sqlc's examples, it's basically this query:

SELECT author.*, book.* FROM author
JOIN author_to_books
JOIN books
WHERE author.first_name = 'John'

Instead of doing this the way we'd like we're going to do an application level join instead (select all the books, select all the authors, stitch them together in memory).

Re your questions:

This proposal doesn't play well with CTEs and other nested queries. Given this query:
would we expect the output to be?

I'd expect more complex query constructions like CTEs to continue with their existing output behavior (all rows combined) until there's time/demand to add the additional smarts to the codegen to propagate typing through where possible.

Looking at this a second time, I think these might be orthogonal concerns. Does your sqlc.embed() proposal solve that problem with your nested CTE example? e.g. if I embed sqlc.embed() into a CTE nested a few CTEs deep, will it be any less hard to propagate that marker through the AST? I'm definitely not expert enough here to understand the implementation nuances, but it appears like these might be two unrelated problems:

1. Should star expansion yield typed table definitions in the codegen be explicit (sqlc.embed or implicit?)
2. Implementing sql table -> output type mapping choice propagation through nested CTEs or subqueries

[troylelandshields]

Definitely would love this.

SELECT p.*, c.course_id FROM backend.product p
INNER JOIN backend.decision_tool_course_products c on p.id = c.product_id
WHERE c.course_id = @course_id::uuid
AND p.type = @product_type

[azrshana]

Is there any plan on adding support for this? Working in a codebase with a lot of joins, this would be ideal!

[azrshana]

cc @kyleconroy

[samfrm]

@kyleconroy Is there any update on this?

[kyleconroy]

Yes! There's a PR up here that implements sqlc.embed. It's a big one, so I haven't had time yet to review it.

[azrshana]

This would be amazing! Do you think this is something that could go in this year? This would be such a life changer for our sql layer code !

[samfrm]

Great news! Waiting for this. We really need it :)
Thank you for the perfect job!

[MuhammedOzdogan]

Still waiting for this feature, it would be great to have it

[andrewmbenton]

As of v1.18.0 you can use sqlc.embed() for this: https://docs.sqlc.dev/en/stable/reference/changelog.html#sqlc-embed

[nobleach]

Am I mistaken in seeing this being an "all or nothing" (every column in the table) approach? If I wanted a query that only pulled a couple of fields from a table and a few from each joined table, is there an easy way to do that?

[mikeschinkel]

@nobleach — If I am not mistaken, the 1.18 release notes show an example of what you are asking for.