How to prepare for integration tests which use PostgreSQL's in memory replacement?

Question

I have learned that using an actual database in integration tests slows them down significantly. So, I have to use an in memory database which may significantly increase speed of my integration tests.

I'm using Springboot for application development. How do I configure PostgreSQL for testing purposes? Is there any in memory database which is highly compatible with PostgreSQL's syntax?

If there is none, how should I perform integration tests.

Answer 1

Some of my db tests on real postgres take 10ms each. and i do multiple commits in each test. so:

To have coverage of postgres-native features you need the same db (as you noticed, h2 and other in-memory db are not very compatible). postgres doesn't have in-memory mode. For functional tests the real database itself is not much slower than any in-memory databases. The difference usually lies in startup time (for postgres 9.6 it's ~4s). But if your testing lifecycle is smart and you can lower number of db starts to 1 or 0 (by having development db always ready), then the problem stops being noticeable.

so get the real postgres and setup its lifecycle correctly. there are some tools that can help you solve some of the problems:

1. testcontainers will help you provide real db.

2. dbunit - will help you clean the data between tests

   cons:

   • a lot of work is required to create and maintain schema and data. especially when your project is in a intensive development stage.
   • it's another abstraction layer so if suddenly you want to use some db feature that is unsupported by this tool, it may be difficult to test it

3. testegration - intents to provide you full, ready to use and extensible lifecycle (disclosure: i'm a creator).

   cons:

   • free only for small projects
   • very young project

another step would be to move db to memory on the OS level. again, first startup time would be similar as everything needs to be loaded. some starting points here and here

cons:

• every dev in your team have to modify his local environment
• not portable between OSes (if your team have heterogenic environments)