29

My application accesses Postgres database and I have many predefined queries (Rank, Partition, 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 - will all Postgres queries work on H2?

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

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

halfer
  • 19,824
  • 17
  • 99
  • 186
Umesh K
  • 13,436
  • 25
  • 87
  • 129
  • 7
    You may find it interesting to read the comments to a similar question [here](http://stackoverflow.com/q/15487818/2144390). For what it's worth, I would side with the people advocating that if your application actually uses PostgreSQL then you should simply use PostgreSQL for your testing too. – Gord Thompson Jun 14 '14 at 19:58
  • Hi @Gord I wish I could use Posgres but as part unit testing I should use in memory database like H2. As part of integration testing I can use Postgres. – Umesh K Jun 14 '14 at 20:05
  • It is not a unit test when youre using a database... – Vortilion Dec 18 '17 at 11:28

1 Answers1

52

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:

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.

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • Hi @Craig thanks for the reply. I cant stop using window functions like rank() so you mean I cant use H2 for rank in postgres compatibility mode. I am lost most of the in memory database does not support window functions. – Umesh K Jun 15 '14 at 15:16
  • Well, you could implement window functions in H2 if its really that important to your project. It won't be easy or quick. Personally I suggest just testing on Pg. It isn't clear to me why you'd can't do this with your unit tests. Sure, it's inconvenient, but its better than not even testing the same thing you will run. – Craig Ringer Jun 15 '14 at 15:37
  • Even I agree with you but people say I should not do unit testing on real database like Postgres I should use in memory db like H2 for this stuff. If I use real db like Postgres it would be integration test not unit test. I am new to database testing so kind of lost. – Umesh K Jun 15 '14 at 16:17
  • 5
    "people say"? References/links? Sounds like BS to me. There's no hard line between " unit" and "integration" tests for one thing. For another, H2 is an external component too. Purist unit tests would have a dummy responder to queries as part of the test harness. – Craig Ringer Jun 15 '14 at 17:40
  • Thanks a lot Craig. I agree with you. My app talks to many database like Sybase IQ, DB2, Postgres, ParAccel etc. I have same logic queries in different forms to support all these targets. So what do you suggest should I go with Postgres as unit test target. If all queries with expected end datasets works fine in Postgress I can assume it will work fine in all other dbs and it works as I did manual testing for them. Please guide. Your guidance will be a great help. – Umesh K Jun 15 '14 at 17:50
  • @CraigRinger, totally agree with your argument that H2 is an external component too. For that reason, provisioning testing environment using docker would be a good approach as well. – ramsvidor Jul 26 '18 at 05:43