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.

Turnkey

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

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.

Anthiathia

So I should have one reference to sql.Open and the resulting *sql.DB. It seems you're right about the pooling, I only get errors when I try to execute a query. Oddly my go application stops being able to connect to MSSQL, but outside of it, I can run tests that are able to connect, as long as I'm not running stored procedures. It's all very strange. – Turnkey

One thing to remember when trying to debug database/sql applications is that, because of the connection pooling, connection errors often don't show up when you do sql.Open; they wait till you run the first query on the connection. – Anthiathia

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.

Tegantegmen