<p>In the context of a web application what would you consider a Go best practice for accessing the database in (HTTP or other) handlers?</p>
<p>I've read some interesting articles that propose different techniques. One is using custom handlers that wrap sql.DB like in <a href="https://medium.com/@benbjohnson/structuring-applications-in-go-3b04be4ff091#9e7e">this article</a>. Taking this idea further instead of wrapping a raw sql.DB object we could create an application context that wraps sql.DB and anything else we might want to use like in <a href="https://elithrar.github.io/article/custom-handlers-avoiding-globals/">this article</a>. Of course this approach could lead into trouble with concurrent access assuming we include something unsafe in the struct. I've also heard about golang.org/x/net/context which seems to be used by Google as explained in <a href="http://blog.golang.org/context">this article</a>. Nevertheless I am not entirely sure how it could help in this case since the <a href="https://godoc.org/golang.org/x/net/context">docs</a> specifically advise against wrapping it in a struct.</p>
<p>I've also heard about using globals and maybe there are some other techniques I have missed. I suppose that any of these ways would work but I am more interested in what the Go community considers as a best practice (or the Go way) for this particular case. Thank you.</p>
<hr/>**评论：**<br/><br/>TornadoTerran: <pre><p>In general Dependency Injection is always welcome. Usualy I use custom <code>type Handler struct{}</code> with custom <code>ServeHTTP</code> method or i use gocraft/web. Whatever im doing im trying to take as much from strong typing as possible. In oposite to previous answars i use <code>context.Context</code> only for timeouts/cancelation and passing request related data (userID, session, request params etc).</p>
<p><strong>Service initialization and handlers setup</strong> <a href="https://github.com/go-soa/charon/blob/master/cli/run.go#L20">link</a>
Each handler gets <code>ServiceContainer</code> (struct with bunch of thread safe instances)</p>
<p><strong>Custom http.Handler implementation</strong> <a href="https://github.com/go-soa/charon/blob/master/controller/web/handler.go#L64">link</a>
As you can see im passing <code>httprouter.Params</code> into context.</p>
<p><strong>Example handler</strong> <a href="https://github.com/go-soa/charon/blob/master/controller/web/registration.go#L78">link</a>
Each handler has <code>Container</code> property that contains reference to each of services (including <code>RepositoryManager</code> in my case).</p>
<p>If you replace properties in Handler by some Interfaces you are able to test in separation almost everything.</p>
<hr/>
<p><em>Edit</em></p>
<p>And keep in mind that error free <code>sql.Open</code> does not mean that connections is established (it only checks if connection string is correct), at least <code>sql.DB.Ping()</code> is necesary. Example <a href="https://github.com/go-soa/charon/blob/master/service/postgres.go#L12">link</a></p></pre>Defender90: <pre><p>Thanks for those links to go-soa. That's beautiful code. </p></pre>TornadoTerran: <pre><p>No problem. GL HF</p></pre>de_dave: <pre><p>I use <a href="http://godoc.org/golang.org/x/net/context">context</a> in conjunction with <a href="http://godoc.org/github.com/jmoiron/sqlx">sqlx</a>. I try not to do any direct database manipulation within a handler, instead deferring that responsibility to functions on my database models which all take an <a href="http://godoc.org/github.com/jmoiron/sqlx#Ext"><code>Ext</code></a> parameter.</p>
<p>Why <code>Ext</code>? Because <code>sqlx.DB</code> and <code>sqlx.Tx</code> both adhere to the <code>sqlx.Ext</code> interface, most database methods can be called outside and inside a transaction, respectively.</p>
<p>So the end result is my middleware fills the request context with a <a href="https://github.com/jmoiron/sqlx/blob/master/sqlx.go#L225"><code>sqlx.DB</code></a> value when the request begins. The actual request handler then extracts the DB handle from the context, and accesses data via model functions.</p></pre>gureggu: <pre><p>Context is the way to go IMO. Here's a tiny package I made to illustrate one way of dealing with it: <a href="https://github.com/guregu/db">https://github.com/guregu/db</a></p>
<p>I don't actually use this at work (we have our own fancy config system) but I think it should get the idea across. </p>
<p>With an approach like this, it's easy to switch out real database drivers with <a href="https://github.com/guregu/mogi">mock ones</a> for testing. Ideally you shouldn't be mocking things at the SQL level though. </p></pre>joeshaw: <pre><p>+1. This is almost exactly what I do. I actually include a lot of global stuff in my context and use it as the root context in my handlers. This includes the <code>sqlx.DB</code>, my app's configuration, a statsd client, etc.</p>
<p>The middleware and handlers then derive their own contexts from this root context with per-request data like X-Request-Id, the currently authenticated user, etc.</p></pre>troy_k: <pre><p>sql.DB already handles concurrent access (and pooling) for you, using a global makes the most sense to me in handlers.</p>
<p>However, using the same transaction can be tricky, so instead of using the global sql.DB in the methods that touch data, I prefer to use an interface:</p>
<pre><code> // Queryer interface is used to query the db
type Queryer interface {
Query(query string, args ...interface{}) (*sql.Rows, error)
}
</code></pre>
<p>which works for everything except explicit prepared statements, so I choose not to use those.</p></pre>mgutz: <pre><p>I prefer to do very little in handlers and pass this kind of logic to models. For one, it makes unit testing easier as you're not dealing with HTTP at all. Second, it's easier to reuse the same models from HTTP handlers, Websocket handlers, RPC</p></pre>Jhsto: <pre><p>In <a href="https://github.com/9uuso/vertigo" rel="nofollow">a blog engine of mine</a> I <a href="https://github.com/9uuso/vertigo/blob/master/databases/gorm/misc.go" rel="nofollow">use a variable</a>. I asked about the matter on Go Slack group and I heard that other developers are using the same method. Some said they also sql.Ping() the variable in a goroutine every now and then, but to my understanding the sql package keeps a good care of the connection even without regular pinging.</p>
<p>I personally liked this method because calling the database in structure methods <a href="https://github.com/9uuso/vertigo/blob/master/databases/gorm/posts.go#L68" rel="nofollow">is quite easy</a>.</p>
<p>It also has the benefit that if I (or any user of the software) decide to switch database driver, I don't have to re-write my route functions, because the variable is not bind to context. Compare the <a href="https://github.com/9uuso/vertigo/blob/master/posts.go#L121" rel="nofollow">current</a> and <a href="https://github.com/9uuso/vertigo/blob/ba6029295e2132a5c4554784983f69fb9a893ae4/posts.go#L157" rel="nofollow">old route</a>.</p>
<p>I can see how this might not sound like very ideal method (if the connection dies, it has to be pinged to be reestablished), but I think think the modularity is a big win.</p></pre>elithrar_: <pre><blockquote>
<p>we could create an application context that wraps sql.DB and anything else we might want to use like in this article[2] . Of course this approach could lead into trouble with concurrent access assuming we include something unsafe in the struct.</p>
</blockquote>
<p>As the author of that article: <code>*sql.DB</code> is safe for concurrent access. Most data-store connection pools are (but it always pays to check). If it weren't, the overhead with setting up/tearing down new connections would be painful. In fact, I called out the need to pay attention to that in the article ;)</p>
<p>If you want a really simple setup, just go with a global. Keep in mind that too many globals can get away from you - it's nice to know <em>where</em> things are being passed/used sometimes. Hence why I'm a fan of the approach in <a href="https://medium.com/@benbjohnson/structuring-applications-in-go-3b04be4ff091#9e7e" rel="nofollow">Ben's article</a> about having your own <code>DB</code> type that embeds <code>*sql.DB</code>. It also makes it easier to test.</p></pre>daveddev: <pre><p>I suggest making anything you need to be passed around that is not request-scoped safe for concurrency. In my current app, I have multiple "server" instances which each receive a "sysUtils" object (holding my db, loggers, and other concurrency-safe utilities) which is embedded within each server instance. Accessing utilities is simple and elegant. For request-scoped data, I use net/context (<a href="https://github.com/codemodus/chain" rel="nofollow">https://github.com/codemodus/chain</a>). context access is uglier and slower than embeds, so I'd hate having that between me and utilities.</p>
<p>Consider:</p>
<pre><code>func hello(ctx context.Context, w http.ResponseWriter, r *http.Request) {
mainDB := db.SQL(ctx, "main") // What does this barrier possibly gain for a DB pool???
mainDB.QueryRow(...)
// ...
}
</code></pre>
<p>vs</p>
<pre><code>func (s *server) hello(ctx context.Context, w http.ResponseWriter, r *http.Request) {
s.db.QueryRow(...)
// ...
}
</code></pre>
<p>If you still desire to place dependencies within net/context (e.g. beyond request-scoped data/utils which are difficult to make safe for concurrent use), "chain" allows you to setup an initial context which subsequent chains will reuse. This can be seen in the Setup example code in the chain package readme ("// Add common data to the context.").</p></pre>elithrar_: <pre><blockquote>
<p>context access is uglier and slower than embeds, so I'd hate having that between me and utilities.</p>
</blockquote>
<p>This is a great point. Stashing a bunch of stuff in your request context's <code>interface{}</code> can be clunky at best and slow at worst. Making your "server-wide" structures (DB pool, templates, sessions instance, Redis pool, etc.) available via either methods-on-structs or via passing an <code>env</code> struct as an argument to custom handlers is certainly more idiomatic.</p></pre>gureggu: <pre><blockquote>
<p>"server" instances which each receive a "sysUtils" object</p>
</blockquote>
<p>Judging from the lowercase of sysUtils, I would assume all your handlers are in the same package. If that's the case, this approach would work fine (and so would a global variable). </p>
<p>The whole point of putting it in the "root" context (from which all request contexts are derived) is so you have an easy to reason about, simple and reliable way to pass around common state <em>across</em> API boundaries (i.e. packages). </p>
<p>When you have handlers and tests involving multiple packages, having all your stuff in a common location is very helpful. Before I used context I tried putting "InitXXX" methods in every package and calling them from main. The hacks and workarounds I had to do to get tests spanning multiple packages to work was far uglier than using context, and avoiding circular dependencies in your tests becomes very difficult (because you have to InitXXX every other package you depend on—even indirectly—in your test's init). Again, if all your stuff is in the same package, (such as the "service" package in <a href="/u/TornadoTerran" rel="nofollow">/u/TornadoTerran</a>'s comment), you don't have to worry about this. </p>
<p>If you have any workarounds for this I'd be very interested. I like static typing too! No one wants to use <code>interface{}</code> if they don't have to. </p>
<p>TL;DR: You gain an easily mockable, easily testable reference to your DBs across packages.</p></pre>daveddev: <pre><p>I'm not following. This seems like you might be thinking in another language. Please, if you are willing to share, explain why other packages would need to share the DB connection from your main. I wonder if you are misusing packages as a way to structure your project files.</p>
<p>Another thought to consider is that if you are reaching for interface{}, more often than not, it's not the right solution. In other words, without seeing code, I suspect that there are other design issues causing the overhead you are trying to avoid.</p>
<p>Apologies if I'm being dense.</p></pre>gureggu: <pre><p>Deciding how to split packages up is definitely one of the hardest parts of maintaining a large project. Ultimately it's personal preference, but I've found that splitting packages by concept/feature is better than having packages by "function" like handlers/models/views. There is a certain point where your project just gets too big to shove unrelated handlers all in the same package. Plus, by keeping the code hitting the DB outside of your main/handlers package, you can create common components re-usable across multiple services. For example, an API server for a mobile app and a batch job that crunches data could share code. </p>
<p>So let's say you're making something like reddit and have a <code>user</code> package that handles authentication and user profiles, and then a <code>article</code> package that handles the article submission process. Both of them have to look at the database. The article package depends on the user package, for keeping track of submitters and comments.</p>
<p>In your main method, you could call <code>user.Init(SysUtils{db, ...})</code> and <code>article.Init(...)</code>. This could put the DB connections in a global, or a handler struct or whatever. </p>
<p>This is fine, but now let's try and write tests. In article/article_test.go you could put something like:</p>
<pre><code>func init() {
testDB := ...
Init(common.SysUtils{testDB, ...})
user.Init(common.SysUtils{testDB, ...})
}
</code></pre>
<p>And achieve the same thing. But let's say our user package is too big now and we want to split up the user profile-y bits and the authentication bits, so we make an <code>auth</code> package. Now, even though your <code>article</code> package doesn't directly depend on <code>auth</code>, you still have to call <code>auth.Init(...)</code> from <code>article</code> tests so that your <code>user</code> package doesn't break in mysterious ways. Suddenly you can't refactor anything, like splitting a big package into two, without breaking every single one of your tests. You can't make a common <code>init()</code> that Inits every package either, because that would be a circular import. </p>
<p>However, if you use context to keep track of your databases, everything is very easy to share. Just make a package that sets up a common testing context with all the necessary DBs and such inside of it.</p>
<pre><code>package testhelper
import (
"golang.org/x/net/context"
"github.com/me/my-cool-project/config"
"github.com/me/my-cool-project/db"
)
var ctx context.Context
func init() {
ctx = context.Background()
ctx = config.NewContext(ctx, /* test config data */)
ctx = db.OpenWhatever(ctx, "test database")
// etc etc
}
func Context() context.Context {
return ctx
}
</code></pre>
<p>Then you can just use <code>testhelper.Context()</code> in all your per-package tests. There's no need to init anything because every package can get the information it needs from your context. In other words, it's much easier to have packages grab the settings they need rather than be handed them. Context is immutable, so if you need slightly different settings for a particular test it's easy to create a new child context deriving from testhelper's without affecting your other tests. </p>
<p>Although x/net/context uses <code>interface{}</code>s on the inside, you are encouraged to hide that implementation detail by using unexported types as keys and FromContext/NewContext functions to wrap the type casting ugliness (see the official blog post on context for more info about this). </p>
<p>I'm curious to what your solution would be for this. Would you put all your handlers/DB code together in the same package? Test everything from the main package instead of per-package? I think the majority of projects never reach a size where thinking about this is necessary, but it's been something I've struggled with for a while now. I found x/net/context to be the cleanest solution, but I'm always looking for improvements. </p>
<p>Edit: fixed code and formatting.</p></pre>TornadoTerran: <pre><p>Iny my example the point is that <code>service</code> directory contains only integration layers, configuration and initialization. All feature code base is in lib (its temporary place for packages that at some point maybe evolve into separate project). </p>
<p>But lets stay with your idea. We have to packages <code>user</code> and <code>forum</code>. In a <code>forum</code> pakage there is a method that makes SQL query with <code>JOIN</code> to get all posts with author data. So you need to provide <code>user.User</code> struct to <code>Scan</code> method.</p>
<p>Then in a <code>user</code> package you need <code>forum.Post</code> struct as type for <code>user.User.Posts</code> field.</p>
<p>In my opinion there is realy no way make one monolitic project in Go without <code>semi-flat</code> directory structure.</p></pre>pcj2bs: <pre><p>We use a really simple context that gets passed to all of our <a href="https://github.com/VividCortex/siesta" rel="nofollow">Siesta</a> handlers as an argument.</p>
<p>We basically take a sql.DB handle and set it in the context using <code>c.Set("db", db)</code>. This happens in a middleware function that runs before everything else.</p></pre>om0tho: <pre><p>Not sure if this is optimal.</p>
<p>I have a <code>database</code> package with my own <code>DB</code> type that embeds <code>*sql.DB</code>. I then attach custom methods like, <code>UpdateUser</code>, to my own <code>DB</code> type.</p>
<p>In main, I save <code>database.DB</code> to to another package called <code>gl</code>.</p>
<p>In my <code>backend</code> package, I call <code>gl.DB.UpdateUser</code>, or whatever, in a handler.</p>
<p>I put different URL prefixes in different packages, e.g. <code>/user/</code> --> <code>package user</code>, <code>/admin/</code> --> <code>package admin</code>.</p></pre>Dont_Reddit_Me: <pre><p>In case you didn't know about it, here is some great documentation: <a href="http://go-database-sql.org/" rel="nofollow">http://go-database-sql.org/</a></p></pre>
