Golang SQL Unit Testing

If You’re building any application using Go, there is high chance that you’ll be using database. Spesifically for SQL type database (Mysql, Postgre, etc), it’s very common for Golang developer to use Go SQL driver. If you’re wondering how to do unit test for function that uses Go SQL query, then this is the right place!

Image Source: https://unsplash.com/photos/n7eJHQwefeI

Contents and the Steps:

1. We’re having sample db application, with several DB queries
2. Make the unit test
3. Additional explanation of codes

============= Let’s Begin =============

First, Simple DB Application

Assuming we have an example application like this

package main

import (
 "database/sql"
 "fmt"
 "log"
 "time"

 _ "github.com/go-sql-driver/mysql"
)

type Service struct {
 database *sql.DB
}

type User struct {
 Name      string
 Address   string
 Age       int
 CreatedAt time.Time
}

func initDB() (*sql.DB, error) {
 db, err := sql.Open("mysql", "user:password@/dbname")
 if err != nil {
  return db, err
 }

 return db, nil
}

func main() {
 db, err := initDB()
 if err != nil {
  log.Fatal(err)
 }

 svc := &Service{
  database: db,
 }

 fmt.Println(svc)
}

func (s *Service) SaveUser(u User) error {
 _, err := s.database.Exec(`INSERT INTO users (name, address, age, created_at) VALUES (?, ?, ?, ?)`,
  u.Name,
  u.Address,
  u.Age,
  u.CreatedAt)
 if err != nil {
  return err
 }

 return nil
}

func (s *Service) GetUserByName(name string) ([]User, error) {
 var users []User

 res, err := s.database.Query(`
  SELECT name, address, age, created_at
  FROM users
  WHERE name = ?
 `, name)

 if err != nil {
  return users, err
 }

 for res.Next() {
  var u User
  err := res.Scan(&u.Name, &u.Address, &u.Age, &u.CreatedAt)
  if err != nil {
   return users, err
  }

  users = append(users, u)
 }

 return users, err
}

In that application, our service can do 2 things:

1. Get user list (search by name, query to DB)
2. Save new user (insert to DB)

and in that example, we’ll be using mysql. Then we want to make a unit test for those two methods. Instead of connecting to DB in the unit test, we can mock the SQL DB connection with this

GitHub - DATA-DOG/go-sqlmock: Sql mock driver for golang to test database interactions

How to Use it:

1. install it using “go get -u github.com/go-sql-driver/mysql” in your repository
2. during the unit test, instead of initiate real DB connection, you can initiate mocked DB using “sqlmock.New” command
3. the Initiation will return mocked DB, sqlmock object, and error
4. You can begin mocking behaviour with the sqlmock object

Example Usage for Previous App:

1) Mocking the DB Exec Command:

Let’s create unit test for SaveUser method. For simplicity, at our first test case, we want to test when everything is success (no error), we can make the code like this

Why we’re using QueryMatcherOption? It will be explained in the last section. For that particular test case, we want to do assertion that our “db” will be called with that string query, that arguments, and it will return db result that is mocked as defined above. Run the unit test and coverage, we will see it successfully asserted.

Then let’s cover the error handling on SaveUser method. In order to do that, we want to add 1 more test case that will return error on the given query and arguments.

see the 2nd test case, error on Exec

Instead of returning mocked result (sql result), we will return error as defined above. We used sql.ErrConnDone error just as an example (can be any error you want). Run the test again and you’ll see all lines are now covered.

1) Mocking the DB Query Command:

Since we have another method of GetUser, let’s do unit test on that.

First let us create the test case when query is returning error, run the test.

Now, we want to make a test in which the returned data is running without errors at all. Let’s add another test case and adjust the mocking.

new success on all query test case

new mocking for 2nd test case (success)

It’s a bit different than before, because instead of returning sql result, we want to return sql rows type. So we put the columns name first, then the 2nd until last rows are the value. Run again the test and it will pass.

If you want to cover the scan error line of code, you can do that by adjusting the mock of return rows to return invalid data type for the user object, but the assertion might needs to be adjusted too. Feel free to try it on your own.

Additional explanation of codes

1. Why do we Query Matcher Option?

func TestFunc() {
  sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual)) // why this
  sqlmock.New() // why not just use this
}

Basically the query matcher is the one that will match our query string expectation with the real query string that is given. There are different matchers available because sql driver query syntax may vary. In example postgres uses “$1”, “$2” as arguments, while mysql uses “?”. For mysql, we cannot use default matcher because the query regex assertion will fail with error, it is stated in the github documentation, we can use QueryMatcherEqual for mysql query syntax.

2. Does this ensure our DB operation is well tested

No, since what this library does is just asserting our string query and its arguments. Because there is no real db connection established in the process, there are no real DB operation / logic being done.