<p>I've been following <a href="https://medium.com/@etiennerouzeaud/how-to-create-a-basic-restful-api-in-go-c8e032ba3181" rel="nofollow">this</a> tutorial on how to create a RESTful API in Go, but it creates an InitDb() function that establishes a connection to the database for every request and defers it to close until after the request finishes processing. This seems wasteful - is this best practice?</p>
<hr/>**评论：**<br/><br/>mrwnmonm: <pre><p>of course you shouldn't open the database and close it on every request, take a look at this site <a href="http://go-database-sql.org/" rel="nofollow">http://go-database-sql.org/</a> will give you useful ideas about how to deal with databases in go </p></pre>robvdl: <pre><p>Also this page specifically was helpful to me <a href="http://go-database-sql.org/surprises.html" rel="nofollow">http://go-database-sql.org/surprises.html</a></p></pre>samling: <pre><p>I was actually just reading an interesting thread on hacker news about this very thing. <a href="https://news.ycombinator.com/item?id=7929699" rel="nofollow">Here's</a> the post. I'm fairly new to Go but based on what I read, you would probably want to use a single DB connection that gets passed around, possibly in conjunction with a sync.Mutex to avoid deadlocks. From <a href="http://go-database-sql.org/accessing.html" rel="nofollow">this page</a>:</p>
<blockquote>
<p>Although it’s idiomatic to Close() the database when you’re finished with it, the sql.DB object is designed to be long-lived. Don’t Open() and Close() databases frequently. Instead, create one sql.DB object for each distinct datastore you need to access, and keep it until the program is done accessing that datastore. Pass it around as needed, or make it available somehow globally, but keep it open. And don’t Open() and Close() from a short-lived function. Instead, pass the sql.DB into that short-lived function as an argument.</p>
</blockquote>
<p>Conversely you <em>do</em> want to close any queries as soon as possible:</p>
<pre><code>rows, err := db.Query(`...`, ...)
if err != nil {
// Handle connection or statement error
}
defer rows.Close()
</code></pre>
<p>That way you always have the same number of connections as you have queries, and you don't run the risk of running out of available connections over time in case some rows don't get closed.</p></pre>i47: <pre><p>Great read - thanks!</p></pre>Franke123: <pre><p>Hook up your db to a context structure, then make all functions that need to access it methods of that context structure, boom, no re opening and closing the DB.</p></pre>Tacticus: <pre><p>as in context.Context structure or some other meaning?</p></pre>Franke123: <pre><p>No, like:</p>
<pre><code>type context struct {
db *sql.DB
}
func (c *context) Hello(w http.ResponseWriter, r *http.Request) {
// use c.db to access db.
}
</code></pre></pre>weberc2: <pre><p>What are the advantages to using context to share it instead of making it available to the handlers that need it via traditional means (a member in the struct that implements the http.Handler interface, for example)?</p></pre>asm-jaime: <pre><p>I wrote an example for mongo(mgo) <a href="https://gist.github.com/asm-jaime/0bd3c294f4cb4f7a7775af6749de4b28" rel="nofollow">https://gist.github.com/asm-jaime/0bd3c294f4cb4f7a7775af6749de4b28</a>, you can set point on interface model to a context in middleware.</p></pre>tiberiousr: <pre><p>For small projects I usually create a type AppDb { *sql.DB } in the main package and just append methods to that. Then I have a global db var that connects to it and gets called wherever it's needed. Go's database/sql interface provides connection pooling for you so you shouldn't have to worry about doing it manually. </p>
<p>If you're using sqlite however there are some extra bits of configuration that you should do when connecting to the database such as disabling connection pooling</p>
<pre><code>// Disable connection pooling
db.SetMaxOpenConns(1)
db.SetMaxIdleConns(0)
</code></pre>
<p>and enabling write ahead logging.</p>
<pre><code>db.Exec("PRAGMA journal_mode=WAL; PRAGMA busy_timeout=5000")
</code></pre>
<p>Do this in a connectDB() function in the main package and just call it in main.go to create a global db object.</p></pre>jns111: <pre><p>I'm using <a href="https://github.com/facebookgo/inject" rel="nofollow">https://github.com/facebookgo/inject</a> combined with <a href="https://github.com/tsenart/nap" rel="nofollow">https://github.com/tsenart/nap</a> . This way I'm injecting one single connection per sql node into all data accessing objects via dependency injection. nap routes exec statements to the master and query statements to the slave. I came from the Java world to Go so I'm used to inject everything everywhere. It's a powerful pattern to keep single instance objects around your application without spaghetti.</p></pre>tmornini: <pre><p>Considering creating your own pool:</p>
<pre><code>package postgres
import "sync"
var pool []*Client
var mutex = &sync.Mutex{}
func ClientFromPool() *Client {
if len(pool) == 0 {
return // create your new client here...
}
var client *Client
mutex.Lock()
client, pool = pool[0], pool[1:] // shift
mutex.Unlock()
return client
}
func ClientToPool(client *Client) {
mutex.Lock()
pool = append(pool, client) // push
mutex.Unlock()
}
</code></pre>
<p>Then use it where you need it like this:</p>
<pre><code>pgClient := postgres.ClientFromPool()
// use PgClient
postgres.ClientToPool(pgClient)
</code></pre>
<p>This way you'll create as few or many connections as you need, and they'll live across requests.</p>
<p>Caution: this may greatly reduce your response times and open DB connections. :-)</p></pre>tgulacsi: <pre><p>What is in a Client?
As sql.DB is already a connection pool in itself, you don't need to pool those.</p>
<p>For a general allocation amortizer, I'd use sync.Pool.
For an idle pool, a slice as you showed.
For a bounded idle pool, a buffered channel.</p></pre>chmikes: <pre><p>If sql.DB is already a pool, how do we get a connection from the pool and release it back to the pool ?</p></pre>tgulacsi: <pre><p>You don't. Just use sql.DB's methods, it does the pooling itself.</p></pre>tmornini: <pre><p>Thank you for reminding me about sync.Pool, I've been meaning to investigate.</p>
<p>I'm shocked to hear that sql.DB does pooling. I'd prefer to have much simpler implementations in stdlib.</p></pre>dchapes: <pre><blockquote>
<p>I'm shocked to hear that sql.DB does pooling.</p>
</blockquote>
<p>It's right there in <a href="https://golang.org/pkg/database/sql/#Open" rel="nofollow">the documentation</a> which explicitly advises against the kind of code you gave:</p>
<blockquote>
<p>The returned DB is safe for concurrent use by multiple goroutines and maintains its own pool of idle connections. Thus, the Open function should be called just once. It is rarely necessary to close a DB.</p>
</blockquote></pre>tmornini: <pre><p>I appreciate that.</p></pre>cawdrizzle: <pre><p>Open your DB as an unexported package level var that doesn't leave your DB package. There are other ways to manage the DB connection but that is far and away the easiest with minimal stupidity.</p>
<p>For the love of fuck don't open and close on each API request. That's just silly.</p></pre>
