如何处理Go应用中的Db连接打开/关闭？

Question

I have a set of functions in my web API app. They perform some operations on the data in the Postgres database.

func CreateUser () {
    db, err := sql.Open("postgres", "user=postgres password=password dbname=api_dev sslmode=disable")
    // Do some db operations here
}

I suppose functions should work with db independently from each other, so now I have sql.Open(...) inside each function. I don't know if it's a correct way to manage db connection.

Should I open it somewhere once the app starts and pass db as an argument to the corresponding functions instead of opening the connection in every function?

Answer 1

Opening a db connection every time it's needed is a waste of resources and it's slow.

Instead, you should create an sql.DB once, when your application starts (or on first demand), and either pass it where it is needed (e.g. as a function parameter or via some context), or simply make it a global variable and so everyone can access it. It's safe to call from multiple goroutines.

Quoting from the doc of sql.Open():

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.

You may use a package init() function to initialize it:

var db *sql.DB

func init() {
    var err error
    db, err = sql.Open("yourdriver", "yourDs")
    if err != nil {
        log.Fatal("Invalid DB config:", err)
    }
}

One thing to note here is that sql.Open() may not create an actual connection to your DB, it may just validate its arguments. To test if you can actually connect to the db, use DB.Ping(), e.g.:

func init() {
    var err error
    db, err = sql.Open("yourdriver", "yourDs")
    if err != nil {
        log.Fatal("Invalid DB config:", err)
    }
    if err = db.Ping(); err != nil {
        log.Fatal("DB unreachable:", err)
    }
}