I am using mysql with golang and reading this doc. It says
Although it’s idiomatic to Close() the database when you’re finished with it, the sql.DB object is designed to be long-lived. Don’t Open() and Close() databases frequently.
I don't know how long should the connnection be. For now, I open the connection each http request if necessary. Is it too frequently?
There is no authoritative answer, as it depends on the driver you're using. If you're using either:
... then you shouldn't close your sql.DB at all. The sql.DB in those cases represents a connection pool, and using it in your handlers/requests/functions means you are just using a connection from that pool (if available).
e.g.
var db *sql.DB
func setup() error {
var err error
db, err = sql.Open(...)
if err != nil {
log.Fatal(err)
}
// Other setup-related activities
}
func main()
err := setup()
if err != nil {
log.Fatal(err)
}
// No need to call `defer db.Close()` here
http.ListenAndServe(...)
}
func SomeHandler(w http.ResponseWriter, r *http.Request) {
u := User{}
res, err := db.GetThings(&u)
// No need to close this here - it's just a connection dynamically pulled
// from our sql.DB connection pool in most cases
}
Further down in the same document it reads:
Instead, create one sql.DB object for each distinct datastore you need to access, and keep it until the program is done accessing that datastore. Pass it around as needed, or make it available somehow globally, but keep it open. And don’t Open() and Close() from a short-lived function. Instead, pass the sql.DB into that short-lived function as an argument.
If you don’t treat the sql.DB as a long-lived object, you could experience problems such as poor reuse and sharing of connections, running out of available network resources, or sporadic failures due to a lot of TCP connections remaining in TIME_WAIT status. Such problems are signs that you’re not using database/sql as it was designed.
Opening and closing database connections is a costly operation so you want to avoid that as much as possible. you definitely don't want to close the connection after each request (unless there's only a few a day and even then you can keep it open as long as the app runs)
The database/sql package uses connection pooling under the hood, so you don't have to worry about managing multiple connections.