Writing complex conditional SQL queries

<p>I&#39;m trying to figure out the best way to write complex conditional SQL queries based on different filtering parameters. I&#39;m currently using <a href="https://github.com/jmoiron/sqlx" rel="nofollow">sqlx</a> to do my struct scanning, but I need a way to build queries at runtime without having a bunch of very long and raw SQL strings with complex concatenation and ordinal incrementing littering my go code.</p> <p>Right now I&#39;m leaning towards using the standard template package and creating custom template functions, <a href="https://clypd.com/simple-database-interaction-in-go/" rel="nofollow">just like how this company does it</a>. It allows me to write the raw queries in a separate file that I can compile into go code before I ship the binary by using <a href="https://github.com/gobuffalo/packr" rel="nofollow">packr</a>. The main benefit from this is that I can visually see what query is going to run, which allows me to fine tune it when I need a performance boost. It also allows me to store other really long and complex sql queries in the same directory that don&#39;t require conditional logic so it doesn&#39;t clutter my go code. The only problem is I don&#39;t know how maintainable this way is. It looks like it can get quite complex really fast and I wouldn&#39;t know until 3 months down the line after it&#39;s baked into the codebase.</p> <p>I&#39;ve found the following solutions as well:</p> <ul> <li><a href="https://github.com/Masterminds/squirrel" rel="nofollow">squirrel</a>: SQL builder tool.</li> <li><a href="https://github.com/go-pg/pg" rel="nofollow">go-pg/pg</a>: Postgres based ORM with query builder capabilities.</li> <li><a href="https://github.com/jinzhu/gorm" rel="nofollow">gorm</a>: Fully featured ORM.</li> </ul> <p>I&#39;ve stayed away from ORM&#39;s thus far because I wanted to really fine tune my SQL queries for pretty high throughput in certain situations, but I&#39;m not against the idea. I&#39;m just trying to KISS right now by keeping the number of dependencies low.</p> <p>Let me know what your experiences are with solving this particular problem!</p> <hr/>**评论：**<br/><br/>Ploobers: <pre><p>I really dislike <code>text/template</code>, and I don&#39;t find that using it like the example you linked to ends up more readable than string concatenation. We&#39;ve done a combination of <code>fmt.Sprintf</code>, <code>bytes.Buffer</code> and a custom sql builder similar to squirrel, depending on the complexity of the query. This is an example of our sql builder that uses functional options:</p> <pre><code>b := mysql.NewBatchSelector(c, &#34;db.Tags&#34;, []string{&#34;WorkspaceID&#34;, &#34;ID&#34;, &#34;Name&#34;, &#34;Description&#34;, &#34;Config&#34;, &#34;Created&#34;, &#34;Modified&#34;, &#34;Deleted&#34;}, // field names []string{&#34;WorkspaceID&#34;, &#34;ID&#34;}, // where fields mysql.BatchSelectorRequireOneRowAffectedPerAdd(), ) for _, tag := range tags { b.Add(tag.WorkspaceID, tag.ID) // params that have to match the where fields } </code></pre></pre>