test api basic go-sqlmock ExpectQuery test pass one query param

api.go

package main

import (
	"database/sql"
	"github.com/gin-gonic/gin"
	_ "github.com/go-sql-driver/mysql"
	"gopkg.in/gorp.v1"
	"log"
	"strconv"
)

type User struct {
	Id   int    `form:"id" binding:"required" db:"id" json:"id"`
	Name string `form:"name" db:"name" json:"name"`
}

var (
	DBCon *gorp.DbMap
)

func DbOpen() *gorp.DbMap {
	db, err := sql.Open("mysql", "not_needed_test:none")
	if err != nil {
		log.Fatalln("sql.Open failed", err)
	}

	DBCon := &gorp.DbMap{Db: db, Dialect: gorp.MySQLDialect{"InnoDB", "UTF8"}}
	return DBCon
}

func initDb(dbcon *gorp.DbMap) *gorp.DbMap {
	dbcon.AddTableWithName(User{}, "user").SetKeys(true, "Id")
	err := dbcon.CreateTablesIfNotExists()
	if err != nil {
		log.Fatalln("Create tables failed", err)
	}

	return dbcon
}

func GetMainEngine(dbcon *gorp.DbMap) *gin.Engine {
	DBCon = dbcon
	r := gin.Default()
	v1 := r.Group("api/v1")
	{
		v1.GET("/users", GetUsers)
		v1.GET("/users/:id", GetUser)
	}
	v1.Use()

	return r
}

func main() {
	dbcon := DbOpen()
	dbcon = initDb(dbcon)
	GetMainEngine(dbcon).Run(":8080")
}

func GetUsers(c *gin.Context) {
	var users []User
	_, err := DBCon.Select(&users, "SELECT * FROM user")

	if err == nil {
		c.JSON(200, users)
	} else {
		c.JSON(404, gin.H{"error": "no users found!"})
	}
}

func GetUser(c *gin.Context) {
	id := c.Params.ByName("id")
	var user User
	err := DBCon.SelectOne(&user, "SELECT * FROM user WHERE id=? LIMIT 1", id)

	if err == nil {
		userId, _ := strconv.Atoi(id)

		content := &User{
			Id:   userId,
			Name: user.Name,
		}
		c.JSON(200, content)
	} else {
		log.Fatal(err)
		c.JSON(404, gin.H{"error": "user not found"})
	}
}

api_test.go

package main

import (
	"fmt"
	"github.com/DATA-DOG/go-sqlmock"
	_ "github.com/gin-gonic/gin"
	"github.com/stretchr/testify/assert"
	"gopkg.in/gorp.v1"
	_ "log"
	"net/http"
	"net/http/httptest"
	"testing"
)

func TestGetUsers(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		fmt.Sprintf("an error '%s' was not expected when opening a stub database connection", err)
	}
	defer db.Close()

	dbMap := &gorp.DbMap{Db: db, Dialect: gorp.MySQLDialect{"InnoDB", "UTF8"}}
	ts := GetMainEngine(dbMap)

	req, err := http.NewRequest("GET", "/api/v1/users", nil)
	if err != nil {
		t.Fatalf("an error '%s' was not expected when making API request", err)
	}

	rows := sqlmock.NewRows([]string{"id", "name"}).AddRow(1, "TestUser1").AddRow(2, "TestUser2")

	mock.ExpectQuery("^SELECT (.+) FROM user$").WillReturnRows(rows)

	resp := httptest.NewRecorder()
	ts.ServeHTTP(resp, req)
	assert.Equal(t, resp.Code, 200)
	assert.JSONEq(t, resp.Body.String(), `[{"id":1,"name":"TestUser1"},{"id":2,"name":"TestUser2"} ]`)
	// we make sure that all expectations were met
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Errorf("there were unfulfilled expections: %s", err)
	}
}

func TestGetSpecificUser(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		fmt.Sprintf("an error '%s' was not expected when opening a stub database connection", err)
	}
	defer db.Close()

	dbMap := &gorp.DbMap{Db: db, Dialect: gorp.MySQLDialect{"InnoDB", "UTF8"}}
	ts := GetMainEngine(dbMap)

	req, err := http.NewRequest("GET", "/api/v1/users/1", nil)
	if err != nil {
		t.Fatalf("an error '%s' was not expected when making API request", err)
	}

	rows := sqlmock.NewRows([]string{"id", "name"}).AddRow(1, "TestUser1")

	//mock.ExpectQuery("^SELECT (.+) FROM user where id=?$").WithArgs(1).WillReturnRows(rows)
	//mock.ExpectQuery("^SELECT (.+) FROM user where id=? LIMIT 1$").WithArgs(1).WillReturnRows(rows)
	//mock.ExpectQuery(`SELECT \* FROM user WHERE id=? LIMIT 1`).WithArgs(1).WillReturnRows(rows)
	//mock.ExpectQuery("^SELECT (.+) FROM user where id=? LIMIT 1").WithArgs(1).WillReturnRows(rows)
	// NOTE: Have to escape "?"
	mock.ExpectQuery("^SELECT \\* FROM user WHERE id=\\? LIMIT 1$").WithArgs("1").WillReturnRows(rows)

	resp := httptest.NewRecorder()
	ts.ServeHTTP(resp, req)
	assert.Equal(t, resp.Code, 200)
	assert.JSONEq(t, resp.Body.String(), `[{"id":1,"name":"TestUser1"}]`)

	// we make sure that all expectations were met
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Errorf("there were unfulfilled expections: %s", err)
	}
}

Your sharing is very useful for me. thanks！