<p>I'm currently building a service that extracts a different data from a database.</p>
<p>I have about 15 similar queries (different in data produced, but same exact parameters), and for each one I have a function that look like that:</p>
<pre><code>stmt, err := db.Prepare(`MY_SQL_QUERY`)
if err != nil {
log.Fatal(err)
}
rows, err := stmt.Query(age)
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
var name string
if err := rows.Scan(&name); err != nil {
log.Fatal(err)
}
// Do something (can be a function that I pass as a parameter)
}
if err := rows.Err(); err != nil {
log.Fatal(err)
}
return something
</code></pre>
<p>However this is very repetitive. I was thinking of storing the queries and their respective data manipulation functions as variables somewhere, but then how would that look like? Not sure how to manage this situation</p>
<hr/>**评论：**<br/><br/>DeathstrokePHP: <pre><p>I was in the same boat and now I'm currently using <a href="https://github.com/jmoiron/sqlx/" rel="nofollow">https://github.com/jmoiron/sqlx/</a>. It helps me reduce the queries down.</p></pre>godevops: <pre><p>hmm I'd like to stick with the standard library, and I think there should be a way to do what I want in a nice way. However if I won't be happy with any other solutions I'll definitely consider that</p></pre>DeathstrokePHP: <pre><p>sqlx is pretty much extensions to the standard database/sql. I was also doing the same thing and try to write my own solution or wrapper to the standard library. Best of luck!</p></pre>So_Brave: <pre><p>The interfaces are really similar between sqlx and the standard lib. It's pretty much just giving you a lot of niceties around struct scanning, transactions, IN queries, named queries (was super helpful pre-1.8). It's all pretty low level--you still are writing your SQL queries out. </p>
<p>For example a <a href="https://github.com/jmoiron/sqlx/blob/master/sqlx.go#L171" rel="nofollow">sqlx row</a> is just a wrapped sql.Rows with additional properties/methods to make scanning easier.</p>
<pre><code>type Row struct {
err error
unsafe bool
rows *sql.Rows
Mapper *reflectx.Mapper
}
</code></pre>
<p>Like Deathstroke mentioned, we were pretty much writing our own solution at first. Would rather use a well documented and tested library written by someone with a strong grasp of the language. It's a great example of idiomatic Go IMO. I learned a lot about the sql standard lib going through the source.</p></pre>qxclpxzk: <pre><p>You have a few options. I'll expound on your idea, propose an alternative, and name other possible solutions.</p>
<p>As you suggested, you can refactor your prepared statements into a high level struct so they can persist and be reused, then write methods to execute queries using them and return usable results.</p>
<p>Here's a pattern you can use. It doesn't do much about the repetitiveness, but it does let you share and reuse prepared statements:</p>
<pre><code>type QueryStore struct {
db *sql.DB
prepared map[string]*sql.Stmt // you might want to use an Enum type instead of string for added safety
}
func NewQueryStore(db *sql.DB) *QueryStore {
q := &QueryStore{db: db, prepared: map[string]*sql.Stmt{}}
// add your prepared statements to the map
stmt, err := db.Prepare("bar")
checkErr(err)
q.prepared[foo] = stmt
// etc
return q
}
func (s QueryStore) getNames(param string) []string{
rows, err := s.prepared[foo].Query(param)
// error checking
var results []string
// row scanning
// more error checking
// etc...
return results
}
</code></pre>
<p>Alternatively, you can use an ORM to significantly cut down on your database code.</p>
<p>If you use Postgres, there's <a href="https://github.com/go-pg/pg"><code>go-pg</code></a>. I'm a big fan of this package. Other databases have support from more general ORMs such as <code>gorm</code> or <code>xorm</code>.</p>
<p>Here are a couple of examples of code using <code>go-pg</code>:</p>
<pre><code>// attempt to select first match into a struct
yourDB.Model(&someStruct).Where("some_field = ?", someVar).Select()
</code></pre>
<p>or</p>
<pre><code>// select just the name column from all rows in a table into an array
err := yourDB.Model(&someArray).Column("name").Select()
if err != nil {
log.Fatal(err) // might be a bad idea if you get an 'ErrNoRows'
}
for x := range someArray {
someFunc(someArray[x].Name)
}
</code></pre>
<p>Other solutions you could investigate include <code>sqlx</code> as others have suggested, or even code generation (not my cup of tea).</p></pre>godevops: <pre><p>Hey, thanks for the comment. I like your idea of QueryStore, however, using prepared statements like that is not a good idea in my opinion. Prepared statements need to be closed after they're used. I have a long-running service and I don't think keeping those statements open for long period of time is even possible (I might be wrong). Regardless, they shouldn't be open when the service is not used (could be days)</p>
<p>I do want to have all the queries at a centralized place so they could easily be updated / extracted to other usages.</p>
<p>Also I should probably mention: The only queries I'm interested in are for data retrieval, never for manipulation</p>
<p>How does something like this sound:</p>
<pre><code>file queries.go
type Query string
// queries are global to the package, this is somewhat OK
quertyNamesUnderTenYearsOld := Query(`
SELECT ...
FROM
WHERE
GROUP BY
`)
quertyAverageHeightOfTenYearsOldsWithName := Query(`
SELECT ...
FROM
WHERE
GROUP BY
`)
...
...
</code></pre></pre>ryeguy: <pre><p>There is no reason to close prepared statements. They are closed with the connection. </p>
<p>Keeping prepared statements open is their entire purpose. If your service is intermittent, then just let the connections die off after no activity. </p></pre>dvk0: <pre><p>To help reduce some of the scan-into-struct repetition, I found <a href="https://github.com/jmoiron/sqlx" rel="nofollow">https://github.com/jmoiron/sqlx</a> to be great. Alternatively, <a href="https://github.com/russross/meddler" rel="nofollow">https://github.com/russross/meddler</a> does just as great a job and supports NULL values too.</p>
<p>For the actual query itself, I'd stick to hand-writing your SQL queries. With the scanning delegated to sqlx or meddler, you'll find most of the functions in your data access layer will only consist of 3-5 lines anyway.</p></pre>PacNinja: <pre><p>sqlx like others mentioned or if you feel like taking ORM route have a look at <a href="https://github.com/vattle/sqlboiler">sqlboiler</a>. It generates an ORM from your DB schema and is performant.</p></pre>thebigredone91: <pre><p>I wrote a library that helps me serialize structs into SQL. It is for postgres but could give you some ideas: <a href="https://github.com/Tebro/gopsql" rel="nofollow">https://github.com/Tebro/gopsql</a></p></pre>pgaleone: <pre><p>Hey, I made something similar some time ago, have a look: <a href="https://github.com/galeone/igor" rel="nofollow">https://github.com/galeone/igor</a></p>
<p>It's basically an abstraction layer over your same implementation</p></pre>quiI: <pre><p>I literally have my editor open working on some SQL queries, this is how we structure it</p>
<pre><code>type LegacyStore struct {
db *sql.DB
logger tools.Logger
statsd tools.StatsD
statements *statements
}
type statements struct {
insertEmail *sql.Stmt
insertMapping *sql.Stmt
insertEmailEvent *sql.Stmt
insertBounced *sql.Stmt
}
</code></pre>
<p>Then there are methods on <code>LegacyStore</code> called things like <code>InsertEmail</code> which use those stored statements. I have a function called <code>NewLegacyStore</code> which returns me a <code>LegacyStore</code> and inside that it prepares all the statements for future use. </p></pre>Growlizing: <pre><p>Thanks for the idea, I will do this next time I write queries!</p></pre>kd7nyq: <pre><p>I took another route. Instead of trying to make SQL code simple, I conceded that SQL code will probably always be plentiful and repetitive. I also don't like to use ORMs because I appreciate transparency and I want to optimize SQL as necessary.</p>
<p>The solution I came up with is an SQL code generator. It generates the database schema and SQL bindings (SELECT, UPDATE, INSERT, DELETE) based on struct definitions. It's not a beautiful solution that requires zero work, but it saves a boat load of time.</p>
<p><a href="https://github.com/jackmanlabs/codegen" rel="nofollow">https://github.com/jackmanlabs/codegen</a></p>
<p>If you have questions about using it, let me know. I'm happy to get feedback and make improvements. Most of the development has been defined by what I need <strong>right now</strong>.</p></pre>pgaleone: <pre><p>If you use PostgreSQL you can use a library I wrote: <a href="https://github.com/galeone/igor" rel="nofollow">https://github.com/galeone/igor</a></p></pre>vagmi: <pre><p>If your queries are complicated, I would suggest something like <a href="https://github.com/nleof/goyesql" rel="nofollow">goyesql</a> along with something like <a href="https://github.com/jmoiron/sqlx" rel="nofollow">sqlx</a>. This lets SQL live in its own file rather than as strings in a <code>.go</code>file. This helps with syntax highlighting and formatting SQL the way you would want it. Also, if you are using PostgreSQL, I cannot recommend <a href="https://github.com/mgutz/dat" rel="nofollow">dat</a> enough. It supports a nice query builder DSL that allows you to have query fragments and make progressive modifications on the base query. </p></pre>koalefant: <pre><p>If you're going to pass this data straight to the client, sometimes I just json_agg the result of the query, scan into a string var and pass that back.</p></pre>knq: <pre><p>We have a tool published, <a href="https://github.com/knq/xo" rel="nofollow">xo</a> that makes this workflow a breeze. You can write your own Go templates that can then be used with the types from your database. Alternatively, you can write your queries in something like <code>.sh</code> script, and then have <code>xo</code> generate the wrapper code around it (again, using custom templates if you'd like).</p>
<p>Also, if you're doing any kind of database work, check out <a href="https://github.com/knq/usql" rel="nofollow">usql</a>.</p>
<p>Cheers, and good luck!</p></pre>brianketelsen: <pre><p>Check out the way go-micro does it:
<a href="https://github.com/micro/user-srv/blob/master/db/db.go" rel="nofollow">https://github.com/micro/user-srv/blob/master/db/db.go</a></p></pre>peterbourgon: <pre><p>
