JSONB fields are scanned as []byte and serialized to Base64 in JSON, not as objects

[sanbei101]

Problem Description

When using sqlc with PostgreSQL and pgx/v5, columns of type JSONB are generated as []byte in the Go model. This causes standard JSON marshaling (e.g., via Gin or encoding/json) to encode the field as a Base64 string instead of the expected raw JSON object.

For example, a spec_template JSONB NOT NULL column becomes:

type Category struct {
    SpecTemplate []byte `json:"spec_template"`
}

When serialized:

{
  "spec_template": "eyJuYW1lIjogInRlc3QifQ=="  // ← Base64, not {"name":"test"}
}

how should i do?
here is all code:

version: "2"
sql:
  - engine: "postgresql"
    queries:
      - "query.sql"
    schema:
      - "schema.sql"
    gen:
      go:
        package: "model"
        out: "model"
        sql_package: "pgx/v5"

CREATE TABLE category (
    id             BIGSERIAL PRIMARY KEY,
    name           VARCHAR(100) NOT NULL,
    parent_id      BIGINT REFERENCES category(id) DEFAULT NULL,
    spec_template  JSONB NOT NULL,
    sort           INT NOT NULL DEFAULT 0,
    created_at     TIMESTAMP NOT NULL DEFAULT NOW()
);

const listCategories = `-- name: ListCategories :many
SELECT id, name, parent_id, spec_template, sort, created_at FROM category 
ORDER BY sort ASC, created_at DESC
`

func (q *Queries) ListCategories(ctx context.Context) ([]Category, error) {
	rows, err := q.db.Query(ctx, listCategories)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []Category
	for rows.Next() {
		var i Category
		if err := rows.Scan(
			&i.ID,
			&i.Name,
			&i.ParentID,
			&i.SpecTemplate,
			&i.Sort,
			&i.CreatedAt,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

        {

            "SpecTemplate": "eyLlhoXlrZgiOiBbIjRHQiIsICI2R0IiLCAiOEdCIiwgIjEyR0IiLCAiMTZHQiJdLCAi5a2Y5YKoIjogWyI2NEdCIiwgIjEyOEdCIiwgIjI1NkdCIiwgIjUxMkdCIiwgIjFUQiJdLCAi6aKc6ImyIjogWyLpu5HoibIiLCAi55m96ImyIiwgIuiTneiJsiIsICLnuqLoibIiLCAi6YeR6ImyIl19",
        },

[adilburaksen]

Use json.RawMessage instead of []byte for JSONB fields. json.RawMessage is []byte under the hood but implements json.Marshaler to output raw JSON rather than Base64.

Override the type mapping in sqlc.yaml:

version: "2"
sql:
  - engine: "postgresql"
    queries: ["query.sql"]
    schema: ["schema.sql"]
    gen:
      go:
        package: "model"
        out: "model"
        sql_package: "pgx/v5"
        overrides:
          - db_type: "jsonb"
            go_type: "encoding/json.RawMessage"

After regenerating, the field becomes:

type Category struct {
    SpecTemplate json.RawMessage `json:"spec_template"`
}

json.RawMessage marshals directly as the raw JSON value (object, array, etc.) without base64 encoding, and it scans correctly from PostgreSQL JSONB columns.

If you need the field to be nullable, use *json.RawMessage or pgtype.Text depending on your preference.

For pgx/v5 specifically, you can also use pgtype.JSONB or just stick with json.RawMessage — both work for scanning JSONB. The json.RawMessage override is the simplest and most idiomatic for API responses.