Mocking Database in Go

We all know writing a unit test that actually hits any database is not writing a unit test, but many still do it even at companies running production-grade software.

Just like writing a test that interacts with the filesystem, which varies depending on the platform and each computer's configuration, a test that needs to set up a "happy" state for a test database, run test against it, and tear down the state is just trying to control the uncontrollable. It's just more bugs in the test waiting to happen.

So I'd like to present a quick walk through to mock a database in Go. More specifically, the *sql.DB.

Here is a sample function that uses the database to do something.

func SaveUserToDB(db *sql.DB, user *User) error {
        _, err := db.Exec(`                                                                                                                          
            INSERT INTO usr (name, email_address, created)                                                                                                                                                                                                                                                                                                                                                                                       
            VALUES ($1, $2, $3);`, 
            user.Name, user.EmailAddress, time.Now(),
        ) 
        if err != nil {
                return err
        }
        return nil
}

In practice, it isn't in your best interest to write a test for this succinct function. It does nothing more than calling Exec() on the DB instance. If you trust the well-tested package, you shouldn't have to test it. However, for the example it is perfect.

Always opt for a dependency injection and make the function accepts the *sql.DB instance as an argument.

In this case, we want to test the Exec method. We need to create an interface that would simply qualify our mock as a *sql.DB. It's time to peek into database/sql documentation and check out DB.Exec's signature:

func (db *sql.DB) Exec(query string, args ...interface{}) (sql.Result, error)

Sweet, now whip up an interface with this signature:

type SQLDB interface {
        Exec(query string, args ...interface{}) (sql.Result, error)
}

Since *sql.DB implements this method, it is qualified as a SQLDB.
Now we can comfortably create an implementation of our own mock DB:

type MockDB struct {}

// Implement the SQLDB interface
func (mdb *MockDB) Exec(query string, args ...interface{}) (sql.Result, error) {
        return nil, nil
}

Note that we are returning nil for sql.Result since we don't care about this value. If you do, you'll need to implement another interface for the type.

Now we want to record the state of the mock when it is called, so we will add a useful field named callParams to the MockDB struct to record the parameters when it is called:

type MockDB struct {
        callParams []interface{}
}

func (mdb *MockDB) Exec(query string, args ...interface{}) sql.Result, error) {
        mdb.callParams = []interface{}{query}
        mdb.callParams = append(mdb.callParams, args...)

        return nil, nil
}

// Add a helper method to inspect the `callParams` field
func (mdb *MockDB) CalledWith() []interface{} {
        return mdb.callParams
}

Last change (but very important one) is to make our function in test SaveUserToDB to accept SQLDB interface instead of *sql.DB instance. This way we can slip our *MockDB in.

func SaveUserToDB(db SQLDB, user *User) error {
        // No changes
}

Here is an example of how we write test for this function:

import (
        "testing"
        "github.com/stretchr/testify/assert"
)

func TestSaveUserToDB(t *testing.T) {

        // Create a new instance of *MockDB
        mockDB := new(MockDB)

        // Call the function with mock DB
        SaveUserToDB(mockDB, &User{"Joe", "[e-mail]"})


        params := mockDB.CalledWith()

        // Normally we shouldn't test equality of query strings
        // since you might miss some formatting quirks like newline
        // and tab characters.
        expectedQuery := `                                                                                                                          
            INSERT INTO usr (name, email_address, created)                                                                                                                                                                                                                                                                                                                                                                                       
            VALUES ($1, $2, $3);`

        // Assert that the first parameter in the call was SQL query
        assert.Equal(t, params[0], expectedQuery)
}

Hope now this would encourage you to write more "stoic" DB tests in your Go code. If you're interested in what we're doing with Go, check out Referkit, a developer-friendly API and SDK for building invite-only campaigns for apps and services.

Comments

[XO39]

Thank you Joe for this amazing tutorial, I was looking for something like.

Can you post another tutorial about mocking/implementing an interface for the return type? I tried to do the same steps for the return type of Query, but I couldn't get it to work, but always getting error:

*sql.DB does not implement SQLDB (wrong type for Query method)
    have Query(string, ...interface {}) (*sql.Rows, error)
    want Query(string, ...interface {}) (SQLRows, error)

I just created a new interface for the return type of the Query function:

type SQLRows interface {
    Close() error
    Next() bool
    Scan(dest ...interface{}) error
}

then added the function Query to the SQLDB interface (but changed it's return type to the new created inteface:

type SQLDB inteface {
    Exec(query string, args ...interface{}) (sql.Result, error)
    Query(query string, args ...interface{}) (SQLRows, error)

then created new MockRows struct:

type MockRows struct {}

func (m MockRows) Query(query string, args ...interface{}) (SQLRows, error) {
    ///
}

But that didn't work, got the error mentioned above!

How can I make it to work?

Thanks in advance!

[Jolyon Direnko-Smith]

The problem is stated clearly in the error message: The signature of the Query() method in your interface is wrong. It must match the signature of the sql.DB Query method exactly, including the return type.

[Jesse Phillips]

I would like to cation testing at this specificity. I realize this example is limited in the logic paths though I would expect the SQL query to be abstracted out of the logic.

If the query becomes more complex then your test is harder to review for correctness. Try to avoid query building in your code and prefer stored procedures. Stored procedures don't need tested, they're magic.

[Pan Chasinga]

Thanks for the recommendation.