Mock sql (sqlx) db on golang

• 04, 15, 2022
•  bullgare
•  Databases, golang, Testing
• No Comments.

I am using this library — https://pkg.go.dev/github.com/data-dog/go-sqlmock.

That’s how you can use it for mocking db querying:

import (
	"database/sql/driver"
	"testing"

	"github.com/go-playground/assert/v2"
	"github.com/jmoiron/sqlx"
	"github.com/stretchr/testify/require"
	"github.com/stretchr/testify/suite"
	"gopkg.in/DATA-DOG/go-sqlmock.v1"
)

type behaviourDB struct {
	orderIDs []driver.Value
	data     *sqlmock.Rows
	error    error
}

type DBTestSuite struct {
	suite.Suite
}

func Test_DBTestSuite(t *testing.T) {
	suite.Run(t, new(DBTestSuite))
}

func (s DBTestSuite) Test_GetResults() {
	tt := []struct {
		name     string
		orderIDs []int64
		behaviourDB
		expectedResults []models.Result
		expectedError   error
	}{

		{
			name:     "happy path",
			orderIDs: []int64{1, 2},
			behaviourDB: behaviourDB{
				orderIDs: []driver.Value{1, 2},
				data: sqlmock.
					NewRows([]string{"quantity", "order_id"}).
					AddRow(10, 1).
					AddRow(11, 2),
				error: nil,
			},
			expectedResults: nil,
			expectedError:   nil,
		},
	}

	for _, tc := range tt {
		s.T().Run(tc.name, func(t *testing.T) {
			db, mock, err := sqlmock.New()
			require.NoError(t, err, "creating a db mock")
			defer db.Close()
			dbx := sqlx.NewDb(db, "sqlmock")
			expectedQuery := mock.ExpectQuery(`SELECT t.quantity, t.order_id FROM table AS t WHERE t.order_id IN \(\?,\?\) ORDER BY t.order_id`).
				WithArgs(tc.orderIDs)
			if tc.behaviourDB.error != nil {
				expectedQuery.WillReturnError(tc.behaviourDB.error)
			} else {
				expectedQuery.WillReturnRows(tc.behaviourDB.data)
			}
			r, err := NewRepository(dbx)
			require.NoError(t, err, "creating the shared repo")

			results, err := r.GetResults(context.Background(), tc.orderIDs)

			require.NoError(t, helpers.CompareErrors(tc.expectedError, err))
			assert.Equal(t, tc.expectedResults, results)
		})
	}
}

That’s how you can test a function that only does the rows.StructScan, for instance:

// ... the same as above
func (s DBTestSuite) Test_doScan() {
	tt := []struct {
		name                 string
		rows                 *sqlmock.Rows
		triggerFakeScanError bool
		expectedResult       models.Result
		expectedError        error
	}{
		{
			name: "happy path",
			rows: sqlmock.
				NewRows([]string{"quantity", "order_id"}).
				AddRow(10, 1),
			triggerFakeScanError: false,
			expectedResult:       models.Result{
				// ...
			},
			expectedError: nil,
		},
	}

	for _, tc := range tt {
		s.T().Run(tc.name, func(t *testing.T) {
			db, mock, err := sqlmock.New()
			require.NoError(t, err, "creating a db mock")
			defer db.Close()
			dbx := sqlx.NewDb(db, "sqlmock")
			mock.ExpectQuery("SELECT 1").
				WillReturnRows(tc.rows)
			rows, err := dbx.Queryx("SELECT 1")
			require.NoError(t, err, "running fake select")
			if !tc.triggerFakeScanError {
				rows.Next()
			}

			result, err := doScan(rows)

			require.NoError(t, helpers.CompareErrors(tc.expectedError, err))
			assert.Equal(t, tc.expectedResult, result)
		})
	}
}