Optimizing SQL data access in go

Question

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.

There is no magic. Just do first request to mysql for sites, then extract IDs using simple loop. Then do second request for posts with WHERE id IN(). Finally, build new data structures using two datasets using simple loops. Just use plain SQL with SQLx API that is pretty well documented. — Alexander R.
I am not expecting magic. I can write a function that will use my single SQL statement and create and fill out Site structures and Post structures. And I assume that I will have to write one. But I want to hide it inside the row scanning process, so at the surface I can use native SQLx APIs. — Mad Wombat

kenfire kenfire · Accepted Answer · 2017-09-28T13:49:13

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)

Optimizing SQL data access in go

1 Answers