SQLC generates different Go types for JSON depending on the db driver

[GetPsyched]

Version

1.21.0

What happened?

Using lib/pq as my preferred db driver for my PostgreSQL db, SQLC generated json.RawMessage types within a struct for column X in a SELECT query. After switching to pgx, it generates []byte for the same columns. This breaks my APIs as it sends malformed data which needs to be post processed if I want to fix it that way.

Relevant log output

No response

Database schema

No response

SQL queries

SELECT
    club.name,
    COALESCE(club.alias, '') AS alias,
    club.category,
    club.short_description,
    club.email,
    club.is_official,
    COALESCE(JSONB_BUILD_OBJECT(
        'about_us', cd.about_us,
        'why_us', cd.why_us,
        'role_of_sophomore', cd.role_of_soph,
        'role_of_junior', cd.role_of_junior,
        'role_of_senior', cd.role_of_senior
    ), '{}')::JSONB AS description,
    (
        SELECT
            COALESCE(JSONB_AGG(JSONB_BUILD_OBJECT(
                'position', club_member.position,
                'roll', s.roll_number,
                'name', s.name,
                'phone', s.mobile,
                'email', s.email
            ) ORDER BY s.name), '[]')::JSONB
        FROM
            student AS s
            JOIN club_member ON s.roll_number = club_member.roll_number AND club.name = club_member.club_name
        WHERE
            s.roll_number = ANY(SELECT roll_number FROM club_member WHERE club_name = club.name AND position != 'Member')
    ) AS admins,
    cd.branch,
    (
        SELECT
            COALESCE(JSONB_AGG(JSONB_BUILD_OBJECT('name', f.name, 'phone', f.mobile) ORDER BY f.name), '[]')::JSONB
        FROM
            faculty AS f
        JOIN club_faculty AS cf ON f.emp_id = cf.emp_id
        WHERE
            cf.club_name = club.name
    ) AS faculties,
    (
        SELECT
            COALESCE(JSONB_AGG(JSONB_BUILD_OBJECT('platform', cs.platform_type, 'link', cs.link) ORDER BY cs.platform_type), '[]')::JSONB
        FROM
            club_social AS cs
        WHERE
            cs.club_name = club.name
    ) AS socials
FROM
    club
JOIN
    club_details AS cd
    ON club.name = cd.club_name
WHERE
    club.name = $1
    OR club.alias = $1;

Configuration

version: 2
sql:
  - engine: "postgresql"
    queries:
      - "./database/queries"
      - "./database/queries/club"
    schema:
      # These are listed separately so that the tables are created in a preferred order
      - "./database/schemas/student.sql"
      - "./database/schemas/faculty.sql"
      - "./database/schemas/guild.sql"
      - "./database/schemas/club.sql"
      - "./database/schemas/course.sql"
      - "./database/schemas/announcement.sql"
    gen:
      go:
        package: "database"
        out: ".sqlc-auto-gen"
        sql_package: "pgx/v5"
        emit_json_tags: true
        json_tags_case_style: "snake"
    database:
      uri: postgresql://{PGUSER}:{PGPASSWORD}@{PGHOST}:{PGPORT}/{PGDATABASE}

Playground URL

No response

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[GetPsyched]

The problem occurs in the columns with the ::JSONB type cast.

Struct that SQLC generated with lib/pq: (this is correct and works)

type GetClubRow struct {
    Name             string          `json:"name"`
    Alias            string          `json:"alias"`
    Category         string          `json:"category"`
    ShortDescription string          `json:"short_description"`
    Email            string          `json:"email"`
    IsOfficial       bool            `json:"is_official"`
    Description      json.RawMessage `json:"description"`
    Admins           json.RawMessage `json:"admins"`
    Branch           []string        `json:"branch"`
    Faculties        json.RawMessage `json:"faculties"`
    Socials          json.RawMessage `json:"socials"`
}

Struct that SQLC generated with pgx: (this breaks my API)

type GetClubRow struct {
    Name             string   `json:"name"`
    Alias            string   `json:"alias"`
    Category         string   `json:"category"`
    ShortDescription string   `json:"short_description"`
    Email            string   `json:"email"`
    IsOfficial       bool     `json:"is_official"`
    Description      []byte   `json:"description"`
    Admins           []byte   `json:"admins"`
    Branch           []string `json:"branch"`
    Faculties        []byte   `json:"faculties"`
    Socials          []byte   `json:"socials"`
}

[andrewmbenton]

Some differences between drivers are expected. I'm not sure if this particular difference is "expected" or not, but it would be a significant breaking change to alter the types for pgx users in this case.

I think you can use an override for the jsonb type though, which would be an easy solution. Here's a playground URL with a working example pulled from a portion of your query: https://play.sqlc.dev/p/4dbf9b25f695e6ab72f56e3764f96d29a82450176fccf0f32ca47f5ea8b94a47

Here's the config extracted for convenience:

version: 2
sql:
  - engine: postgresql
    queries: query.sql
    schema: query.sql
    gen:
      go:
        package: "db"
        out: "."
        sql_package: "pgx/v5"
        overrides:
          - db_type: jsonb
            go_type:
              import: "encoding/json"
              type: "RawMessage"
          - db_type: jsonb
            go_type:
              import: "encoding/json"
              type: "RawMessage"
            nullable: true

[GetPsyched]

Hey, @andrewmbenton, thanks a lot for the config! It almost works. Unfortunately, it's trying to import json.RawMessage from some json lib rather than encoding/json from where it actually is. Do you know of a work-around for this? Sorry, I'm not versed enough in SQLC configuration.

[andrewmbenton]

Unfortunately, it's trying to import json.RawMessage from some json lib rather than encoding/json from where it actually is.

Oh yeah sorry, that was me being a little too hasty. I'll edit my original reply with a corrected config and playground link.

[GetPsyched]

Thanks a ton for the help! You've ended a lot of frustration on my end haha. Switching to pgx had broken my APIs that I had to rollback. I think I can finally move forward now :)