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!

score 7 · Accepted Answer · edited May 23 '17 at 12:34

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

Would you say the way I have described in my original post is a decent way to do it? Set a db global variable for my package and set it with an `init` then `ping` it? Where would I put the `defer close`? Your answer is fantastic and answers most questions, just the one other thing was what's the best way to open the initial connection. Like if I don't have a `main()` function. Thanks! — Datsik, Nov 07 '15 at 03:58
@Datsik - one global pool (either as a global var, or with a pointer you pass around explicitly) is fine - see https://golang.org/pkg/database/sql/#Open - `Thus, the Open function should be called just once.` — elithrar, Nov 07 '15 at 04:14
Hey thanks for the fantastic answer. Would it be bad practice to have a file with just an init because as it stands I have 2 files in my package with no main because I don't need one can I add a third one and call it `init.go` just to declare my db? — Datsik, Nov 07 '15 at 16:05

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

1 Answers1