H2 postgresql mode seems not working for me

Question

My application accesses Postgres database and I have many predefined queries (Rank,Parition,complex join etc) I fire against Postgres. Now I want to go for unit testing these queries behaviour with small test data. So I started with H2/JUnit. I found out that most of Postgres queries like Rank, Partition, Complex case when update etc. So I thought of using H2 PosgreSQL compatibility mode by thinking all Postgres queries will work on H2 please correct me if I am wrong.

I followed H2 documentation saying:

To use the PostgreSQL mode, use the database URL jdbc:h2:~/test;MODE=PostgreSQL or the SQL statement SET MODE PostgreSQL.

I enabled mode using SET MODE PostgreSQL and I tried to fire one of the query which involves rank() and works in Postgres but it did not work H2. It gives me the following exception:

Function "RANK' not found; in SQL statement 

Please guide I am new to H2 and database testing. Thanks in advance. I am using H2 JDBC driver to fire Postgres queries by thinking H2 Posgress compatibility mode will allow me to fire Postgres queries.

Answer 1

So I thought of using H2 PosgreSQL compatibility mode by thinking all postgres queries will work on H2 please correct me if I am wrong

I'm afraid that's not true.

H2 tries to emulate PostgreSQL syntax and support a few features and extensions. It'll never be a full match for PostgreSQL's behaviour, and doesn't support all features.

The only options you have are:

• Use PostgreSQL in testing; or
• Stop using features not supported by H2

I suggest using Pg for testing. It is relatively simple to write a test harness that initdb's a postgres instance and launches it for testing then tears it down after.

Update based on comments:

There's no hard line between " unit" and "integration" tests. In this case, H2 is an external component too. Purist unit tests would have a dummy responder to queries as part of the test harness. Testing against H2 is just as much an "integration" test as testing against PostgreSQL. The fact that it's in-process and in-memory is a convenience, but not functionally significant.

If you want to unit test you should write another database target for your app to go alongside your "PostgreSQL", "SybaseIQ", etc targets. Call it, say, "MockDatabase". This should just return the expected results from queries. It doesn't really run the queries, it only exists to test the behaviour of the rest of the code.

Personally, I think that's a giant waste of time, but that's what a unit testing purist would do to avoid introducing external dependencies into the test harness.

If you insist on having unit (as opposed to integration) tests for your DB components but can't/won't write a mock interface, you must instead find a way to use an existing one. H2 would be a reasonable candidate for this - but you'll have to write a new backend with a new set of queries that work for H2, you can't just re-use your PostgreSQL backend. As we've already established, H2 doesn't support all the features you need to use with PostgreSQL so you'll have to find different ways to do the same things with H2. One option would be to create a simple H2 database with "expected" results and simple queries that return those results, completely ignoring the real application's schema. The only real downside here is that it can be a major pain to maintain ... but that's unit testing.

Personally, I'd just test with PostgreSQL. Unless I'm testing individual classes or modules that stand alone as narrow-interfaced well-defined units, I don't care whether someone calls it a "unit" or "integration" test. I'll unit test, say, data validation classes. For database interface code purist unit testing makes very little sense and I'll just do integration tests.

While having an in-process in-memory database is convenient for that, it isn't required. You can write your test harness so that the setup code initdbs a new PostgreSQL and launches it; then the teardown code kills the postmaster and deletes the datadir. I wrote more about this in this answer.

See also:

• Running PostgreSQL in memory only

As for:

If all queries with expected end datasets works fine in Postgress I can assume it will work fine in all other dbs

If I understand what you're saying correctly then yes, that's the case - if the rest of your code works with a dataset from PostgreSQL, it should generally work the same with a dataset containing the same data from another database. So long as it's using simple data types not database specific features, of course.