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)
Comment From: github-actions[bot]
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 ✨
Comment From: rkalli
Author,
Please check this isssue.
Thanks Ravi Kalli
Sent from my iPhone
On Oct 4, 2020, at 1:52 PM, github-actions[bot] notifications@github.com wrote:
Closed #3565.
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or unsubscribe.
Comment From: mohanasundaramn
@jinzhu How to mock Gorm v2 with sqlmock. Any example would be greatly helpful
Comment From: jinzhu
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
Comment From: jinzhu
@rkalli you could disable the default transaction https://gorm.io/docs/gorm_config.html#SkipDefaultTransaction
Comment From: hielfx
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
}
Comment From: jinzhu
When using MySQL, you need to use SkipInitializeWithVersion: true
https://gorm.io/docs/connecting_to_the_database.html#MySQL
Comment From: hielfx
@jinzhu Thanks for the quick response, it's working now
Comment From: hexuejian
gorm2.0 ExpectQuery -> ExpectExec
Comment From: sebastianbuechler
gorm2.0 ExpectQuery -> ExpectExec
Can you give an example of what should run? Maybe even with the original code snippet of this issue?
Comment From: DionAngga
please give me example gorm2.0 with sqlmock. i really need this
Comment From: addonrizky
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)
}
}
Comment From: josk2
When using MySQL, you need to use
SkipInitializeWithVersion: truehttps://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{})
Comment From: ityulkanov
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:
Comment From: sebastianbuechler
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)
}
}
Comment From: ityulkanov
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:
```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(
Comment From: sebastianbuechler
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: ```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:
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
)
Comment From: avanshee
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: ```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:
``` 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?
Comment From: HimangshuKakati
@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
Comment From: marcusvnac
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
Comment From: marcusvnac
@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?
Comment From: rcodina-tp
if I use `func TestRepository_FirebaseMessage(t *testing.T) { db, mock, err := sqlmock.New() assert.NoError(t, err) defer db.Close()
// Expectation for SELECT VERSION()
mock.ExpectQuery("SELECT VERSION()").WillReturnRows(sqlmock.NewRows([]string{"version"}).AddRow("8.0.23"))
gormDB, err := gorm.Open(mysql.New(mysql.Config{
Conn: db,
}), &gorm.Config{})
assert.NoError(t, err)
type args struct {
userID int
}
tests := []struct {
name string
args args
mockSetup func()
token string
Err error
wantErr bool
}{
{
name: "fcm_token retriven sucessfully",
args: args{
userID: 1,
},
mockSetup: func() {
mock.ExpectQuery("SELECT * FROM `fcm_tokens` WHERE user_id = ? `fcm_tokens`.`id` LIMIT ?").
WithArgs(1).
WillReturnRows(sqlmock.NewRows([]string{"token"}).AddRow("valid_token"))
},
token: "valid_token",
},
}
for _, tt := range tests {
t.Run(tt.name, func(t *testing.T) {
tt.mockSetup()
r := &Repository{
Db: gormDB,
}
got, err := r.FirebaseMessage(tt.args.userID)
if tt.wantErr {
assert.Error(t, err)
assert.Equal(t, tt.Err.Error(), err.Error())
} else {
assert.NoError(t, err)
assert.Equal(t, tt.token, got)
}
err = mock.ExpectationsWereMet()
assert.NoError(t, err)
})
}
}`
generate a problem although the sql es equal than excpected
could not match actual sql: "SELECT * FROM fcm_tokens WHERE user_id = ? ORDER BY fcm_tokens.id LIMIT ?" with expected regexp "SELECT * FROM fcm_tokens WHERE user_id = ? fcm_tokens.id LIMIT ?"