GoLang: Losing scope in database

New to Go, come from .Net-land so bear with me... Would love some guidance on architecting my Go app specifically with regard to managing and reusing DB connections.

I have split up my db code into a package to allow for db-lookups into package that handles sql lookups like a repository layer. I want to have flexibility to decouple my db layer from my app services, so I can easily replace my database if necessary.

Basically I am looking for some guidance on how and when to handle sql connect and holding a sql.DB pointer in the app. Do I need to hold onto a global pointer in main.go or can I manage the connection in my mysql package?

Here's my code:

package mysqlstorage

import (
	"database/sql"
	"fmt"
	"log"
	"types"
)

var db *sql.DB

func Connect() {
	db, dberr := sql.Open(“<CONNECTION_STRING>“)

	if dberr != nil {
		fmt.Println(dberr)
	}
}

func SaveUser(u types.User) {
    // use db here! 
    ....
}

func GetUser(id string) types.User {
    // use db here!
    ....
}

On running my main.go and using my userservices package to attempt to save a user, I hit a problem where I appear to lose scope of my pointer:-

2015/05/03 17:49:08 http: panic serving [::1]:50106: runtime error: invalid memory address or nil pointer dereference goroutine 7 [running]: net/http.func·011() 
/usr/local/Cellar/go/1.4.2/libexec/src/net/http/server.go:1130 +0xbb database/sql.(*DB).conn(0x0, 0x10, 0x0, 0x0) 	
/usr/local/Cellar/go/1.4.2/libexec/src/database/sql/sql.go:634 +0x7ae database/sql.(*DB).Ping(0x0, 0x0, 0x0) 	
/usr/local/Cellar/go/1.4.2/libexec/src/database/sql/sql.go:462 +0x3a mysqlstorage.SaveUser(0x0, 0xc20805425a, 0x7, 0xc208054280, 0x11, 0xc208054268, 0x6, 0xc208054274, 0x5) 	
/Users/<USERNAME>/Desktop/go/<APPNAME>/api/src/mysqlstorage/mysqlstorage.go:24
    +0x35 services.CreateUser(0x57c148, 0xc2080563c0, 0xc2080329c0) 	
/Users/<USERNAME>/Desktop/go/<APPNAME>/api/src/services/userservices.go:30
    +0x398 net/http.HandlerFunc.ServeHTTP(0x3d02a0, 0x57c148, 0xc2080563c0, 0xc2080329c0) 	
/usr/local/Cellar/go/1.4.2/libexec/src/net/http/server.go:1265 +0x41 github.com/gorilla/mux.(*Router).ServeHTTP(0xc20803c140, 0x57c148, 0xc2080563c0, 0xc2080329c0) 	
/Users/<USERNAME>/Desktop/go/<APPNAME>/api/src/github.com/gorilla/mux/mux.go:98
    +0x297 net/http.(*ServeMux).ServeHTTP(0xc20803a720, 0x57c148, 0xc2080563c0, 0xc2080329c0) 	
/usr/local/Cellar/go/1.4.2/libexec/src/net/http/server.go:1541 +0x17d net/http.serverHandler.ServeHTTP(0xc2080543c0, 0x57c148, 0xc2080563c0, 0xc2080329c0) 	
/usr/local/Cellar/go/1.4.2/libexec/src/net/http/server.go:1703 +0x19a net/http.(*conn).serve(0xc208056320) 	
/usr/local/Cellar/go/1.4.2/libexec/src/net/http/server.go:1204 +0xb57 created by net/http.(*Server).Serve 	
/usr/local/Cellar/go/1.4.2/libexec/src/net/http/server.go:1751 +0x35e

Any guidance would appreciated! Thanks guys!

I think that your problem is that db in the Connect() scope is shadowed due to your use of the := operator. If you change your method to declare a dberr var:

func Connect() {
    var dberr error
    db, dberr = sql.Open(“<CONNECTION_STRING>“)

    if dberr != nil {
        fmt.Println(dberr)
    }
}

Your code will work as intended

As noted by @Lander your out-of-scope happens because you are creating a local variable db through the db, err := assignment which then goes out of scope when the Connect func returns.

Generally on the use of sql.DB

From the database/sql package:

> DB is a database handle representing a pool of zero or more underlying connections. It's safe for concurrent use by multiple goroutines.
>
>The sql package creates and frees connections automatically; it also maintains a free pool of idle connections.

and

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

That said you want to open the connection once and then keep it open for the duration of the process. With that the only reliable way to actually close it when you are done (or run into a fatal or panic) would be to do the idiomatic go defer db.Close() right after opening it which would need to be done in the main function.

You could still keep your Connect function in your package and also use it to initialize the global package variable db, but call it from main once and return *sql.DB to main so you can defer db.Close().