Testing SQL table-valued parameters in sqlmock

Question

I have a function that is designed to insert a large number of elements into an MSSQL database using a table-valued parameter and a procedure.

func (requester *Requester) doQuery(ctx context.Context, dtos interface{}) error {
    conn, err := requester.conn.Conn(ctx)
    if err != nil {
        return err
    }

    defer func() {
        if clErr := conn.Close(); clErr != nil {
            err = clErr
        }
    }()

    tx, err := conn.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelRepeatableRead, ReadOnly: false})
    if err != nil {
        return err
    }

    defer func() {
        if p := recover(); p != nil {
            tx.Rollback()
            panic(p)
        } else if err != nil {
            tx.Rollback()
        } else {
            tx.Commit()
    }()

    param := sql.Named("TVP", mssql.TVP{
        TypeName: "MyTypeName",
        Value:    dtos,
    })

    return tx.ExecContext(ctx, "EXEC [dbo].[usp_InsertConsumption] @TVP", param)
}

The test I wrote for this function is included below (note that it depends on ginkgo and gomega):

Describe("SQL Tests", func() {

    It("AddConsumption - No failures - Added", func() {

        db, mock, _ := sqlmock.New()
        requester := Requester{conn: db}
        defer db.Close()

        mock.ExpectBegin()
        mock.ExpectExec(regexp.QuoteMeta("EXEC [dbo].[usp_InsertConsumption] @TVP")).
            WithArgs("").WillReturnResult(sqlmock.NewResult(1, 1))
        mock.ExpectExec(regexp.QuoteMeta("EXEC [dbo].[usp_InsertTags] @TVP")).
            WithArgs("").WillReturnResult(sqlmock.NewResult(1, 1))
        mock.ExpectCommit()

        err := requester.doQuery(context.TODO(), generateData())
        Expect(err).ShouldNot(HaveOccurred())
        Expect(mock.ExpectationsWereMet()).ShouldNot(HaveOccurred())
    })
})

Now, this code was written for a MySQL context and since I've ported the code over to MSSQL, I've been getting a peculiar error:

sql: converting argument with name \"TVP\" type: unsupported type mssql.TVP, a struct

It appears that sqlmock is attempting to call ConvertValue on the TVP object, which is invalid. So, how do I make sqlmock handle this value correctly so I can unit test around the query?

Answer 1

What I discovered here is that sqlmock has a function called ValueConverterOption, accepts an implementation of the driver.ValueConverter interface. This will be used in place of the standard function for every invocation of ConvertValue. If you want to test around the ExecContext function when it receives a non-standard argument, a TVP in this case, then you can use this function to inject custom conversion logic into sqlmock.

type mockTvpConverter struct {}

func (converter *mockTvpConverter) ConvertValue(raw interface{}) (driver.Value, error) {

    // Since this function will take the place of every call of ConvertValue, we will inevitably
    // the fake string we return from this function so we need to check whether we've recieved
    // that or a TVP. More extensive logic may be required
    switch inner := raw.(type) {
    case string:
        return raw.(string), nil
    case mssql.TVP:

        // First, verify the type name
        Expect(inner.TypeName).Should(Equal("MyTypeName"))

        // VERIFICATION LOGIC HERE

        // Finally, return a fake value that we can use when verifying the arguments
        return "PASSED", nil
    }

    // We had an invalid type; return an error
    return nil, fmt.Errorf("Invalid type")
}

which means, the test then becomes:

Describe("SQL Tests", func() {

    It("AddConsumption - No failures - Added", func() {

        db, mock, _ := sqlmock.New(sqlmock.ValueConverterOption(&mockTvpConverter{}))
        requester := Requester{conn: db}
        defer db.Close()

        mock.ExpectBegin()
        mock.ExpectExec(regexp.QuoteMeta("EXEC [dbo].[usp_InsertConsumption] @TVP")).
            WithArgs("PASSED").WillReturnResult(sqlmock.NewResult(1, 1))
        mock.ExpectExec(regexp.QuoteMeta("EXEC [dbo].[usp_InsertTags] @TVP")).
            WithArgs("PASSED").WillReturnResult(sqlmock.NewResult(1, 1))
        mock.ExpectCommit()

        err := requester.doQuery(context.TODO(), generateData())
        Expect(err).ShouldNot(HaveOccurred())
        Expect(mock.ExpectationsWereMet()).ShouldNot(HaveOccurred())
    })
})