Best practice to use go-sql-driver/mysql package?

[Veliko_Kosev]

What is the best practice to use the go-sql-driver/mysql package ?

At the moment i’m opening and closing the connection in any of my handlers and functions every time I need to interact with my database.

Example

func someHandler(w http.ResponseWriter, r *http.Request){

    db, err := sql.Open("mysql", "user:password@/dbname")
    defer db.Close()

    //Do stuff with the db handle.
}

Now, I’ve red that the connection should be opened once and use that one db handler where i need it, but i’m not sure how that should happen. Should I write all my function which need to interact with the database to accept a db handler as argument ? How should I use the db handler in my http handlers ?

Example

package main

//Open connection globally
db, err := sql.Open("mysql", "user:password@/dbname")
defer db.Close()

func someHandler(w http.ResponseWriter, r *http.Request) {
    //Do stuff with the db handler created globally.
}

func someFooFunction(db database){
    //Do stuff with the passed db handler.
}

func otherFooFunction(){
    //Do stuff with the db handler created globally.
}

func main() {
    //Do stuff with my functions and handlers.
}

[nstratos (Stratos)]

I think a very good practice is to define a struct e.g. DB which contains the sql.DB object and your database operations as methods. Then you can have another struct e.g. app which has your handlers as methods and inject DB. For example:

type DB struct {
	*sql.DB
}

func (db *DB) GetFoo(id int64) (*Foo, error) { ... }

type app struct {
	db *DB
}

func (app app) someHandler(w http.ResponseWriter, r *http.Request) {
	id := ...
	app.db.GetFoo(id)
	// ...
}

You can take this further and make it more testable by using an interface. For example:

// in your db package e.g. mysql

type FooService struct {
	db *sql.DB
}

func NewFooService(dataSource string) (*FooService, error) {
    // caller is responsible for closing db
	db, err := sql.Open("mysql", dataSource)
	return &FooService{db}, err
}

func (db *FooService) GetFoo(id int64) (*Foo, error) { ... }
func (db *FooService) Close() error                  { ... }

// in your root package

type FooGetter interface {
	GetFoo(id int64) (*Foo, error)
}

type App struct {
	foo FooGetter
}

func NewApp(foo FooGetter) *App {
	return &App{foo}
}

func (app *App) someHandler(w http.ResponseWriter, r *http.Request) {
	id := ...
	foo, err := app.foo.GetFoo(id)
	// ...
}

You can inject the implementation of the interface in main.go.

func main() {
	foo, err := mysql.NewFooService("user:password@/dbname")
	if err != nil {
		log.Fatal(err)
	}
	defer foo.Close()

	app := rootpkg.NewApp(foo)

	http.HandleFunc("/foo", app.someHandler)
	// ...
}

Have a look at the section “Using an interface” in Practical Persistence in Go: Organising Database Access.

To help organize your packages, I highly recommend reading Standard Package Layout.

[oleg578]

In any case, if you use database in Web service, you need give database connection for each client.
The time of open new connection in my i7-4702MQ is near 10 microseconds.
I don’t think that we need optimize this.

[ofpiyush (Piyush Mishra)]

This might be because both your code and your db are on the same machine.

That’s seldom the case in production environments.

Please measure again with a network in between if you haven’t

[acim (Boban Acimovic)]

You can also use a handler wrapper:

func MyHandler(db *sql.DB) http.HandlerFunc {
    fn := func(w http.ResponseWriter, r *http.Request) {
        res, err := db.Whatever(...)
        ...
    }

    return http.HandlerFunc(fn)
}

Type for db can also be an interface, like @nstratos suggested. Some people pass db pointer inside context, but that’s not nice.