<p>Hi@all,</p>
<p>I'm currently writing my first application with a database connection.
I choose the <a href="https://github.com/go-sql-driver/mysql">go-sql-driver/mysql Driver</a> and have multiple functions calling the database.</p>
<p>I have read that these DB connections should be long-lived, therefore I'm setting up the connection once when the program starts in main, and then giving a reference of the <em>sql.DB</em> object to the different functions.</p>
<p>However, this setup is less than ideal, because currently I can only call these "SQL-functions" from <em>main()</em>, because only <em>main()</em> has the <em>sql.DB</em>-Handler.</p>
<p>I also thought about creating a global variable for <em>sql.DB</em>, but I don't think it is thread-safe by default.</p>
<p>How are you gophers approaching this issue?</p>
<hr/>
<p>EDIT:</p>
<p>Thanks for all your answers! Since I'm still a beginner with Go, I'll stick with the default sql.DB (since it is thread-safe, TIL).</p>
<hr/>**评论：**<br/><br/>coussej: <pre><p>I came across this post a while back, gives a nice overview! <a href="http://www.alexedwards.net/blog/organising-database-access">http://www.alexedwards.net/blog/organising-database-access</a></p></pre>daveddev: <pre><p>That is a great find.</p></pre>chickencheesebagel: <pre><p>I create my own package to make the database connection act as a singleton. I initialize it in main with my params and defer the close statement. Whenever something wants to interact with the database, I call database.GetDB() to get the connection. sql.DB is thread safe.</p></pre>daveddev: <pre><p>As per <a href="https://github.com/go-sql-driver/mysql#usage" rel="nofollow">https://github.com/go-sql-driver/mysql#usage</a>, <a href="https://golang.org/pkg/database/sql/#Open" rel="nofollow">https://golang.org/pkg/database/sql/#Open</a> states that the returned *DB is safe for concurrent use.</p>
<p>For applications which do not need to be tested thoroughly, a global should be fine. Otherwise, I tend to favor dependency injection. If you provide more context about what you are working on (preferably using <a href="http://play.golang.org/" rel="nofollow">http://play.golang.org/</a>), I or someone else may provide code for you to review.</p>
<p>Edit to add, based on <a href="/u/coussej" rel="nofollow">/u/coussej</a>'s reply:
The second example of DI is how I approach this need - <a href="http://www.alexedwards.net/blog/organising-database-access#using-an-interface" rel="nofollow">http://www.alexedwards.net/blog/organising-database-access#using-an-interface</a></p></pre>tgaz: <pre><p>From <a href="https://golang.org/pkg/database/sql/#DB" rel="nofollow">https://golang.org/pkg/database/sql/#DB</a></p>
<p>"DB is a database handle representing a pool of zero or more underlying connections. It's safe for concurrent use by multiple goroutines."</p>
<p>A clean way would be to bind it together with registering the network handlers. Alternatively, build something around the context pattern, see <a href="https://godoc.org/golang.org/x/net/context" rel="nofollow">x/net/context</a>. (Unfortunately the standard <code>net/http</code> package doesn't support context objects in their handlers. Some third-party network libraries do.)</p>
<p>Edit: Discard the context idea as per <a href="/u/daveddev" rel="nofollow">/u/daveddev</a>'s comment.</p></pre>daveddev: <pre><p>Please don't use net/context for this.</p>
<p>From <a href="https://godoc.org/golang.org/x/net/context:">https://godoc.org/golang.org/x/net/context:</a></p>
<p>"Use context Values only for request-scoped data that transits processes and APIs, not for passing optional parameters to functions."</p></pre>tgaz: <pre><p>Having read <a href="http://blog.golang.org/context">http://blog.golang.org/context</a>, I'll have to stand corrected. Thanks.</p></pre>gureggu: <pre><blockquote>
<p><a href="http://blog.golang.org/context" rel="nofollow">http://blog.golang.org/context</a></p>
</blockquote>
<p>I "abuse" x/net/context to hold database connections. If you have a complex app with multiple databases, you can use middleware or other techniques to associate the correct database with incoming requests. I set up a base context including the default DB connections, logger, etc. at startup and replace them on a per-request basis as needed.</p>
<p>I think it's a useful technique when you have lots of internal packages you need to tie together. It makes testing really easy, you simply set up a "testing mode" context and throw it at your handler. However, it's arguably against the spirit of what x/net/context is intended for. Personally I think it is preferable compared to globals or complex <code>InitThingy(...)</code> sequences. </p>
<p>There was another thread about this in the past. You can check out the arguments <a href="https://www.reddit.com/r/golang/comments/38hkor/go_best_practice_for_accessing_database_in/crv41ps" rel="nofollow">for</a> and <a href="https://www.reddit.com/r/golang/comments/38hkor/go_best_practice_for_accessing_database_in/crvi6re" rel="nofollow">against</a> using x/net/context.</p>
<p>FWIW, ultimately I think a hybrid approach is the best. Use <a href="http://www.alexedwards.net/blog/organising-database-access#using-an-interface" rel="nofollow">interfaces like this</a> and pass context.Context to them. If you can come up with a clean way to initialize and test these without storing the database connection itself in your context, great! You can use request-scoped info stored in your context like the current User ID and such in your error reports when your database access fails. </p></pre>
