gorm v2.0 unit testing with sqlmock
rkalli opened this issue · 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)
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 ✨
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
@rkalli you could disable the default transaction https://gorm.io/docs/gorm_config.html#SkipDefaultTransaction
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
}
When using MySQL, you need to use SkipInitializeWithVersion: true
gorm2.0 ExpectQuery -> ExpectExec
gorm2.0 ExpectQuery -> ExpectExec
Can you give an example of what should run? Maybe even with the original code snippet of this issue?
please give me example gorm2.0 with sqlmock. i really need this
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)
}
}
When using MySQL, you need to use
SkipInitializeWithVersion: true
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{})
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:
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)
}
}
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(
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
)
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?
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
@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?