How to share mysql connection between http goroutines?

Question

I'm a Go noob and can't find any complete examples of opening a mysql connection in Go and then sharing it among http handlers. Here is my code so far, how would I use the db connection that I opened in main() in my HomeHandler?

package main  import (   "database/sql"   "fmt"   _ "github.com/go-sql-driver/mysql"   "github.com/gorilla/mux"   "log"   "net/http" )  func main() {    fmt.Println("starting up")    db, err := sql.Open("mysql", "root:@/mydb?charset=utf8")   if err != nil {     log.Fatalf("Error opening database: %v", err)   }    db.SetMaxIdleConns(100)    r := mux.NewRouter()   r.HandleFunc("/", HomeHandler)    http.Handle("/", r)   http.ListenAndServe(":8080", nil)  }  func HomeHandler(w http.ResponseWriter, r *http.Request) {    fmt.Fprintf(w, "home")  } 

Answer 1

The database/sql package manages the connection pooling automatically for you.

sql.Open(..) returns a handle which represents a connection pool, not a single connection. The database/sql package automatically opens a new connection if all connections in the pool are busy.

Applied to your code this means, that you just need to share the db-handle and use it in the HTTP handlers:

package main  import (     "database/sql"     "fmt"     "github.com/gorilla/mux"     _ "github.com/go-sql-driver/mysql"     "log"     "net/http" )  var db *sql.DB // global variable to share it between main and the HTTP handler  func main() {     fmt.Println("starting up")      var err error     db, err = sql.Open("mysql", "root@unix(/tmp/mysql.sock)/mydb") // this does not really open a new connection     if err != nil {         log.Fatalf("Error on initializing database connection: %s", err.Error())     }      db.SetMaxIdleConns(100)      err = db.Ping() // This DOES open a connection if necessary. This makes sure the database is accessible     if err != nil {         log.Fatalf("Error on opening database connection: %s", err.Error())     }      r := mux.NewRouter()     r.HandleFunc("/", HomeHandler)      http.Handle("/", r)     http.ListenAndServe(":8080", nil) }  func HomeHandler(w http.ResponseWriter, r *http.Request) {     var msg string     err := db.QueryRow("SELECT msg FROM hello WHERE page=?", "home").Scan(&msg)     if err != nil {         fmt.Fprintf(w, "Database Error!")     } else {         fmt.Fprintf(w, msg)     } }