[SOLVED] Structuring Nested Data with sqlc, PostgreSQL, and Golang

Structuring Nested Data with sqlc, PostgreSQL, and Golang

I am using sqlc with a PostgreSQL database in a Golang project. My database has the following simplified schema:

CREATE TABLE users (
  username VARCHAR(255) PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE flats (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE user_flats (
  id SERIAL PRIMARY KEY,
  username VARCHAR(255) NOT NULL REFERENCES users(username),
  flat_id INTEGER NOT NULL REFERENCES flats(id)
);

I need to retrieve flat details with an array of associated users given a flat ID, intending to return a Go struct like this:

type FlatDetails struct {
    FlatID   int32
    FlatName string
    Users    []User
}

However, when using an SQL query to join flats, user_flats, and users tables, sqlc generates a Go struct without a nested User slice, treating each user field as an individual field. Here is an example join

SELECT 
  f.id AS flat_id,
  f.name AS flat_name,
  u.username,
  u.email
FROM
  flats f
JOIN 
  user_flats uf ON f.id = uf.flat_id
JOIN 
  users u ON uf.username = u.username
WHERE 
  f.id = $1;

While the current approach works by manually mapping the fields to the Go struct, it becomes quite complicated and inefficient with more complex joins and larger tables. Is there a more efficient or idiomatic way to handle this scenario using sqlc, where I can directly map the query result to a struct with nested slices or arrays? Any pointers or recommendations are highly appreciated!

Solution

If anyone is still seeking an answer, you should use sqlc.embed

For example


-- name: GetFlatWithUsers :many
SELECT 
  sqlc.embed(f),
  sqlc.embed(u)
FROM
  flats f
JOIN 
  user_flats uf ON f.id = uf.flat_id
JOIN 
  users u ON uf.username = u.username
WHERE 
  f.id = $1;