Storing & retrieving PostgreSQL JSON data using Gorp
Enabling JSON data using Go and gorp
JSON — yeahhh,, PostgreSQL has a really good support for this type. With this type, user can store a key-value pair, even in a hierarchical model. Currently I code using Go and use gorp to interact with the database. Gorp make it easy to insert & get data from the database. Until a few days ago I found problem when querying the table that has a JSON column type. Everything was fail — insert & get were not working. After spending several hours to find out how to solve it, finally it’s done. Here’s my summary.
At the time of this writing, I’m working at the largest social crowdfunding startup in Indonesia — Kitabisa.com. Recently I was given a reponsibility to develop new feature to enable user to donate a certain amount of donation and distribute it to several categories. So, my approach was to map user to certain category by using json data and store it into PostgreSQL. In a simple way (means this is not our actual implementation), my DDL and DML will be like this:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE IF NOT EXISTS plans (
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id int NOT NULL,
campaigns json NOT NULL DEFAULT '{"data": []}',
created_at timestamp with time zone NOT NULL DEFAULT now(),
updated_at timestamp with time zone NOT NULL DEFAULT now()
);-- generate dummy data
INSERT INTO public.plans (user_id, campaigns)
VALUES (60, '{"data":[{"category_id":2,"net_amount":120000},{"category_id":4,"net_amount":60000}]}');
and so do the model:
type Plan struct {
ID string `db:"id, primarykey" json:"id"`
UserID int64 `db:"user_id" json:"user_id"`
Campaigns Campaign `db:"campaigns" json:"campaigns"`
CreatedAt time.Time `db:"created_at" json:"created_at"`
UpdatedAt time.Time `db:"updated_at" json:"updated_at"`
}type Campaign struct {
Data []CampaignItem `json:"data"`
}type CampaignItem struct {
CategoryID int `json:"category_id"`
NetAmount float64 `json:"net_amount"`
}
The campaigns column will save the data in JSON format that has been defined in the model. The sample data should be like this:
{
"data": [
{
"category_id": 2,
"net_amount": 100000
},
{
"category_id": 4,
"net_amount": 50000
}
]
}Campaign data consist of 2 items: Rp 100.000 for category_id 2 and RP 50.000 for category_id 4.
Then we create one function to initialize db connection:
const (
host = "localhost"
port = 5432
username = "postgres"
password = "my-secret-pw"
dbName = "test"
)func InitDB() (*gorp.DbMap, error) {
db, err := sql.Open("postgres", fmt.Sprintf("host=%s port=%d user=%s dbname=%s password=%s sslmode=disable", host, port, username, dbName, password))
if err != nil {
return nil, err
} err = db.Ping()
if err != nil {
return nil, err
} dbMap := &gorp.DbMap{
Db: db,
Dialect: gorp.PostgresDialect{},
} dbMap.AddTableWithName(Plan{}, "plans").SetKeys(true, "ID")
return dbMap, nil
}
and one repository to interact with database. The functionalities should be simple — only insert and get.
type PlanRepo struct {
DBMap *gorp.DbMap
}func NewPlanRepo(d *gorp.DbMap) *PlanRepo {
return &PlanRepo{
DBMap: d,
}
}func (p *PlanRepo) Insert(data Plan) error {
return p.DBMap.Insert(&data)
}func (p *PlanRepo) Get() (data []Plan, err error) {
_, err = p.DBMap.Select(&data, "SELECT * FROM plans")
return
}
Now after the functionalities are done, lets jump into the implementation. It should call InitDB() to create database connection and then pass it to NewPlan() in the repository. We have these two functions in the main.go file — insert() that will insert dummy data to the database and printData() which will retrieve data from the database and print them to the console.
func insertData(repo *PlanRepo) {
data := Plan{
UserID: 13,
Campaigns: Campaign{
Data: []CampaignItem{
{
CategoryID: 2,
NetAmount: 100000.0,
},
{
CategoryID: 4,
NetAmount: 50000.0,
},
},
},
CreatedAt: time.Now(),
UpdatedAt: time.Now(),
} err := repo.Insert(data)
if err != nil {
log.Fatalln(err.Error())
}
}func printData(repo *PlanRepo) {
planFromDB, err := repo.Get()
if err != nil {
log.Fatalln(err.Error())
} b, err := json.MarshalIndent(planFromDB, "", " ")
if err != nil {
log.Fatalln(err.Error())
}
fmt.Println(string(b))
}func main() {
db, err := InitDB()
if err != nil {
log.Fatalln(err.Error())
}
defer db.Db.Close() planRepo := NewPlanRepo(db)
insertData(planRepo)
printData(planRepo)
}
Now run the program by executing this command in the terminal.
$ go run main.goFailed!
Okay, you will get error instead of success running program. If you try to insert data you will get this kind of error:
$ go run main.go
2020/03/01 13:28:30 sql: converting argument $2 type: unsupported type main.Campaign, a struct
exit status 1and if you try to retrieve data, this error will show up:
$ go run main.go
2020/03/01 13:29:02 sql: Scan error on column index 2, name "campaigns": unsupported Scan, storing driver.Value type []uint8 into type *main.Campaign
exit status 1Why is this happen? It’s because gorp does not support struct as a data type in its default postgres dialect. If you see the source code, gorp postgres dialect only supports primitive data type, slice, and time. This really pissed me off because it made me think that I cannot use postgre JSON by using this gorp.
After searching for the solution, finally I got help from one of the very old closed issue in the repo. You can see it here https://github.com/go-gorp/gorp/issues/142. (I don’t know why gorp doesn’t put this thing in its readme file or at least gives us reference link to this issue). Fortunately gorp provides us TypeConverter which can we use to transform data from one type to another type.
So now we need to create our own custom type converter which implements ToDb() and FromDb() functions and handles how it transforms our Campaign struct as a json.
type CustomTypeConverter struct{}func (t CustomTypeConverter) ToDb(val interface{}) (interface{}, error) {
switch t := val.(type) {
case Campaign:
b, err := json.Marshal(t)
if err != nil {
return "", err
}
return string(b), nil
}
return val, nil
}func (t CustomTypeConverter) FromDb(target interface{}) (gorp.CustomScanner, bool) {
switch target.(type) {
case *Campaign:
binder := func(holder, target interface{}) error {
s, ok := holder.(*string)
if !ok {
return errors.New("FromDb: Unable to convert Plan entry to *string")
}
b := []byte(*s)
return json.Unmarshal(b, target)
}
return gorp.CustomScanner{new(string), target, binder}, true
}
return gorp.CustomScanner{}, false
}
After that, let’s use this type converter in our code. We will put CustomTypeConverter{} in our gorp initialization to make it global to our database connection. It should be like this:
dbMap := &gorp.DbMap{
Db: db,
Dialect: gorp.PostgresDialect{},
TypeConverter: model.CustomTypeConverter{},
}Okay everything looks fine. Now it’s time to run our program again and everything should be working properly. If nothing is error, you should see that our program will first insert new data to the database and then try to get data from the database and print them like this:
$ go run main.go
[
{
"id": "4ecaa342-d6f4-43cc-9724-c92f78ca4e6a",
"user_id": 60,
"campaigns": {
"data": [
{
"category_id": 2,
"net_amount": 120000
},
{
"category_id": 4,
"net_amount": 60000
}
]
},
"created_at": "2020-03-01T06:19:54.761734Z",
"updated_at": "2020-03-01T06:19:54.761734Z"
},
{
"id": "4cc74c66-bff2-46f0-808f-599938aac2db",
"user_id": 13,
"campaigns": {
"data": [
{
"category_id": 2,
"net_amount": 100000
},
{
"category_id": 4,
"net_amount": 50000
}
]
},
"created_at": "2020-03-01T06:32:34.913764Z",
"updated_at": "2020-03-01T06:32:34.913764Z"
}
]If you look into the database, you will see that the data is inserted successfully.
I hope that this will be useful for you. If you have any other solution (or maybe even better), please feel free to leave a comment.
