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.

[msherman]

Below is an approach I took to mocking out the pgx layer and gaining control of the methods in case others come to this and are thinking of ways to mock out pgx.

Main package

package main

import (
	"context"
	"fmt"
	"github.com/jackc/pgx/v4"
	"github.com/jackc/pgx/v4/pgxpool"
	"log"
	"os"
)

// This interface is created to allow us to override it in the test.
type dbOperations interface {
	QueryRow(ctx context.Context, sql string, args ...interface{}) pgx.Row
}

var conn dbOperations = nil

type Credentials struct {
	Username string
	Password string
}

var findUserByUsernameDBQuery = "select username, password from schema.users where username = '%s'"

func FindUserByUsername(username string) *Credentials {
	connectToDB()
	creds := Credentials{}
	userSearchString := fmt.Sprintf(findUserByUsernameDBQuery, username)
	err := conn.QueryRow(context.Background(), userSearchString).Scan(&creds.Username, &creds.Password)
	if err != nil {
		log.Println(err.Error())
	}
	return &creds
}

// Implemented a singleton for the connection which comes in  handy within the test
// In the test we will set the global var conn to a mocked connection and then when the
// FindUserByUsername() function calls connectToDB() it will return our mocked connection
func connectToDB() {
	if conn != nil {
		return
	}
	dbConn, dbErr := pgxpool.Connect(context.Background(), "conn_string_here")
	if dbErr != nil {
		log.Println("Failed to connect to DB")
		os.Exit(1)
	}
        //dbConn which is of type *pgxpool.Pool can be assigned to type dbOperations because at a minimum
       //it implements the one method we have inside our dbOperations interface.
	conn = dbConn
	return
}

Testing package

package main

import (
	"context"
	"github.com/jackc/pgx/v4"
	"testing"
)

type mockDBConnection struct {
}

type mockRow struct {
	username string
	password string
}

func (this mockRow) Scan(dest ...interface{}) error {
	username := dest[0].(*string)
	password := dest[1].(*string)

	*username = this.username
	*password = this.password
	return nil
}

func (this mockDBConnection) QueryRow(ctx context.Context, sql string, args ...interface{}) pgx.Row {
	return mockRow{"user1", "pass"}
}

func TestFindUserByUsernameReturnsUser(t *testing.T) {
        // This assigns the conn global in the Main package to our mockedDBConnection allowing us to have
        // full control over its functionality. When connectToDB() gets called our mockDBConnection is returned
	conn = mockDBConnection{}
	creds := FindUserByUsername("test1")

	if creds.Username != "user1" && creds.Password != "pass" {
		t.Errorf("Received wrong user: %s and pass: %s", creds.Username, creds.Password)
	}
}

A couple thoughts:

1. Ideally repository layer code should be as dumb as a possible and only handling working with the database. Any logic within a repository should be elevated to a higher level package like a service which would maintain the business logic code.
2. As additional packages are added the connectToDB() function should be elevated to a utils package and called from each repository package to meet the DRY principle.
3. There is definitely a way to create a generic mocking service that would be reusable inside the code. I haven't made it to having a need for it so I haven't thought that part through yet.

Two edits.

1. Finished my thought on 1.
2. Updated to actually using the mockRow

[msherman]

A follow up to my example as I literally ran in to an issue with the above not being flexible enough. Here is a bit more robust variation which allows each individual test to set the behavior of QueryRow and Scan. For devs coming from a java/mockito world this starts to match this behavior with the when/then being defined within a function.

The below allows each test to define the functionality for the functions QueryRow and Scan within itself.

package auth

import (
	"bytes"
	"context"
	"errors"
	"github.com/jackc/pgconn"
	"github.com/jackc/pgx/v4"
	"log"
	"strings"
	"testing"
)
// each mockDBConnection will implement its own queryRow
type MockQueryRow func(ctx context.Context, sql string, args ...interface{}) pgx.Row
type mockDBConnection struct {
	mockQueryRow MockQueryRow
}

type Scan func(dest ...interface{}) error
type mockRow struct {
	scan Scan
	username string
	password string
}

func (this mockRow) Scan(dest ...interface{}) error {
	return this.scan(dest...)
}

func (this mockDBConnection) QueryRow(ctx context.Context, sql string, args ...interface{}) pgx.Row {
	return this.mockQueryRow(ctx, sql, args...)
}

func (this mockDBConnection) Begin(ctx context.Context) (pgx.Tx, error) {
	return transaction{}, nil
}

func TestFindUserByUsernameReturnsUser(t *testing.T) {
	mockScanReturn := func(dest ...interface{}) error {
		username := dest[0].(*string)
		password := dest[1].(*string)

		*username = "user1"
		*password = "pass"
		return nil
	}
	mockQueryRowReturn := func(ctx context.Context, sql string, args ...interface{}) pgx.Row {
		return mockRow{scan: mockScanReturn}
	}
	conn = mockDBConnection{mockQueryRow: mockQueryRowReturn}

	creds := FindUserByUsername("test1")

	if creds.Username != "user1" && creds.Password != "pass" {
		t.Errorf("Received wrong user: %s and pass: %s", creds.Username, creds.Password)
	}
}

func TestFindUserByUsernameLogsOnError(t *testing.T) {
	mockScanReturn := func(dest ...interface{}) error {
		return errors.New("empty user returned")
	}
	mockQueryRowReturn := func(ctx context.Context, sql string, args ...interface{}) pgx.Row {
		return mockRow{scan: mockScanReturn}
	}
	conn = mockDBConnection{mockQueryRow: mockQueryRowReturn}
	var logOutput bytes.Buffer
	log.SetOutput(&logOutput)

	_ = FindUserByUsername("t")

	if !strings.Contains(logOutput.String(), "empty user returned") {
		t.Errorf("Did not have error %s", logOutput.String())
	}
}

[pashagolub]

You can try my work on this: https://github.com/pashagolub/pgxmock

[maxzaleski]

You can try my work on this: https://github.com/pashagolub/pgxmock

@pashagolub Thank you very much. Works as intended!

[Shiv-Singh33]

I know i am pretty late to this thread, however hoping to get some help since thread is still open :)
So I followed the steps mentioned by @jackc for implementing the interface and mocking them to suit my needs which worked very well.
However i got stuck at a use case where i am beginning the transaction, executing a query and committing the transaction once query has been executed.

So, seq of steps :

1. tx, err := dbClient.Begin(context)
2. dbClient.Exec(context, "some query")
3. err := tx.Commit(context)

The problem i am facing now is i can mock the response of dbClient (this is the interface name where i have defined methods), however for tx.Commit() i am unable to.
Would really appreciate some guidance here on how can i go about addressing this (mocking tx) ,also if there is an alternate approach i am open to that too.

Note : i did noticed in the code that Tx has interfaces defined which i can leverage (i think) but trying to avoid it since doing that way exposes my code to possible failures if the interface contract changes.

Also 1 month old to goLang so i maybe missing to provide some useful info, please do let me know if anything else is needed from my end.
Thank you in advance!!

[Shiv-Singh33]

Update : i was able to mock the Tx.Commit() but the downside is that i had to mock all the methods defined in Tx interface to use the mock commit method.
As per understanding of golang (very minimal understanding so far) interfaces are implicit in nature i.e as a consumer i should be ideally defining the interface and provider should not be defining the interfaces (i.e pgx in this case)
Am i missing something here?

[maxzaleski]

@Shiv-Singh33 Have you tried @pashagolub's https://github.com/pashagolub/pgxmock?

[Shiv-Singh33]

@maxzaleski thanks for pointing me to the library, yes i did have a look at it, and it looks pretty cool and awesome!!
however to learn the testing and mocking better i decided to do it manually. 😅

[maxzaleski]

@maxzaleski thanks for pointing me to the library, yes i did have a look at it, and it looks pretty cool and awesome!!
however to learn the testing and mocking better i decided to do it manually. 😅

"...i decided to do it manually" Famous last words, haha.

Anyway, hope you find what you are looking for :)

Best of luck!

[denghejun]

You guys also can take look at this repo, to mock pgxpools, it works for me: https://github.com/driftprogramming/pgxpoolmock

func TestName(t *testing.T) {
	t.Parallel()
	ctrl := gomock.NewController(t)
	defer ctrl.Finish()

	// given
	mockPool := pgxpoolmock.NewMockPgxPool(ctrl)
	columns := []string{"id", "price"}
	pgxRows := pgxpoolmock.NewRows(columns).AddRow(100, 100000.9).ToPgxRows()
	mockPool.EXPECT().Query(gomock.Any(), gomock.Any(), gomock.Any()).Return(pgxRows, nil)
	orderDao := testdata.OrderDAO{
		Pool: mockPool,
	}

	// when
	actualOrder := orderDao.GetOrderByID(1)

	// then
	assert.NotNil(t, actualOrder)
	assert.Equal(t, 100, actualOrder.ID)
	assert.Equal(t, 100000.9, actualOrder.Price)
}

[sasakiyori]

I write down some examples with different mock levels according to the discussions above, hope it helps😄
https://github.com/sasakiyori/pgxmock-examples