How to mock a pg.DB in tests?

[barbu110]

Hi!

I am relatively new to Go but I took a deep dive already. I cannot find any reasonable way of writing tests for functions that use a pg.DB in any way.

Could you please give some advice or is there something missing here?

[vmihailenco]

I am using real PostgreSQL database for tests. Postgres is configured for max performance and each table is truncated before each test. It is reasonably fast and satisfies my needs.

[barbu110]

@vmihailenco always configuring a huge database is not a good thing for our build times.

[mark-adams]

I've had good luck using transactions during tests to quickly bring my DB back to a clean state. My setup works something like this:

1. Drop (if it exists) and Initialize the test DB at the start of the test suite
2. For each test, create a transaction using db.Begin() and pass the resulting *pg.Tx into my application code (which expects an orm.DB which both pg.Tx and pg.DB implement).
3. Run the test
4. At the end of the rest, call pg.Tx.Rollback() to rollback the transaction and return the DB to a clean state.

This tends to make test runs quite fast especially if you have a lot of extra setup (populating tables, etc.) to do prior to running the tests.

[barbu110]

@mark-adams this seems like a good approach! it's worth looking into! thanks.

I will close this for the moment.

[Fyb3roptik]

@mark-adams Can we get an example? I am new to this kind of unit testing and am trying to get this implemented

[Fyb3roptik]

@vmihailenco sure would be nice if we could use Polluter or TestFixtures. Both of those expect sql.DB as the driver. Can we please put this on a roadmap? Unit tests are super important and I dont want to have to spin up a REAL database to do them!

[barbu110]

I have reopened the issue so Polluter or TestFixtures can more easily draw people’a attention. Hope to see such integration soon!

[Fyb3roptik]

The main issue is it is not using the sql.DB driver and instead uses one built by go-pg. Which I am certain makes it faster, but does not allow for it to run any tests. My suggestion here is to either integrate with https://github.com/DATA-DOG/go-txdb so we can use that for our mock db or integrate with Polluter/TestFixtures

[hixichen]

@Fyb3roptik potentially, another potential way is to use "https://godoc.org/gopkg.in/ory-am/dockertest.v3" to launch a postgresql container then load the schema.

Note that the main purpose here should be unit test or local test. Since currently either linux/macos or jenkins server, docker is everywhere. We decide to use go-pg with postgresql container for unit test.

[hrbustor]

I've had good luck using transactions during tests to quickly bring my DB back to a clean state. My setup works something like this:

1. Drop (if it exists) and Initialize the test DB at the start of the test suite
2. For each test, create a transaction using db.Begin() and pass the resulting *pg.Tx into my application code (which expects an orm.DB which both pg.Tx and pg.DB implement).
3. Run the test
4. At the end of the rest, call pg.Tx.Rollback() to rollback the transaction and return the DB to a clean state.

This tends to make test runs quite fast especially if you have a lot of extra setup (populating tables, etc.) to do prior to running the tests.

It's a really good usage that likes java spring TX

[Terminator637]

@Fyb3roptik potentially, another potential way is to use "https://godoc.org/gopkg.in/ory-am/dockertest.v3" to launch a postgresql container then load the schema.

Note that the main purpose here should be unit test or local test. Since currently either linux/macos or jenkins server, docker is everywhere. We decide to use go-pg with postgresql container for unit test.

Yes, dockertest is a really Great choice! A container starts within a couple of seconds and ready to work, then you can run any tests with a real DB with an acceptable speed.

[jh125486]

I think there still exists a few problems with using a real DB for unit testing:

1. Injection of errors at each step in a call flow
2. Concurrent tests/subtests

For integration testing, having a real DB is still the best option I believe.

[pehowell]

Agree, and there shouldn't be a need for a real database for unit tests. For example, you can create a minimal interface that go-pg's DB struct implements, then use that in your code instead. Then, for unit tests, use a test DB struct that implements the same interface to test different db responses.

[dionb]

I have a pressing need for this, so I would like to try to put together a PR for exposing a connect method that you can pass an *sql.DB into. Does that sound feasible? This way we can use something like data-dog/go-sqlmock

[yusufsyaifudin]

I also hope go-pg can be tested using go-sqlmock but it only can be used if we use native database/sql from Go. Since recently I am learned that unit test should not contain any external dependency such as real database connection, so, I create a project https://github.com/yusufsyaifudin/gopg-mock to give some proof of concept whether this go-pg package can be really tested in a unit test or not.

[Fyb3roptik]

No, you have to setup an actual test database as go-pg does not use database/sql.

[Kern046]

Hi there, I think being able to mock the database is necessary to perform real unit testing and also for continuous integration scripts, where we can't always afford to create a real database.

In my case, I managed to create a mock of the DB implementing the DB struct interface in a custom DB struct, which allows me to unit test functions performing insert, update et delete operations, but I'm unable to do so for select operations cause there aren't any interface for Query struct, therefore I can't mock it.

I think a possible solution is to create an interface for Query, however I don't know how painful would it be to implement in the library. Anyway, thanks for all your work on it :) !

[yusufsyaifudin]

Lately, I change my unit test using https://godoc.org/github.com/stretchr/testify/mock So, instead of using *pg.DB I always and recommend to use orm.DB when passing it as an argument to other function. So, in our test file, we can create struct that implements orm.DB interface and mock it using testify/mock package.

[calvinchengx]

Lately, I change my unit test using https://godoc.org/github.com/stretchr/testify/mock So, instead of using *pg.DB I always and recommend to use orm.DB when passing it as an argument to other function. So, in our test file, we can create struct that implements orm.DB interface and mock it using testify/mock package.

So this works fine for the methods that return error or (orm.Result, error).
For methods that return *orm.Query, we are unable to mock them any further unless go-pg implements an interface that *orm.Query struct implements.

Specifically,

• func (p *goPgDB) Model(model ...interface{}) *orm.Query
• func (p *goPgDB) ModelContext(c context.Context, model ...interface{}) *orm.Query

returns *orm.Query.

orm.Query itself is simply a struct and so there's nothing we can do further when attempting to mock these two methods.

Will it be possible for go-pg to add in an interface like that:-

// QueryI ...
type QueryI interface {
	// New returns new zero Query binded to the current db.
	New() *QueryI
	// Clone clones the Query.
	Clone() *QueryI
	Context(c context.Context) *QueryI
	DB(db *pg.DB) *QueryI
	Model(model ...interface{}) *QueryI
	TableModel() orm.TableModel
	// Deleted adds `WHERE deleted_at IS NOT NULL` clause for soft deleted models.
	Deleted() *QueryI
	// AllWithDeleted changes query to return all rows including soft deleted ones.
	AllWithDeleted() *QueryI
	// With adds subq as common table expression with the given name.
	With(name string, subq *QueryI) *QueryI
	WithInsert(name string, subq *QueryI) *QueryI
	WithUpdate(name string, subq *QueryI) *QueryI
	WithDelete(name string, subq *QueryI) *QueryI
	// WrapWith creates new Query and adds to it current query as
	// common table expression with the given name.
	WrapWith(name string) *QueryI
	Table(tables ...string) *QueryI
	TableExpr(expr string, params ...interface{}) *QueryI
	Distinct() *QueryI
	DistinctOn(expr string, params ...interface{}) *QueryI
	// Column adds a column to the Query quoting it according to PostgreSQL rules.
	// Does not expand params like ?TableAlias etc.
	// ColumnExpr can be used to bypass quoting restriction or for params expansion.
	// Column name can be:
	//   - column_name,
	//   - table_alias.column_name,
	//   - table_alias.*.
	Column(columns ...string) *QueryI
	// ColumnExpr adds column expression to the Query.
	ColumnExpr(expr string, params ...interface{}) *QueryI
	// ExcludeColumn excludes a column from the list of to be selected columns.
	ExcludeColumn(columns ...string) *QueryI
	// Relation adds a relation to the query. Relation name can be:
	//   - RelationName to select all columns,
	//   - RelationName.column_name,
	//   - RelationName._ to join relation without selecting relation columns.
	Relation(name string, apply ...func(*QueryI) (*QueryI, error)) *QueryI
	Set(set string, params ...interface{}) *QueryI
	// Value overwrites model value for the column in INSERT and UPDATE queries.
	Value(column string, value string, params ...interface{}) *QueryI
	Where(condition string, params ...interface{}) *QueryI
	WhereOr(condition string, params ...interface{}) *QueryI
	// WhereGroup encloses conditions added in the function in parentheses.
	//
	//    q.Where("TRUE").
	//    	WhereGroup(func(q *QueryI) (*QueryI, error) {
	//    		q = q.WhereOr("FALSE").WhereOr("TRUE").
	//    		return q, nil
	//    	})
	//
	// generates
	//
	//    WHERE TRUE AND (FALSE OR TRUE)
	WhereGroup(fn func(*QueryI) (*QueryI, error)) *QueryI
	// WhereGroup encloses conditions added in the function in parentheses.
	//
	//    q.Where("TRUE").
	//    	WhereNotGroup(func(q *QueryI) (*QueryI, error) {
	//    		q = q.WhereOr("FALSE").WhereOr("TRUE").
	//    		return q, nil
	//    	})
	//
	// generates
	//
	//    WHERE TRUE AND NOT (FALSE OR TRUE)
	WhereNotGroup(fn func(*QueryI) (*QueryI, error)) *QueryI
	// WhereOrGroup encloses conditions added in the function in parentheses.
	//
	//    q.Where("TRUE").
	//    	WhereOrGroup(func(q *QueryI) (*QueryI, error) {
	//    		q = q.Where("FALSE").Where("TRUE").
	//    		return q, nil
	//    	})
	//
	// generates
	//
	//    WHERE TRUE OR (FALSE AND TRUE)
	WhereOrGroup(fn func(*QueryI) (*QueryI, error)) *QueryI
	// WhereOrGroup encloses conditions added in the function in parentheses.
	//
	//    q.Where("TRUE").
	//    	WhereOrGroup(func(q *QueryI) (*QueryI, error) {
	//    		q = q.Where("FALSE").Where("TRUE").
	//    		return q, nil
	//    	})
	//
	// generates
	//
	//    WHERE TRUE OR NOT (FALSE AND TRUE)
	WhereOrNotGroup(fn func(*QueryI) (*QueryI, error)) *QueryI
	// WhereIn is a shortcut for Where and pg.In:
	WhereIn(where string, slice interface{}) *QueryI
	// WhereInMulti is a shortcut for Where and pg.InMulti:
	WhereInMulti(where string, values ...interface{}) *QueryI
	// WherePK adds condition based on the model primary keys.
	// Usually it is the same as:
	//
	//    Where("id = ?id")
	WherePK() *QueryI
	// WhereStruct generates conditions for the struct fields with non-zero values:
	//    - Foo int - Where("foo = ?", strct.Foo)
	//    - Foo []int - Where("foo = ANY(?)", pg.Array(strct.Foo))
	//    - FooNEQ int - Where("foo != ?", strct.Foo)
	//    - FooExclude int - Where("foo != ?", strct.Foo)
	//    - FooGT int - Where("foo > ?", strct.Foo)
	//    - FooGTE int - Where("foo >= ?", strct.Foo)
	//    - FooLT int - Where("foo < ?", strct.Foo)
	//    - FooLTE int - Where("foo <= ?", strct.Foo)
	//
	// urlstruct.Decode can be used to decode url.Values into the struct.
	//
	// Following field tags are recognized:
	//    - pg:"-" - field is ignored.
	//    - pg:",nowhere" - field is decoded but is ignored by WhereStruct.
	//    - pg:",nodecode" - field is not decoded but is used by WhereStruct.
	//    - pg:",required" - condition is added for zero values as well.
	WhereStruct(strct interface{}) *QueryI
	Join(join string, params ...interface{}) *QueryI
	// JoinOn appends join condition to the last join.
	JoinOn(condition string, params ...interface{}) *QueryI
	JoinOnOr(condition string, params ...interface{}) *QueryI
	Group(columns ...string) *QueryI
	GroupExpr(group string, params ...interface{}) *QueryI
	Having(having string, params ...interface{}) *QueryI
	Union(other *QueryI) *QueryI
	UnionAll(other *QueryI) *QueryI
	Intersect(other *QueryI) *QueryI
	IntersectAll(other *QueryI) *QueryI
	Except(other *QueryI) *QueryI
	ExceptAll(other *QueryI) *QueryI
	// Order adds sort order to the Query quoting column name. Does not expand params like ?TableAlias etc.
	// OrderExpr can be used to bypass quoting restriction or for params expansion.
	Order(orders ...string) *QueryI
	// Order adds sort order to the Query.
	OrderExpr(order string, params ...interface{}) *QueryI
	Limit(n int) *QueryI
	Offset(n int) *QueryI
	OnConflict(s string, params ...interface{}) *QueryI
	// Returning adds a RETURNING clause to the query.
	//
	// `Returning("NULL")` can be used to suppress default returning clause
	// generated by go-pg for INSERT queries to get values for null columns.
	Returning(s string, params ...interface{}) *QueryI
	For(s string, params ...interface{}) *QueryI
	// Apply calls the fn passing the Query as an argument.
	Apply(fn func(*QueryI) (*QueryI, error)) *QueryI
	// Count returns number of rows matching the query using count aggregate function.
	Count() (int, error)
	// First sorts rows by primary key and selects the first row.
	// It is a shortcut for:
	//
	//    q.OrderExpr("id ASC").Limit(1)
	First() error
	// Last sorts rows by primary key and selects the last row.
	// It is a shortcut for:
	//
	//    q.OrderExpr("id DESC").Limit(1)
	Last() error
	// Select selects the model.
	Select(values ...interface{}) error
	// SelectAndCount runs Select and Count in two goroutines,
	// waits for them to finish and returns the result. If query limit is -1
	// it does not select any data and only counts the results.
	SelectAndCount(values ...interface{}) (count int, firstErr error)
	// SelectAndCountEstimate runs Select and CountEstimate in two goroutines,
	// waits for them to finish and returns the result. If query limit is -1
	// it does not select any data and only counts the results.
	SelectAndCountEstimate(threshold int, values ...interface{}) (count int, firstErr error)
	// ForEach calls the function for each row returned by the query
	// without loading all rows into the memory.
	//
	// Function can accept a struct, a pointer to a struct, an orm.Model,
	// or values for the columns in a row. Function must return an error.
	ForEach(fn interface{}) error
	// Insert inserts the model.
	Insert(values ...interface{}) (orm.Result, error)
	// SelectOrInsert selects the model inserting one if it does not exist.
	// It returns true when model was inserted.
	SelectOrInsert(values ...interface{}) (inserted bool, _ error)
	// Update updates the model.
	Update(scan ...interface{}) (orm.Result, error)
	// Update updates the model omitting fields with zero values such as:
	//   - empty string,
	//   - 0,
	//   - zero time,
	//   - empty map or slice,
	//   - byte array with all zeroes,
	//   - nil ptr,
	//   - types with method `IsZero() == true`.
	UpdateNotZero(scan ...interface{}) (orm.Result, error)
	// Delete deletes the model. When model has deleted_at column the row
	// is soft deleted instead.
	Delete(values ...interface{}) (orm.Result, error)
	// Delete forces delete of the model with deleted_at column.
	ForceDelete(values ...interface{}) (orm.Result, error)
	CreateTable(opt *orm.CreateTableOptions) error
	DropTable(opt *orm.DropTableOptions) error
	// Exec is an alias for DB.Exec.
	Exec(query interface{}, params ...interface{}) (orm.Result, error)
	// ExecOne is an alias for DB.ExecOne.
	ExecOne(query interface{}, params ...interface{}) (orm.Result, error)
	// Query is an alias for DB.Query.
	Query(model, query interface{}, params ...interface{}) (orm.Result, error)
	// QueryOne is an alias for DB.QueryOne.
	QueryOne(model, query interface{}, params ...interface{}) (orm.Result, error)
	// CopyFrom is an alias from DB.CopyFrom.
	CopyFrom(r io.Reader, query interface{}, params ...interface{}) (orm.Result, error)
	// CopyTo is an alias from DB.CopyTo.
	CopyTo(w io.Writer, query interface{}, params ...interface{}) (orm.Result, error)
	AppendQuery(fmter orm.QueryFormatter, b []byte) ([]byte, error)
	// Exists returns true or false depending if there are any rows matching the query.
	Exists() (bool, error)
}

[slax0rr]

I agree that it is not always possible to or desireable to spin up a new database just for testing, and interfaces should be used in order to provide a possibility of mocking the database instance. Since go-pg does not provide such interfaces, I've written a small wrapper around go-pg that operates with interfaces, due to my need in a project.

If you have a similar need, feel free to use it, https://gitlab.com/slax0rr/go-pg-wrapper

This is far from ideal of course.

[hazcod]

@yusufsyaifudin : do note that orm.DB does not implement everything used in handlers such as WithContext.

[jon-fearer]

Would the maintainers be open to a PR that adds an orm.Query interface similar to what @calvinchengx commented above? ~~Additionally, thinking that an interface for pg.DB might make sense?~~ If so, I can put something together

Edit: I now see that there is already an interface for pg.DB

[vmihailenco]

The plan is to get rid of orm subpackage in v11 so we can move orm.DB to pg.IDB or something and add missing methods.

I am not sure about pg.IQuery interface though. As I understand go-pg will have to use that interface everywhere instead of pg.IQuery? I am not a big fan of that.

Perhaps you have an example how pg.IQuery is supposed to be used and we could try to find an alternative solution?

[jon-fearer]

My use case for a QueryInterface is to be able to use mockgen to stub out methods like Select() easily for unit testing. My suggestion is to add a QueryInterface and modify the Model() and ModelContext() methods to return the interface instead of the struct. The Query struct would still be passed around between methods, to avoid using the interface everywhere. However, if you are deprecating orm then it might not make sense to put a QueryInterface there and I think modifying Model() and ModelContext() would be a breaking change. If you think the use case of having a QueryInterface makes sense (mostly to help with unit testing), then I'll certainly defer to you on what that should look like. Here's what I'm conceptualizing

[rustyx]

GoMock can stub out interfaces, not structs, so yes, all orm.Query APIs will need to return the interface.

An example use-case: given a query like

err := db.Model(&rows).
    Where("transact_time = ?", txTime).
    Where("source_type = ?", srcType).
    Select()

The corresponding mock can be something like ...

mock := gomock.NewController(t)
defer mock.Finish()
db := NewMockDB(mock)
db.EXPECT().Model(gomock.Any()).DoAndReturn(func(rows *[]MyTable) orm.IQuery {
	query := NewMockQuery(mock)
	query.EXPECT().Where(gomock.Any(), gomock.Any()).AnyTimes().Return(query)
	query.EXPECT().Select().DoAndReturn(func() error {
		*rows = []MyTable{{ /* mock data */ }}
		return nil
	})
	return query
})

[vmihailenco]

I've marked this for v11 but TBH this does not look promising. We have support for mocks in go-redis for a couple of months and number of users is relatively low (~1%).

Perhaps the answer here should be that you should abstract database access with another layer and mock that layer instead of mocking go-pg.

[mvdkleijn]

I've marked this for v11 but TBH this does not look promising. We have support for mocks in go-redis for a couple of months and number of users is relatively low (~1%).

Perhaps the answer here should be that you should abstract database access with another layer and mock that layer instead of mocking go-pg.

This reaction puzzles me to be honest. Anyone who's writing unit tests would want to mock their interactions with the DB. Having to be forced to write a wrapper around go-pg simply to make code using it (unit) testable seems strange. Testability of code using go-pg should be one of the core features of the library IMHO. Isn't one of the core but unnamed goals of any library to "make life easier" for its users? 😄

In case anyone wants to say: "testing against a real DB is better"... Yes it is, but only when you're doing integration testing, not unit testing IMHO. In other words, that's more important for a library like go-pg itself that is actually talking to the DB, rather than for someone using go-pg as part of their code.

[Emixam23]

Hey guys :)

So... How do we mock query? is there finally any solution? I am getting stuck on it and really need any workaround ASAP :( I could get a new approach and mock de orm.DB part but still... methods such as Model() does return *pg.Query that I can't find the way to mock...

Thanks for any feedbacks!

Max

[slayerjain]

It would be really cool to have support for database/sql to also make this more interoperable with more SQL drivers, apart from the big usecase of writing mocks for tests

[ekulabuhov]

Hey all!

I've experimented with adding a wrapper that converts method chaining to functional options, following this article: https://www.calhoun.io/using-functional-options-instead-of-method-chaining-in-go/

Resulting unit tests are much more readable than trying to add interfaces around orm.Query at the cost of rewriting your code a bit:

package gopgwrap

import (
	"github.com/go-pg/pg/v10/orm"
)

type OrmWrap struct {
	db orm.DB
}

func New(db orm.DB) *OrmWrap {
	return &OrmWrap{
		db,
	}
}

type OptionFn func(query *orm.Query) *orm.Query

func (w *OrmWrap) Exists(model interface{}, opts ...OptionFn) (bool, error) {
	query := w.db.Model(model)

	for _, opt := range opts {
		opt(query)
	}

	return query.Exists()
}

func (w *OrmWrap) Update(model interface{}, opts ...OptionFn) (orm.Result, error) {
	query := w.db.Model(model)

	for _, opt := range opts {
		opt(query)
	}

	return query.Update()
}

func (w *OrmWrap) Insert(model interface{}, opts ...OptionFn) (orm.Result, error) {
	query := w.db.Model(model)

	for _, opt := range opts {
		opt(query)
	}

	return query.Insert()
}

func (w *OrmWrap) Delete(model interface{}, opts ...OptionFn) (orm.Result, error) {
	query := w.db.Model(model)

	if len(opts) == 0 {
		query.WherePK()
	}

	for _, opt := range opts {
		opt(query)
	}

	return query.Delete()
}

func (w *OrmWrap) Select(model interface{}, opts ...OptionFn) error {
	if len(opts) == 0 {
		return w.db.Select(model)
	}

	query := w.db.Model(model)

	for _, opt := range opts {
		opt(query)
	}

	return query.Select()
}

func WherePK() OptionFn {
	return func(query *orm.Query) *orm.Query {
		return query.WherePK()
	}
}

func Where(condition string, params ...interface{}) OptionFn {
	return func(query *orm.Query) *orm.Query {
		return query.Where(condition, params...)
	}
}

func OnConflict(s string, params ...interface{}) OptionFn {
	return func(query *orm.Query) *orm.Query {
		return query.OnConflict(s, params...)
	}
}

func Returning(s string, params ...interface{}) OptionFn {
	return func(query *orm.Query) *orm.Query {
		return query.Returning(s, params...)
	}
}

// repository.go
...
// replace function chaining
err := tx.Model(&customer).Where("name = ?", name).Select()
// with optional parameters
err := tx.Select(&customer, gopgwrap.Where("name = ?", name));

// repository_test.go
...
mockOrmWrap.EXPECT().Select(&customer, gomock.Any())
...