如何在代码中的多个位置传递* sql.DB变量？

Question

I read that you should not close the *sql.DB variable.

http://go-database-sql.org/accessing.html

And it also says that I should: "Pass it around as needed, or make it available somehow globally, but keep it open."

But this article says I should not use global variables but should use closures: https://medium.com/@benbjohnson/structuring-applications-in-go-3b04be4ff091

I found an example of closure here: https://gist.github.com/tsenart/5fc18c659814c078378d

My question is: How should I pass around this variable to different packages?

For example if I have a package called User like this:

package user

import "errors"

var userNotFound = errors.New("User was not found.")

// Should I have a pointer to sql.DB as a property of the User type?
type User struct {
    // db *sql.DB
    Id       int
    Email    string
    Username string
}

// Should I pass in *sql.DB as parameter in the function?
func FindById(id int) (*User, error) {
    // Access *sql.DB somehow

    // Do query and look if user with id is found

    // Should I return an error if the user is not found?
    return &User{}, nil
}

Should I then have a pointer to sql.DB as property of the User type? Or should I pass a pointer to it in the findById method?

If I want to find a user by it's id, how should I do all this from a main function like below?

func getUserById(db *sql.DB) httprouter.Handle {
    return func(w http.ResponseWriter, r *http.Request, ps httprouter.Params) {
        fmt.Fprint(w, ps.ByName("id"))

        // I will create a new(User) here, how should I use the *sql.DB in the user package?
    }
}

func main() {

    dsn := fmt.Sprintf("%s:%s@%s(%s:%s)/%s?charset=utf8",
        cfg.DbUser, cfg.DbPass, cfg.DbProtocol, cfg.DbAddress, cfg.DbPort, cfg.DbName)

    db, err := sql.Open("mysql", dsn)
    err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }

    router := httprouter.New()
    router.GET("/api/user/:id", getUserById(db))
    router.NotFound = &DefaultHandler{}
    log.Fatal(http.ListenAndServe(":8080", router))
}

How should I this? What is a good preferred way, or kind of a best practice?

Answer 1

Super simple? A global var db *sql.DB object. *sql.DB is thread-safe and therefore can be accessed concurrently.

Alternatively, you can define your query methods on a type that wraps *sql.DB. It's my opinion that func (u *User) FindByID(id string) (*User, error) doesn't make a whole lot of sense - you accept a User pointer but return a new pointer to a User?

As a simplified example, you could change your code to resemble the below:

type DB struct {
    *sql.DB
}

func NewDB(host, port string) (*DB, error) {
    db, err := sql.Open(...)
    if err != nil {
        return nil, err
    }

    return &DB{db}, nil 
}

func (db *DB) UserByID(id string) (*User, error) {
    res, err := db.Query(...)
    // etc.
}

func (db *DB) UsersList(limit int) ([]*User, error) {
    res, err := db.Query(...)
    // etc.
}

In order to call these functions from your handlers you can either:

• Use closures (as you are doing now)
• Define an "environment" or "services" struct that contains your DB type or just vanilla *sql.DB as a field and create your handlers as methods on this. This could also be a global (noting that any members MUST be thread safe).
• Define a custom handler type (my approach) where you call router.GET("/user/:id", GetUserByID(env) instead of using closures.

Some additional reading:

1. http://www.alexedwards.net/blog/organising-database-access (covers multiple approaches - one of the most comprehensive articles)
2. https://robots.thoughtbot.com/interface-with-your-database-in-go
3. https://medium.com/@benbjohnson/structuring-applications-in-go-3b04be4ff091
4. http://elithrar.github.io/article/http-handler-error-handling-revisited/#a-little-different