Chapter about database/sql testing

[S7evinK]

I'm not quite sure if that's covered in other chapters, but I'm struggling with testing my database/sql stuff. The mocking and dependency injection chapters were useful, but I'm still lost.

Let's say, that I've got something like that:

type Connection struct {
	*sql.DB
}

func NewDatabaseConnection(host, database, user, password string) (*Connection, error) {
	u := &url.URL{
		Scheme: "sqlserver",
		User:   url.UserPassword(user, password),
		Host:   fmt.Sprintf("%s:%d", host, 1433),
	}

	db, err := sql.Open("mssql", u.String())
	if err != nil {
		log.Fatal("Error opening database connection:", err)
	}

	return &Connection{db}, nil
}

How, if it's even needed, should I test that? Or something like a GetUser function. A chapter about that would be awesome =) Maybe, since we've already got a "HTTP server" and "JSON" chapter, a little extension to those?

[quii]

It's certainly a good fit for a chapter and i'll add it to the list

FWIW I would not mock the DB layer in these scenarios, i'd write an integration test with a real local DB.

But I'd design my DB layer to be as "dumb" as I can, try not to encode any business logic in there.

[djangulo]

Hi! I've ran into issues regarding the DB state. At first, I mirrored the InMemoryPlayerStore (from the HTTP Server chapter) with an Sqlite implementation (through GORM), I resolved to delete the test_db.db file after each test.

Now I've moved it to Postgres and I'm dumbfounded. I've been running into DB state issues with those same integration tests, i.e. POST x3 -- want 3 got 6 (because it still has the state of the previous run).

Is there a way to save the DB state (which should be "fresh install"), run the tests, make assertions, then revert back to that point? (that is, without resorting to backup/restore scripts outside of the application).

[quii]

A few things

1. Awesome you're trying this out. I wish I had the time to do this.
2. I'd expect things like tables, indexes etc to be done programatically by the software that "owns the db". SO that is always reproducible from an empty DB. I like to exercise that with my tests too. This is usually referred to as "db migrations" which you can maybe read more about
3. I cant remember the specifics with postgres but im pretty sure you can do something like setting autocommit to false or manipulating transactions some way so the changes each test make are not permanent. Again requires a bit of googling

[djangulo]

OK so I ditched GORM and changed my logic to use "database/sql" with github.com/lib/pq.

I then proceeded to create a a wrapper around each integration test, creating a Postgres Savepoint "manually", to no avail: scores were still accumulating in the database, 3 at a time (for the 3 requests).

func savepointWrapper(
	t *testing.T,
	store *poker.PostgreSQLPlayerStore,
	name string,
	inner func(t *testing.T),
) {
	tx, err := store.DB.Begin()
	if err != nil {
		t.Fatalf("error: %v", err)
	}
	// Create savepoint
	_, err = tx.Exec(`SAVEPOINT test_savepoint;`)
	if err != nil {
		log.Printf("savepoint error: %v", err)
	}
	// Run test functions
	t.Run(name, inner)
	// Rollback
	_, err = tx.Exec(`ROLLBACK TO SAVEPOINT test_savepoint;`)
	if err != nil {
		log.Printf("rollback error: %v", err)
	}
	// Release savepoint
	_, err = tx.Exec(`RELEASE SAVEPOINT test_savepoint;`)
	if err != nil {
		log.Printf("release error: %v", err)
	}
	// Commit empty transaction
	tx.Rollback() // tx.Commit() had the same outcome
}

What managed to get the tests passing was a very inelegant (by my estimate) alternative New() function, which:

• Connects to the main DB
• Creates a test DB
• Plugs in said test DB for test
• Drops the test DB on the removeDatabase() method

func newTestPostgreSQLPlayerStore(
	host,
	port,
	user,
	pass string,
) (*poker.PostgreSQLPlayerStore, func()) {
	mainConnStr := fmt.Sprintf(
		"user=%s password=%s host=%s port=%s sslmode=disable",
		user, pass, host, port,
	)
	mainDB, err := sql.Open("postgres", mainConnStr)
	if err != nil {
		log.Fatalf("failed to connect database %v", err)
	}
	_, err = mainDB.Exec(`CREATE DATABASE test_database;`)
	if err != nil {
		log.Fatalf("failed to create test database %v", err)
	}

	testConnStr := fmt.Sprintf(
		"user=%s password=%s host=%s port=%s dbname=%s sslmode=disable",
		user,
		pass,
		host,
		port,
		"test_database",
	)
	testDB, errOpenTest := sql.Open("postgres", testConnStr)
	if errOpenTest != nil {
		log.Fatalf("failed to connect to test database %v", errOpenTest)
	}

	_, errCreateTable := testDB.Exec(`
	CREATE TABLE IF NOT EXISTS players (
		id		serial		PRIMARY KEY,
		name	varchar(80)	NOT NULL UNIQUE,
		wins	int			DEFAULT 0
	);
	`)
	if errCreateTable != nil {
		log.Fatalf("failed to create test DB table %v", errCreateTable)
	}

	removeDatabase := func() {
		testDB.Close()
		mainDB.Exec(`DROP DATABASE test_database;`)
		mainDB.Close()
	}

	return &poker.PostgreSQLPlayerStore{testDB}, removeDatabase
}

with this wrapper the tests pass (and behave as expected).

For future readers, all the code can be found in https://github.com/djangulo/go-fast/tree/sql.

Problems I see with this approach:

• Tests are slower, not by much (it's only 2 tests after all), but if this were a real application with many more tests, it would be aggravating.
• The PostgreSQL user passed into the application needs the CREATEDB privileges, test suite would fail without it.

My questions for you:

• How would you DRY this up?
• Any other method or approach you think would be as effective? (I'm willing to write it and try)
• How have you dealt with this issue in a real world application?

P.S. I'd like to help out with the chapter requested by OP (database/sql), please let me know any guidelines or directives you may have for me.

Thank you for taking the time to answer and writing this amazing book!

EDIT June 24, 2019:

Reading through the transactions section on the sqlx guide, I found the following:

Since transactions are connection state, the Tx object must bind and control a single connection from the pool. A Tx will maintain that single connection for its entire life cycle, releasing it only when Commit() or Rollback() is called.

As it turns out, my first approach was not working because I was using a New store for the tests, effectively a different connection.

Now, since *sql.Tx is fundamentally a different object than an *sql.DB, we're posed with three(?) different solutions, none of which is ideal:

1. Coping the PostgreSQLPlayerStore code into a helper struct (let's call it transactionWalledPlayerStore):

   • This implementation would pass an *sql.Tx object to the store
   • This solution involves duplicating ALL THE CODE from the original PostgreSQLPlayerStore store (since it needs to implement the interface, and methods must actually perform the operations in order for other tests to pass)
   • The difference would lie in the New method, where it would create and return the transaction, and close it with the removeDatabase closure.

// Weird formatting to save space
func newTransactionWalledPlayerStore(t *testing.T, host, port, user, dbname, pass string) (*transactionWalledPlayerStore, func()) {
	connStr := fmt.Sprintf(	"user=%s password=%s host=%s port=%s dbname=%s sslmode=disable",
		user, pass, host, port,	dbname,)
	db, err := sql.Open("postgres", connStr)
	if err != nil {
		log.Fatalf("failed to connect database %v", err)
	}
	tx, err := db.Begin()
	if err != nil { t.Fatalf("error creating transaction: %v", err) }
	_, err = tx.Exec(`SAVEPOINT test_savepoint;`)
	if err != nil { log.Printf("savepoint error: %v", err) }
	_, errCreate := tx.Exec(`
	CREATE TABLE IF NOT EXISTS players (
		id		serial		PRIMARY KEY,
		name	varchar(80)	NOT NULL UNIQUE,
		wins	int			DEFAULT 0
	);`)
	if errCreate != nil { log.Fatalf("failed to create table %v", errCreate) }
	removeDatabase := func() {
		_, err = tx.Exec(`ROLLBACK TO SAVEPOINT test_savepoint;`)
		if err != nil {	log.Printf("rollback error: %v", err) }
		_, err = tx.Exec(`RELEASE SAVEPOINT test_savepoint;`)
		if err != nil {log.Printf("release error: %v", err)}
		tx.Rollback() // tx.Commit() had the same outcome
		db.Close()
	}
	return &transactionWalledPlayerStore{tx}, removeDatabase
}

2. Modifying the Store struct to include a transaction in it's api:
   • This inserts a useless dependency which would only be used for testing
   • I haven't analyzed this thoroughly, but I would assume a memory leak would occur if you forget to close (via tx.Commit() or tx.Rollback()) the transaction at some point.

type PostgreSQLPlayerStore struct {
	DB *sql.DB
        Tx *sql.Tx // <- Only used for tests
}

3. Changing the store to interface{}, which would be an *sql.DB for the application, or *sql.Tx for integration tests.
   • This approach sacrifices compiler type-checking
   • Leaves the application in a brittle state, as anything could be passed to the store instead

type PostgreSQLPlayerStore struct {
	DB interface{} // <- BAD IDEA
}

I'm partial to no. 1, even though it's not DRY at all, it provides a solution without slowing the tests down or forcing us to change (or jeopardize) application code.

Thoughts, opinions, suggestions welcome!

[howarddo2208]

Definitely nice if there's a chapter about testing with database

[quii]

Latest chapter covers this https://github.com/quii/learn-go-with-tests/blob/main/working-without-mocks.md