sqlmock with gorm INSERT

Question

I'm having a lot of trouble mocking gorm INSERT queries. I was able to get my tests passing when selecting fine, but I am running into this error when inserting.

# gorms's debug output
INSERT INTO "groups" ("created_at","updated_at","deleted_at","name","description") VALUES ('2018-05-01 17:46:15','2018-05-01 17:46:15',NULL,'Group 1','A good group') RETURNING "groups"."id"

# Error returned from *gorm.DB.Create
2018/05/01 17:46:15 Error creating group: call to Query 'INSERT INTO "groups" ("created_at","updated_at","deleted_at","name","description") VALUES ($1,$2,$3,$4,$5) RETURNING "groups"."id"' with args [{Name: Ordinal:1 Value:2018-05-01 17:46:15.384319544 -0700 PDT m=+0.005382104} {Name: Ordinal:2 Value:2018-05-01 17:46:15.384319544 -0700 PDT m=+0.005382104} {Name: Ordinal:3 Value:<nil>} {Name: Ordinal:4 Value:Group 1} {Name: Ordinal:5 Value:A good group}], was not expected, next expectation is: ExpectedExec => expecting Exec or ExecContext which:
  - matches sql: '^INSERT INTO "groups" (.+)$'
  - is with arguments:
    0 - {}
    1 - {}
    2 - <nil>
    3 - Group 1
    4 - A good group
  - should return Result having:
      LastInsertId: 1
      RowsAffected: 1

I have tried multiple different versions of the regex, even tested the match using golang on regex101.com, but I can't seem to get my sqlmock to match gorm's insert.

Here is my test:

type AnyTime struct{} // I don't actually know if I even need this

func (a AnyTime) Match(v driver.Value) bool {
    _, ok := v.(time.Time)
    return ok
}

func TestGroupService_Create(t *testing.T) {
    db, mock, err := sqlmock.New()
    if err != nil {
        log.Fatalf("can't create sqlmock: %s", err)
    }
    models.DB, err = gorm.Open("postgres", db)
    if err != nil {
        log.Fatalf("can't open gorm connection: %s", err)
    }
    defer db.Close()

    models.DB.LogMode(true)

    name := "Group 1"
    description := "A good group"

    mock.ExpectExec("^INSERT INTO \"groups\" (.+)$").WithArgs(AnyTime{}, AnyTime{}, nil, name, description).WillReturnResult(sqlmock.NewResult(1, 1))

    s := GroupService{}

    req := &pb.CreateGroupRequest{
        Name: name,
        Description: description,
    }

    resp, err := s.Create(context.Background(), req)
    assert.Nil(t, err)

    if assert.NotNil(t, resp) {
        assert.Equal(t, resp.Group.Name, name)
        assert.Equal(t, resp.Group.Description, description)
    }

    err = mock.ExpectationsWereMet()
    assert.Nil(t, err)
}

and my service method I'm trying to test:

func (server *GroupService) Create(ctx context.Context, request *pb.CreateGroupRequest) (*pb.CreateGroupReply, error) {
    var group models.Group

    group.Name = request.Name
    group.Description = request.Description

    db := models.DB

    if err := db.Create(&group).Error; err != nil {
        log.Printf("Error creating group: %v", err)
        return nil, err
    }

    createReply := pb.CreateGroupReply{
        Group: mapGroupToService(group),
    }

    return &createReply, nil
}

I just can't seem to figure this out. Thanks!

Answer 1

This is a complete working example using mock.ExpectQuery as recommended in the answer from quexer:

package gorm_test

import (
  "regexp"
  "testing"
  "time"

  "github.com/DATA-DOG/go-sqlmock"
  "github.com/google/uuid"
  "gorm.io/driver/postgres"
  "gorm.io/gorm"
  "github.com/stretchr/testify/assert"
)

type User struct {
    UserID    uuid.UUID `json:"user_id" gorm:"primary_key;type:uuid;default:uuid_generate_v4()"`
    FirstName *string   `json:"first_name"`
    LastName  *string   `json:"last_name"`
    CreatedAt time.Time `json:"created_at" gorm:"default:CURRENT_TIMESTAMP"`
    UpdatedAt time.Time `json:"updated_at" gorm:"default:CURRENT_TIMESTAMP"`
}

func TestDB(parentT *testing.T) {
  parentT.Parallel()

  sqlDB, mock, errSQLMock := sqlmock.New()
  assert.Nil(parentT, errSQLMock)

  db, errDB := gorm.Open(postgres.New(postgres.Config{Conn: sqlDB}), &gorm.Config{})
  assert.Nil(parentT, errDB)

  parentT.Run("User", func(t *testing.T) {
    t.Parallel()

    userID := uuid.New()
    firstName := "firstName"
    lastName := "lastName"
    createdAt := time.Now()
    updatedAt := time.Now()

    // this expects the default transaction to begin
    mock.ExpectBegin()

    // this is the query to be expected
    mock.ExpectQuery(
      regexp.QuoteMeta(`
        INSERT INTO "users" ("first_name","last_name")
        VALUES ($1,$2)
        RETURNING "user_id","created_at","updated_at"
       `)).
      WithArgs(firstName, lastName).
      WillReturnRows(
        sqlmock.NewRows([]string{"user_id", "created_at", "updated_at"}).
          AddRow(userID, createdAt, updatedAt))

    // this expects the default transaction to commit
    mock.ExpectCommit()

    user := User{
      FirstName: &firstName,
      LastName: &lastName,
    }

    err := db.Create(&user).Error
    assert.Nil(t, err)

    // ensure that all fields were set on the User object
    assert.Equal(t, user.UserID, userID)
    assert.Equal(t, *user.FirstName, firstName)
    assert.Equal(t, *user.LastName, lastName)
    assert.WithinDuration(t, user.CreatedAt, createdAt, 0)
    assert.WithinDuration(t, user.UpdatedAt, updatedAt, 0)

    // ensure that all expectations are met in the mock
    errExpectations := mock.ExpectationsWereMet()
    assert.Nil(t, errExpectations)
  })
}

Answer 2

You need to change mock.ExpectExec to mock.ExpectQuery.

It's a known issue for GORM with PostgreSQL.

For more explanation, check this article.

Answer 3

I was able to mock and insert by switching to the go-mocket library. It seems to be made specifically for GORM. I'd love to know why the sqlmock inserting wasn't working, but this is my solution for now:

func SetupTests() *gorm.DB {
    mocket.Catcher.Register()

    db, err := gorm.Open(mocket.DRIVER_NAME, "")
    if err != nil {
        log.Fatalf("error mocking gorm: %s", err)
    }
    // Log mode shows the query gorm uses, so we can replicate and mock it
    db.LogMode(true)
    models.DB = db

    return db
}

func TestGroupService_Create(t *testing.T) {
    db := SetupTests()
    defer db.Close()

    var mockedId int64 = 64
    mocket.Catcher.Reset().NewMock().WithQuery("INSERT INTO \"groups\"").WithId(mockedId)

    s := GroupService{}

    name := "Group 1"
    description := "A good group"

    req := &pb.CreateGroupRequest{
        Name: name,
        Description: description,
    }

    resp, err := s.Create(context.Background(), req)
    assert.Nil(t, err)

    if assert.NotNil(t, resp) {
        assert.Equal(t, uint32(mockedId), resp.Group.Id)
        assert.Equal(t, name, resp.Group.Name)
        assert.Equal(t, description, resp.Group.Description)
    }
}