Go/Golang sql.DB reuse in functions

Question

sql.Open() returns a variable of type *sql.DB

I have a function that calls 10 other functions that all need to make database calls

Is it more correct/efficient to:

• Send the *sql.DB pointer to every function, or
• Create a new *sql.DB object in each function

Meaning

func DoLotsOfThings() {
    db, _ := sql.Open()
    defer db.Close()
    DoTask1(db)
    DoTask2(db)
}

or

func DoLotsOfThings() {
    DoTask1()
    DoTask2()
}

func DoTask1() {
    db, _ := sql.Open()
    defer db.Close()
}

func DoTask1() {
    db, _ := sql.Open()
    defer db.Close()
}

The reason why I'm asking is because I am currently sending the pointer to each function and my driver seems to break. I'm using http://code.google.com/p/odbc , which leads me to believe each function should have its own, and that I can rely on the driver's internals.

EDIT

RE driver breakage, it only happens under high traffic environments. And it only happens after say, ten minutes or so of time. Which leads me to believe that there is some sort of memory leak that makes using the driver stop working. However I defer db.Close() for every instance of *sql.DB, so I don't know what else I can do to resolve this issue.

andybalholm says the connection pooling is handled internally, which seems to be accurate, because it only breaks after I attempt to execute something, not when I invoke sql.Open()

If I leave my Go app running, it will not be able to execute any sort of SQL queries, but if I attempt to run other Go tests separately connecting to MSSQL and running queries, it works.

Answer 1

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.

Probably you need to look elsewhere for the cause of your driver breakage. Some more details about that would make it easier for people to figure out what is going on.

Answer 2

Declare a var db *sql.DB globally, and then reuse it across your code. Here is an example (simplified):

var db *sql.DB

func DoLotsOfThings() {
    DoTask1(db)
    DoTask2(db)
}

func main() {
  db, _ = sql.Open() # or whatever you use
  defer db.Close()
  DoLotsOfThings()
}

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