Pitfall one: column names
We recently started a Hibernate project with Postgresql as a database. And on of our database scripts looked like this
CREATE TABLE person ( "id" integer NOT NULL PRIMARY KEY, "name" VARCHAR(250) )
And then we started doing our unit testing in HSQLDB a pretty neat in memory database. In memory database and unit testing is a perfect marriage IMHO. A simple migration of our database scripts was all that was needed.
Unfortunately it did not work. Executing a simple statement like
insert into person (id, name) values (10, 'Natan')
resulted in an obscure
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: ID
It took us some time to figure out but it turns out it was not that obscure after all, the column in postgresql is named
id but in hsqldb it is
"id". Small difference! And I must say, after fixing the problem, HSQLDB behaves more logical.
Pitfall two: sequence numbers
For this we had a script in postgresql that created a sequence
CREATE SEQUENCE person_seq
which works fine. In fact it seemed to work fine in HSQLDB as well until we had a test that inserted two persons.
It turns out that sequences in HSQLDB are starting from 0 and that in combination with Hibernate it tries to insert both persons with ID 0.
CREATE SEQUENCE person_seq STARTS WITH 1
simply fixed our problem.
In the end we got our unit tests running just fine but it took us a bit more time than expected.