Unclear on expected usage of pgtype JSONB

[adamdevigili]

Trying to transition an existing project over to using sqlc, and all was well until I needed to use a jsonb type using pgx/pgtype. I've been unable to find a standard/doc on how to use it properly. I'm fairly new to SQL DBs, so trying to learn the "right" way to do things first..

sqlc.yaml

version: 1
packages:
  - path: "pkg/models"
    engine: "postgresql"
    schema: "sql/schema/"
    queries: "sql/queries/"
    sql_package: "pgx/v4"
    emit_json_tags: true
    emit_exported_queries: true

sql/schema/player.sql

CREATE TABLE IF NOT EXISTS player (
  ...
  skills jsonb
);

pkg/models/player.sql.go

type Player struct {
        ...
	Skills    pgtype.JSONB `json:"skills"`
}

I've gotten it to work by defining an internal struct and using the Set() method on the JSONB type..

player.go

type skillJSONB struct {
    Name string `json:"name"`
    Value int16 `json:"value"`
}

p := models.Player{}
p.Skills.Set(skillJSONB {
    Name: "foo",
    Value: 1,
})

And then I copy over the field to the CreatePlayerParams struct..

return &models.CreatePlayerParams {
    ...
    Skills: p.Skills,
}

Is this the expected way to be handling this? I saw on #819 that a user is using the overrides directive and implementing the Scanner interface, but I'd like to avoid that if there's a better way.

[kyleconroy]

You're using the pgx / jsonb support the correct way. Using overrides is only necessary if you want to map the Skills column to a specific Go type.