DEV Community

Cover image for Mocking Database in Go
Pan Chasinga
Pan Chasinga

Posted on

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

Sweet, now whip up an interface with this signature:

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

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
Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

import (

func TestSaveUserToDB(t *testing.T) {

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

        // Call the function with mock DB
        SaveUserToDB(mockDB, &User{"Joe", ""})

        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)
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (4)

xo39 profile image

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)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

then created new MockRows struct:

type MockRows struct {}

func (m MockRows) Query(query string, args ...interface{}) (SQLRows, error) {
Enter fullscreen mode Exit fullscreen mode

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

How can I make it to work?

Thanks in advance!

deltics profile image
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.

jessekphillips profile image
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.

pancy profile image
Pan Chasinga

Thanks for the recommendation.