Database connection best practice

Question

I've an app that uses net/http. I register some handlers with http that need to fetch some stuff from a database before we can proceed to writing the response and be done with the request.

My question is in about which the best pratice is to connect to this database. I want this to work at one request per minute or 10 request per second.

I could connect to database within each handler every time a request comes in. (This would spawn a connection to mysql for each request?)

package main

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "net/http"
    "fmt"
)

func main() {

    http.HandleFunc("/",func(w http.ResponseWriter, r *http.Request) {
        db, err := sql.Open("mysql","dsn....")
        if err != nil {
            panic(err)
        }
        defer db.Close()

        row := db.QueryRow("select...")
        // scan row

        fmt.Fprintf(w,"text from database")
    })

    http.ListenAndServe(":8080",nil)
}

I could connect to database at app start. Whenever I need to use the database I Ping it and if it's closed I reconnect to it. If it's not closed I continue and use it.

package main

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "net/http"
    "fmt"
    "sync"
)

var db *sql.DB
var mutex sync.RWMutex

func GetDb() *sql.DB {

    mutex.Lock()
    defer mutex.Unlock()

    err := db.Ping()
    if err != nil {
        db, err = sql.Open("mysql","dsn...")
        if err != nil {
            panic(err)
        }
    }

    return db
}

func main() {

    var err error
    db, err = sql.Open("mysql","dsn....")
    if err != nil {
        panic(err)
    }

    http.HandleFunc("/",func(w http.ResponseWriter, r *http.Request) {

        row := GetDb().QueryRow("select...")
        // scan row

        fmt.Fprintf(w,"text from database")
    })

    http.ListenAndServe(":8080",nil)
}

Which of these ways are the best or is there another way which is better. Is it a bad idea to have multiple request use the same database connection?

It's unlikly I will create an app that runs into mysql connection limit, but I don't want to ignore the fact that there's a limit.

Answer 1

The best way is to create the database once at app start-up, and use this handle afterwards. Additionnaly, the sql.DB type is safe for concurrent use, so you don't even need mutexes to lock their use. And to finish, depending on your driver, the database handle will automatically reconnect, so you don't need to do that yourself.