Issue using go-sqlmock and inserting arguments into mock queries

I am trying to mock my query functions using go-sqlmock & replicate the database table similarly. However, I am not getting the results that I expect. The query is not behaving as it should, arguments are not being inserted into the query & the actual result is incorrect. What am I doing wrong here?

This is the function & the query I am mocking:

func (y *YumDatabase) getTransactionId(pkg string) (int, error) {
	var id int

	queryForTid := "SELECT tid FROM trans_cmdline WHERE cmdline LIKE '%install " + pkg + "%' ORDER BY tid DESC LIMIT 1"
	row := y.db.QueryRow(queryForTid)
	switch err := row.Scan(&id); err {
	case sql.ErrNoRows:
		fmt.Println("No rows were returned")
		return 0, err
	case nil:
		return id, nil
	default:
		return 0, err
	}
}

And this is the mock test function:

func TestGetTransactionId(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("err not expected: %v", err)
	}
	pkg := "tcpdump"
	rows := sqlmock.NewRows([]string{"tid"}).AddRow("1").AddRow("3")
	mock.ExpectQuery("SELECT tid FROM trans_cmdline WHERE cmdline LIKE '%install " + pkg + "%' ORDER BY tid DESC LIMIT 1").WillReturnRows(rows)

	mockdb := &YumDatabase{
		db: db,
	}

	got, err := mockdb.getTransactionId("tcpdump")
	assert.Equal(t, 3, got)
}

If the above worked as expected, I would get back '3' in 'got' but instead I get back '1'

Secondly, is it possible to change Rows to the following:

rows := sqlmock.NewRows([]string{"tid", "cmdline"}).AddRow("1", "install test").AddRow("3", "delete test2")

And actually do the comparison "WHERE cmdline LIKE '%install XYZ%'", because I tried this and I got back the following error (all of the main code builds & works including the queries, so this is an issue with the mock code I've wrote I'm guessing):

error sql: expected 2 destination arguments in Scan, not 1

I expect to see the highest tid returned from the SQL query, not the first one specified in "AddRow", and I expect the query to implement a check of the "cmdline" row from the mocks.

I managed your requirement in this way. First, let me share the code, then, I'll walk you through all of the relevant changes. The code is contained in two files: repo.go and repo_test.go.

repo.go file

package repo

import (
	"database/sql"
	"fmt"
)

func GetTransactionId(db *sql.DB, pkg string) (int, error) {
	var id int
	row := db.QueryRow("SELECT tid FROM trans_cmdline WHERE cmdline LIKE '%install $1%' ORDER BY tid DESC LIMIT 1", pkg)
	switch err := row.Scan(&id); err {
	case sql.ErrNoRows:
		fmt.Println("No rows were returned")
		return 0, err
	case nil:
		return id, nil
	default:
		return 0, err
	}
}

Here, there are two small improvements:

1. The *sql.DB passed in as a parameter. As suggested by the best practices, Functions are first-class citizens. That's why I prefer to stick to them whenever possible.
2. I used the prepared statement to pass in the argument of the query. Not a simple string concatenation. Thanks to this, it's easier to intercept the arguments passed to the query and set up expectations over them

Now let's switch to the test code.

repo_test.go file

package repo

import (
	"database/sql"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/stretchr/testify/assert"
)

func TestGetTransactionId(t *testing.T) {
	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual))
	if err != nil {
		t.Fatalf("err not expected while opening mock db, %v", err)
	}
	t.Run("HappyPath", func(t *testing.T) {
		rows := sqlmock.NewRows([]string{"tid"}).AddRow("1")
		mock.ExpectQuery("SELECT tid FROM trans_cmdline WHERE cmdline LIKE '%install $1%' ORDER BY tid DESC LIMIT 1").
            WithArgs("tcpdump").
            WillReturnRows(rows)

		got, err := GetTransactionId(db, "tcpdump")

		assert.Equal(t, 1, got)
		assert.Nil(t, err)
	})

	t.Run("NoRowsReturned", func(t *testing.T) {
		mock.ExpectQuery("SELECT tid FROM trans_cmdline WHERE cmdline LIKE '%install $1%' ORDER BY tid DESC LIMIT 1").
            WithArgs("tcpdump").
            WillReturnError(sql.ErrNoRows)

		got, err := GetTransactionId(db, "tcpdump")

		assert.Equal(t, 0, got)
		assert.Equal(t, sql.ErrNoRows, err)
	})
}

Here, there are more changes that you need to be aware of:

1. While instantiating the db and mock, you should pass the sqlmock.QueryMatcherEqual as an argument to. Thanks to this, it'll exactly match queries.
2. The ExpectQuery method now used the prepared statement feature and expects one argument (e.g. tcpdump in this case).
3. Refactored the assertions to take advantage of the github.com/stretchr/testify/assert package.

I hope that this helps you in solving your issue, let me know!