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.

If you can post a reproducible example here code.google.com/p/odbc/issues/list, I will try to fix it. — alex

Kavu Kavu · Accepted Answer · 2013-11-14T12:52:42

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.

Go/Golang sql.DB reuse in functions

2 Answers