How to use PostgreSQL as an in-memory SGBD (similar to HYPERSONIC) for unit testing?

Question

I'm trying to use PostgreSQL with Spring, Maven and Hibernate as an in-memory SGBD when running my tests.

The error I'm getting is that the database doesn't exist.

Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (FATAL: database "test" does not exist)

Is there any way to specify PostgreSQL to create its own tables in-memory, through hibernate, or feed it an sql query that would build the database?

Here are a few properties that I used with HYPERSONIC_IN_MEMORY and worked just fine, how can I apply the same to PostgreSQL?

<properties>
    <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect" />
    <property name="hibernate.hbm2ddl.auto" value="create" />
</properties>

It's my first time using PostgreSQL and any help would be greatly appreciated.

1. There is no "Postgre" - it's PostgreSQL or Postgres or PGSQL or PG. 2. PostgreSQL doesn't run "in memory" except in the case you ran it on a RAM-disk. — Richard Huxton, Nov 13 '12 at 15:08
Ok, great. Any way I can link it to my .sql file in the project that creates the database? — Eugen, Nov 13 '12 at 15:09
Sorry - can't figure out what you mean. Link what to which .sql file? — Richard Huxton, Nov 13 '12 at 19:05

score 2 · Accepted Answer · edited May 23 '17 at 11:43

You appear to want PostgreSQL to create a database if you connect to one that doesn't exist. It does not support doing that.

Yes, you can "feed it an sql query that would build the database" ... but you need to be connected to a database to issue the appropriate CREATE DATABASE command.

Try using a pre- class or script in your unit testing framework to make a JDBC connection to PostgreSQL and create the test database. Or request that as a feature for your unit testing framework, or as a feature request for Hibernate. See this forum thread for prior discussion.

The general advice is to get the ORM to update the existing schema, not create a new one, or to use pre-test code to drop and re-create the DB.

As for the "in RAM" part ... if that's a speed concern, see Optimise PostgreSQL for fast testing .

How to use PostgreSQL as an in-memory SGBD (similar to HYPERSONIC) for unit testing?

1 Answers1