Real database vs Mock

Testing using real technology instead of mocks was not easy before we had containers. For databases, usually, there was only one database server per machine and only one database per unit of application.

Mocking is the generally accepted solution for testing database interaction. Mocks can help with edge cases like losing a connection mid execution, but would you rather test that or test your queries and ensure they are working as expected with a real database. You can easily start testing against real databases using Docker.

As things stand in 2022 and considering the available tools, mocking the database feels like testing Go code by writing a Go interpreter.

Testing Methodologies

I share the same views with Bill Kennedy in regards to testing methodologies and couldn’t have said it better myself. I treat every package as an unit of code and when it comes to testing an unit of code in my projects, my biggest concern is ensuring that it works in production.

Following the thread from this tweet, you can find a response that serves as a catalyst for this article and the repositories implemented.

Why test against a real database?

• validate queries; no surprises in production.
• identify misusages
• compose complex scenarios; ensure your constraints are working as expected.

• high cost of managing a database lifecycle. Solved in this article :)

What tools are necessary?

• Docker

Before diving into the code I’m assuming that you are already familiar with Go and its powerful standard library. The code located in gists

Here is an example repository where you can find and run the tests presented in this article.

I will start by presenting the outcome of the tests, then I will run you through the setup needed to execute the tests and I will end up with the tests implementation.

Outcome

The tests found in the ./internal/psql package are all executed, in parallel, against a real PostgreSQL database. The main takeaway from here is the execution time, sitting at ~4 seconds. The execution time includes starting and destroying a fresh PostgreSQL database, programmatically, with go.

Setup

Before running the package tests a setup for the PostgreSQL database must be implemented. This is achieved with the help of TestMain function and the following external packages: psqldocker +psqltest .

TestMain allows the developer to run arbitrary code before and after running the package tests.

I developed psqldocker to programmatically manage the lifecycle of PostgreSQL Docker containers.

With psqldocker.NewContainer() a new PostgreSQL Docker container is created. The container is stopped in the, which is executed after running the package tests with m.Run() .

I also developed psqltest, a collection of PostgreSQL testing utilities similar to what/net/http/httptest is for net/http. The psqltest.Register() function is a wrapper over DATA-DOG/go-txdb/db.go:Register() which registers a new SQL driver that when opening a database connection using it, will start that connection in an isolated SQL transaction.

Tests Implementation

Since every test opens a new database connection in a separate SQL transaction, parallel test execution is safe. This enables the developer to work with the same database entity across multiple tests, for example, the same User entity can be used for multiple tests that might alter its state.

With psqltest.NewTransactionTestingDB() a new database connection is created using the driver previously registered in TestMain with psqltest.Register(). The DSN(second input parameter) provided to sql.Open() is the test name, it acts as an identifier for the underlying SQL transaction. Using the same DSN, the developer can open multiple database connections to the same transaction. For example, the identifier for the transaction in the first test is TestUserRepository/CreateUser/Success.

The psqltest.NewTransactionTestingDB() implementation:

Conclusion

There you go, in this article, I showed you how to programmatically ramp up and teardown a PostgreSQL database using Docker. I also implemented safe parallel tests to be executed against the database instance by opening the database connections in separate SQL transactions.