Using CREATE DATABASE ... TEMPLATE

Hibernate ORM
1

Hi

I heard that using this command is very fast. Fast enough to consider “resetting” the DB between individual unit tests (given a DB of type toy-data-ish).

In my Spring + Hibernate app, how can I leverage this? The idea is that when a new DB appears, I don’t want Hibernate to reinitialize, start introspecting it, etc.

I had a long discussion w/ my friend Gemini (AI from Google). It is convinced that reconnecting a “live” Hibernate “object tree” to a replaced DB is not doable. Even using dirty hacks, such as a proxy that doesn’t actually kill the DB connection to Java. It said that new DB means new OIDs; Hibernate is tied to the initial OIDs, and it would throw weird errors.

Have you got opinions/hints?

Thanks in advance.
Cristian

2

I would suggest you the following approach, which I personally also used in the past.

Have a separate task for managing the template database that you run manually after git pull or when switching branches:

  1. Try migrate database db_original with your schema management tool (Liquibase, Flyway)
  2. If migration is not possible, drop db_original and recreate db_original to then run the migration scripts
  3. Create a template DB db_tmp based on db_original

Then for tests:

  1. Create database db from template db_tmp
  2. Bootstrap Hibernate once with hbm2ddl.action=none connecting to db
  3. After every test, ensure all connections to db are closed (configure connection pool min-size 0 or forcefully close connections)
  4. Drop database db
  5. Create database db from template db_tmp
  6. Run next test and repeat with 3.

I had a very quick turnaround time with this approach. The creation of a database based on a template DB only takes a few milliseconds, even with schemas that have around 1000 tables.

3

Thanks @beikov.

I adapted your answer (to my Spring setup), and made it work :slightly_smiling_face:. I am using:

public class RoutingDataSource extends AbstractRoutingDataSource {

In the past, for a multi-tennancy use case we used AbstractDataSourceBasedMultiTenantConnectionProviderImpl (Hibernate specific). I think the principles are the same.

So this mechanism switches to the new DB everytime.

However,

  • I have about 300-400 ms for a small DB. About 20 tables. Some tables have < 10 row;
    others are empty.
  • About 600 - 700 ms: I tried also on another DB, about 100+ tables. About 20 tables have about 10 rows.

Between the 2 cases: not really proportional. However I find the times a bit high compared to what I heard/read/expecting. Is there a way for me to debug/diagnose/profile?

My PC is pretty powerful (gaming PC, high performance NVMe, plenty of RAM). I use a docker container for the dev DB; it’s not throttled (e.g. only a bit of CPU, RAM, etc).

4

Actually I see a small detail in your answer:

  • 1/ once: db_original → db_tmp
  • 2/ for each test: db_tmp → db

What is the motivation for this, instead of:

  • for each test: db_original → db
5

I don’t remember if a dedicate template database concept existed. It seems that you can just use any database as template nowadays though, so there is probably no reason to have db_tmp.

It’s important that you configure your PostgreSQL container to use tmpfs etc. e.g. use this config:

docker run --tmpfs /pgtmpfs:size=131072k \
      -d docker.io/postgis/postgis:17-3.5 \
      -c fsync=off \
      -c synchronous_commit=off \
      -c full_page_writes=off \
      -c shared_buffers=256MB \
      -c maintenance_work_mem=256MB \
      -c max_wal_size=1GB \
      -c checkpoint_timeout=1d
6

Thank you for the hint :+1:.

I have tried this setup. The time improved, indeed :partying_face:. For my test data, it dropped from 450ms to 50ms.

I’d say that my DB is pretty small:

SELECT pg_size_pretty(pg_database_size('leave-management-test'));
--- 7475 kB

There is another issue which existed since the beginning; but now it became important.

For the moment, in order to create a new temp DB (scoped for the duration of a single test), I need to delete 1/ disconnect from it and 2/ delete the old temp DB. “old temp DB” = the DB used for the previous test.

For the moment, to disconnect I do the “disconnect forcibly” approach:

        String sql = "SELECT pg_terminate_backend(pid) " +
                     "FROM pg_stat_activity " +
                     "WHERE datname = '" + dbName + "' " +
                     "AND pid <> pg_backend_pid()";

It works. It’s reasonably fast. However, the connection pool doesn’t like this. It yells at me with multiple lines such as:

2025-07-22 20:31:06.276  WARN   --- [      Thread-27] com.zaxxer.hikari.pool.PoolBase          : HikariPool-3 - Failed to validate connection org.postgresql.jdbc.PgConnection@5be0b67c (This connection has been closed.). Possibly consider using a shorter maxLifetime value.
...

It seems to recover is I give it a little “time to breathe”. E.g. sleep 1 second. But w/o this sleep, the connection breaks and it doesn’t reconnect:

2025-07-26 19:50:02.195  WARN   --- [      Thread-12] com.zaxxer.hikari.pool.ProxyConnection   : HikariPool-3 - Connection org.postgresql.jdbc.PgConnection@30de8451 marked as broken because of SQLSTATE(57P01), ErrorCode(0)

org.postgresql.util.PSQLException: FATAL: terminating connection due to administrator command
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)

2025-07-26 19:50:02.195  WARN   --- [      Thread-12] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 57P01
2025-07-26 19:50:02.196 ERROR   --- [      Thread-12] o.h.engine.jdbc.spi.SqlExceptionHelper   : FATAL: terminating connection due to administrator command
2025-07-26 19:50:02.196  WARN   --- [      Thread-12] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 08006
2025-07-26 19:50:02.196 ERROR   --- [      Thread-12] o.h.engine.jdbc.spi.SqlExceptionHelper   : An I/O error occurred while sending to the backend.
2025-07-26 19:50:02.197 ERROR   --- [      Thread-12] o.s.t.i.TransactionInterceptor           : Application exception overridden by rollback exception

javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not extract ResultSet

Is there a way to do this in a nice way? Preferably, to initiate the disconnect from Java. However, afterwards I need to: 1/ reconnect and 2/ do this fast. Can I somehow configure the connection pool, or interact w/ it? Or maybe I can get rid of it, if things are simpler so.

7

A small update. I implemented a DataSource Proxy like this (that delegates to ds):

public class MyDataSource implements DataSource {

	private HikariDataSource ds;

	public MyDataSource(Provider<HikariDataSource> dsProvider) {
		super();
		this.dsProvider = dsProvider;
		this.ds = dsProvider.get();
	}
	
	public void reconnect() {
		ds.close();
		ds = dsProvider.get();
	}

For the first few calls I get about 10 ms for the reconnect operation. In line w/ my previous approach. However, after a few calls, the reconnect starts to cost about 250 ms.

I thought of getting rid of the connection pool, and implement something like this:

    public Connection getConnection(String username, String password) throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

But I am affraid to test my code w/o connection pool. I remember that I saw issues in production related to connection pool; so that’s why I intend to say that it’s more “production like” to have the connection pool also for tests.

8

I would suggest that you try to use a different configuration for your connection pool when testing. Something like minSize = 0 and maxSize = 2 or even maxSize = 1 if that works for you.
You can also configure connection validation to avoid waiting for connections to be pruned by background threads, or flush the connection pool manually somehow like you attempted with the custom datasource.