How frequently should I be calling sql.Open in my program?

Question

As the title says I don't know if having multiple sql.Open statements is a good or bad thing or what or if I should have a file with just an init that is something like:

var db *sql.DB


func init() {
    var err error
    db, err = sql.Open
}

just wondering what the best practice would be. Thanks!

Answer 1

You should at least check the error.
As mentioned in "Connecting to a database":

Note that Open does not directly open a database connection: this is deferred until a query is made. To verify that a connection can be made before making a query, use the Ping function:

if err := db.Ping(); err != nil {
  log.Fatal(err)
}

After use, the database is closed using Close.

If possible, limit the number of opened connection to a database to a minimum.
See "Go/Golang sql.DB reuse in functions":

You shouldn't need to open database connections all over the place.
The database/sql package does connection pooling internally, opening and closing connections as needed, while providing the illusion of a single connection that can be used concurrently.

As elithrar points out in the comment, database.sql/#Open does mention:

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.

As mentioned here

Declaring *sql.DB globally also have some additional benefits such as SetMaxIdleConns (regulating connection pool size) or preparing SQL statements across your application.

You can use a function init, which will run even if you don't have a main():

var db *sql.DB
func init() {
    db, err = sql.Open(DBparms....)
}

init() is always called, regardless if there's main or not, so if you import a package that has an init function, it will be executed.
You can have multiple init() functions per package, they will be executed in the order they show up in the code (after all variables are initialized of course).