Connection pooling with SQL driver in Go

Question

What is the best practice for storing a connection to a database in Go language?

In Java for example you can use singletons, some IoC containers like Spring. What is the best practice in it's lifecycle? How to release it after application close?

Answer 1

There is nothing wrong about using a Singleton pattern here too.

I would use something like this:

var db *sql.DB = nil

func GetDB() (*sql.DB, error) {
    if db == nil {
        conn := fmt.Sprintf("host=%s user=%s password=%s dbname=%s sslmode=require",
            DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
        log.Println("Creating a new connection: %v", conn)

        d, err := sql.Open("postgres", conn)
        if err != nil {
            return nil, err
        }
        db = d
    }

    return db, nil
}

With this exported function you can receive a connection from all other packages.

Update of the answer according to the comments (thanks @all for the information)!:

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.¹

It is rare to Close a DB, as the DB handle is meant to be long-lived and shared between many goroutines.²

I would say that there is no forcing reason to call close on the database connection. I found no other statements. Despite this I would use a defer GetDB().close() in the main function - just for the completeness of the code.

Another thing I would like to note is that the connection should be verified by a db.Ping() otherwise the connection could be established but the database may not exist.

With this new information I wouldn't bother using some mutexes to ensure that the database is established. I would create a new DBInit() and run it inside the init() function of the main package.