Feature Request: Add param values struct Matcher for use with sqlc

[StevenACoffman]

We use pgx/v5 with sqlc and pgxmock, and we would like to contribute some convenience functions (written by my co-worker @coady) for this combination (without adding any new dependencies in pgxmock).

sqlc will generate structs like the Params object that we can use with pgxmock.

Background

Given a SQL query like:

-- name: CreateAuthor :one
INSERT INTO authors (
          name, bio
) VALUES (
  $1, $2
)
RETURNING id, name, bio

sqlc will generate a structs like Params and others that we can use with pgxmock.

We can then add a structmock.go:

package structmock

// Database mock utilities.

import (
	"reflect"
	"runtime"
	"strings"

	"github.com/pashagolub/pgxmock/v4"
)

func typeOf(arg any) reflect.Type {
	t := reflect.TypeOf(arg)
	if t.Kind() == reflect.Ptr {
		t = t.Elem()
	}
	return t
}

func validStruct(arg any) bool {
	t := typeOf(arg)
	return t.Kind() == reflect.Struct &&
		(strings.HasSuffix(t.Name(), "Params") || strings.HasSuffix(t.Name(), "Row"))
}

func structNames(arg any) []string {
	if !validStruct(arg) {
		return []string{""}
	}
	fields := reflect.VisibleFields(typeOf(arg))
	names := make([]string, len(fields))
	for i, field := range fields {
		names[i] = field.Name
	}
	return names
}

func structValues(arg any) []any {
	if !validStruct(arg) {
		return []any{arg}
	}
	v := reflect.Indirect(reflect.ValueOf(arg))
	values := make([]any, v.NumField())
	for i := range values {
		values[i] = v.Field(i).Interface()
	}
	return values
}

func typedNil(arg any) bool {
	v := reflect.ValueOf(arg)
	return v.Kind() == reflect.Ptr && v.IsNil()
}

func structArgs(params any) []any {
	if params == nil { // untyped nil
		return []any{}
	}
	if typedNil(params) {
		return AnyArgs(len(structNames(params)))
	}
	return structValues(params)
}

// Match any fixed number of args; pass to `WithArgs`.
func AnyArgs(count int) []any {
	args := make([]any, count)
	for i := range args {
		args[i] = pgxmock.AnyArg()
	}
	return args
}

// Extends the `Expecter` interface to support `Params` and `Row` structs.
type StructMock struct{ pgxmock.PgxPoolIface }

// NewRows(...).AddRows(...) from `Row` structs (or pointers to)
//
// The first is also used to extract field names.
// A typed nil pointer will not add values, i.e, indicates zero rows.
func (sm *StructMock) StructRows(fields any, rows ...any) *pgxmock.Rows {
	mockRows := sm.NewRows(structNames(fields))
	if !typedNil(fields) {
		mockRows.AddRow(structValues(fields)...)
	}
	for i := range rows {
		mockRows.AddRow(structValues(rows[i])...)
	}
	return mockRows
}

// ExpectQuery(...).WithArgs(...) from a `Params` struct (or pointer to)
//
// The name and args are derived from the struct.
// A typed nil pointer will match any args.
func (sm *StructMock) ExpectQueryWith(params any) *pgxmock.ExpectedQuery {
	name := "^.* " + strings.TrimSuffix(typeOf(params).Name(), "Params") + " .*$"
	return sm.ExpectQuery(name).WithArgs(structArgs(params)...)
}

// ExpectExec(...).WithArgs(...) from a `Params` struct (or pointer to)
//
// The name and args are derived from the struct.
// A typed nil pointer will match any args.
func (sm *StructMock) ExpectExecWith(params any) *pgxmock.ExpectedExec {
	name := "^.* " + strings.TrimSuffix(typeOf(params).Name(), "Params") + " .*$"
	return sm.ExpectExec(name).WithArgs(structArgs(params)...)
}

// Mock expected Query or Exec with the most common options.
//
// f: the name and expected type are derived from the function
// params: if a struct its fields will be used; may be nil for no args
// rows: if structs their fields will be used
func (sm *StructMock) Expect(f any, params any, rows ...any) {
	names := strings.Split(runtime.FuncForPC(reflect.ValueOf(f).Pointer()).Name(), ".")
	name := "^.* " + strings.Split(names[len(names)-1], "-")[0] + " .*$"
	if typeOf(f).NumOut() <= 1 { // only returns `error` means not a query
		exec := sm.ExpectExec(name).WithArgs(structArgs(params)...)
		exec.WillReturnResult(pgxmock.NewResult("", 0))
	} else {
		query := sm.ExpectQuery(name).WithArgs(structArgs(params)...)
		if len(rows) > 0 {
			query.WillReturnRows(sm.StructRows(rows[0], rows[1:]...)).RowsWillBeClosed()
		}
	}
}

ExpectQuery

ExpectQuery allows us to modify our tests to go from:

	s.Mock.ExpectExec("^.* SetEndDateForKhanmigoRoster .*$").
		WithArgs(asOfDate, testDistrictArg, []string{"kaid_s2"}).
		WillReturnResult(pgxmock.NewResult("", 0))
	s.Mock.ExpectExec("^.* SetEndDateForKhanmigoRoster .*$").
		WithArgs(asOfDate, testDistrictArg, []string{"kaid_t4"}).
		WillReturnResult(pgxmock.NewResult("", 0))

to:

s.Mock.ExpectExecWith(sql.SetEndDateForKhanmigoRosterParams{
		EndDate:    asOfDate,
		DistrictID: testDistrictArg,
		Kaid:       []string{"kaid_s2"},
	}).WillReturnResult(pgxmock.NewResult("", 0))

ExpectExec

ExpectExec allows us to modify our tests to go from:

	s.Mock.ExpectExec("^.* SetEndDateForKhanmigoRoster .*$").
		WithArgs(asOfDate, testDistrictArg, []string{"kaid_s2"}).
		WillReturnResult(pgxmock.NewResult("", 0))
	s.Mock.ExpectExec("^.* SetEndDateForKhanmigoRoster .*$").
		WithArgs(asOfDate, testDistrictArg, []string{"kaid_t4"}).
		WillReturnResult(pgxmock.NewResult("", 0))

to this:

s.Mock.ExpectExecWith(sql.SetEndDateForKhanmigoRosterParams{
		EndDate:    asOfDate,
		DistrictID: testDistrictArg,
		Kaid:       []string{"kaid_s2"},
	}).WillReturnResult(pgxmock.NewResult("", 0))

Expect

Expect allows us to modify our tests to go from:

	s.Mock.ExpectQuery("^.* ClassroomsLastUpdated .*$").
		WillReturnRows(pgxmock.NewRows([]string{""}).AddRow(time.Time{}))
	s.Mock.ExpectExec("^.* UpsertClassroom .*$").
		WithArgs(
			sqldb.ToUUID(cdis[0].GetID()),
			sqldb.ToUUID(testDistrict1.GetID()),
			sqldb.ToUUID(school1KeyID),
			cdis[0].GetDescriptor(),
			sqldb.CastStrings(cdis[0].OfferingValues),
			sqldb.ToUUIDs(cdis[0].AllTeacherUDIsIDs),
			sqldb.CastStrings(cdis[0].StudentGrades),
			cdis[0].ModificationTimestamp,
		).

to this:

params := sql.UpsertClassroomParams{
		CdiID:           sqldb.ToUUID(cdis[0].GetID()),
		DistrictID:      sqldb.ToUUID(testDistrict1.GetID()),
		SchoolID:        sqldb.ToUUID(school1KeyID),
		ClassDescriptor: cdis[0].GetDescriptor(),
		OfferingValues:  sqldb.CastStrings(cdis[0].OfferingValues),
		TeacherIds:      sqldb.ToUUIDs(cdis[0].AllTeacherUDIsIDs),
		StudentGrades:   sqldb.CastStrings(cdis[0].StudentGrades),
		LastUpdated:     cdis[0].ModificationTimestamp,
	}
	s.Mock.Expect(s.Querier.UpsertClassroom, params)

[coady]

Or a subset of the above example. A minimal variant would be:

• a struct version of WithArgs
• a struct version of AddRows

[pashagolub]

Hello.

Why cannot you just implement QueryRewriter interface for Params struct and then use it as the argument?

Some examples are available here.

[StevenACoffman]

The current situation is that you have variadic args that are strongly typed but only fail in runtime, rather than at compile time. The variadic situation means that we get positional failures where code is working in production, but fail in the mock without good feedback as to what is wrong or how to correct it.

The same thing happens for the Rows as for the Params when they are mismatched. We get runtime failures that are hard to diagnose and correct.

We would like to pass structs for both WithArgs and AddRows and that will be both self-documenting and give compile-time insight into positional errors.

[pashagolub]

You can pass struct to WithArg. Struct should implement QueryRewriter interface as I said. Or I don't understand your issue.

[pashagolub]

Check this test code:

func TestQueryRewriter(t *testing.T) {
	t.Parallel()
	mock, err := NewConn(QueryMatcherOption(QueryMatcherEqual))
	a := assert.New(t)
	a.NoError(err)


	update := `UPDATE "user" SET email = @email, password = @password, updated_utc = @updated_utc WHERE id = @id`


	mock.ExpectExec(update).WithArgs(pgx.NamedArgs{
		"id":          "mockUser.ID",
		"email":       "mockUser.Email",
		"password":    "mockUser.Password",
		"updated_utc": AnyArg(),
	}).WillReturnError(errPanic)


	_, err = mock.Exec(context.Background(), update, pgx.NamedArgs{
		"id":          "mockUser.ID",
		"email":       "mockUser.Email",
		"password":    "mockUser.Password",
		"updated_utc": time.Now().UTC(),
	})
	a.Error(err)
	a.NoError(mock.ExpectationsWereMet())
}

[coady]

That example works because NamedArgs is also used as input to the actual query. However we use sqlc to generate a query interface. It provides methods which call Exec/Query with unpacked args. Maybe we could open an issue to that project to use QueryRewriter when generating for pgx/v5.

So, what about rows then? There is an identical issue where we have Row structs for the actual queries, but no way to use them with AddRow{s} mocks.

[pashagolub]

However we use sqlc to generate a query interface. It provides methods which call Exec/Query with unpacked args.

I thought you are absolutely free to modify sqlc template. This way you can implement QueryRewriter in the template, so every struct will have the method needed.

So, what about rows then?

Seems I missed that part completely. Would you please bring some example code? Thanks in advance!

[coady]

An example:

// generated code, used in production
type MyRow struct {
	Name  string
	Count int64
}

// in test
.AddRow("", int64(0))
// becomes
.AddStructRows(MyRow{Name: "", Count: 0}, ...)

Benefits:

• field order changes pass instead of fail
• type changes pass or don't compile, instead of failing at runtime
• field removals don't compile instead of failing at runtime (or pass if implicitly zero-valued)
• field additions pass if zero-valued (if not zero-valued they should fail)
• no need to cast literals (in my experience that's been tedious and less readable - int32 and int64 repeatedly)