Pgx: Local Testing: How to mock a connection

[Nokel81]

I have an application that I am trying to test where I am using a pgx connection. I would like to be able to inject a mock connection into my code where I can then expect/return expectations for that connection.

I tried to use https://github.com/DATA-DOG/go-sqlmock but pgx rejects it. I have looked at pgmock but that doesn't seem to do this at all.

Any ideas?

[jackc]

You can use pgmock to create a mock server that pgx can connect to. It's fairly low level. It basically requires specifying what you want to happen at the wire protocol level. A higher level layer could be built on top of it...

If your looking for more of a fake *pgx.Conn that doesn't have an underlying connection then that doesn't exist out of the box. But database/sql and pgx v3, the pgx v4 Rows type is an interface instead of a pointer. This was done specifically to allow for mocking the entire database connection. The way to do that would be for your application to work with interfaces instead of an actual *pgx.Conn. Then you could pass in objects that responded to Query and Exec however you wanted.

So to summarize, the low level plumbing is there to do this at the wire level or at the connection handle level, but there are no high level mocking tools in pgx.

[Nokel81]

Okay thanks for the response. However I don't quite see how the Rows interface can help with mocking the whole connection since it is only returned by conn.Query and similar so there doesn't seem to be a way to have them returned when possessing a dummy connection.

[jackc]

How it helps is that you can make an entire mock connection. If Rows was a struct with private members you couldn't mock Query because you would have no way to build the Rows it returns.

Here's a bigger picture explanation. Your code that is using a DB connection probably shouldn't be using a reference to a Conn or a Pool.

Instead define your own interface with the subset of methods you use. This is what I have in one of my projects:

type dbconn interface {
    Begin(ctx context.Context) (pgx.Tx, error)
    Exec(ctx context.Context, sql string, arguments ...interface{}) (pgconn.CommandTag, error)
    Query(ctx context.Context, sql string, optionsAndArgs ...interface{}) (pgx.Rows, error)
    QueryRow(ctx context.Context, sql string, optionsAndArgs ...interface{}) pgx.Row
}

Then I have a method that uses the database. e.g.

func CreateBook(ctx context.Context, db dbconn, book Book) (*Book, error) {
// ...
}

If I wanted to test CreateBook with a mock database during testing I could create a mock object that implemented the dbconn interface. It's possible to implement that dbconn interface because you can implement a mock Rows because it is an interface.

You'll have to build those mock implementations of dbconn and Rows -- but it is possible.

As another benefit to implementing your application in terms of something like the dbconn interface -- your code now will work whether a pool, a conn, a Tx, or a test mock is passed to it.

[lpar]

This would be a really useful thing to add to the documentation.

[audrenbdb]

Thanks Jack for all your work. Would you have a small example on how to create the mock objet you are talking about ? I understand the interface part but don't really know where to go next.

[jackc]

A goal for v4 was to make mocking the database connection _possible_ for those who need / want the ability, but to be honest I've never found it useful (in application level code). So unfortunately, I do not have an example.

Mocking the Query method wouldn't be difficult, but building a mock implementation of the Rows interface would be more challenging.