Efficiently mapping one-to-many many-to-many database to struct in Golang
the sql in postgres :
create schema temp;
set search_path = temp;
create table item
(
id INT generated by default as identity primary key
);
create table tag
(
id INT generated by default as identity primary key,
name VARCHAR(160),
item_id INT references item (id)
);
create view item_tags as
select id,
(
select
array_to_json(array_agg(row_to_json(taglist.*))) as array_to_json
from (
select tag.name, tag.id
from tag
where item_id = item.id
) taglist ) as tags
from item ;
-- golang query this maybe
select row_to_json(row)
from (
select * from item_tags
) row;
then golang query this sql:
select row_to_json(row)
from (
select * from item_tags
) row;
and unmarshall to go struct:
pro:
postgres manage the relation of data. add / update data with sql functions.
golang manage business model and logic.
it's easy way.
.
Use one struct for multiple SQL queries
Answer taken from here: https://go.dev/doc/database/querying#multiple_rows
Using the Scan method you can assign populate one variable for every query Column of the query result.
Note: the values not populated by scan(), will default to the zero value of the field type.
type S1 struct {
X int
Y int
Z string
}
func run() {
rows, err := db.Queryx("SELECT X,Y,Z FROM SomeTable")
for rows.Next() {
var p S1
err = rows.Scan(&S1.X, &S1.Y, &S1.Z)
}
rows, err := db.Queryx("SELECT Y,Z FROM SomeTable")
for rows.Next() {
var p S1
err = rows.Scan(&S1.Y, &S1.Z)
}
rows, err := db.Queryx("SELECT X,Y FROM SomeTable")
for rows.Next() {
var p S1
err = rows.Scan(&S1.X, &S1.Y)
}
}
Retrieve relation one to many into JSON sql pure, Golang, Performance
1) Bad about this would be the sql request per iteration. So here a solution that does not make an extra request per Publisher:
func (r *PublisherRepository) GetAllPublishers() []*Publisher {
sql := "SELECT * FROM publishers"
ps := make(map[int]*Publisher)
rows, err := connection.Query(sql)
if err != nil {
// log
}
for rows.Next() {
p := &Publisher{}
rows.Scan(&p.ID,&p.Name)
ps[p.ID] = p
}
sql = "SELECT * FROM books"
rows, err := connection.Query(sql)
if err != nil {
//log
}
for rows.Next() {
b := &Book{}
rows.Scan(&b.ID, &b.Name, &b.PublisherID)
ps[b.PublisherID].Books = append(ps[b.PublisherID].Books, b)
}
// you might choose to keep the map as a return value, but otherwise:
// preallocate memory for the slice
publishers := make([]*Publisher, 0, len(ps))
for _, p := range ps {
publishers = append(publishers, p)
}
return publishers
}
2) Unless you create the PublisherRepository only once, this might be a bad idea creating and closing loads of connections. Depending also on your sql client implementation I would suggest (and also have seen it for many other go database clients) to have one connection for the entire server. Pooling is done internally by many of the sql clients, that is why you should check your sql client.
If your sql client library does pooling internally use a global variable for the "connection" (it's not really one connection if pooling is done internally):
connection *sql.DB
func New () *PublisherRepository {
repo := &PublisherRepository{}
return repo.connect()
}
type PublisherRepository struct{
}
func (r *PublisherRepository) connect() *PublisherRepository {
// open new connection if connection is nil
// or not open (if there is such a state)
// you can also check "once.Do" if that suits your needs better
if connection == nil {
// ...
}
return r
}
So each time you create a new PublisherRepository, it will only check if connection already exists. If you use once.Do, go will only create the "connection" once and you are done with it.
If you have other structs that will use the connection as well, you need a global place for your connection variable or (even better) you write a little wrapper package for your sql client, that is in turn used in all your structs.
Golang Gorm one-to-many with has-one
As described in this issue, gorm is not designed to use joins to preload other structs values. If you would like to continue to use gorm and have the ability to use joins to load values, one must use the SQL Builder exposed in gorm, and write some code to scan the desired values.
This would become burdensome if there are numerous tables that have to be accounted for.
If xorm is available as an option, they support loading struct values. Described under the find bullet point, here.
Note: I did not scan all the fields, just enough to get the point across.
EXAMPLE:
package main
import (
"log"
"github.com/jinzhu/gorm"
_ "github.com/jinzhu/gorm/dialects/sqlite"
"github.com/kylelemons/godebug/pretty"
)
// Order
type Order struct {
gorm.Model
Status string
OrderItems []OrderItem
}
// Order line item
type OrderItem struct {
gorm.Model
OrderID uint
ItemID uint
Item Item
Quantity int
}
// Product
type Item struct {
gorm.Model
ItemName string
Amount float32
}
var (
items = []Item{
{ItemName: "Go Mug", Amount: 12.49},
{ItemName: "Go Keychain", Amount: 6.95},
{ItemName: "Go Tshirt", Amount: 17.99},
}
)
func main() {
db, err := gorm.Open("sqlite3", "/tmp/gorm.db")
db.LogMode(true)
if err != nil {
log.Panic(err)
}
defer db.Close()
// Migrate the schema
db.AutoMigrate(&OrderItem{}, &Order{}, &Item{})
// Create Items
for index := range items {
db.Create(&items[index])
}
order := Order{Status: "pending"}
db.Create(&order)
item1 := OrderItem{OrderID: order.ID, ItemID: items[0].ID, Quantity: 1}
item2 := OrderItem{OrderID: order.ID, ItemID: items[1].ID, Quantity: 4}
db.Create(&item1)
db.Create(&item2)
// Query with joins
rows, err := db.Table("orders").Where("orders.id = ? and status = ?", order.ID, "pending").
Joins("Join order_items on order_items.order_id = orders.id").
Joins("Join items on items.id = order_items.id").
Select("orders.id, orders.status, order_items.order_id, order_items.item_id, order_items.quantity" +
", items.item_name, items.amount").Rows()
if err != nil {
log.Panic(err)
}
defer rows.Close()
// Values to load into
newOrder := &Order{}
newOrder.OrderItems = make([]OrderItem, 0)
for rows.Next() {
orderItem := OrderItem{}
item := Item{}
err = rows.Scan(&newOrder.ID, &newOrder.Status, &orderItem.OrderID, &orderItem.ItemID, &orderItem.Quantity, &item.ItemName, &item.Amount)
if err != nil {
log.Panic(err)
}
orderItem.Item = item
newOrder.OrderItems = append(newOrder.OrderItems, orderItem)
}
log.Print(pretty.Sprint(newOrder))
}
Output:
/tmp/main.go.go:55)
[2018-06-18 18:33:59] [0.74ms] INSERT INTO "items" ("created_at","updated_at","deleted_at","item_name","amount") VALUES ('2018-06-18 18:33:59','2018-06-18 18:33:59',NULL,'Go Mug','12.49')
[1 rows affected or returned ]
(/tmp/main.go.go:55)
[2018-06-18 18:33:59] [0.50ms] INSERT INTO "items" ("created_at","updated_at","deleted_at","item_name","amount") VALUES ('2018-06-18 18:33:59','2018-06-18 18:33:59',NULL,'Go Keychain','6.95')
[1 rows affected or returned ]
(/tmp/main.go.go:55)
[2018-06-18 18:33:59] [0.65ms] INSERT INTO "items" ("created_at","updated_at","deleted_at","item_name","amount") VALUES ('2018-06-18 18:33:59','2018-06-18 18:33:59',NULL,'Go Tshirt','17.99')
[1 rows affected or returned ]
(/tmp/main.go.go:58)
[2018-06-18 18:33:59] [0.71ms] INSERT INTO "orders" ("created_at","updated_at","deleted_at","status") VALUES ('2018-06-18 18:33:59','2018-06-18 18:33:59',NULL,'pending')
[1 rows affected or returned ]
(/tmp/main.go.go:61)
[2018-06-18 18:33:59] [0.62ms] INSERT INTO "order_items" ("created_at","updated_at","deleted_at","order_id","item_id","quantity") VALUES ('2018-06-18 18:33:59','2018-06-18 18:33:59',NULL,'49','145','1')
[1 rows affected or returned ]
(/tmp/main.go.go:62)
[2018-06-18 18:33:59] [0.45ms] INSERT INTO "order_items" ("created_at","updated_at","deleted_at","order_id","item_id","quantity") VALUES ('2018-06-18 18:33:59','2018-06-18 18:33:59',NULL,'49','146','4')
[1 rows affected or returned ]
(/tmp/main.go.go:69)
[2018-06-18 18:33:59] [0.23ms] SELECT orders.id, orders.status, order_items.order_id, order_items.item_id, order_items.quantity, items.item_name, items.amount FROM "orders" Join order_items on order_items.order_id = orders.id Join items on items.id = order_items.id WHERE (orders.id = '49' and status = 'pending')
[0 rows affected or returned ]
--- ONLY ONE QUERY WAS USED TO FILL THE STRUCT BELOW
2018/06/18 18:33:59 {Model: {ID: 49,
CreatedAt: 0001-01-01 00:00:00 +0000 UTC,
UpdatedAt: 0001-01-01 00:00:00 +0000 UTC,
DeletedAt: nil},
Status: "pending",
OrderItems: [{Model: {ID: 0,
CreatedAt: 0001-01-01 00:00:00 +0000 UTC,
UpdatedAt: 0001-01-01 00:00:00 +0000 UTC,
DeletedAt: nil},
OrderID: 49,
ItemID: 145,
Item: {Model: {ID: 0,
CreatedAt: 0001-01-01 00:00:00 +0000 UTC,
UpdatedAt: 0001-01-01 00:00:00 +0000 UTC,
DeletedAt: nil},
ItemName: "Go Mug",
Amount: 12.489999771118164},
Quantity: 1},
{Model: {ID: 0,
CreatedAt: 0001-01-01 00:00:00 +0000 UTC,
UpdatedAt: 0001-01-01 00:00:00 +0000 UTC,
DeletedAt: nil},
OrderID: 49,
ItemID: 146,
Item: {Model: {ID: 0,
CreatedAt: 0001-01-01 00:00:00 +0000 UTC,
UpdatedAt: 0001-01-01 00:00:00 +0000 UTC,
DeletedAt: nil},
ItemName: "Go Keychain",
Amount: 6.949999809265137},
Quantity: 4}]}
Related Topics
Select Something That Has More/Less Than X Character
For Each Quarter Between Two Dates, Add Rows Quarter by Quarter in SQL Server
How to Check Type of Value in Postgres
What Is Wrong with a Transitive Dependency
Remove Uniqueness of Index in Postgresql
Returning the Distinct First Character of a Field (Mysql)
Why Is Query with Phone = N'1234' Slower Than Phone = '1234'
Besides a Declarative Language, Is SQL a Functional Language
How to Generate a Random, Unique, Alphanumeric Id of Length N in Postgres 9.6+
SQL - Select Distinct Only on One Column
Pivot with Dynamic Columns in Oracle
Importing .SQL File on Windows to Postgresql
Delete the 'First' Record from a Table in SQL Server, Without a Where Condition