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!
