Dealing with large SQL queries

<p>I have an application that serves data via an HTTP API from Redshift and RDS that needs to do pretty complicated queries (in the ballpark of 100-200 lines each). Each route needs to SUM, GROUP, ORDER, filter, etc. Is there a &#34;good&#34; or &#34;idiomatic&#34; way of keeping these queries maintainable in Go (or any language, for that matter)? A package with constants for the queries? An ORM or query builder (not great as we&#39;d lose some flexibility)? </p> <p>TL;DR How do you maintain large SQL query strings?</p> <hr/>**评论：**<br/><br/>sescob27: <pre><p>What about writing sql query templates, so you have them stored separately and then read them and inject variables from go and execute it? </p></pre>jns111: <pre><p>I&#39;m using this: <a href="https://github.com/gchaincl/dotsql" rel="nofollow">https://github.com/gchaincl/dotsql</a> It let&#39;s you write all your queries in a separate .sql file. (Gogland IDE supports auto complete). You load the .sql file from filesystem or embedded byte array. Then simply reference whatever statement you want to execute by name, add variables if placeholders are specified and that&#39;s it. I like this solution because it&#39;s a clean separation of application code and sql statements.</p></pre>TastedPegasus: <pre><p>Query templates have been something I have been thinking about a lot lately. Seems like it would work for queries that don&#39;t have conditional logic based directly on the API request. In my experience when dealing with large dynamic queries,query building can really be helpful. I&#39;ve used both sqlx and squirrel. If you have trying to add conditional logic in the queries either of these two query builders can really help.</p> <p>i.e Request sends in filter by name [...names]<br/> Now the query needs to have a &#34;where name IN (...names)&#34; ( or equiv. ) as well as another conditionals. </p> <p>Maybe this would require a join on a id or some primary key. Obviously you don&#39;t want a join sitting around that is not needed. So instead of have every possible query stored in a file, you can evaluate the request, do some basic checks and form a query inline. If you are working with a query build, then I would focus first on getting a query string and args. Once that is working you are back to working directly with your db driver. From there you can look into more package features such as caching statements. If you queries are very very large, benchmark the query generation and make sure you are happy with the performance tradeoff. </p> <p>Being able to still use placeholders was one of the main reason why I went with a query builder. One big down sides when dealing with dynamic queries is that you never truly know what&#39;s coming through haha. So test,test,test </p> <p>Would love to know that route you go with. I have run into this question my self a fair amount lately. </p></pre>jiimji: <pre><blockquote> <p>How do you maintain large SQL query strings?</p> </blockquote> <p>Use functions,views or stored procedure instead of queries.</p></pre>tgulacsi: <pre><p>Can you use text/template? For speed and debug, you can generate all the possible queries with go generate at compile time, or once on program start if routes are less static.</p></pre>kardianos: <pre><p>Large is relative.</p> <p>I would look into tradeoffs. You could write one query per file and use an embedder where in debug mode they are read directly from disk, but in PROD mode they are embedded into the application. Otherwise I would just put them in a multiline comment in the application.</p> <p>If you wanted to design to support different databases, I could see value in making some type of interface within each package, but I wouldn&#39;t put all your queries in a dedicated sqltext package. Code should live with related code, SQL is very related code that happens to be executed in a different process. Either way, I would keep all SQL text internal / private to the packages you write.</p></pre>ezeql: <pre><p>Like this <a href="https://github.com/nleof/goyesql" rel="nofollow">https://github.com/nleof/goyesql</a></p></pre>