moesef moesef - 7 months ago 22
SQL Question

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?

Answer

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

doc for JSONText