Postgresql H2 mode doesn't seem to work for me

Question

Hi, my application accesses the Postgres database and I have many predefined queries (Rank, Parition, complex connection, etc.). I am running Postgres. Now I want to switch to unit testing of these queries with a little test data. So, I started with H2 / Junit. I found out that most Postgres requests like Rank, Partition, Complex case when updating, etc. So I thought about using PosgreSQL H2 compatibility mode, thinking that all postgres queries will work on H2, please correct me if I am wrong.

I followed an H2 document saying that to use PostgreSQL mode, use the jdbc database url: h2: ~ / test; MODE = PostgreSQL or SQL SET MODE PostgreSQL statement.

I turned on the mode using SET MODE PostgreSQL , and I tried to run one of the queries, which includes rank (), and works in postgres, but it does not work H2. This gives me the following exception.

 Function "RANK' not found; in SQL statement 

Please explain that I am new to H2 and database testing. Thank you in advance. I am using the H2 jdbc driver to run postgres requests, thinking that the H2 Posgress compatibility mode will allow me to run postgres requests.

Answer 1

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

I am afraid that this is not so.

H2 tries to emulate PostgreSQL syntax and supports several functions and extensions. It will never fully comply with the behavior of PostgreSQL and does not support all functions.

Only the following options are available:

• Use PostgreSQL when 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 instances postgres and runs it for testing, and then rips it off after.

Comment Based Update:

There is no hard line between the unit and integration tests. In this case, H2 is also an external component. Purist module tests would have a dummy request responder as part of a test harness. Testing against H2 is the same “integration” test as testing against PostgreSQL. The fact that it is in-process and in-memory is convenience, but not functionally significant.

If you want a unit test, you must write a different database target for your application in order to go near your "PostgreSQL", "SybaseIQ", etc. Call it, say, "MockDatabase". This should simply return the expected results from the queries. It really doesn't run queries; it exists only to test the behavior of the rest of the code.

Personally, I think it's a huge waste of time, but what the purist will do is check the block to avoid introducing external dependencies into the test harness.

If you insist on testing units of measure (as opposed to integration) for your database components, but cannot / will not write a mock interface, you must find a way to use the existing one. H2 would be a reasonable candidate for this, but you would need to write a new backend with a new set of queries that work for H2, you cannot just reuse your PostgreSQL server. As we already installed, H2 does not support all the functions that you need to use with PostgreSQL, so you will have to look for different ways to do the same with H2. One option would be to create a simple H2 database with "expected" results and simple queries that return these results, completely ignoring the actual application design. The only real flaw here is that it can be a serious pain to maintain ... but it is unit testing.

Personally, I'm just testing PostgreSQL. If I don’t test individual classes or modules that are autonomous, like narrowly conjugated, well-defined units, I don’t care, someone calls it “unit” or “integration”. I will be unit test, say data validation classes. For database interface code, purist module testing makes little sense, and I'll just do integration tests.

While having a built-in database in the database is convenient for this, this is not required. You can write test posting to the initdb installation code of the new PostgreSQL and run it; then the break code kills the postmaster and removes the datadir. I wrote more about this in this answer .

See also:

• Running PostgreSQL in memory only

Concerning:

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

If I understand what you are saying correctly, then yes, this case is if the rest of your code works with a data set from PostgreSQL, it should usually work with a data set containing the same data from another database. As long as it uses simple data types, not functions specific to the database, of course.