Have you ever started writing an application in Go that reads and writes to a database only to later be confused about how to properly write tests for it? How do you ensure that your tests run quickly but are also effectively testing the edge cases? When and what do you mock in your tests, if at all?

Most examples on how to write tests in Go conveniently assume everything is in memory and leave out how to effectively write tests with any persistence.

Hopefully, this post can answer some of your questions while providing examples of a testing strategy that works with an actual database.

An Example App

Let’s assume that you’ve started writing a standard reading list application that keeps track of books that you want to read or have read and their authors. This application persists its data to a SQLite database.

This app is structured in a few layers:

┌─────────────────────────┐
│                         │
│         API             │ ────► User-facing. Ex: JSON REST API
│                         │
├─────────────────────────┤
│                         │
│        Service          │ ────► Enforces 'business' rules
│                         │
├─────────────────────────┤
│                         │
│        Storage          │ ────► Reads/Writes to the database
│                         │
└─────────────────────────┘

note: We aren’t concerned about the API layer for this post but only about testing the service and storage layers.

To model the business logic, the ‘data models’ look something like this:

type Book struct {
    ID      int
    AuthorID int
    Title   string
    ISBN    string
    Subject string
}

type Author struct {
    ID int
    FirstName string
    LastName  string
}

Next, you write all the code required to do basic CRUD in the application. This code is responsible for taking the ‘models’ in Go code and converting the data to SQL and back again.

The actual implementation of this CRUD logic is outside of the scope of this post, but there are some excellent tutorials on how to use SQL in Go such as:

• https://golang.org/doc/tutorial/database-access
• https://www.alexedwards.net/blog/introduction-to-using-sql-databases-in-go
• https://www.sohamkamani.com/golang/sql-database/

A small snippet of this CRUD code may look something like this for inserting a book:

type BookStore struct {
    db *sql.DB
}

func (s *BookStore) InsertBook(ctx context.Context, b *Book) error {
    const stmt = "INSERT INTO books (author_id, title, isbn) VALUES ($1, $2, $3)"

    res, err := s.db.ExecContext(ctx, stmt, b.AuthorID, b.Title, b.ISBN)
    if err != nil {
       return fmt.Errorf("could not insert row: %w", err)
    }

    if _, err := result.RowsAffected(); err != nil {
        return fmt.Errorf("could not get affected rows: %w", err)
    }

    return nil
}

Again, these details don’t matter for the sake of this post, but what does matter is that we have a type BookStore that can perform actions on books such as inserting, reading, updating, and deleting them in the database.

Next, you flesh out the rest of the BookStore with methods such as UpdateBook, GetBook, and DeleteBook. Of course, you will also need an AuthorStore to manage the authors in your app.

After you’ve completed writing all the code and wiring everything up, you set out to ensure that the code is bug-free (ha) and everything works as expected. You do this manually first, by inserting, updating, deleting books and authors in your application, but as an experienced developer, you know that you really should write tests!

Thankfully, the Go ecosystem makes testing first class via its built-in testing package!

Let’s Write Some Tests

Now we get to the meat of the issue, how/what do you test?

Before jumping into more code, let’s go over a few self-imposed constraints:

1. We have logically separated the storage and service layers in the application.
2. We want these tests to run quickly.
3. We want to test that we can correctly interact with the database and also test that the business logic is correct.
4. We want to keep the test data separate from production data.

These constraints raise an interesting question, namely: When should you mock in your tests and when should you interact with the real database?

What Should we Mock?

What even is a mock?

According to Wikipedia:

Mock objects meet the interface requirements of, and stand in for, more complex real ones.

In this application, the database is a complex, real system, and it is external to the code, so it seems like a good candidate to mock. After all, since mocks are in memory, it would satisfy requirements 2 and 4 above, as it would allow the tests to run quickly and keep the test data separate from production data in the database.

However, I would argue that mocking the database when testing your SQL code is an anti-pattern that you should avoid, mainly because it violates requirement 3 since it isn’t actually testing that we can correctly interact with a real database as we are mocking the database itself!

Because of this, I propose that you should not use mocks when testing the persistence layer and only use mocks for testing the service layer and above.

Finally, Some Tests

To setup the tests for the storage layer, you’ll need to first create a real SQL database and also create the books and authors tables.

note: In this example, we’re using SQLite, but the same can be done for a hosted database such as MySQL or Postgres.

In the db_test.go file we can make use of Go’s built-in testing package and the TestMain function. TestMain will run once before the actual tests, allowing us to do setup and then tear down of the test database.

// storage/db_test.go

func TestMain(m *testing.M) {
    // os.Exit skips defer calls
    // so we need to call another function
    code, err := run(m)
    if err != nil {
        fmt.Println(err)
    }
    os.Exit(code)
}

func run(m *testing.M) (code int, err error) {
    // pseudo-code, some implementation excluded:
    //
    // 1. create test.db if it does not exist
    // 2. run our DDL statements to create the required tables if they do not exist
    // 3. run our tests
    // 4. truncate the test db tables

    db, err := sql.Open("sqlite3", "file:../test.db?cache=shared")
    if err != nil {
        return -1, fmt.Errorf("could not connect to database: %w", err)
    }

    // truncates all test data after the tests are run
    defer func() {
        for _, t := range string{"books", "authors"} {
            _, _ = db.Exec(fmt.Sprintf("DELETE FROM %s", t))
        }

        db.Close()
    }()

    return m.Run(), nil
}

Now we can write the actual storage layer tests that test the basic CRUD functionality like so:

// storage/book_test.go

func TestInsertBook(t *testing.T) {
    store := &BookStore{
        db: db,
    }

    b, err := store.InsertBook(context.TODO(), &Book{
        Title: "The Go Programming Language",
        AuthorID: 1,
        ISBN: "978-0134190440",
        Subject: "computers",
    })

    // using https://github.com/stretchr/testify library for brevity
    require.NoError(t, err)

    assert.Equal(t, "The Go Programming Language", b.Name)
    assert.Equal(t, 1, b.AuthorID)
    assert.Equal(t, "978-0134190440", b.ISBN)
    assert.Equal(t, "computers", b.Subject)
    assert.NotZero(t, b.ID)
}

The storage tests will now be reading and writing to the database, so we can be more confident that our code works as intended.

As you probably noticed, this example test is very simple as it does not test cases such as missing fields or invalid values. This is because I believe that this validation type code should be abstracted from the concrete storage code as much as possible since validation is really a business logic concern.

Testing Our Service Layer

Now that we know that the code that interacts with the database can be well tested and uses actual SQL, we can start testing our service layer by introducing mocks when needed.

To do this, we’ll need a slight refactor of our Store code by abstracting away the implementation and instead relying on interfaces in the service layer.

Here is what that could look like:

// storage/book.go

// 'new' exported BookStore interface that can be mocked
type BookStore interface {
    InsertBook(context.Context, *Book) error
    // ....
}

// exported 'constructor'
func NewBookStore(db *sql.DB) *bookStore {
    return &bookStore{
        db: db,
    }
}

// unexported SQL implementation
type bookStore struct {
    db      *sql.DB
}

func (s *bookStore) InsertBook(ctx context.Context, b *Book) error {
    const stmt = "INSERT INTO books (author_id, title, isbn) VALUES ($1, $2, $3)"

    res, err := s.db.ExecContext(ctx, stmt, b.AuthorID, b.Title, b.ISBN)
    if err != nil {
       return fmt.Errorf("could not insert row: %w", err)
    }

    if _, err := result.RowsAffected(); err != nil {
        return fmt.Errorf("could not get affected rows: %w", err)
    }

    return nil
}

A few things changed in the code above:

1. We abstracted BookStore to be an interface so now it can be mocked
2. We made the actual SQL implementation unexported
3. We created a NewBookStore ‘constructor’ function that we will use in our actual production code to return the ‘real’ implementation

Now in our service layer code, we can rely on the BookStore interface like so:

// service/book.go

// BookService relies on the BookStore
type BookService type {
   store *storage.BookStore
}

func (s *BookService) CreateBook(ctx context.Context, b *Book) error {
    // do validation/business rule validation here
    // .. more book stuff
    // finally, insert into the DB
    return s.store.InsertBook(ctx, b)
}

We can now mock the responses from the BookStore interface type to test any complicated combination of errors/invalid objects that we want!

Mocking is too big of a topic to expand upon here as this post is already getting long, but here are some good tutorials the subject:

• https://betterprogramming.pub/a-gomock-quick-start-guide
• https://tutorialedge.net/golang/improving-your-tests-with-testify-go

And here are two great mocking libraries for Go:

• https://github.com/golang/mock
• https://github.com/stretchr/testify/mock

Of course, you can always hand write your mocks if you want!

Teardown

Some may argue that writing tests that connect to a ‘real’ database to read and write data are actually integration tests and not unit tests.. and they might be correct, however I ask, does it matter?

I’d rather be confident that my test suite covers as much of my code as possible (to a point) than be a testing ‘purest’ that insists that all unit tests only exercise code paths with no external dependencies.

I’ve encountered enough issues where code that I thought was well tested resulted in DB syntax or connection errors when deployed to production, simply because it depended entirely on mocks.

I think a user on the Gopher slack put it best when they said:

Don’t try to mock SQL. you’ll only end up unhappy

— dylanb

You need to actually test your SQL code.

If you’d like to see this type of testing in actual code that also supports several databases, check out my open-source feature flag solution Flipt as I use this pattern pretty extensively in the storage/sql package for my tests.

Hit me up on Twitter and let me know what you thought of this post or if you have any tips regarding testing.