When should I close the database connection in this simple web app?

Question

I’m writing a simple Go web application that uses PostgreSQL. My main function looks like

var db *sql.DB

func main() {
    var err error
    db, err = sql.Open("postgres", "...")
    if err != nil {
        log.Fatalf("Couldn't connect to the database: %v", err)
    }

    http.HandleFunc("/whatever", whateverHandler)
    http.ListenAndServe("127.0.0.1:8080", nil)
}

It seems like I should be calling Close() on the database connection at some point, but when? This application serves forever (i.e. until I kill it with ^C). If I put code after the ListenAndServe call it doesn’t get run, because my ^C has killed the entire application. Should my application be structured differently?

Answer 1

In this particular case, I tend to say you don't even need to bother: the connection will be closed when the program end, so you won't leak anything.

If you really need to close things properly, the simpler choice is to use a graceful server, and defer the resources closing.

Or, if your use case is more complicated, do it by hand by catching signals and gracefully shutting down your own way (using a closing channel for example).

Answer 2

It is important to understand that sql.Open() does not open a connection to the database... http://golang.org/pkg/database/sql/#Open

Open may just validate its arguments without creating a connection to the database. To verify that the data source name is valid, call Ping.

Also it is important to understand that the driver handles the maintance of connections. So you will likely want to keep it open for the lifetime of your app.

The returned DB is safe for concurrent use by multiple goroutines and maintains its own pool of idle connections. Thus, the Open function should be called just once. It is rarely necessary to close a DB.