sqlc Joins Nested Structs and Embedding Done Cleanly

Joins are where sqlc's flat model gets in the way, and where it offers its most useful trick. A query that joins two tables returns columns from both, and sqlc must put them somewhere. By default it makes a new struct for that query, with every column as a field. That is correct and it is repetitive: a project with thirty joins has thirty row types that each look almost like two of the models. sqlc.embed answers it by nesting the existing models inside the result.

This article runs both, plus aggregates, against three small tables on PostgreSQL 18.6 with sqlc v1.30.0, pgx v5.11.0 and Go 1.25.6, and it records the one case where embed fails. It follows the sqlc tutorial. The query side of joins, such as how to read a plan for them, is in the explain and index article, and the whole topic belongs to the database cornerstone.

The schema

Authors write books, and a book optionally has a publisher, so there is an inner relationship and an optional one:

create table authors (id serial primary key, name text not null);
create table publishers (id serial primary key, name text not null);
create table books (
    id           serial primary key,
    author_id    int not null references authors (id),
    publisher_id int references publishers (id),
    title        text not null
);
insert into authors (name) values ('Ada'), ('Grace'), ('Linus');
insert into publishers (name) values ('Acme Press');
insert into books (author_id, publisher_id, title) values (1, 1, 'Notes'), (1, null, 'Engines'), (2, 1, 'Compilers');

The queries cover five ways of getting data out:

-- name: FlatJoin :many
select b.id, b.title, a.name as author from books b join authors a on a.id = b.author_id order by b.id;

-- name: Embedded :many
select sqlc.embed(books), sqlc.embed(authors) from books join authors on authors.id = books.author_id order by books.id;

-- name: EmbeddedLeft :many
select sqlc.embed(books), sqlc.embed(publishers) from books left join publishers on publishers.id = books.publisher_id order by books.id;

-- name: BookCounts :many
select authors.*, count(books.id) as books from authors left join books on books.author_id = authors.id group by authors.id order by authors.id;

-- name: TitlesPerAuthor :many
select a.name, array_agg(b.title order by b.id)::text[] as titles from authors a join books b on b.author_id = a.id group by a.id order by a.id;

-- name: LeftColumns :many
select books.title, publishers.id as publisher_id, publishers.name as publisher from books left join publishers on publishers.id = books.publisher_id order by books.id;

version: "2"
sql:
  - engine: postgresql
    schema: schema.sql
    queries: query.sql
    gen:
      go:
        package: main
        out: .
        sql_package: pgx/v5

Running them

package main

import (
	"context"
	"fmt"
	"log"

	"github.com/jackc/pgx/v5/pgxpool"
)

const dsn = "postgres://postgres:[email protected]:55432/sqlc0694?sslmode=disable"

func must(err error) {
	if err != nil {
		log.Fatal(err)
	}
}

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, dsn)
	must(err)
	defer pool.Close()
	q := New(pool)

	flat, err := q.FlatJoin(ctx)
	must(err)
	fmt.Printf("1. plain join: row type %T, first row %+v\n", flat[0], flat[0])

	emb, err := q.Embedded(ctx)
	must(err)
	fmt.Printf("2. sqlc.embed: row type %T\n   first row: Book %+v, Author %+v\n", emb[0], emb[0].Book, emb[0].Author)

	_, err = q.EmbeddedLeft(ctx)
	fmt.Println("3. sqlc.embed on the nullable side of a left join:", err)

	cols, err := q.LeftColumns(ctx)
	must(err)
	fmt.Printf("   listing the columns instead gives %T\n", cols[0])
	for _, r := range cols {
		fmt.Printf("   %-10s publisher_id %+v publisher %+v\n", r.Title, r.PublisherID, r.Publisher)
	}

	counts, err := q.BookCounts(ctx)
	must(err)
	fmt.Printf("4. authors.* plus an aggregate: %T, %+v\n", counts[0], counts)

	titles, err := q.TitlesPerAuthor(ctx)
	must(err)
	fmt.Printf("5. array_agg into []string: %+v\n", titles)
}

set -e
export PGOPTIONS='-c client_min_messages=warning'
DB=sqlc0694
docker exec -e PGOPTIONS="$PGOPTIONS" gh-lab-pg psql -U postgres -qc "drop database if exists $DB" -c "create database $DB"
docker exec -i -e PGOPTIONS="$PGOPTIONS" gh-lab-pg psql -U postgres -d $DB -q < schema.sql
"$HOME/go/bin/sqlc" generate
go run .

1. plain join: row type main.FlatJoinRow, first row {ID:1 Title:Notes Author:Ada}
2. sqlc.embed: row type main.EmbeddedRow
   first row: Book {ID:1 AuthorID:1 PublisherID:{Int32:1 Valid:true} Title:Notes}, Author {ID:1 Name:Ada}
3. sqlc.embed on the nullable side of a left join: can't scan into dest[4] (col: id): cannot scan NULL into *int32
   listing the columns instead gives main.LeftColumnsRow
   Notes      publisher_id {Int32:1 Valid:true} publisher {String:Acme Press Valid:true}
   Engines    publisher_id {Int32:0 Valid:false} publisher {String: Valid:false}
   Compilers  publisher_id {Int32:1 Valid:true} publisher {String:Acme Press Valid:true}
4. authors.* plus an aggregate: main.BookCountsRow, [{ID:1 Name:Ada Books:2} {ID:2 Name:Grace Books:1} {ID:3 Name:Linus Books:0}]
5. array_agg into []string: [{Name:Ada Titles:[Notes Engines]} {Name:Grace Titles:[Compilers]}]

A flat join

FlatJoin selects b.id, b.title and a.name as author. sqlc generated FlatJoinRow with ID int64, Title string and Author string. The first row printed {ID:1 Title:Notes Author:Ada}.

This is the right shape for a query built for one view: a list page that shows a title and an author name. The as author alias is how you name the field, since both tables have a column called name, and sqlc will not generate two fields with one name. Without an alias on one of them it adds a numeric suffix (Name_2), which is legal and unreadable.

The cost is reuse. FlatJoinRow is not a Book, so a function that takes a Book cannot take it, and you copy fields across by hand.

Embedding whole models

Embedded selects sqlc.embed(books), sqlc.embed(authors). The result type was EmbeddedRow, with two fields: a Book and an Author, the same Book and Author that GetBook and GetAuthor return. The first row printed Book {ID:1 AuthorID:1 PublisherID:{Int32:1 Valid:true} Title:Notes}, Author {ID:1 Name:Ada}.

That is the clean version. A function that renders an author takes an Author, and the join result gives you one without copying. When the schema adds a column, both the model and the join result gain it. The generated SQL still lists every column of both tables explicitly, so the expansion is the same as select * would give but safe against column order.

Embed has limits that come from how sqlc builds the SQL:

• You embed whole tables, not subsets. Selecting only a few columns of authors means writing them out instead.

The left join that failed

EmbeddedLeft embeds books and publishers with left join publishers. It generated without complaint, and calling it failed:

can't scan into dest[4] (col: id): cannot scan NULL into *int32

The book "Engines" has no publisher, so the left join produced NULLs for every publisher column. The embedded Publisher struct has a plain ID int32 field, because publishers.id is not null in the table, and sqlc did not make the embedded fields nullable even though the join can produce NULLs for them. The first row with a publisher would have scanned fine, which is how a bug like this reaches production: every test row had a publisher.

The query compiled and the types looked right. Nothing warned. The lesson is not to trust embed on the nullable side of an outer join.

The fix the lab used is to list the columns:

select books.title, publishers.id as publisher_id, publishers.name as publisher
from books left join publishers on publishers.id = books.publisher_id

For plain columns, sqlc does know that a left join's right side can be NULL, so it generated nullable fields: PublisherID as pgtype.Int4 and Publisher as pgtype.Text. The output shows the book with no publisher as publisher_id {Int32:0 Valid:false}, and the two books with one as Valid:true. Same rows, correct types, at the price of naming the columns.

Two other ways round it, if you need the nested shape:

1. Two queries. Fetch the books, then fetch the publishers for the distinct ids with = any(@ids). It costs a round trip and no cleverness, and it avoids both the NULL problem and wide rows with repeated parent data.
2. Map in Go. Use the explicit column version, then build a *Publisher in a small function that returns nil when PublisherID.Valid is false.

Aggregates

BookCounts selects authors.* and a count(books.id) and groups by author. sqlc reused the table's columns and added an int64 field named Books from the alias, as a BookCountsRow. The output shows all three authors, including Linus with 0 books, because the join is a left one and count of a NULL column is zero.

TitlesPerAuthor uses array_agg(b.title order by b.id)::text[]. The explicit ::text[] cast is what makes the field a []string; without it sqlc cannot tell the element type of the aggregate and falls back to interface{}. The output has Ada with [Notes Engines] and Grace with [Compilers]. Linus is absent because that query uses an inner join.

An aggregate that returns many child rows per parent in one row is often a better answer than a join for one to many data, because the parent columns are not repeated for every child. The same idea with jsonb_agg and json_build_object can produce whole child objects, which the JSON article shows how to type.

Picking a shape

Checklist

• Alias every column that would collide, so field names say something.
• Use sqlc.embed for inner joins only, and test an outer join with a row that has no match.
• Cast aggregates, such as array_agg(...)::text[], so the type is not interface{}.
• Prefer two simple queries to a wide join when the parent row is large and repeated.
• Add a test with an unmatched row for every outer join, as the testing article sets up.

The embed syntax is documented in sqlc's embedding structs how-to, and join semantics in PostgreSQL's table expressions chapter.