[rbanffy]

While I agree testing well is crucial, running your tests on one RDBMS that's fast and running them less frequently (think "before merging into production") is a good compromise. PostgreSQL is slower here than SQLite and if your tests take 20 minutes to run, your developers won't test as often as you would like them to.

It also prevents you from doing things that only one RDBMS does (I was bitten by this because SQLite supports timestamps with timezone data and, at that time, the MySQL we used in production didn't) making your app more "robust" (you are using an ORM for a reason, right?).

Imagine you only test your app in PostgreSQL and, because of that, your app makes assumptions about the environment only PostgreSQL satisfies. You simply can't move to anything else without extensive rewriting. Now, when the workload changes, you can't just change your storage backend to one that suits your workload. You need to make PostgreSQL do whatever your new needs are. PostgreSQL is probably a good enough solution, but it's not the optimal solution for all problems.

[electrum]

Aside from a few seconds to spin up a new PostgreSQL instance, it should not be any slower than SQLite. We have a wrapper [1] (forked from [2]) that starts a new PostgreSQL instance from Java unit tests. We have the same for MySQL [3]. This allows every developer to automatically run tests against the target database without needing to set anything up.

(The common alternative in Java is to use H2 or Derby, which are similar in concept to SQLite)

1. https://github.com/airlift/testing-postgresql-server

2. https://github.com/opentable/otj-pg-embedded

3. https://github.com/airlift/testing-mysql-server

[anthonybsd]

Hmm, I'm actually on PostgreSQL right now using H2, this might come useful. Thanks.

[electrum]

Just add the Maven dependency and see their own tests for an example:

https://github.com/airlift/testing-postgresql-server/blob/ma...

https://github.com/airlift/testing-mysql-server/blob/master/...

Though you want to use @BeforeClass / @AfterClass or similar rather than creating one for each test.