Spoiler for Solution

Using custom struct for JSONB Type in SQLC

Before reading this content, I highly recommend you follow the Using SQLC for ORM alternative as prior reading material. This note will continues where we left of that part.

How SQLC Handles JSONB Types By Default

It’s common for any models to have metadata column that may contains any form of data that may related to our models but not classifiable:

CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name varchar NOT NULL,
    description TEXT,
    slug varchar NOT NULL,
    public_metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    private_metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
);

In this example we have public_metadata and private_metadata. By default, sqlc will assign those jsonb to a byte array:

type Product struct {
    ID int64
    Name string
    Description pgtype.Text
    Slug string
    PublicMetadata []byte
    PrivateMetadata []byte
}

This might be alright, but sometimes we want to at least have some structure to it. For example, we know that the metadata format will always be consist of type map[string]string or even slightly more abstract map[string]interface{}.

Only having []byte will requires us to cast and convert the format.

How to Add Type definition for JSONB

To solve that issue, we can do following things. First we may define our new type that we want that value to be assigned on:

package typeext

// in this example I want to assign it to map[string]interface{}
type JSONB map[string]interface{}

// You can put any kind of type tho, including struct
type CustomParams struct{
    Params1 string
    Params2 int64
}

Then we can just put it in the sqlc.yaml inside the gen: go: segment:

gen:
    go:
        package: "db"
        sql_package: "pgx/v5"
        out: "db"
        overrides:
            - column: "products.public_metadata"
              go_type:
                  # for import, package and type please refer the go code above
                  import: "github.com/muhwyndhamhp/material-go/utils/typeext"
                  package: "typeext"
                  # put your type here, 
                  # if you want struct instead, put the CustomParams
                  type: "JSONB"
            - column: "products.private_metadata"
              go_type:
                  import: "github.com/muhwyndhamhp/material-go/utils/typeext"
                  package: "typeext"
                  type: "JSONB"

and re generate via:

sqlc generate

It will change the product struct into:

type Product struct {
    ID int64
    Name string
    Description pgtype.Text
    Slug string
    PublicMetadata typeext.JSONB
    PrivateMetadata typeext.JSONB
}

And that’s it!

By the way, this solution does not only constrained on JSONB, but basically any data type, though it is most useful for JSONB type.

Thanks for reading!!