CODEWITHSUNDEEP
gogo-gormsqlx
Mad Wombat
Mad Wombat

Reputation: 15125

Optimizing SQL data access in go

I am trying to switch an API implementation I have been working on from GORM ORM library to SQLx to make data access more efficient. In particular, I am trying to get rid of some SELECT N+1 problems. So, I have a one to many relationship where a site has posts. The API I am implementing returns a list of sites as a JSON object and each site has a nested posts list. The structure looks somewhat like this

{
    "sites": [
        {
            "id": 1,
            "name": "Site #1",
            "posts" [
                {"title": "Post #1", "published": "1/2/2000", ... },
                {"title": "Post #2", "published": "1/3/2000", ... },
                ... more posts ...
            ]
        },
        {
            "id": 2,
            "name": "Site #2",
            "posts": [
                 ... post list for site #2 ...
            ]
        }
        ... more sites ...
   ]
}

This was easy to implement in GORM, but once I looked at the SQL GORM was running to implement this, I realized that it was doing a SELECT from posts for every site in the list. So I am trying to use SQL like this to avoid the N+1 problem.

SELECT s.id, s.name, p.title, p.published 
FROM sites as s, posts as p 
WHERE p.site_id = s.id

This gets me all the data I need in a single query. But I am somewhat stuck on how to scan all of this into a list of site structs. In GORM, I had following structs defined (simplified for brevity)

type struct Post {
    Id        uint      `json:"-"`
    Title     string
    Published time.Time
    SiteId    uint      `json:"-"`
    Site      Site      `json:"-"`
}

type struct Site {
    Id   uint
    Name string
}

And then I would do something like

var sites []Site
result := db.Preload('Posts').Find(&sites)
if result.Error != nil {
    ... error handling ...
} else {
   operate on sites here
}

So the question is, how do I scan my new SQL using SQLx into a slice of structs in a way that results in a similar data structure that GORM produced? I don't mind writing my own scanner, but I still want to be able to use SQLx Select() and Get() methods. What do I need to do to make this work?

var sites []Site
err := db.Select(query, &sites) // where query is SQL from above

Edit: it seems that if I do the exact code I present in this question, GORM doesn't actually do N+1 selects, it runs two queries, one simple SELECT for sites and one SELECT ... WHERE ... IN ... for posts and then collates the two result sets. I still want to know how to do this in SQLx though.

Upvotes: 2

Views: 1190

Answers (1)

kenfire
kenfire

Reputation: 1355

This might not be an answer but too long for a comment.

If you are still using GORM you could have created a custom SQL. See documentation: http://jinzhu.me/gorm/advanced.html#sql-builder

For you it could be something like this: 

// Scan

type struct Post {
    Id        uint      `json:"-"`
    Title     string
    SiteId    uint      `json:"-"`
    Site      Site      `json:"-"`
}

var result Post

db.Raw("
SELECT s.id, s.name, p.title, p.published 
FROM sites as s, posts as p 
WHERE p.site_id = s.id").Scan(&result)

Upvotes: 0

Related Questions