Go, gorm, nested JSON and associations
gorm provides a clean way to store a nested JSON with the relation of associations among tables. The following code creates a DB with three levels of nesting.
package main
import (
"encoding/json"
"fmt"
"github.com/jinzhu/gorm"
_ "github.com/lib/pq"
)
const (
host = "localhost"
port = 5432
user = "postgres"
password = ""
dbname = "postgres"
)
type Page struct {
ID int64 `sql:"auto_increment" json:"-"`
Number int64 `json:"number"`
Book Book `gorm:"foreignkey:book_id" json:"-"`
BookID int64 `json:"book_id"`
Text string `json:"text"`
}
type Book struct {
ID int64 `sql:"auto_increment" json:"-"`
ShelfPlace int64 `json:"shelf_place"`
Shelf Shelf `gorm:"foreignkey:shelf_id" json:"-"`
ShelfID int64 `json:"shelf_id"`
Author string `json:"author" gorm:"unique;not null"`
Publisher string `json:"publisher"`
PagesAmount int64 `json:"pages_amount"`
Pages []Page `json:"pages"`
}
type Shelf struct {
ID int64 `sql:"auto_increment" json:"-"`
Number int64 `json:"number"`
BooksAmount int64 `json:"books_amount"`
Book []Book `json:"books"`
}
func main() {
psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
"password=%s dbname=%s sslmode=disable",
host, port, user, password, dbname)
db, err := gorm.Open("postgres", psqlInfo)
if err != nil {
panic(err)
}
defer db.Close()
// Create
//db.Create(&Shelf{
record := `{
"number": 1,
"books": [
{
"shelf_place": 5,
"author": "Lewis Carroll",
"publisher": "EA",
"pages_amount": 2,
"pages": [
{
"number": 2,
"text": "lorem ipsum"
},
{
"number": 4,
"text": "dolor sit amet"
}
]
},
{
"shelf_place": 7,
"author": "Mark Twain",
"publisher": "Activision",
"pages_amount": 3,
"pages": [
{
"number": 1,
"text": "this is"
},
{
"number": 3,
"text": "a test"
},
{
"number": 6,
"text": "of json"
}
]
}
]
}`
var shelf Shelf
err = json.Unmarshal([]byte(record), &shelf)
fmt.Printf("err=%v\n", err)
db.DropTableIfExists(&Shelf{})
db.DropTableIfExists(&Page{})
db.DropTableIfExists(&Book{})
// Migrate the schema
db.AutoMigrate(&Shelf{})
db.AutoMigrate(&Page{})
db.AutoMigrate(&Book{})
db.Create(&shelf)
// Fails because author is a unique attribute
//db.Create(&shelf)
// Preload is necessary to query nested structure.
db.Preload("Book").Where("author = ?", "Mark Twain").Find(&shelf)
fmt.Printf("shelf=%v", shelf)
}
References
- https://stackoverflow.com/questions/57367780/deep-nested-structures-in-go-gorm
- https://gorm.io/docs/preload.html
- https://gorm.io/docs/belongs_to.html
- https://www.sohamkamani.com/blog/2017/10/18/parsing-json-in-golang/
- https://gorm.io/docs/connecting_to_the_database.html
Written with StackEdit.