Test driven development to check database queries involved methods

I use a global variable to store the data source (or connection string) of current database and set to different value in test function. Since there is only one database I need to operate so I choose the easiest way.

I had a similar question not long ago when refactoring some of my own tests, and there's a couple of ways you can do it:

a) Provide an exported type and an Open or Connect function that returns it - e.g.

type DB struct {
    db *sql.DB
}

// Using http://jmoiron.github.io/sqlx/ for this example, but
// it has the same interface as database/sql
func Open(opts *Options) (*DB, error) {
    db, err := sqlx.Connect(opts.Driver, fmt.Sprintf("host=%s user=%s dbname=%s sslmode=%s", opts.Host, opts.User, opts.Name, opts.SSL))
    if err != nil {
        return nil, err
    }

    return &DB{db}, nil
}

... and then each of your tests, write setup & teardown functions that return an instance of *DB that you define your database functions on (as methods - i.e. func (db *DB) GetUser(user *User) (bool, error)):

// Setup the test environment.
func setup() (*DB, error) {
    err := withTestDB()
    if err != nil {
        return nil, err
    }

    // testOptions is a global in this case, but you could easily
    // create one per-test
    db, err := Open(testOptions)
    if err != nil {
        return nil, err
    }

    // Loads our test schema
    db.MustLoad()
    return db, nil
}

// Create our test database.
func withTestDB() error {
    db, err := open()
    if err != nil {
        return err
    }
    defer db.Close()

    _, err = db.Exec(fmt.Sprintf("CREATE DATABASE %s;", testOptions.Name))
    if err != nil {
        return err
    }

    return nil
}

Note that this is somewhat "integration" testing, but I strongly prefer to test against a "real" database since mocking the interface won't help you catch issues with your queries/query syntax.

b) The alternative, although less extensible on the application side, is to have a global db *sql.DB variable that you initialise in init() within your tests—since tests have no guaranteed order you'll need to use init()—and then run your tests from there. i.e.

var db *sql.DB

func init() {
    var err error
    // Note the = and *not* the assignment - we don't want to shadow our global
    db, err = sqlx.Connect(...)
    if err != nil {
        ...
    }

    err := db.loadTestSchema
    // etc.
}

func TestGetUser(t *testing.T) {
   user := User{}
   exists, err := db.GetUser(user)
   ...
}

You can find some practical examples in drone.io's GitHub repo, and I'd also recommend this article on structuring Go applications (especially the DB stuff).