gorm v2.0 unit testing with sqlmock

Question

gorm v2.0 unit testing with sqlmock

rkalli opened this issue 4 years ago · 21 comments

Description

Unit testing with sqlmock works great with V1. But after updating to V2 the same test fails with following error:

/workspace/go/src/github.com/go-gorm/playground/gorm-v2-sqlmock_test.go:73 call to ExecQuery 'INSERT INTO "students" ("id","name") VALUES ($1,$2)' with args [{Name: Ordinal:1 Value:123456} {Name: Ordinal:2 Value:Test 1}] was not expected; call to Rollback transaction was not expected

I am unable to push to playground and getting permission denied. But here are the test files and also attaching the repository as a zip file.

package main

import (
	"database/sql"
	"regexp"
	"testing"

	"github.com/jinzhu/gorm"
	"gopkg.in/DATA-DOG/go-sqlmock.v1"
)

type v1Suite struct {
	db      *gorm.DB
	mock    sqlmock.Sqlmock
	student Student
}

func TestGORMV1(t *testing.T) {
	s := &v1Suite{}
	var (
		db  *sql.DB
		err error
	)

	db, s.mock, err = sqlmock.New()
	if err != nil {
		t.Errorf("Failed to open mock sql db, got error: %v", err)
	}

	if db == nil {
		t.Error("mock db is null")
	}

	if s.mock == nil {
		t.Error("sqlmock is null")
	}

	s.db, err = gorm.Open("postgres", db)
	if err != nil {
		t.Errorf("Failed to open gorm db, got error: %v", err)
	}

	if s.db == nil {
		t.Error("gorm db is null")
	}

	s.student = Student{
		ID:   "123456",
		Name: "Test 1",
	}

	defer db.Close()

	s.mock.MatchExpectationsInOrder(false)
	s.mock.ExpectBegin()

	s.mock.ExpectQuery(regexp.QuoteMeta(
		`INSERT INTO "students" ("id","name") 
					VALUES ($1,$2) RETURNING "students"."id"`)).
		WithArgs(s.student.ID, s.student.Name).
		WillReturnRows(sqlmock.NewRows([]string{"id"}).
			AddRow(s.student.ID))

	s.mock.ExpectCommit()

	if err = s.db.Create(&s.student).Error; err != nil {
		t.Errorf("Failed to insert to gorm db, got error: %v", err)
	}

	err = s.mock.ExpectationsWereMet()
	if err != nil {
		t.Errorf("Failed to meet expectations, got error: %v", err)
	}
}

After updated to V2:

package main

import (
	"database/sql"
	"regexp"
	"testing"

	"gopkg.in/DATA-DOG/go-sqlmock.v1"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
)

type v2Suite struct {
	db      *gorm.DB
	mock    sqlmock.Sqlmock
	student Student
}

func TestGORMV2(t *testing.T) {
	s := &v2Suite{}
	var (
		db  *sql.DB
		err error
	)

	db, s.mock, err = sqlmock.New()
	if err != nil {
		t.Errorf("Failed to open mock sql db, got error: %v", err)
	}

	if db == nil {
		t.Error("mock db is null")
	}

	if s.mock == nil {
		t.Error("sqlmock is null")
	}

	dialector := postgres.New(postgres.Config{
		DSN:                  "sqlmock_db_0",
		DriverName:           "postgres",
		Conn:                 db,
		PreferSimpleProtocol: true,
	})
	s.db, err = gorm.Open(dialector, &gorm.Config{})
	if err != nil {
		t.Errorf("Failed to open gorm v2 db, got error: %v", err)
	}

	if s.db == nil {
		t.Error("gorm db is null")
	}

	s.student = Student{
		ID:   "123456",
		Name: "Test 1",
	}

	defer db.Close()

	s.mock.MatchExpectationsInOrder(false)
	s.mock.ExpectBegin()

	s.mock.ExpectQuery(regexp.QuoteMeta(
		`INSERT INTO "students" ("id","name")
					VALUES ($1,$2) RETURNING "students"."id"`)).
		WithArgs(s.student.ID, s.student.Name).
		WillReturnRows(sqlmock.NewRows([]string{"id"}).
			AddRow(s.student.ID))

	s.mock.ExpectCommit()

	if err = s.db.Create(&s.student).Error; err != nil {
		t.Errorf("Failed to insert to gorm db, got error: %v", err)
	}

	err = s.mock.ExpectationsWereMet()
	if err != nil {
		t.Errorf("Failed to meet expectations, got error: %v", err)
	}
}
[playground.zip](https://github.com/go-gorm/gorm/files/5319499/playground.zip)

Answer 1 · 2020-10-02T03:07:41.000Z

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 2 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨

Answer 2 · 2020-10-04T23:02:12.000Z

Author, Please check this isssue. Thanks Ravi Kalli

…

Sent from my iPhone

On Oct 4, 2020, at 1:52 PM, github-actions[bot] ***@***.***> wrote: Closed #3565. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or unsubscribe.

Answer 3 · 2020-10-10T15:01:06.000Z

@jinzhu How to mock Gorm v2 with sqlmock. Any example would be greatly helpful

Answer 4 · 2020-10-12T02:36:33.000Z

Checkout connect with existing database connection, personally I don't use sqlmock for testing, use real databases.

https://gorm.io/docs/connecting_to_the_database.html#Existing-database-connection

Answer 5 · 2020-10-12T02:37:52.000Z

@rkalli you could disable the default transaction https://gorm.io/docs/gorm_config.html#SkipDefaultTransaction

Answer 6 · 2020-10-19T11:24:17.000Z

Checkout connect with existing database connection, personally I don't use sqlmock for testing, use real databases.

https://gorm.io/docs/connecting_to_the_database.html#Existing-database-connection

Following the steps here with sql-mock is returning this error failed to initialize database, got error all expectations were already fulfilled, call to Query 'SELECT VERSION()' with args [] was not expected

Reproducible code:

package main

import (
	"github.com/DATA-DOG/go-sqlmock"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)

func main() {

	sqlDB, _, err := sqlmock.New()
	if err != nil {
		panic(err)
	}

	gormDB, err := gorm.Open(mysql.New(mysql.Config{
		Conn: sqlDB,
	}), &gorm.Config{})
	if err != nil {
		panic(err) // Error here
	}

	_ = gormDB
}

Answer 7 · 2020-10-19T12:10:20.000Z

When using MySQL, you need to use SkipInitializeWithVersion: true

https://gorm.io/docs/connecting_to_the_database.html#MySQL

Answer 8 · 2020-10-19T12:54:15.000Z

@jinzhu Thanks for the quick response, it's working now

Answer 9 · 2022-03-30T15:56:13.000Z

gorm2.0 ExpectQuery -> ExpectExec

Answer 10 · 2022-03-31T20:19:48.000Z

gorm2.0 ExpectQuery -> ExpectExec

Can you give an example of what should run? Maybe even with the original code snippet of this issue?

Answer 11 · 2022-04-18T07:50:11.000Z

please give me example gorm2.0 with sqlmock. i really need this

Answer 12 · 2022-04-26T12:15:08.000Z

please give me example gorm2.0 with sqlmock. i really need this

try this snippet @DionAngga

package test

import (
	"database/sql"
	"regexp"
	"strconv"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)

type v2Suite struct {
	db      *gorm.DB
	mock    sqlmock.Sqlmock
	student Student
}

type Student struct {
	ID   string
	Name string
}

func TestGORMV2(t *testing.T) {
	s := &v2Suite{}
	var (
		db  *sql.DB
		err error
	)

	db, s.mock, err = sqlmock.New()
	if err != nil {
		t.Errorf("Failed to open mock sql db, got error: %v", err)
	}

	if db == nil {
		t.Error("mock db is null")
	}

	if s.mock == nil {
		t.Error("sqlmock is null")
	}

	s.db, err = gorm.Open(mysql.New(mysql.Config{
		Conn:                      db,
		SkipInitializeWithVersion: true,
	}), &gorm.Config{})
	if err != nil {
		panic(err) // Error here
	}

	defer db.Close()

	s.student = Student{
		ID:   "123456",
		Name: "Test 1",
	}

	defer db.Close()

	studentID, _ := strconv.Atoi(s.student.ID)

	s.mock.ExpectBegin()

	s.mock.ExpectExec(
		regexp.QuoteMeta("INSERT INTO `students` (`id`,`name`) VALUES (?,?)")).
		WithArgs(s.student.ID, s.student.Name).
		WillReturnResult(sqlmock.NewResult(int64(studentID), 1))

	s.mock.ExpectCommit()

	if err = s.db.Create(&s.student).Error; err != nil {
		t.Errorf("Failed to insert to gorm db, got error: %v", err)
		t.FailNow()
	}

	err = s.mock.ExpectationsWereMet()
	if err != nil {
		t.Errorf("Failed to meet expectations, got error: %v", err)
	}
}

Answer 13 · 2022-11-28T09:46:43.000Z

When using MySQL, you need to use SkipInitializeWithVersion: true

https://gorm.io/docs/connecting_to_the_database.html#MySQL

Thanks @jinzhu .
I just add SkipInitializeWithVersion: true and pass

db, err := gorm.Open(mysql.New(mysql.Config{
		Conn:                      sqlDB,
		DriverName:                "mysql",
		SkipInitializeWithVersion: true,
	}),
		&gorm.Config{})

Answer 14 · 2023-02-10T14:56:00.000Z

How do I make the code above works with Postgres? Seems like it requires some more tuning( call to Query 'INSERT INTO "students" ("name","id") VALUES ($1,$2) RETURNING "id"' with args [{Name: Ordinal:1 Value:Test 1} {Name: Ordinal:2 Value:123456}], was not expected, next expectation is: ExpectedExec => expecting Exec or ExecContext which:

Answer 15 · 2023-02-13T09:36:14.000Z

How do I make the code above works with Postgres? Seems like it requires some more tuning( call to Query 'INSERT INTO "students" ("name","id") VALUES ($1,$2) RETURNING "id"' with args [{Name: Ordinal:1 Value:Test 1} {Name: Ordinal:2 Value:123456}], was not expected, next expectation is: ExpectedExec => expecting Exec or ExecContext which:

Here you go:

package test

import (
	"database/sql"
	"regexp"
	"strconv"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
)

type v2Suite struct {
	db      *gorm.DB
	mock    sqlmock.Sqlmock
	student Student
}

type Student struct {
	ID   string
	Name string
}

func TestGORMV2(t *testing.T) {
	s := &v2Suite{}
	var (
		db  *sql.DB
		err error
	)

	db, s.mock, err = sqlmock.New()
	if err != nil {
		t.Errorf("Failed to open mock sql db, got error: %v", err)
	}

	if db == nil {
		t.Error("mock db is null")
	}

	if s.mock == nil {
		t.Error("sqlmock is null")
	}

	s.db, err = gorm.Open(postgres.New(
		postgres.Config{
			Conn:       db,
			DriverName: "postgres",
		},
	), &gorm.Config{})
	if err != nil {
		panic(err) // Error here
	}

	defer db.Close()

	s.student = Student{
		ID:   "123456",
		Name: "Test 1",
	}

	defer db.Close()

	studentID, _ := strconv.Atoi(s.student.ID)

	s.mock.ExpectBegin()

	s.mock.ExpectExec(
		regexp.QuoteMeta(`INSERT INTO "students" ("id","name") VALUES ($1,$2)`)).
		WithArgs(s.student.ID, s.student.Name).
		WillReturnResult(sqlmock.NewResult(int64(studentID), 1))

	s.mock.ExpectCommit()

	if err = s.db.Create(&s.student).Error; err != nil {
		t.Errorf("Failed to insert to gorm db, got error: %v", err)
		t.FailNow()
	}

	err = s.mock.ExpectationsWereMet()
	if err != nil {
		t.Errorf("Failed to meet expectations, got error: %v", err)
	}
}

Answer 16 · 2023-02-13T20:43:29.000Z

call to Query 'INSERT INTO "students" ("name","id") VALUES ($1,$2) RETURNING "id"' with args [{Name: Ordinal:1 Value:Test 1} {Name: Ordinal:2 Value:123456}], was not expected, next expectation is: ExpectedExec => expecting Exec or ExecContext which:

How do I make the code above works with Postgres? Seems like it requires some more tuning( call to Query 'INSERT INTO "students" ("name","id") VALUES ($1,$2) RETURNING "id"' with args [{Name: Ordinal:1 Value:Test 1} {Name: Ordinal:2 Value:123456}], was not expected, next expectation is: ExpectedExec => expecting Exec or ExecContext which:

Here you go:

package test

import (
	"database/sql"
	"regexp"
	"strconv"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
)

type v2Suite struct {
	db      *gorm.DB
	mock    sqlmock.Sqlmock
	student Student
}

type Student struct {
	ID   string
	Name string
}

func TestGORMV2(t *testing.T) {
	s := &v2Suite{}
	var (
		db  *sql.DB
		err error
	)

	db, s.mock, err = sqlmock.New()
	if err != nil {
		t.Errorf("Failed to open mock sql db, got error: %v", err)
	}

	if db == nil {
		t.Error("mock db is null")
	}

	if s.mock == nil {
		t.Error("sqlmock is null")
	}

	s.db, err = gorm.Open(postgres.New(
		postgres.Config{
			Conn:       db,
			DriverName: "postgres",
		},
	), &gorm.Config{})
	if err != nil {
		panic(err) // Error here
	}

	defer db.Close()

	s.student = Student{
		ID:   "123456",
		Name: "Test 1",
	}

	defer db.Close()

	studentID, _ := strconv.Atoi(s.student.ID)

	s.mock.ExpectBegin()

	s.mock.ExpectExec(
		regexp.QuoteMeta(`INSERT INTO "students" ("id","name") VALUES ($1,$2)`)).
		WithArgs(s.student.ID, s.student.Name).
		WillReturnResult(sqlmock.NewResult(int64(studentID), 1))

	s.mock.ExpectCommit()

	if err = s.db.Create(&s.student).Error; err != nil {
		t.Errorf("Failed to insert to gorm db, got error: %v", err)
		t.FailNow()
	}

	err = s.mock.ExpectationsWereMet()
	if err != nil {
		t.Errorf("Failed to meet expectations, got error: %v", err)
	}
}

Copied and pasted to Goland this exact code, and it produces the same error: call to Query 'INSERT INTO "students" ("name","id") VALUES ($1,$2) RETURNING "id"' with args [{Name: Ordinal:1 Value:Test 1} {Name: Ordinal:2 Value:123456}], was not expected, next expectation is: ExpectedExec => expecting Exec or ExecContext which: I guess there is some curse on my machine(

Answer 17 · 2023-02-14T08:28:23.000Z

call to Query 'INSERT INTO "students" ("name","id") VALUES ($1,$2) RETURNING "id"' with args [{Name: Ordinal:1 Value:Test 1} {Name: Ordinal:2 Value:123456}], was not expected, next expectation is: ExpectedExec => expecting Exec or ExecContext which:
How do I make the code above works with Postgres? Seems like it requires some more tuning( call to Query 'INSERT INTO "students" ("name","id") VALUES ($1,$2) RETURNING "id"' with args [{Name: Ordinal:1 Value:Test 1} {Name: Ordinal:2 Value:123456}], was not expected, next expectation is: ExpectedExec => expecting Exec or ExecContext which:

Here you go:
package test

import (
	"database/sql"
	"regexp"
	"strconv"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
)

type v2Suite struct {
	db      *gorm.DB
	mock    sqlmock.Sqlmock
	student Student
}

type Student struct {
	ID   string
	Name string
}

func TestGORMV2(t *testing.T) {
	s := &v2Suite{}
	var (
		db  *sql.DB
		err error
	)

	db, s.mock, err = sqlmock.New()
	if err != nil {
		t.Errorf("Failed to open mock sql db, got error: %v", err)
	}

	if db == nil {
		t.Error("mock db is null")
	}

	if s.mock == nil {
		t.Error("sqlmock is null")
	}

	s.db, err = gorm.Open(postgres.New(
		postgres.Config{
			Conn:       db,
			DriverName: "postgres",
		},
	), &gorm.Config{})
	if err != nil {
		panic(err) // Error here
	}

	defer db.Close()

	s.student = Student{
		ID:   "123456",
		Name: "Test 1",
	}

	defer db.Close()

	studentID, _ := strconv.Atoi(s.student.ID)

	s.mock.ExpectBegin()

	s.mock.ExpectExec(
		regexp.QuoteMeta(`INSERT INTO "students" ("id","name") VALUES ($1,$2)`)).
		WithArgs(s.student.ID, s.student.Name).
		WillReturnResult(sqlmock.NewResult(int64(studentID), 1))

	s.mock.ExpectCommit()

	if err = s.db.Create(&s.student).Error; err != nil {
		t.Errorf("Failed to insert to gorm db, got error: %v", err)
		t.FailNow()
	}

	err = s.mock.ExpectationsWereMet()
	if err != nil {
		t.Errorf("Failed to meet expectations, got error: %v", err)
	}
}
Copied and pasted to Goland this exact code, and it produces the same error: call to Query 'INSERT INTO "students" ("name","id") VALUES ($1,$2) RETURNING "id"' with args [{Name: Ordinal:1 Value:Test 1} {Name: Ordinal:2 Value:123456}], was not expected, next expectation is: ExpectedExec => expecting Exec or ExecContext which: I guess there is some curse on my machine(

Maybe can you try another environment?

I ran it on my machine and it works without an issue:

I'm using Windows 10.0.22621, VS Code 1.75.1 and Go 1.18.1
My go.mod file for the test:

module gomocktest

go 1.18

require (
	github.com/DATA-DOG/go-sqlmock v1.5.0
	gorm.io/driver/postgres v1.4.7
	gorm.io/gorm v1.24.5
)

require (
	github.com/jackc/pgpassfile v1.0.0 // indirect
	github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a // indirect
	github.com/jackc/pgx/v5 v5.2.0 // indirect
	github.com/jinzhu/inflection v1.0.0 // indirect
	github.com/jinzhu/now v1.1.5 // indirect
	golang.org/x/crypto v0.4.0 // indirect
	golang.org/x/text v0.5.0 // indirect
)

Answer 18 · 2023-02-18T05:27:40.000Z

call to Query 'INSERT INTO "students" ("name","id") VALUES ($1,$2) RETURNING "id"' with args [{Name: Ordinal:1 Value:Test 1} {Name: Ordinal:2 Value:123456}], was not expected, next expectation is: ExpectedExec => expecting Exec or ExecContext which:
How do I make the code above works with Postgres? Seems like it requires some more tuning( call to Query 'INSERT INTO "students" ("name","id") VALUES ($1,$2) RETURNING "id"' with args [{Name: Ordinal:1 Value:Test 1} {Name: Ordinal:2 Value:123456}], was not expected, next expectation is: ExpectedExec => expecting Exec or ExecContext which:

Here you go:
package test

import (
	"database/sql"
	"regexp"
	"strconv"
	"testing"

	"github.com/DATA-DOG/go-sqlmock"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
)

type v2Suite struct {
	db      *gorm.DB
	mock    sqlmock.Sqlmock
	student Student
}

type Student struct {
	ID   string
	Name string
}

func TestGORMV2(t *testing.T) {
	s := &v2Suite{}
	var (
		db  *sql.DB
		err error
	)

	db, s.mock, err = sqlmock.New()
	if err != nil {
		t.Errorf("Failed to open mock sql db, got error: %v", err)
	}

	if db == nil {
		t.Error("mock db is null")
	}

	if s.mock == nil {
		t.Error("sqlmock is null")
	}

	s.db, err = gorm.Open(postgres.New(
		postgres.Config{
			Conn:       db,
			DriverName: "postgres",
		},
	), &gorm.Config{})
	if err != nil {
		panic(err) // Error here
	}

	defer db.Close()

	s.student = Student{
		ID:   "123456",
		Name: "Test 1",
	}

	defer db.Close()

	studentID, _ := strconv.Atoi(s.student.ID)

	s.mock.ExpectBegin()

	s.mock.ExpectExec(
		regexp.QuoteMeta(`INSERT INTO "students" ("id","name") VALUES ($1,$2)`)).
		WithArgs(s.student.ID, s.student.Name).
		WillReturnResult(sqlmock.NewResult(int64(studentID), 1))

	s.mock.ExpectCommit()

	if err = s.db.Create(&s.student).Error; err != nil {
		t.Errorf("Failed to insert to gorm db, got error: %v", err)
		t.FailNow()
	}

	err = s.mock.ExpectationsWereMet()
	if err != nil {
		t.Errorf("Failed to meet expectations, got error: %v", err)
	}
}
Copied and pasted to Goland this exact code, and it produces the same error: call to Query 'INSERT INTO "students" ("name","id") VALUES ($1,$2) RETURNING "id"' with args [{Name: Ordinal:1 Value:Test 1} {Name: Ordinal:2 Value:123456}], was not expected, next expectation is: ExpectedExec => expecting Exec or ExecContext which: I guess there is some curse on my machine(
Maybe can you try another environment?

I ran it on my machine and it works without an issue: I'm using Windows 10.0.22621, VS Code 1.75.1 and Go 1.18.1 My go.mod file for the test:
module gomocktest

go 1.18

require (
	github.com/DATA-DOG/go-sqlmock v1.5.0
	gorm.io/driver/postgres v1.4.7
	gorm.io/gorm v1.24.5
)

require (
	github.com/jackc/pgpassfile v1.0.0 // indirect
	github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a // indirect
	github.com/jackc/pgx/v5 v5.2.0 // indirect
	github.com/jinzhu/inflection v1.0.0 // indirect
	github.com/jinzhu/now v1.1.5 // indirect
	golang.org/x/crypto v0.4.0 // indirect
	golang.org/x/text v0.5.0 // indirect
)

Hi, I m facing an issue of Error: on line s.db.Create(&s.student).Error.
Error message:
ExecQuery: could not match actual sql: "INSERT INTO mock.mockobj (SUBSCRIPTION_ID,SUBSCRIPTION_PROVIDER,mock_ACCOUNT_ID,PRODUCT_ID,SUBSCRIPTION_SCOPE,HOME_ID,PAY_STATE,VALID,PERIOD_END_DATE) VALUES (?,?,?,?,?,?,?,?,?)" with expected regexp "INSERT INTO 'mock'.'mockobj' ('SUBSCRIPTION_ID', 'SUBSCRIPTION_PROVIDER', 'mock_ACCOUNT_ID', 'PRODUCT_ID', 'SUBSCRIPTION_SCOPE', 'HOME_ID', 'PAY_STATE', 'VALID','PERIOD_END_DATE') VALUES (?, ?, ?, ?, ?, ?,?, ?, ?)"; call to Rollback transaction, was not expected

Not sure why regexp is adding \ or if the \is making any difference?

Answer 19 · 2023-02-28T14:13:30.000Z

@rkalli Was this issue fixed after disabling the default transaction. I don't know about mysql but on postgres I'm still facing this issue. @jinzhu

Answer 20 · 2023-06-09T15:04:24.000Z

I have the same problem here with the Postgres version.
Running on Mac OS 13.4, Go 1.20.4, gorm.io/driver/postgres v1.5.2 and gorm.io/gorm v1.25.1

Answer 21 · 2023-06-09T17:02:27.000Z

@rkalli Was this issue fixed after disabling the default transaction. I don't know about mysql but on postgres I'm still facing this issue. @jinzhu

@HimangshuKakati did you find any workaround for this issue with Postgres?