Nested slice of struct type in sqlc, postgres and golang

Question

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

CREATE TABLE users (
  id SERIAL NOT NULL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  username TEXT NOT NULL UNIQUE,
  password TEXT NOT NULL,
  image TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);


CREATE TABLE posts (
  id SERIAL NOT NULL PRIMARY KEY,
  title TEXT NOT NULL,
  content JSONB,
  subreddit_id INT NOT NULL REFERENCES subreddits(id) ON DELETE CASCADE,
  author_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE votes (
  id SERIAL NOT NULL PRIMARY KEY,
  post_id INT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  type vote_type NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,

  UNIQUE(user_id, post_id)
);

This is the query that i have written so sqlc can generate code from it:

-- name: FindPostWithAuthorAndVotes :one
SELECT posts.*, sqlc.embed(users), sqlc.embed(votes)
FROM posts
  INNER JOIN users ON users.id = posts.author_id
  INNER JOIN votes ON votes.post_id = posts.id
WHERE posts.id = $1;

what i expect to have in the generated type in go code is:

type FindPostWithAuthorAndVotesRow struct {
    ID          int32                `json:"id"`
    Title       string               `json:"title"`
    Content     sql.NullRawMessage   `json:"content"`
    SubredditID int32                `json:"subredditId"`
    AuthorID    int32                `json:"authorId"`
    CreatedAt   time.Time            `json:"createdAt"`
    UpdatedAt   time.Time            `json:"updatedAt"`
    User        User                 `json:"user"`
    Vote        []Vote               `json:"vote"`
}

but what i get is:

type FindPostWithAuthorAndVotesRow struct {
    ID          int32                `json:"id"`
    Title       string               `json:"title"`
    Content     types.NullRawMessage `json:"content"`
    SubredditID int32                `json:"subredditId"`
    AuthorID    int32                `json:"authorId"`
    CreatedAt   time.Time            `json:"createdAt"`
    UpdatedAt   time.Time            `json:"updatedAt"`
    User        User                 `json:"user"`
    Vote        Vote                 `json:"vote"`
}

i want the Vote field's type to be a slice of Vote. but i get it as just Vote struct. using sqlc.embed(votes) is not working. is there a way to make it work?

Answer 1

The reason seems to be that a post may have a single author (this is why it's singular), but there may be multiple votes on the same post, hence the plurality. A way to cope with that would be to have an aggregate table (or view, if that works as well) for voting summary to posts and have a foreign key from posts to this aggregate vote table, ensuring that you have a single aggregate vote. If that can be a view, then you just need a select query to define the view so you get the proper result. Otherwise you may need to maintain the table when votes are cast.

Answer 2

You can achieve this by creating an intermediary view that converts the votes to json. You can then use the overrides to cast this back to a slice of the Vote struct. For example.

CREATE TABLE users (
  id SERIAL NOT NULL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  username TEXT NOT NULL UNIQUE,
  password TEXT NOT NULL,
  image TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE posts (
  id SERIAL NOT NULL PRIMARY KEY,
  title TEXT NOT NULL,
  content JSONB,
  subreddit_id INT NOT NULL REFERENCES subreddits(id) ON DELETE CASCADE,
  author_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE votes (
  id SERIAL NOT NULL PRIMARY KEY,
  post_id INT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  type vote_type NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  UNIQUE(user_id, post_id)
);

CREATE VIEW post_view AS
SELECT
    p.*,
    JSON_AGG(u.*) AS author,
    JSON_AGG(v.*) AS votes
FROM
    posts p
JOIN
    users u ON p.author_id = u.id
JOIN
    votes v ON p.id = v.post_id
WHERE
    p.id = $1
GROUP BY
    p.id, u.id;

This simplifies the query to

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

Now you need to override the types of your post_view columns in your sqlc.(yaml|yml) or sqlc.json file, I use json in this example.

{
  "version": "2",
  "sql": [
    {
      "schema": "schema.sql",
      "queries": "query.sql",
      "engine": "postgresql",
      "gen": {
        "go": {
          "sql_package": "pgx/v5",
          "out": "db",
          "emit_json_tags": true,
          "emit_db_tags": true,
          "overrides": [
             {
              "column": "post_view.content",
              "go_type": {
                "import": "sql",
                "type": "NullRawMessage"
              }
            },
            {
              "column": "post_view.votes",
              "go_type": {
                "type": "Vote",
                "slice": true
              }
            },
            {
              "column": "post_view.author",
              "go_type": {
                "type": "User"
              }
            }            
          ]
        }
      }
    }
  ]
}

This should give you a model like so, with the correct []Vote slice on the PostView struct, along with the User for the Author, etc.

type PostView struct {
    ID          int32              `db:"id" json:"id"`
    Title       string             `db:"title" json:"title"`
    Content     sql.NullRawMessage `db:"content" json:"content"`
    SubredditID int32              `db:"subreddit_id" json:"subreddit_id"`
    AuthorID    int32              `db:"author_id" json:"author_id"`
    CreatedAt   pgtype.Timestamp   `db:"created_at" json:"created_at"`
    UpdatedAt   pgtype.Timestamp   `db:"updated_at" json:"updated_at"`
    Author      User               `db:"author" json:"author"`
    Votes       []Vote             `db:"votes" json:"votes"`
}

I made a working playground for all this here https://play.sqlc.dev/p/024fe40a264802366856de1713086382669e7d88c7bd9f480b4168196a807e29