sqlmock

db, mock, _ := New()
result := NewErrorResult(fmt.Errorf("some error"))
mock.ExpectExec("^INSERT (.+)").WillReturnResult(result)
res, _ := db.Exec("INSERT something")
_, err := res.LastInsertId()
fmt.Println(err)

Output:

some error

// configure to use case sensitive SQL query matcher
// instead of default regular expression matcher
db, mock, err := New(QueryMatcherOption(QueryMatcherEqual))
if err != nil {
	fmt.Println("failed to open sqlmock database:", err)
}
defer db.Close()

rows := NewRows([]string{"id", "title"}).
	AddRow(1, "one").
	AddRow(2, "two")

mock.ExpectQuery("SELECT * FROM users").WillReturnRows(rows)

rs, err := db.Query("SELECT * FROM users")
if err != nil {
	fmt.Println("failed to match expected query")
	return
}
defer rs.Close()

for rs.Next() {
	var id int
	var title string
	rs.Scan(&id, &title)
	fmt.Println("scanned id:", id, "and title:", title)
}

if rs.Err() != nil {
	fmt.Println("got rows error:", rs.Err())
}

Output:

scanned id: 1 and title: one
scanned id: 2 and title: two

db, mock, err := New()
if err != nil {
	fmt.Println("failed to open sqlmock database:", err)
}
defer db.Close()

rows := NewRows([]string{"id", "title"}).
	AddRow(1, "one").
	AddRow(2, "two")

mock.ExpectQuery("SELECT").WillReturnRows(rows)

rs, _ := db.Query("SELECT")
defer rs.Close()

for rs.Next() {
	var id int
	var title string
	rs.Scan(&id, &title)
	fmt.Println("scanned id:", id, "and title:", title)
}

if rs.Err() != nil {
	fmt.Println("got rows error:", rs.Err())
}

Output:

scanned id: 1 and title: one
scanned id: 2 and title: two

db, mock, err := New()
if err != nil {
	fmt.Println("failed to open sqlmock database:", err)
}
defer db.Close()

rows := NewRows([]string{"id", "title"}).CloseError(fmt.Errorf("close error"))
mock.ExpectQuery("SELECT").WillReturnRows(rows)

rs, _ := db.Query("SELECT")

// Note: that close will return error only before rows EOF
// that is a default sql package behavior. If you run rs.Next()
// it will handle the error internally and return nil bellow
if err := rs.Close(); err != nil {
	fmt.Println("got error:", err)
}

Output:

got error: close error

db, mock, err := New()
if err != nil {
	fmt.Println("failed to open sqlmock database:", err)
}
defer db.Close()

rows := NewRows([]string{"id", "null_int"}).
	AddRow(1, 7).
	AddRow(5, sql.NullInt64{Int64: 5, Valid: true}).
	AddRow(2, sql.NullInt64{})

mock.ExpectQuery("SELECT").WillReturnRows(rows)

rs, _ := db.Query("SELECT")
defer rs.Close()

for rs.Next() {
	var id int
	var num sql.NullInt64
	rs.Scan(&id, &num)
	fmt.Println("scanned id:", id, "and null int64:", num)
}

if rs.Err() != nil {
	fmt.Println("got rows error:", rs.Err())
}

Output:

scanned id: 1 and null int64: {7 true}
scanned id: 5 and null int64: {5 true}
scanned id: 2 and null int64: {0 false}

db, mock, err := New()
if err != nil {
	fmt.Println("failed to open sqlmock database:", err)
}
defer db.Close()

rows := NewRows([]string{"id", "title"}).AddRow(1, "john")
mock.ExpectQuery("SELECT").WillReturnRows(rows).RowsWillBeClosed()

db.Query("SELECT")

if err := mock.ExpectationsWereMet(); err != nil {
	fmt.Println("got error:", err)
}

Output:

got error: expected query rows to be closed, but it was not: ExpectedQuery => expecting Query, QueryContext or QueryRow which:
  - matches sql: 'SELECT'
  - is without arguments
  - should return rows:
    row 0 - [1 john]

db, mock, err := New()
if err != nil {
	fmt.Println("failed to open sqlmock database:", err)
}
defer db.Close()

rows := NewRows([]string{"id", "binary"}).
	AddRow(1, []byte(`one binary value with some text!`)).
	AddRow(2, []byte(`two binary value with even more text than the first one`))

mock.ExpectQuery("SELECT").WillReturnRows(rows)

rs, _ := db.Query("SELECT")
defer rs.Close()

type scanned struct {
	id  int
	raw sql.RawBytes
}
fmt.Println("initial read...")
var ss []scanned
for rs.Next() {
	var s scanned
	rs.Scan(&s.id, &s.raw)
	ss = append(ss, s)
	fmt.Println("scanned id:", s.id, "and raw:", string(s.raw))
}

if rs.Err() != nil {
	fmt.Println("got rows error:", rs.Err())
}

fmt.Println("after reading all...")
for _, s := range ss {
	fmt.Println("scanned id:", s.id, "and raw:", string(s.raw))
}

Output:

initial read...
scanned id: 1 and raw: one binary value with some text!
scanned id: 2 and raw: two binary value with even more text than the first one
after reading all...
scanned id: 1 and raw: ☠☠☠ MEMORY OVERWRITTEN ☠
scanned id: 2 and raw: ☠☠☠ MEMORY OVERWRITTEN ☠☠☠ ☠☠☠ MEMORY

db, mock, err := New()
if err != nil {
	fmt.Println("failed to open sqlmock database:", err)
}
defer db.Close()

rows := NewRows([]string{"id", "title"}).
	AddRow(0, "one").
	AddRow(1, "two").
	RowError(1, fmt.Errorf("row error"))
mock.ExpectQuery("SELECT").WillReturnRows(rows)

rs, _ := db.Query("SELECT")
defer rs.Close()

for rs.Next() {
	var id int
	var title string
	rs.Scan(&id, &title)
	fmt.Println("scanned id:", id, "and title:", title)
}

if rs.Err() != nil {
	fmt.Println("got rows error:", rs.Err())
}

Output:

scanned id: 0 and title: one
got rows error: row error

db, mock, err := New()
if err != nil {
	fmt.Println("failed to open sqlmock database:", err)
}
defer db.Close()

// note this line is important for unordered expectation matching
mock.MatchExpectationsInOrder(false)

result := NewResult(1, 1)

mock.ExpectExec("^UPDATE one").WithArgs("one").WillReturnResult(result)
mock.ExpectExec("^UPDATE two").WithArgs("one", "two").WillReturnResult(result)
mock.ExpectExec("^UPDATE three").WithArgs("one", "two", "three").WillReturnResult(result)

var wg sync.WaitGroup
queries := map[string][]interface{}{
	"one":   {"one"},
	"two":   {"one", "two"},
	"three": {"one", "two", "three"},
}

wg.Add(len(queries))
for table, args := range queries {
	go func(tbl string, a []interface{}) {
		if _, err := db.Exec("UPDATE "+tbl, a...); err != nil {
			fmt.Println("error was not expected:", err)
		}
		wg.Done()
	}(table, args)
}

wg.Wait()

if err := mock.ExpectationsWereMet(); err != nil {
	fmt.Println("there were unfulfilled expectations:", err)
}

Output: