Lesson 15. Mock DB for testing HTTP API in Go and achieve 100% coverage

In previous lesson we learn how to implement the API, when it comes to testing. This tutorial demonstrates how to use gomock to mock your database for testing RESTful HTTP APIs in Go, allowing you to write faster, more robust, and independent tests that can achieve 100% coverage.

When testing HTTP APIs in Go, you have two main approaches:

Mock the database
Connect to a real database

1. Why Mock the Database?

Mocking the database instead of connecting to a real one offers several advantages:

Independent Tests: Each test uses its own in-memory mock, preventing data conflicts between tests.
Faster Execution: Tests run significantly faster as there’s no need for network communication with a database.
100% Test Coverage: Easily simulate edge cases like database errors or connection issues, which is difficult with a real database.

The code that interacts with the real database should already be separately tested, so we can be confident in our mocks as long as they implement the same interface as the real database wrapper.

Two Approaches to Mocking

Fake DB Implementation: Create a struct that implements the interface using in-memory storage (maps, slices). Simple, but requires a lot of extra code for testing only.
Stub-based Mocking ✅ (Recommended): Use tools like GoMock to generate mocks that return hard-coded values. Define what each function should return in different scenarios. Cleaner, faster, less boilerplate.

We’ll focus on the stub-based approach using GoMock because it’s more maintainable and requires less code.

2. Prerequisites: Setting up `gomock`

First, you need to install gomock and the mockgen tool. Open gomock github page offical is not maintain by Google, we are using its fork from https://github.com/uber-go/mock

go install go.uber.org/mock/mockgen@latest

After running above command, a mockgen binary file will be available in the go/bin folder in our system.

ls -l ~/go/bin
total 181120
-rwxr-xr-x  1 userName  staff  12747570 Jul 11 11:04 air
-rwxr-xr-x  1 userName  staff  37898866 Jun 20 09:23 gopls
-rwxr-xr-x  1 userName  staff   9557154 Aug  1 17:02 mockgen
-rwxr-xr-x  1 userName  staff  14709474 Jul 11 11:03 staticcheck
-rwxr-xr-x  1 userName  staff  17808178 Jul 24 00:47 swag

We will use this tool to generate the mock db, So it’s important to make sure that it is executable from anywhere.

We check, if the gomock tools exist or not in our system that by running which mockgen.

which mockgen
/Users/userName/go/bin/mockgen

Step 1: Refactor Store into an Interface for Testability

In order to use mockgen to generate a mock db, we have to update our code a bit. At the moment, our NewServer function is accepting a db store object, which will always connect to the real database is not for mock test. We need to generalize it into an interface so that both the real DB and the mock can implement it.

To substitute a mock database during tests, your application code should depend on an interface, not a concrete database struct.

Define a Store Interface: Create a in db.Store interface that defines all the actions your application can perform on the database. This should include CRUD operations and any custom database transactions.

func NewServer( store db.Store) *Server, erro {

The api/server.go NewServer function accept db store object, at this stage it accept real database and to used the mock DB in the API server test, we need to replace the store object with interface in db/sqlc/store.go

type Store struct {
   db * sql.DB
   *Queries
} 

replace it to 



type Store interface {
	Querier
	TransferTx(ctx context.Context, arg TransferTxParams) (TransferTxResult, error)
}

// SQLStore provide all function to execute db queries and transactions. Composition for extending the queries
type SQLStore struct {
	*Queries
	db *sql.DB
}

func NewStore(db *sql.DB) Store { //replace pointer to Store 
	return &SQLStore{ // change to SQLStore from Store
		db:      db,
		Queries: New(db),
	}
}

func (store *SQLStore) execTx(ctx context.Context, fn func(*Queries) error) error {
	tx, err := store.db.BeginTx(ctx, nil)

We need to change the receiver in db/sqlc/store.go func that used store need to change from *Store to *SQLStore.

In our new Store interface, we have to define the list of actions that the store interface can do. Basically it should have all the functions of the Queries struct plus one more function to execute the Transfere money Transaction.

As adding all the functions interface from db/sqlc/account.sql.go for the Querier is time consuming plus extra work. We can easily add this by using SQLC package that we used to generate CRUD codes also have option to emit an interface that contains all of the functions of the Queries struct.

Ensure your Querier interface is generated by sqlc by
Step 1: etting emit_interface: true in your sqlc configuration.

# sqlc.yaml in root folder set this to true
version: "1"
packages:
  - name: "db"
    path: "./db/sqlc"
    queries: "./db/query/"
    schema: "./db/migration/"
    engine: "postgresql"
    emit_json_tags: true
    emit_prepared_queries: false
    emit_interface: true # <--- Set this to true
    emit_exact_table_names: false

Step 2: Generate Querier Interface with SQLC:
Run make sqlc in terminal, to regenerate the codes, This will create a querier.go file containing a Querier interface.

make sqlc

Now in db/sqlc folder we can see new file called querier.go file, it contains the generated Querier interface, with all functions to insert and query data from the database.

// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.29.0

package db

import (
	"context"
)

type Querier interface {
	AddAccountBalance(ctx context.Context, arg AddAccountBalanceParams) (Account, error)
	CreateAccount(ctx context.Context, arg CreateAccountParams) (Account, error)
	CreateEntry(ctx context.Context, arg CreateEntryParams) (Entry, error)
	CreateTransfer(ctx context.Context, arg CreateTransferParams) (Transfer, error)
	CreateUser(ctx context.Context, arg CreateUserParams) (User, error)
	DeleteAccount(ctx context.Context, id int64) error
	DeleteEntry(ctx context.Context, id int64) error
	DeleteTransfer(ctx context.Context, id int64) error
	GetAccount(ctx context.Context, id int64) (Account, error)
	GetAccountForUpdate(ctx context.Context, id int64) (Account, error)
	GetEntry(ctx context.Context, id int64) (Entry, error)
	GetTransfer(ctx context.Context, id int64) (Transfer, error)
	GetUser(ctx context.Context, username string) (User, error)
	ListAccounts(ctx context.Context, arg ListAccountsParams) ([]Account, error)
	ListAllEntries(ctx context.Context, arg ListAllEntriesParams) ([]Entry, error)
	ListAllTransfers(ctx context.Context, arg ListAllTransfersParams) ([]Transfer, error)
	ListEntries(ctx context.Context, arg ListEntriesParams) ([]Entry, error)
	ListTransfers(ctx context.Context, arg ListTransfersParams) ([]Transfer, error)
	ListTransfersFrom(ctx context.Context, arg ListTransfersFromParams) ([]Transfer, error)
	ListTransfersTo(ctx context.Context, arg ListTransfersToParams) ([]Transfer, error)
	UpdateAccount(ctx context.Context, arg UpdateAccountParams) (Account, error)
	UpdateEntry(ctx context.Context, arg UpdateEntryParams) (Entry, error)
	UpdateTransfer(ctx context.Context, arg UpdateTransferParams) (Transfer, error)
}

var _ Querier = (*Queries)(nil)

At the end of this generated file we can see _ blank varaible declare to make sure that the Queries struct must implment al functions of this Queries interface.

Embed the generated Querier interface into your Store interface that we had done at the first of this section. This ensures your Store has all the methods generated by sqlc in addition to the TransferTx function that we’ve added before.

In our api/server.go we need to remove the * from the db.Store because it is no longer a struct pointer but an interface.

func NewServer(store db.Store) *Server {

As we have dbStore interface, now we can work with test data, let implement gomock to generate the mock test implementation.

Generating Mock Store {#generating-mock}

Let first create new folder mock inside the db folder with db package. Use mockgen to generate a mock implementation of your Store interface. It’s best to use the “reflect mode” and we only need to provide the name of the package and the interface.

Let mockgen use reflection to automatically figure out what to do.

mockgen -destination db/mock/store.go \
    github.com/your-username/simplebank/db/sqlc Store

This generates MockStore implementing all Store methods.

Command breakdown:

-destination: Output file location
github.com/your-username/simplebank/db/sqlc: Import path to your interface
Store: Interface name to mock

Run mockgen: Execute the following command, replacing simplebank with your module name. This command tells mockgen to find the Store interface in the specified package, generate a mock for it, and save it to db/mock/store.go. We can see this file generated in db/mock.
Create a Makefile Command (Optional): Add the mockgen command to your Makefile for easy regeneration. mock: mockgen -package mockdb -destination db/mock/store.go simplebank/db/sqlc Store .PHONY: mock


mock:
	mockgen -package mockdb -destination db/mock/store.go github.com/ngodup/simplebank/db/sqlc Store
.PHONY: ... other makefile command mock

Generated Mock Store.go Structure

The generated db/mock/store.go contains, and in this file had two important sturct mock store and mock recorder. The MockStore is the struct that implements all the required functions of the Store interface. The MockRecorder also has a function with the same name and same number of the arguments. However the types of these arguments are different, in MockRecorder are general interface type. Later we will see how this function is ued to build the stub.

// db/mock/store.go (auto-generated)
package mockdb

type MockStore struct {
    ctrl     *gomock.Controller
    recorder *MockStoreMockRecorder
}

func NewMockStore(ctrl *gomock.Controller) *MockStore {
    // ...
}

func (m *MockStore) GetAccount(ctx context.Context, id int64) (Account, error) {
    // Mock implementation that uses recorder
}

type MockStoreMockRecorder struct {
    mock *MockStore
}

func (mr *MockStoreMockRecorder) GetAccount(ctx, id interface{}) *gomock.Call {
    // Used to build expectations/stubs
}

The current generated packages name is mock_sqlc, which doesn’t look very idiomatic and we change it to something like mockdb, we can instruct mockgen to do that usign the package options. We do it

mockgen -package mockdb -destination db/mock/store.go github.com/your-username/simplebank/db/sqlc Store

by running this code the packages name change from mock_sqlc to mockdb as we wanted.

Writing the Unit Tests for the GetAccount API

Now let create test GetAccount API, create api/account_test.go, we have several API to mange bank accounts in our application, in this tutorial we will only focus unit testing on GetAccount API, based on it we can write test for other API and we had already done with only api testing in our github repository of this project.

package api

import (
    "bytes"
    "database/sql"
    "encoding/json"
    "fmt"
    "io/ioutil"
    "net/http"
    "net/http/httptest"
    "testing"

    "github.com/golang/mock/gomock"
    "github.com/stretchr/testify/require"
    mockdb "github.com/your-username/simplebank/db/mock"
    db "github.com/your-username/simplebank/db/sqlc"
    "github.com/your-username/simplebank/util"
)

func TestGetAccountAPI(t *testing.T) {
    account := randomAccount()

    // Create mock controller
    ctrl := gomock.NewController(t)
    defer ctrl.Finish() // Very important!

    // Create mock store
    store := mockdb.NewMockStore(ctrl)

    // Build stub/expectations
    store.EXPECT().
        GetAccount(gomock.Any(), gomock.Eq(account.ID)).
        Times(1).
        Return(account, nil)

    // Create server with mock store
    server := NewServer(store)
    recorder := httptest.NewRecorder()

    // Create request
    url := fmt.Sprintf("/accounts/%d", account.ID)
    request, _ := http.NewRequest(http.MethodGet, url, nil)

    // Send request
    server.router.ServeHTTP(recorder, request)

    // Check response
    require.Equal(t, http.StatusOK, recorder.Code)
    requireBodyMatchAccount(t, recorder.Body, account)

}

func randomAccount(owner string) db.Account {
	return db.Account{
		ID:       int64(util.RandomInt(1, 1000)),
		Owner:    owner,
		Balance:  util.RandomMoney(),
		Currency: util.RandomCurrency(),
	}
}

Now, you can write unit tests for your API handlers using the generated mock store.

Test Setup: In your test function, create a gomock.Controller and use it to create a new MockStore. Remember to defer controller.Finish() to verify that all expected calls were made.
We need an account first to test this API, we have add separate funciton to generate a random account. The defer ctrl.Finish() code is important it will check to see if all methods that were expected to be called were called. defer ctrl.Finish()
ensures the mock controller is properly cleaned up after the test.
We need to create a new mock store using NewMockStore function from db/mock/store.go file this will create a controller
Build Stubs: Use store.EXPECT() to define the expected behavior of your mock and expect two arguments. This is called “building a stub”. For the “happy path” of a GetAccount API call, you expect the GetAccount method to be called once with a specific ID and to return a valid account object and a nil error. // Example Stub store.EXPECT(). GetAccount(gomock.Any(), gomock.Eq(account.ID)). Times(1). Return(account, nil)
We can use TestHTTPServer, for testing we dont need real HTTP API and server. We can use the Recorder feature of the HTTPtest package to record the response of the API request.
Execute the Request: Use the httptest package to create a ResponseRecorder and a new HTTP request. You can then serve the request using your server’s router and check the response.
We declare the url path of the API we want to call, in our case it it should be /accounts/ID of the account we want to get. Then we create a new HTTP request with method GET to the URL. Since it’s GET request, we can use nil for the request body.
Check the Response: Assert that the HTTP status code is http.StatusOK and that the response body contains the correct data, marshaled as JSON. Basically server.router.ServeHTTP(recorder, request) this will send our API request through the server router and record its response in the recorder and all we need to do is to check that response and simplest way to check is the HTTP status code.

This is a Go test function named TestGetAccountAPI that tests the GetAccountAPI endpoint. The test function uses a table-driven approach to test multiple scenarios. We can now click on run test on this function and we get result PASS.

Now if we want to check more than just the HTTP status code and to make the test more robust, we should check the response body as well. The response body is stored in teh recorder.Body field, which is in fact a bytes.Buffer and we expect it to match the account that we generated at the top of the test. For that we need to write a new function requireBodyMatchAccount below our existing function.

func requireBodyMatchAccount(t *testing.T, body *bytes.Buffer, account db.Account) {
    data, err := ioutil.ReadAll(body)
    require.NoError(t, err)

// Store account object we got from teh response body data
    var gotAccount db.Account
    err = json.Unmarshal(data, &gotAccount)
    require.NoError(t, err)
    require.Equal(t, account, gotAccount)
}

This function is we called at the end of our unit test of TestGetAccountAPI function and rerun the test and we got PASS result. Now inside the api/account.go in getAccount handler function and change this account to an empty object just adding

func (server *Server) getAccount(ctx *gin.Context) {
 ....
account = db.Account{}
//add before sending the response to the clien
ctx.JSON(http.StatusOK, account)

This time we expect the test to fail because the response body won’t match and rerunning the test we got failed TEST. We can see that GetAccount API unit testing is working very well and but it only covers the happy case for now. Next we will transform this unit test into a table-driven test set to cover all possible scenarios of the GetAccount API and to get 100% coverage.

6. Achieving 100% Coverage with Table-Driven Tests

To test all possible scenarios and achieve 100% coverage, convert your test into a table-driven test. We have define testCases array variable holding list of test cases. We have used an anoymous class to store the test data.

Define Test Cases: Create a slice of structs, where each struct represents a test case with a unique name and contains the necessary data, such as the accountID to test, a buildStubs function, and a checkResponse function. The buildStubs function takes mock store as input and we can use this mock store to build the stub that suit the purpose of each test case. The checkResponse function to check the output of the API.
Create Scenarios: Add test cases for every scenario:
- OK (Happy Path): The standard, successful case.
- Not Found: The requested resource doesn’t exist. The mock should return sql.ErrNoRows. The expected status is http.StatusNotFound.
- Internal Server Error: A database error occurs. The mock should return an error like sql.ErrConnDone. The expected status is http.StatusInternalServerError.
- Bad Request (Invalid ID): The client provides an invalid ID in the URL. The GetAccount method should not be called (Times(0)). The expected status is http.StatusBadRequest.
Run Sub-tests: We have use simple for Loop to iterate through our test cases and run each one as a sub-test using t.Run(). Inside each sub-test, call the corresponding buildStubs and checkResponse functions. Our first case is happy case scenario OK.
First add only happy case OK, then run the test if it is working then we can add one by one test case after it.

func TestGetAccountAPI(t *testing.T) {
    account := randomAccount()

    testCases := []struct {
        name          string
        accountID     int64
        buildStubs    func(store *mockdb.MockStore)
        checkResponse func(t *testing.T, recorder *httptest.ResponseRecorder)
    }{
        {
            name:      "OK",
            accountID: account.ID,
            buildStubs: func(store *mockdb.MockStore) {
                store.EXPECT().
                    GetAccount(gomock.Any(), gomock.Eq(account.ID)).
                    Times(1).
                    Return(account, nil)
            },
            checkResponse: func(t *testing.T, recorder *httptest.ResponseRecorder) {
                require.Equal(t, http.StatusOK, recorder.Code)
                requireBodyMatchAccount(t, recorder.Body, account)
            },
        },
        {
            name:      "NotFound",
            accountID: account.ID,
            buildStubs: func(store *mockdb.MockStore) {
                store.EXPECT().
                    GetAccount(gomock.Any(), gomock.Eq(account.ID)).
                    Times(1).
                    Return(db.Account{}, sql.ErrNoRows)
                    // return empty account
            },
            checkResponse: func(t *testing.T, recorder *httptest.ResponseRecorder) {
                require.Equal(t, http.StatusNotFound, recorder.Code)
            },
        },
        { //one possible error 
            name:      "InternalError", 
            accountID: account.ID,
            buildStubs: func(store *mockdb.MockStore) {
                store.EXPECT().
                    GetAccount(gomock.Any(), gomock.Eq(account.ID)).
                    Times(1).
                    Return(db.Account{}, sql.ErrConnDone)
            },
            checkResponse: func(t *testing.T, recorder *httptest.ResponseRecorder) {
                require.Equal(t, http.StatusInternalServerError, recorder.Code)
            },
        },
        { // bad request
            name:      "InvalidID",
            accountID: 0, // Invalid ID 0
            buildStubs: func(store *mockdb.MockStore) {
                // GetAccount should not be called for invalid ID
                store.EXPECT().
                    GetAccount(gomock.Any(), gomock.Any()).
                    Times(0)
            },
            checkResponse: func(t *testing.T, recorder *httptest.ResponseRecorder) {
                require.Equal(t, http.StatusBadRequest, recorder.Code)
            },
        },
    }

    for i := range testCases {
        tc := testCases[i]
        
        t.Run(tc.name, func(t *testing.T) {
            ctrl := gomock.NewController(t)
            defer ctrl.Finish()

            store := mockdb.NewMockStore(ctrl)
            tc.buildStubs(store)

            server := NewServer(store)
            recorder := httptest.NewRecorder()

            url := fmt.Sprintf("/accounts/%d", tc.accountID)
            request, err := http.NewRequest(http.MethodGet, url, nil)
            require.NoError(t, err)

            server.router.ServeHTTP(recorder, request)
            tc.checkResponse(t, recorder)
        })
    }
}

This is a Go test function named TestCreateAccountAPI that tests the creation of an account API endpoint.

The test function defines a slice of test cases, each representing a different scenario for creating an account. The test cases cover the following scenarios:

Successful account creation (“OK”)
Internal server error during account creation (“InternalError”)
Invalid currency provided in the request body (“InvalidCurrency”)

For each test case, the function:

Builds mock database stubs to simulate the expected behavior
Sends a POST request to the /accounts endpoint with the test case’s request body
Verifies the response code and body match the expected result

The test function uses the gomock library to create a mock database store and the httptest library to create a test HTTP server and recorder.

7. Final Polish: Clean Up Test Logs

By default, Gin runs in debug mode, which creates noisy logs during tests and can have many duplicate debug logs written by Gin thus make it harder to read the test result. To clean this up, create a new main_test.go file in your api package and set Gin to test mode. The content of this file is very similar to that of the main_test.go file in the db package and we only add the thing that we are required in this.

// api/main_test.go
package api

import (
    "os"
    "testing"

    "github.com/gin-gonic/gin"
)

func TestMain(m *testing.M) {
    gin.SetMode(gin.TestMode)
    os.Exit(m.Run())
}

We need to only set the gin.SetMode change to gin.TestMode. This will result in cleaner, more readable test output. With these steps, you can effectively test your Go HTTP APIs with a mocked database, leading to more reliable and maintainable code. Rerun the test case will have cleaner and readible logs and we can create unit test case for other api like this.

Table of Contents