Storing Golang JSON into Postgresql

I want to store a certain struct into my database that has a JSON field within it.

type Comp struct {
	CompId               int64           `db:"comp_id" json:"comp_id"`
	StartDate            time.Time       `db:"start_date" json:"start_date"`
	EndDate              time.Time       `db:"end_date" json:"end_date"`
	WeeklySchedule       json.RawMessage `db:"weekly_schedule" json:"weekly_schedule"`
}

The schema for the table is:

CREATE TABLE IF NOT EXISTS Tr.Comp(
	comp_id 			    SERIAL,
	start_date				timestamp NOT NULL,
	end_date				timestamp NOT NULL,
	weekly_schedule			json NOT NULL,
	PRIMARY KEY (comp_id)
);

I am using sqlx and lib/pq driver in my project and the following will not execute. Instead it panics saying there is a nil pointer. DB is a global *sqlx.DB struct

	tx := DB.MustBegin()

    compFixture := Comp{
		StartDate:            time.Now(),
		EndDate:              time.Now().AddDate(1, 0, 0),
		WeeklySchedule:       json.RawMessage([]byte("{}")),
	}
	_, err = tx.NamedExec(
		`INSERT INTO 
			Tr.Comp(comp_id, 
				start_date, end_date, weekly_schedule) 
			VALUES (DEFAULT, 
				:start_date, :end_date, :weekly_schedule)  
			RETURNING comp_id;`, compFixture)
	if err != nil {
		t.Fatal("Error creating fixture.", err)
	}

When I remove weekly_schedule from the schema and fixture things run fine. But for some reason, the when this field is included, the program panics. Any idea as to how I should define the weekly_schedule field in both my DB schema and Go struct?

sqlx has a type JSONText in github.com/jmoiron/sqlx/types that will do what you need

doc for JSONText

I don't know how clean of a solution this is but I ended up making my own data type JSONRaw. The DB driver sees it as a []btye but it can still be treated like a json.RawMessage in the Go Code.

type JSONRaw json.RawMessage

func (j JSONRaw) Value() (driver.Value, error) {
	byteArr := []byte(j)

	return driver.Value(byteArr), nil
}

func (j *JSONRaw) Scan(src interface{}) error {
	asBytes, ok := src.([]byte)
	if !ok {
		return error(errors.New("Scan source was not []bytes"))
	}
	err := json.Unmarshal(asBytes, &j)
	if err != nil {
		return error(errors.New("Scan could not unmarshal to []string"))
	}

	return nil
}

func (m *JSONRaw) MarshalJSON() ([]byte, error) {
	return *m, nil
}

func (m *JSONRaw) UnmarshalJSON(data []byte) error {
	if m == nil {
		return errors.New("json.RawMessage: UnmarshalJSON on nil pointer")
	}
	*m = append((*m)[0:0], data...)
	return nil
}

This is copy paste reimplementation of MarshalJSON and UnmarshalJSON from the encoding/json library.

from the go documentation:

json.RawMessage is a raw encoded JSON object. It implements Marshaler and Unmarshaler and can be used to delay JSON decoding or precompute a JSON encoding.

if you log.Printf("%#", colors) in the example provided at package json json.RawMessage you can see that after unmarshalling the json object 'Point'-member isn't unmarshalled but left in []byte format until the color-format is fixed and 'Point' is unmarshalled explicitly.

Did you try something like unmarshal WeeklySchedule before putting it into the DB?