Any suggestion for idiomatic sql code?

<p>Hey </p> <p>I am kind of tired of the ORM at my student job, it is 5 years old now and has a bunch of bugs. I think in particular my problem with not using a ORM is the mapping and doing joins. I was thinking if most of you guys just handcode your SQL? if it is maintainable that way and if you guys have any suggestions or references on how to maybe use multiple result sets to select something as a many-to-many. And how to maintain go code that fulfils standard usecases such as converting a query string into sql. </p> <hr/>**评论：**<br/><br/>Mikojan: <pre><p>Why would you not just handcode your SQL? It is a language designed specifically for humans to declaratively prepare, store and manipulate information in an RDBMS.</p> <p>There&#39;s also SQL generators like <code>github.com/Masterminds/squirrel</code> but I only use those when I want meta-programming in SQL. Which I generally don&#39;t because I generally don&#39;t want meta-programming.</p> <blockquote> <p>how to maybe use multiple result sets to select something as a many-to-many.</p> </blockquote> <p>If you have the relevant information you should be able to construct what ever data type(s) you want. How to do that depends on exactly what that is.</p></pre>mattinielsen: <pre><p>I suppose the main problem with handcoding sql is that it tends to strictly fulfil one goal, so as soon as you need some flexibility it gets much harder, e.g. if you have to implement the jsonapi format, you can have a ?include=&lt;some relationship&gt; which requires a join, and if it isn&#39;t included you shouldn&#39;t do the join. So if you handwrite the sql you would have to have 2 seperate calls just for that one usecase right. And the list goes on and on - and i find that it often leads to just fetching everything from the database, and then filtering it directly in the go code instead of actually using the DB features. I also think it is problematic that everytime you update a struct, which happens quite often, you have to modify code in 20 different locations. So i was just thinking if someone had a github repo or something, where handwritten SQL looks maintainable, also in a big application. </p></pre>saturn_vk: <pre><p>For my side project, most of my SQL is hardcoded. For a few places I use text/template to insert some dynamic bits into a particular SQL, such as an extra join or two, or more where clauses. All in all, I&#39;d much rather write the SQL directly than use an ORM or an SQL builder, since the former is so easy to write and I already know it.</p></pre>paul2048: <pre><p>It&#39;s not clear what your issue is. You either use an ORM, generate structs and methods from a database schema with a code generator, or write code manually. And yes it&#39;s going to be verbose, because Go doesn&#39;t allow any form of meta-programming that is not verbose. </p> <p>That&#39;s personally why I&#39;d prototype an app with NodeJS instead of Go, until the data model is stable. Both solutions just can&#39;t compare in terms of productivity.</p></pre>mattinielsen: <pre><p>I am not looking for a &#34;fit all&#34; solution, i know the limitations of Go and why you would use/not use a ORM. I am just looking for pointers from you guys. Because i think whenever i read blog posts about &#34;idiomatic&#34; db code with Go, it is always a hilarious example of &#34;SELECT * FROM BLABLA&#34;; Which is pretty far from the real world. So pointers/small hints/niceness specifically dealing with ORDER BY, JOINS, converting queryStrings in a good way, etc. is what i am looking for. </p></pre>baijum: <pre><p>I have a side project which uses hand coded SQL: <a href="https://github.com/kaaryasthan/kaaryasthan" rel="nofollow">https://github.com/kaaryasthan/kaaryasthan</a> </p></pre>kerakk19: <pre><p>Hey, after quick glance at your project i do have few questions.</p> <p>Aren&#39;t you afraid of using global variables? I mean Database and Config specifically.</p> <p>Don&#39;t you think separating models and handlers would be a good idea? Currently i see yours files are pretty messy, and it only will be worse.</p> <p>Also you should wrap your backend in separate directory, so it won&#39;t be hanging out with frontend.</p></pre>baijum: <pre><p>Thanks for the feedback!</p> <p>I just had a discussion about global database handler last week here: <a href="https://www.reddit.com/r/golang/comments/77p17c/structuring_go_applications/" rel="nofollow">https://www.reddit.com/r/golang/comments/77p17c/structuring_go_applications/</a></p> <p>After that I started thinking about about changing that approach. When I get some more free time, I will try that.</p> <p>Now I will think about other suggestions that you given here :)</p></pre>natefinch: <pre><p>I&#39;ve been working on an awesome code generator for databases.<br/> <a href="https://github.com/gnormal/gnorm" rel="nofollow">https://github.com/gnormal/gnorm</a> Docs at <a href="https://gnorm.org" rel="nofollow">https://gnorm.org</a> Example project with ready-to-use templates at <a href="https://github.com/gnormal/postgres-go" rel="nofollow">https://github.com/gnormal/postgres-go</a></p> <p>I build gnorm because I dislike ORMs. You build your database using the tools meant to build databases, i.e. sql and your database&#39;s native tooling. Then point gnorm at your database, and it generates the boilerplate to interact with it.</p> <p>In the case of postgres-go, that means strongly typed code that uses no reflection. Super fast, super simple to understand, no magic. </p></pre>natefinch: <pre><p>The postgres-go gnorm solution doesn&#39;t have explicit join support yet, but it&#39;ll be coming soon. Contributions also welcome :)</p></pre>