Testing Database Using Sqlmock for BIG Agent

BIG Agent is our on-demand platform who connecting human resources with jobs, such as promotion, telesales, and acquisition. Under Ralali.com, BIG Agent helps business or principal to reach market and acknowledge various consumer characteristic. On the other side, it helps our agent (the human resources) to get income within flexible working hours. In the process of developing this application, we are required to be fast and well-delivered. This is due to fullfiling the process to verify the job that agent has done, so that they can earn commision immediately.

At the beginning of the application development, we made it quick, but did not deliver properly. Why doesn’t it well-delivered? Because the number of bugs found is quite a lot. When we try to debug, sometimes we found there are different data between production and development stage. To deal with this problem, we do the testing with mocking database.

Testing

When we try to make a test in our code, sometimes we can call it easy when it is not rely on other external tools or system. What if our code require connection from database for processing something? What if the tests that we do occur before migration? How can we create a test with database connection in different environment such development, staging, or production? One of them is to create a mock for database connection.

In Go, there are many packages that can mock database. Sqlmock is one package that can be used for mocking database. Sqlmock package describes:

“A mock library implementing sql/driver. Which has one and only purpose — to simulate any sql driver behavior in tests, without needing a real database connection”

With sqlmock, we can test our code that connected to dummy database.

Dummy is on progress

Then we try to test our code. For example, we want to test our GetPostalCode function. This function is query the database from table `postal_codes`.

This is the GetPostalCode function

func (repository *V2PostalCodeRepository) GetPostalCode() ([]models.PostalCodeModel, error) {
var modelsData []models.PostalCodeModel
query := repository.DB.Table(“postal_codes”)
query = query.Select("id, postal_code, name, province_id, city_id, district_id, created_at, deleted_at, updated_at")
query = query.Scan(&modelsData)
return modelsData, query.Error
}

We get the data from table `query := repository.DB.Table(“postal_codes”)`

We select the column that we want `query = query.Select(“id, postal_code, name, province_id, city_id, district_id, created_at, deleted_at, updated_at”)`

Test Code (with regexp)

func TestShouldGetPostalCode(t *testing.T) {
db, mock, err := sqlmock.New()
if err != nil {
t.Fatalf("an error '%s' was not expected when opening a stub database connection", err)
}
defer db.Close()
database, _ := gorm.Open("mysql", db)
rows := sqlmock.NewRows([]string{"id", "postal_code", "name", "province_id", "city_id", "district_id", "created_at", "deleted_at", "updated_at"}).
AddRow(1, "80352", "Abiansemal", 1, 1, 1, time.Now(), time.Now(), time.Now()).
AddRow(2, "80361", "Kedonganan", 1, 1, 2, time.Now(), time.Now(), time.Now())
mock.ExpectQuery("SELECT (.+) FROM `postal_codes`").
WillReturnRows(rows)
repository := V2PostalCodeRepositoryHandler(database)
_, err = repository.GetPostalCode()
assert.Nil(t, err)
// we make sure that all expectations were met
if err = mock.ExpectationsWereMet(); err != nil {
t.Errorf("there were unfulfilled expectations: %s", err)
  }
}

Let’s see the test code

db, mock, err := sqlmock.New()
if err != nil {
t.Fatalf("an error '%s' was not expected when opening a stub database connection", err)
}
defer db.Close()

First, we create a mock that used as a database connection

mock.ExpectQuery("SELECT (.+) FROM `postal_codes`").
WillReturnRows(rows)

We set the expectation what we wanted to query. ExpectQuery function using regex. In the example, I don’t care about what column that I select from the table. So I use regex (.+) to accept atleast one column selected.

This function `WillReturnRows(rows)` is expecting the query will return a rows that we created.

rows := sqlmock.NewRows([]string{"id", "postal_code", "name", "province_id", "city_id", "district_id", "created_at", "deleted_at", "updated_at"}).
AddRow(1, "80352", "Abiansemal", 1, 1, 1, time.Now(), time.Now(), time.Now()).
AddRow(2, "80361", "Kedonganan", 1, 1, 2, time.Now(), time.Now(), time.Now())

We create data source that expected will return when the query run.

repository := V2PostalCodeRepositoryHandler(database)
_, err = repository.GetPostalCode()

`repository := V2PostalCodeRepositoryHandler(database)` we create new repository handler that inject a mocking database

`_, err = repository.GetPostalCode()` this is the function that connect to database to get the postal code

assert.Nil(t, err)
// we make sure that all expectations were met
if err = mock.ExpectationsWereMet(); err != nil {
t.Errorf("there were unfulfilled expectations: %s", err)
}

Last, we can combain it with assertion. The assertion that i used is checking if the function create an error or not. Fora mocking, ExpectationsWereMet() use for check if mock expectation that we create is suit.

We can run the test

go test ./repository/locations/…

Test result

=== RUN   TestShouldGetPostalCode
--- PASS: TestShouldGetPostalCode (0.00s)
PASS

We can test the code without regex. So if you want to strict the query that you wrote, you don’t need a regex. But your expect query will be a long, long, long query (lol). When we use a strict query expect, we can know right away when there are new column in table or our query has been changed by someone

Test code (with regex)

mock.ExpectQuery("SELECT (.+) FROM `postal_codes`")

Test code (without regex)

This expect query is strict for selecting column in table. This expect query don’t select the name, so it will return an error when we test it

mock.ExpectQuery("SELECT id, postal_code, province_id, city_id, district_id, created_at, deleted_at, updated_at FROM `postal_codes`")

Run test

go test ./repository/locations/…

Test result

=== RUN   TestShouldGetPostalCode
--- FAIL: TestShouldGetPostalCode (0.00s)
v2_postal_code_repository_test.go:31:
Error Trace:    v2_postal_code_repository_test.go:31
Error:          Expected nil, but got: &errors.errorString{s:"Query: could not match actual sql: \"SELECT id, postal_code, name, province_id, city_id, district_id, created_at, deleted_at, updated_at FROM `postal_codes`\" with expected regexp \"SELECT id, postal_code, province_id, city_id, district_id, created_at, deleted_at, updated_at FROM `postal_codes`\""}
Test:           TestShouldGetPostalCode
v2_postal_code_repository_test.go:35: there were unfulfilled expectations: there is a remaining expectation which was not matched: ExpectedQuery => expecting Query, QueryContext or QueryRow which:
- matches sql: 'SELECT id, postal_code, province_id, city_id, district_id, created_at, deleted_at, updated_at FROM `postal_codes`'
- is without arguments
- should return rows:
row 0 - [1 80352 Abiansemal 1 1 1 2019-09-11 02:19:37.7395724 +0000 UTC m=+0.396719301 2019-09-11 02:19:37.7395973 +0000 UTC m=+0.396743501 2019-09-11 02:19:37.7396211 +0000 UTC m=+0.396767301]
row 1 - [2 80361 Kedonganan 1 1 2 2019-09-11 02:19:37.7396514 +0000 UTC m=+0.396797301 2019-09-11 02:19:37.7396749 +0000 UTC m=+0.396821301 2019-09-11 02:19:37.7396991 +0000 UTC m=+0.396845801]

So, when someone has changed the query, they will be founded by the strict expect query that you wrote on the test (evil) :D.

We also can use this mock for database transaction process

func TestShouldUpdateAdminFree(t *testing.T) {
db, mock, err := sqlmock.New()
if err != nil {
t.Fatalf("an error '%s' was not expected when opening a stub database connection", err)
}
defer db.Close()
database, _ := gorm.Open("mysql", db)
mock.ExpectBegin()
mock.ExpectExec("UPDATE `banks` SET (.+) WHERE (.+)").
WithArgs(true, 1).
WillReturnResult(sqlmock.NewResult(1, 1))
mock.ExpectCommit()
service := V2BankServiceHandler(database)
requestData := requests.V2SetAdminFreequest{
ID: 1,
}
err = service.SetAdminFree(requestData)
assert.Nil(t, err)
// we make sure that all expectations were met
if err = mock.ExpectationsWereMet(); err != nil {
t.Errorf("there were unfulfilled expectations: %s", err)
}
}

This is for expecting our code creating a transaction

mock.ExpectBegin()

This is for expecting our code will commit after the transaction done

mock.ExpectCommit()

We can test if error happened, and we must rollback the transaction

func TestShouldRollbackAdminFreeOnFailure(t *testing.T) {
db, mock, err := sqlmock.New()
if err != nil {
t.Fatalf("an error '%s' was not expected when opening a stub database connection", err)
}
defer db.Close()
database, _ := gorm.Open("mysql", db)
mock.ExpectBegin()
mock.ExpectExec("UPDATE `banks` SET (.+) WHERE (.+)").
WithArgs(true, 1).
WillReturnError(fmt.Errorf("error update admin free"))
mock.ExpectRollback()
service := V2BankServiceHandler(database)
requestData := requests.V2SetAdminFreequest{
ID: 1,
}
err = service.SetAdminFree(requestData)
assert.NotNil(t, err)
// we make sure that all expectations were met
if err = mock.ExpectationsWereMet(); err != nil {
t.Errorf("there were unfulfilled expectations: %s", err)
}
}

From the mocking transaction, we will create an error

WillReturnError(fmt.Errorf("error update admin free"))

Then, when error happened, we expect will run a rollback transaction

mock.ExpectRollback()

The impact of the testing with mocking database that has been done:

• Helps in maintaining the query when changes occur
• Simulates behaviour of real database but in a more controlled manner
• The tests run much faster
• It is easy to test all the failure scenarios generated by the mock database

In Bang Gen, we trust!

Testing with mocking database is very useful. Make a test run faster than using a real database directly. But, in some cases we also need to do a test using a real database in order to know the actual events on the real database.

Written by

Ahmad Hafizh