How load JSON column as an unmarshalled struct?

[alex-ntousias]

Hello everyone,

First of all congratulations for the awesome library and many thanks for supporting it. We've been using SQLBoiler in our project for more than a year now and it has been a treat!

I have a JSON column in my DB table and I want to generate the SQLBoiler model but instead of the usual types.JSON for that field, I want it to have the actual type that it has when unmarshalled.

For example, let's say we have the following table:

CREATE TABLE configs (
    id character varying NOT NULL,
    display_config jsonb DEFAULT '{}'::jsonb NOT NULL
);

And the following struct for the display configs:

type DisplayConfig struct {
  DisplayHeader bool `json:"display_header"`
  Padding int `json:"padding"`
  ...
}

I would like my generated Configs SQLBoiler struct to be something like the following:

type Config struct {
  ID string `boil:"id" json:"id" toml:"id" yaml:"id"`
  DisplayConfig DisplayConfig `boil:"display_config" json:"display_config" toml:"display_config" yaml:"display_config"`
}

Is there any way to achieve this in SQLBoiler?
If not, is there a specific pattern people usually use to achieve the above?

Many thanks,
Alex

[stephenafamo]

You can do this using in the configuration file. For example:

[[types]]
  [types.match]
    tables = ['users']    # Leave empty to affect all columns with this name
    name = "metadata"    # Name of column to override the type
    nullable = false    # Is it nullable?

  [types.replace]
    type = "customTypes.UserMeta"    # The name of the type to replace with

  [types.imports]
    third_party = ['customTypes "github.com/path/to/custom/types"']    # The package containing your custom type

[stephenafamo]

To be clear, you custom type MUST implement the scanner and valuer interfaces.

For example, for JSON type:

package types

import (
	"bytes"
	"database/sql/driver"
	"encoding/json"
	"fmt"
)

// UserMeta is a structued type for the metadata json field of the User Model
type UserMeta struct {
	IsHappy     bool   `json:"is_happy"`
}

// Value implements the driver Valuer interface.
func (u UserMeta) Value() (driver.Value, error) {
	buf := &bytes.Buffer{}
	err := json.NewEncoder(buf).Encode(u)
	return buf.Bytes(), err
}

// Scan implements the Scanner interface.
func (u *UserMeta) Scan(value interface{}) error {
	var err error

	switch x := value.(type) {
	case string:
		err = json.NewDecoder(bytes.NewBuffer([]byte(x))).Decode(u)
	case []byte:
		err = json.NewDecoder(bytes.NewBuffer(x)).Decode(u)
	case nil:
		return nil

	default:
		err = fmt.Errorf("cannot scan type %T into type UserMeta: %v", value, value)
	}

	return err
}

[alex-ntousias]

Thank you very much @stephenafamo , it worked like a charm.