SQLMock and Gorm: Mocking Postgres Insert

Question

When I try to mock a Postgres insert with SQLMock and Gorm.io I receive an error that the query isn't expected. I tried to use regexp.QuoteMeta() to wrap and escape my string, but it doesn't work. I added and removed args and result, but the error continues to appear

How can I set the expected query by SQLMock?

I give you the raw PostgresQuery and the UserModel

//RAW QUERY
INSERT INTO "users" ("id","name","surname","birthdate","company","custom_claims","deleted") VALUES ($1,$2,$3,$4,$5,$6,$7)' with args [{Name: Ordinal:1 Value:my_user_id} {Name: Ordinal:2 Value:<nil>} {Name: Ordinal:3 Value:<nil>} {Name: Ordinal:4 Value:<nil>} {Name: Ordinal:5 Value:<nil>} {Name: Ordinal:6 Value:<nil>} {Name: Ordinal:7 Value:<nil>}]

//Gorm model
type User struct {
    ID           string `gorm:"primaryKey"`
    Name         *string
    Surname      *string
    Birthdate    *time.Time
    Company      *string
    CustomClaims *json.RawMessage
    Deleted      gorm.DeletedAt
}

func (repository Repository) CreateUser(user users.User) (*users.User, error) {
    newUser := toRepositoryModel(user)

    err := repository.db.Create(newUser).Error //db -> *gorm.DB

    //....
}

//TEST
const expectedQuery = `INSERT INTO "users" ("id","name","surname","birthdate","company","custom_claims","deleted") VALUES ($1,$2,$3,$4,$5,$6,$7)' with args [{Name: Ordinal:1 Value:my_user_id} {Name: Ordinal:2 Value:<nil>} {Name: Ordinal:3 Value:<nil>} {Name: Ordinal:4 Value:<nil>} {Name: Ordinal:5 Value:<nil>} {Name: Ordinal:6 Value:<nil>} {Name: Ordinal:7 Value:<nil>}]`
suite.mock.ExpectQuery(regexp.QuoteMeta(experctedQuery)) //HOW SHOULD BE MODIFIED?
user, err2 := postgres.CreateUser(users.User{
   ID: ID,
})

//ERROR
"call to ExecQuery 'INSERT INTO \"users\" (\"id\",\"name\",\"surname\",\"birthdate\",\"company\",\"custom_claims\",\"deleted\") VALUES ($1,$2,$3,$4,$5,$6,$7)' with args [{Name: Ordinal:1 Value:my_user_id} {Name: Ordinal:2 Value:<nil>} {Name: Ordinal:3 Value:<nil>} {Name: Ordinal:4 Value:<nil>} {Name: Ordinal:5 Value:<nil>} {Name: Ordinal:6 Value:<nil>} {Name: Ordinal:7 Value:<nil>}], was not expected, next expectation is: ExpectedQuery => expecting Query, QueryContext or QueryRow which:\n  - matches sql: 'INSERT INTO \"users\" \\(\"id\",\"name\",\"surname\",\"birthdate\",\"company\",\"custom_claims\",\"deleted\"\\) VALUES \\(\\$1,\\$2,\\$3,\\$4,\\$5,\\$6,\\$7\\)' with args \\[\\{Name: Ordinal:1 Value:my_user_id\\} \\{Name: Ordinal:2 Value:<nil>\\} \\{Name: Ordinal:3 Value:<nil>\\} \\{Name: Ordinal:4 Value:<nil>\\} \\{Name: Ordinal:5 Value:<nil>\\} \\{Name: Ordinal:6 Value:<nil>\\} \\{Name: Ordinal:7 Value:<nil>\\}\\]'\n  - is without arguments",
          }
          call to ExecQuery 'INSERT INTO "users" ("id","name","surname","birthdate","company","custom_claims","deleted") VALUES ($1,$2,$3,$4,$5,$6,$7)' with args [{Name: Ordinal:1 Value:my_user_id} {Name: Ordinal:2 Value:<nil>} {Name: Ordinal:3 Value:<nil>} {Name: Ordinal:4 Value:<nil>} {Name: Ordinal:5 Value:<nil>} {Name: Ordinal:6 Value:<nil>} {Name: Ordinal:7 Value:<nil>}], was not expected, next expectation is: ExpectedQuery => expecting Query, QueryContext or QueryRow which:
            - matches sql: 'INSERT INTO "users" \("id","name","surname","birthdate","company","custom_claims","deleted"\) VALUES \(\$1,\$2,\$3,\$4,\$5,\$6,\$7\)' with args \[\{Name: Ordinal:1 Value:my_user_id\} \{Name: Ordinal:2 Value:<nil>\} \{Name: Ordinal:3 Value:<nil>\} \{Name: Ordinal:4 Value:<nil>\} \{Name: Ordinal:5 Value:<nil>\} \{Name: Ordinal:6 Value:<nil>\} \{Name: Ordinal:7 Value:<nil>\}\]'
            - is without arguments
      occurred

Do you have a working example with Postgres? maybe I'm missing something. I also tried to user arguments, but it isn't working — AndreaCostanzo1, May 01 '21 at 12:45

AndreaCostanzo1 · Accepted Answer · 2021-05-01T15:02:30.813

3

I've made it work.

suite.mock.ExpectExec(regexp.QuoteMeta(`INSERT INTO "users" ("id","name","surname","birthdate","company","custom_claims","deleted") VALUES ($1,$2,$3,$4,$5,$6,$7)`)).WithArgs(ID, nil, nil, nil, nil, nil, nil).WillReturnResult(sqlmock.NewResult(0, 1))

UPDATE

Postgres SELECT :

//Simulate returned row(s)
userMockRows := sqlmock.NewRows([]string{"id", "name", "surname", "birthdate", "company", "custom_claims", "deleted"}).AddRow(ID, nil, nil, nil, nil, nil, nil)

//Expect SELECT
suite.mock.ExpectQuery(regexp.QuoteMeta(`SELECT * FROM "users" WHERE id = $1`)).WithArgs(ID).WillReturnRows(userMockRows)

Postgres UPDATE:

suite.mock.ExpectExec(regexp.QuoteMeta(`UPDATE "users" SET "name"=$1,"surname"=$2,"birthdate"=$3,"company"=$4,"custom_claims"=$5,"deleted"=$6 WHERE "id" = $7`)).WithArgs(newName, nil, nil, nil, nil, nil, ID).WillReturnResult(sqlmock.NewResult(0, 1))

edited May 01 '21 at 15:02

answered May 01 '21 at 13:01

AndreaCostanzo1

1,799
1
12
30

Is there a way to use `sqlmock` for mocking without having to "expect" queries? I mean, what if I don't care about the query being executed, but only on the end result. – csmathhc May 06 '22 at 06:16
1

Fast answer: no. But remember that both things are the same. When you run the same query you get the same result (when the database remains in a certain state). Sqlmock is not a complete mockup of the DB, but actually relies on this assumption and intercepts the queries created. In this way, you can verify if you are performing the queries you expect on your db. – AndreaCostanzo1 May 06 '22 at 07:36

SQLMock and Gorm: Mocking Postgres Insert

1 Answers1