Mock sql query Golang

I have a function :

    func (db *DBSqlx) GetRefreshToken(oldToken string, tx *sqlx.Tx) (string, error) {
	var refreshToken string

	err := db.TemplateGet(
		tx,
		&refreshToken,
		`query`,
		oldToken,
	)

	if err != nil {
		return "", errors.Wrap(err, "не удалось запросить рефреш токен для указанного токена")
	}

	return refreshToken, err
}

How to write a test for this function with mock response?

DB has type :

    type DBSqlx struct {
    PgConn    *sqlx.DB
    PgConnCtx context.Context
}

I tried to write this code. But I don't understand how to use the package correctly.

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()

mock.ExpectQuery("query").WillReturnRows()

You could use something like https://github.com/DATA-DOG/go-sqlmock to mock database queries with all kinds of control over responses by the database.

Though it should be noted, that mocking database queries in tests is generally not considered good testing practice because it is most likely testing the actual implementation of your program and not it's behavior.

You can abstract your storage and underlying db handle (for making it useful with pure db and tx) using some wrapper and then substitute it with another stub interface. It does not even need to include additional libraries to your codebase.

You should keep in mind potential serialization issues with real database, NULL values and etc, adding some intergration testing with real data using https://github.com/ory/dockertest

But for simple cases wrapper is enough

// root package 
type TokenStorage interface {
	GetToken(ctx context.Context, oldToken string) (string, error)
}

// yourtestfile_test.go
type TokenStorageStub struct {}

func (t *TokenStorageStub) GetToken(ctx context.Context, oldToken string) (string, error) {
	b := make([]byte, 16)
	n, err := rand.Read(b)
	if n != len(b) || err != nil {
		return "", fmt.Errorf("could not successfully read from the system CSPRNG.")
	}
	return hex.EncodeToString(b), nil
}

// postgres || mysql || sqlite package 

// TokenStorage impelements root interface using postgres. 
type TokenStorage struct {
	db ExtendedDB 
}

func NewTokenStorage(db ExtendedDB) (*TokenStorage, error) {
	if db == nil {
		return nil, errors.New("provided db handle is nil")
	}
	return &TokenStorage{db: db}, nil 
}

func (s *TokenStorage) GetToken(ctx context.Context, oldToken string) (string, error) {
	const query = `SELECT ...`
	var token string 
	if err := s.db.QueryRowContext(ctx, query, oldToken).Scan(&token); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, rootpkg.ErrTokenNotFound
		}
		return nil, fmt.Errorf("postgres: problem while get token using old token: %w", err)
	}
	return token, nil
}

// Queryer is an interface used for selection queries.
type Queryer interface {
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
}

// Execer is an interface used for executing queries.
type Execer interface {
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
}

// ExtendedDB is a union interface which can query, and exec, with Context.
type ExtendedDB interface {
	Queryer
	Execer
}

This is a problem with most existing sql clients in Golang, they were not really created to be mockable so writing a mock as we usually do (using an interface to decouple from it) doesn't work.

To be more clear about exactly why using an interface to decouple and then mocking the interface doesn't work the reason is because the Query and Exec functions return structs with private attributes that you cannot change.

So for example if you create an interface like this one:

type MyDB interface{
    Exec(query string, args ...any) (sql.Result, error)
    Query(query string, args ...any) (*sql.Rows, error)
}

You can write a mock for it like this:

type MyDBMock struct{
    // ...
}

func (m MyDBMock) Exec(query string, args ...any) (sql.Result, error) {
    // ... do some mocky thing ...
    return sql.Result{
        // nothing to do here, because all atributes are private
    }
}

func (m MyDBMock) Query(query string, args ...any) (*sql.Rows, error) {
    // ... do some mocky thing ...
    return &sql.Rows{
        // nothing to do here, because all atributes are private
    }
}

But since you can't set the values of sql.Rows nor sql.Results it is only possible to return the zero values for these two structs which is hardly what you need if you are writing tests.

The most used mocking technique for database/sql

The alternative is to mock the whole driver that was used to connect to the database which is a lot of work for little profit.

Fortunately that's exactly what the https://github.com/DATA-DOG/go-sqlmock library that was mentioned in other answers does, and it does a good job at that. And it's actually the only way I know how to do it with the existing sql libraries.

Maybe not mocking is a better idea

It is widely considered a bad practice to mock database requests when writing tests, because the best/only way of knowing if you SQL code is working is to send it to a real instance of the database and run it there.

So mocking it might just cause you to have a false positive on your tests and then have problems on production.

If you want to go this route, there is an interesting technique using the https://github.com/ory/dockertest where you can just start your database instance inside a docker container directly from the Golang code, and then stop this machine when the test is over.

This gives you most of the benefits of a unit test but actually allows you to have your queries running in a real database. This is my favorite approach for testing the database.

There is an example of how I do it for postgres here and I actually have one example for each of the biggest SQL DBMSs in here if that helps.

Using a library that actually helps you to mock it

> Disclaimer: I am referring to a library I wrote myself in this section

There are some situations where you might want to mock your database calls just so you can test your business logic in smaller faster tests and then have other slower tests testing your actual queries. This is not often the case but I did write a very good sql library for Go that among many other things also provides a mock you can out of the box, so that is also an option:

https://github.com/vingarcia/ksql

And here its explained how to use the built-in mock of this library:

https://github.com/VinGarcia/ksql/wiki/Testing-Tools-and-ksql.Mock