title: Optimizing SQL data access in go
body: <p>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 <code>posts</code> list. The structure looks somewhat like this</p>
  
  <pre>
  {
      "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 ...
     ]
  }
  </pre>
  
  <p>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.</p>
  
  <pre>
  SELECT s.id, s.name, p.title, p.published 
  FROM sites as s, posts as p 
  WHERE p.site_id = s.id
  </pre>
  
  <p>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)</p>
  
  <pre>
  type struct Post {
      Id        uint      `json:"-"`
      Title     string
      Published time.Time
      SiteId    uint      `json:"-"`
      Site      Site      `json:"-"`
  }
  
  type struct Site {
      Id   uint
      Name string
  }
  </pre>
  
  <p>And then I would do something like</p>
  
  <pre>
  var sites []Site
  result := db.Preload('Posts').Find(&sites)
  if result.Error != nil {
      ... error handling ...
  } else {
     operate on sites here
  }
  </pre>
  
  <p>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 <code>Select()</code> and <code>Get()</code> methods. What do I need to do to make this work?</p>
  
  <pre>
  var sites []Site
  err := db.Select(query, &sites) // where query is SQL from above
  </pre>
  
  <p><strong>Edit:</strong> 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.</p>