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 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
}