title: Efficiently mapping one-to-many many-to-many database to struct in Golang
body: <h1>Question</h1>
  
  <p>When dealing with a one-to-many or many-to-many SQL relationship in Golang, what is the best (efficient, recommended, "Go-like") way of mapping the rows to a struct?</p>
  
  <p>Taking the example setup below I have tried to detail some approaches with Pros and Cons of each but was wondering what the community recommends.</p>
  
  <h1>Requirements</h1>
  
  <ul>
  <li>Works with PostgreSQL (can be generic but not include MySQL/Oracle specific features)</li>
  <li>Efficiency - No brute forcing every combination</li>
  <li>No ORM - Ideally using only <code>database/sql</code> and <code>jmoiron/sqlx</code></li>
  </ul>
  
  <h1>Example</h1>
  
  <p><em>For sake of clarity I have removed error handling</em></p>
  
  <p><strong>Models</strong></p>
  
  <pre class="lang-golang prettyprint-override"><code>type Tag struct {
    ID int
    Name string
  }
  
  type Item struct {
    ID int
    Tags []Tag
  }
  </code></pre>
  
  <p><strong>Database</strong></p>
  
  <pre class="lang-sql prettyprint-override"><code>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)
  );
  </code></pre>
  
  <p><strong>Approach 1 - Select all Items, then select tags per item</strong></p>
  
  <pre class="lang-golang prettyprint-override"><code>var items []Item
  sqlxdb.Select(&amp;items, "SELECT * FROM item")
  
  for i, item := range items {
    var tags []Tag
    sqlxdb.Select(&amp;tags, "SELECT * FROM tag WHERE item_id = $1", item.ID)
    items[i].Tags = tags
  }
  </code></pre>
  
  <p><em>Pros</em></p>
  
  <ul>
  <li>Simple</li>
  <li>Easy to understand</li>
  </ul>
  
  <p><em>Cons</em></p>
  
  <ul>
  <li>Inefficient with the number of database queries increasing proportional with number of items</li>
  </ul>
  
  <p><strong>Approach 2 - Construct SQL join and loop through rows manually</strong></p>
  
  <pre class="lang-golang prettyprint-override"><code>var itemTags = make(map[int][]Tag)
  
  var items = []Item{}
  rows, _ := sqlxdb.Queryx("SELECT i.id, t.id, t.name FROM item AS i JOIN tag AS t ON t.item_id = i.id")
  for rows.Next() {
    var (
      itemID  int
      tagID   int
      tagName string
    )
    rows.Scan(&amp;itemID, &amp;tagID, &amp;tagName)
    if tags, ok := itemTags[itemID]; ok {
      itemTags[itemID] = append(tags, Tag{ID: tagID, Name: tagName,})
    } else {
      itemTags[itemID] = []Tag{Tag{ID: tagID, Name: tagName,}}
    }
  }
  for itemID, tags := range itemTags {
    items = append(Item{
      ID: itemID,
      Tags: tags,
    })
  }
  </code></pre>
  
  <p><em>Pros</em></p>
  
  <ul>
  <li>A single database call and cursor that can be looped through without eating too much memory</li>
  </ul>
  
  <p><em>Cons</em></p>
  
  <ul>
  <li>Complicated and harder to develop with multiple joins and many attributes on the struct</li>
  <li>Not too performant; more memory usage and processing time vs. more network calls</li>
  </ul>
  
  <p><strong>Failed approach 3 - sqlx struct scanning</strong></p>
  
  <p>Despite failing I want to include this approach as I find it to be my current aim of efficiency paired with development simplicity. My hope was by explicitly setting the <code>db</code> tag on each struct field <code>sqlx</code> could do some advanced struct scanning </p>
  
  <pre class="lang-golang prettyprint-override"><code>var items []Item
  sqlxdb.Select(&amp;items, "SELECT i.id AS item_id, t.id AS tag_id, t.name AS tag_name FROM item AS i JOIN tag AS t ON t.item_id = i.id")
  </code></pre>
  
  <p>Unfortunately this errors out as <code>missing destination name tag_id in *[]Item</code> leading me to believe the <code>StructScan</code> is not advanced enough to recursively loop through rows (no criticism - it is a complicated scenario)</p>
  
  <p><strong>Possible approach 4 - PostgreSQL array aggregators and <code>GROUP BY</code></strong></p>
  
  <p>While I am sure this will <em>not</em> work I have included this untested option to see if it could be improved upon so it <em>may</em> work.</p>
  
  <pre class="lang-golang prettyprint-override"><code>var items = []Item{}
  sqlxdb.Select(&amp;items, "SELECT i.id as item_id, array_agg(t.*) as tags FROM item AS i JOIN tag AS t ON t.item_id = i.id GROUP BY i.id")
  </code></pre>
  
  <p>When I have some time I will try and run some experiments here. </p>