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")

}

Jonathan Hall · Accepted Answer · 2014-11-17 15:04:24Z

69

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)
    }
}

edited Nov 17, 2014 at 15:04

Jonathan Hall

80.5k19 gold badges162 silver badges206 bronze badges

answered Jun 29, 2013 at 20:04

Julien Schmidt

1,0608 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Jason Over a year ago

Hi Julien, thanks for answering. Couldn't expect a better source than the author of the Go-MySQL-Driver! It seems there might be a typo in the code as I get a compile error: ./main.go:18: cannot assign *sql.DB to db (type sql.DB) in multiple assignment

Julien Schmidt Over a year ago

Sorry, I fixed the type now. @fmt.Println.MKO that this doesn't work is still not true. The database/sql is designed exactly for concurrent use cases like this.

Julien Schmidt Over a year ago

FYI, here are a few more simple examples how to access a DB in HTTP handlers: github.com/TechEmpower/FrameworkBenchmarks/blob/master/go/src/…

Jason Over a year ago

Thanks Julien! It works perfectly. Nice work on the driver btw - I remember watching Go climb in the framework benchmarks over the last few rounds which led me to start learning Go because of the raw speed.

Julien Schmidt Over a year ago

Sounds a lot like the beloved := shadowning. Let me guess you have db, err := sql.Open in your code? This uses a local db var instead of the global one.

|

Collectives™ on Stack Overflow

How to share mysql connection between http goroutines?

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related