How do you deal with RDBMS relationships in Go when not using an ORM ?

<p>I&#39;d like to know how you guys deal with JOIN queries in Go.</p> <p>Say you have 2 tables, <code>recipes</code> and <code>ingredients</code> with a one to many relationship, and one struct for each table, with the structs fields mirroring the tables&#39;s columns. How would you receive the result of a query like:</p> <p><code>SELECT i.ingredient_name, r.recipe_name FROM ingredients i JOIN recipes r ON r.id = i.recipe_id</code></p> <p>The best thing I could come up with right now is to have one struct per query, that&#39;s dedicated to receive the SELECTed columns from JOIN queries, but then is that needed every time ? Is there anything better / more idiomatic ?</p> <p>Thanks for the help :)</p> <hr/>**评论：**<br/><br/>peterbourgon: <pre><p>Presumably when you make that SELECT statement, you&#39;re doing it to satisfy (i.e. fill up) some domain type in your program. You can database/sql.Rows.Scan into it directly, i.e.</p> <pre><code>type MySQLStore struct { db *sql.DB } func (s *MySQLStore) GetRecipe(recipeID int) (r Recipe, err error) { row := s.db.QueryRow( &#34;SELECT i.ingredient_name, r.recipe_name FROM ... WHERE r.recipe_id = ?&#34;, recipeID, ) if row == nil { return r, ErrNoSuchRecipe } return r, row.Scan(&amp;r.IngredientName, &amp;r.RecipeName) } </code></pre></pre>QuietPort: <pre><p>cheers. So I was right when thinking that domain types must be prepared to receive JOIN queries, as opposed to composing the &#34;tables&#39;s structs&#34; together somehow.. ?</p></pre>peterbourgon: <pre><p>Hmm, I think &#34;must&#34; is too strong, I think it&#39;s a matter of perspective. I&#39;ve given you one perspective, which is definitely my opinion! </p> <p>Other people might say it makes sense to have &#34;table structs&#34; or &#34;ORM types&#34; or whatever, which mirror your DB tables, and then either use those types in your program directly, or define mappings or compositions from those types to your domain types. I would personally never structure an application this way, but I can see how people from other backgrounds might prefer it.</p></pre>QuietPort: <pre><p>Gotcha.</p> <p>I&#39;m indeed mainly working with rails right now (and I&#39;m not a fan), so probably carrying inadequate reflexes along in my Go. I&#39;m interested to hear what are the weaknesses of this approach compared to yours if you don&#39;t mind.</p></pre>metamatic: <pre><p>And of course, there&#39;s nothing stopping you from scanning into separate Ingredient and Recipe structs, with a different selection of columns going into each.</p></pre>Ploobers: <pre><p>Just make your struct match what your data looks like if that&#39;s what you are going for. You&#39;ll still have to determine when it&#39;s better to do a JOIN in a single query, meaning that you&#39;ll be duplicating scanning and using more bandwidth for the parent table, vs doing two separate queries, but that&#39;s the same for any language.</p> <pre><code>type Recipe struct { Name string Ingredients []Ingredient } type Ingredient struct { Name string } rows, err := db.Query(&#34;SELECT i.ingredient_name, r.recipe_name FROM ... WHERE r.recipe_id = ?&#34;, recipeID) if err != nil { log.Fatal(err) } defer rows.Close() r := Recipe{} for rows.Next() { i := Ingredient{} err := rows.Scan(&amp;i.Name, &amp;r.Name) if err != nil { log.Fatal(err) } r.Ingredients = append(r.Ingredients, i) } </code></pre></pre>skidooer: <pre><blockquote> <p>You&#39;ll still have to determine when it&#39;s better to do a JOIN in a single query, meaning that you&#39;ll be duplicating scanning and using more bandwidth for the parent table, vs doing two separate queries, but that&#39;s the same for any language.</p> </blockquote> <p>You could also return the joined values as an array column in RDBMSes with necessary support. You still have to consider the tradeoffs with each approach, but I think it makes reasoning about the data more clear when the array tradeoffs are acceptable.</p></pre>QuietPort: <pre><p>My example is a little contrived as I was especially interested in choosing the right approach for mapping results of JOIN queries and at the same time re-using &#34;model&#34; structs (if ever that was a good idea...).</p> <p>From what I can see here, it looks like ad-hoc structs will be used wherever there is the need, which is what I wanted to know.</p> <p>Thank you for the example, it helps :)</p></pre>Ploobers: <pre><p>I&#39;m not using any ad-hoc structs, I am using what you refer to as &#34;model&#34; structs, assuming that <code>Recipe</code> and <code>Ingredient</code> represent your real-world objects. Like <a href="/u/peterbourgon" rel="nofollow">/u/peterbourgon</a>, I have never used a struct created only for the results of a specific query.</p></pre>Brasilberg: <pre><p>This works in Go:</p> <p>type StructA { }</p> <p>type Struct B{ }</p> <p>type StructBoth {</p> <p>StructA</p> <p>StructB</p> <p>...</p> <p>}</p> <p>...though many times I have a prefer a custom struct (maybe an anonymous one inside the function - I name them response or output) so I can customize which fields are converted etc.</p></pre>codingconcepts: <pre><p>My company roll our own database code and generate the boilerplate stuff. Currently it’s just MySQL but I’m working on CockroachDB-flavoured Postgres support at the moment :)</p> <p>All open source of course!</p></pre>monkey_that: <pre><p>Is there anything public?</p></pre>codingconcepts: <pre><p>Yep! <a href="https://github.com/LUSHDigital/modelgen" rel="nofollow">https://github.com/LUSHDigital/modelgen</a></p></pre>: <pre><p>[removed]</p></pre>BrokenPhoneBooth: <pre><p>I&#39;m dad</p></pre>justinlindh: <pre><p>bad bot</p></pre>GoodBot_BadBot: <pre><p>Thank you, justinlindh, for voting on im_bot-hi_bot. </p> <p>This bot wants to find the best and worst bots on Reddit. <a href="https://goodbot-badbot.herokuapp.com/" rel="nofollow">You can view results here</a>. </p> <hr/> <p>^{^Even} ^{^if} ^{^I} ^{^don&#39;t} ^{^reply} ^{^to} ^{^your} ^{^comment,} ^{^I&#39;m} ^{^still} ^{^listening} ^{^for} ^{^votes.} ^{^Check} ^{^the} ^{^webpage} ^{^to} ^{^see} ^{^if} ^{^your} ^{^vote} ^{^registered!}</p></pre>phoger: <pre><p>bad bot</p></pre>hybsuns: <pre><p>Like others&#39; said, you need a designated struct to do so, and you will need to write the SQL code in Go.</p> <p>I use a SQL builder, squirrel, for simple select and join statements like the one in your example. For complex join statements, I usually write a draft against the database to make sure it produces the result that I wanted, then I format it to fit Go&#39;s need.</p> <p>Personally, I am not a fan of ORM as I usually don&#39;t trust the performance and/or the correctness when it comes to very complex query statements. I prefer to write my own DAL code and take the responsibility.</p> <p>I hope it helps.</p></pre>