Efficiently mapping one-to-many many-to-many database to struct in Golang : <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>
