How to bootstrap mock db with sqlx

[zoop-btc]

Since I'm using sqlx in my application I'd need to mock my db through it. But I am having issues setting it up properly.
I'm new to testing so I must be doing something completely wrong. Here is what I wrote but can't figure out where the error is.
It results in db_test.go:43: Testing the test DB Failed.failed to connect to 'host=/tmp user=root database=': dial error (dial unix /tmp/.s.PGSQL.5432: connect: no such file or directory)

import (
	"testing"

	"github.com/go-sqlx/sqlx"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/stdlib"
	"github.com/pashagolub/pgxmock/v3"
)

func CreateTestDB(t *testing.T) (db *sqlx.DB, err error) {

	mock, err := pgxmock.NewConn()
	if err != nil {
		t.Fatal("Unable to initialize mock db")
	}

	conf, conferr := pgx.ParseConfig(mock.Config().ConnString())
	if conferr != nil {
		t.Fatalf("Unable to create config for sqlx connection: " + conferr.Error())
	}
	
	pgxdb := stdlib.OpenDB(*conf)
	db = sqlx.NewDb(pgxdb, "pgx")

	if err !=nil{
		t.Fatalf("Unable to create sqlx db: " + err.Error())
	}

	_, err = db.Exec("SELECT true")
	if err != nil {
		return db, err
	}

	return db, err
}

func TestDB(t *testing.T) {

	_, err := CreateTestDB(t)
	if err != nil {
		t.Errorf("Testing the test DB Failed." + err.Error())
	}

}

Any help is appreciated,
Thank you

[pashagolub]

Since you are using sql.Db you should use sqlmock for testing.

Another way is to stop using sqlx and use built-in pgx functionality instead.

[zoop-btc]

What about this where I don't use sql.Db? It gives me the same error.

I don't think I can use sqlmock because I have postgres specific queries.

import (
	"testing"

	"github.com/go-sqlx/sqlx"
	 _ "github.com/jackc/pgx/v5/stdlib"
	"github.com/pashagolub/pgxmock/v3"
)

func CreateTestDB(t *testing.T) (db *sqlx.DB, err error) {

	mock, err := pgxmock.NewConn()
	if err != nil {
		t.Fatal("Unable to initialize mock db")
	}

	db, err = sqlx.Connect("pgx",mock.Config().ConnString())

	if err !=nil{
		t.Fatalf("Unable to create sqlx db: " + err.Error())
	}

	_, err = db.Exec("SELECT true")
	if err != nil {
		return db, err
	}

	return db, err
}

func TestDB(t *testing.T) {

	_, err := CreateTestDB(t)
	if err != nil {
		t.Errorf("Testing the test DB Failed." + err.Error())
	}

}

[pashagolub]

sqlx uses sql.Db. That is how it works. To mock sql.Db use sqlmock.

sqlx is a library which provides a set of extensions on go's standard database/sql library. The sqlx versions of sql.DB, sql.TX, sql.Stmt, et al. all leave the underlying interfaces untouched, so that their interfaces are a superset on the standard ones. This makes it relatively painless to integrate existing codebases using database/sql with sqlx.

To use pgxmock you can switch to pgx. stdlib is not a pgx.

[zoop-btc]

Thank you for this explanation, I was confused about how pgx and sqlx worked together.
I'll investigate how much effort it would be to switch to pgx without sqlx.

[pashagolub]

And, of course, you can use sqlmock! It's database agnostic. Maybe you have unclear picture of how mocking works? There is no real server. You, as a developer, handle what and how your program see with a mock. So you control the mock