Create PostgreSQL database on the fly using Hibernate even if the DB doesn't exist

Question

Using H2,

Environment.HBM2DDL_AUTO, "create"

creates the database if it does not exist yet.

However, in Postgres, the non existing DB is not created and thus an exception which says something like "DB does not exist" is thrown. Is there a way to configure Postgres to create a non existing database on demand?

The following configuration files can be used to reproduce the problem:

Works fine using H2:

package test.postgressql;

import java.util.Properties;

import javax.sql.DataSource;

import org.hibernate.cfg.Environment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.hibernate4.HibernateExceptionTranslator;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.Database;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@PropertySource("file:C:/springconfig/qpmlib.properties")
@ComponentScan(basePackages = {"test.postgressql"})
@EnableJpaRepositories(basePackages = { "test.postgressql" })
@EnableTransactionManagement
public abstract class H2DBConfig {

    @Autowired
    org.springframework.core.env.Environment env;

    public static final String DB_NAME = getNewDBName();

    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dmds = new DriverManagerDataSource();
        dmds.setDriverClassName("org.h2.Driver");
        dmds.setUrl("jdbc:h2:tcp://localhost/~/" + DB_NAME );
        dmds.setUsername(env.getProperty("h2user"));
        dmds.setPassword(env.getProperty("h2pw"));
        return dmds;
    }

    private static String getNewDBName() {
        return "H2DBTest";
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
        LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
        factory.setDataSource(dataSource());
        factory.setPersistenceUnitName(DB_NAME);
        factory.setPackagesToScan("test.postgressql");
        factory.setJpaVendorAdapter(jpaAdapter());
        factory.setJpaProperties(jpaProperties());
        factory.afterPropertiesSet();
        return factory;
    }

    @Bean
    public PlatformTransactionManager transactionManager() {
        JpaTransactionManager txm = new JpaTransactionManager(
                entityManagerFactory().getObject());
        return txm;
    }

    @Bean
    public JpaVendorAdapter jpaAdapter() {
        HibernateJpaVendorAdapter adapter = new HibernateJpaVendorAdapter();
        adapter.setDatabase(Database.H2);
        adapter.setGenerateDdl(true);
        adapter.setShowSql(true);
        return adapter;
    }

    @Bean
    public HibernateExceptionTranslator exceptionTranslator() {
        return new HibernateExceptionTranslator();
    }

    public Properties jpaProperties() {
        Properties properties = new Properties();
        properties.put(Environment.SHOW_SQL, "true");
        properties.put(Environment.HBM2DDL_AUTO, "create");
        properties.put(Environment.DIALECT,"org.hibernate.dialect.H2Dialect");
        return properties;
    }

}

Fails using Postgres

package test.postgressql;

import java.util.Properties;

import javax.sql.DataSource;

import org.hibernate.cfg.Environment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.hibernate4.HibernateExceptionTranslator;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.Database;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@PropertySource("file:C:/springconfig/qpmlib.properties")
@ComponentScan(basePackages = {"test.postgressql"})
@EnableJpaRepositories(basePackages = { "test.postgressql" })
@EnableTransactionManagement
public abstract class PGDBConfig {

    @Autowired
    org.springframework.core.env.Environment env;

    public static final String DB_NAME = getNewDBName();

    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dmds = new DriverManagerDataSource();
        dmds.setDriverClassName("org.postgresql.Driver");
        dmds.setUrl("jdbc:postgresql://localhost:5432/" + DB_NAME);
        dmds.setUsername(env.getProperty("postgresuser"));
        dmds.setPassword(env.getProperty("postgrespw"));
        return dmds;
    }

    private static String getNewDBName() {
        return "PostgresDBTest";
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
        LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
        factory.setDataSource(dataSource());
        factory.setPersistenceUnitName(DB_NAME);
        factory.setPackagesToScan("test.postgressql");
        factory.setJpaVendorAdapter(jpaAdapter());
        factory.setJpaProperties(jpaProperties());
        factory.afterPropertiesSet();
        return factory;
    }

    @Bean
    public PlatformTransactionManager transactionManager() {
        JpaTransactionManager txm = new JpaTransactionManager(
                entityManagerFactory().getObject());
        return txm;
    }

    @Bean
    public JpaVendorAdapter jpaAdapter() {
        HibernateJpaVendorAdapter adapter = new HibernateJpaVendorAdapter();
        adapter.setDatabase(Database.POSTGRESQL);
        adapter.setGenerateDdl(true);
        adapter.setShowSql(true);
        return adapter;
    }

    @Bean
    public HibernateExceptionTranslator exceptionTranslator() {
        return new HibernateExceptionTranslator();
    }

    public Properties jpaProperties() {
        Properties properties = new Properties();
        properties.put(Environment.SHOW_SQL, "true");
        properties.put(Environment.HBM2DDL_AUTO, "create");
        properties.put(Environment.DIALECT,"org.hibernate.dialect.PostgreSQL9Dialect");
        return properties;
    }
}

Vlad Mihalcea · Accepted Answer · 2022-07-21T07:35:07.963

16

The hbmddl tool can only create tables for an existing schema, and it can't create a schema for you. The database must exist prior to running the tool. That's because a database must be created by an administrator, and it should get an owner assigned.

Because in most applications, the application can only access a database role with restrictive privileges, there's no need for such a feature.

PostgreSQL doesn't support creating the database on-the-fly, from the connection URL. You can add an InitializingBean at application startup connecting to the database server using the administrator account and the default PostgreSQL database and issue a CREATE DATABASE if the application DB doesn't exist.

Or use Testcontainers to bootstrap the DB in Docker, like this.

edited Jul 21 '22 at 07:35

answered May 16 '15 at 21:46

Vlad Mihalcea

142,745
71
566
911

I am not saying hbmdll is or should create the database. All I am saying is that using h2 the database is automatically created if it does not exist. I am aware that this a feature of h2, not of hbmddl. – Ueli Hofstetter May 17 '15 at 01:14
PostgreSQL doesn't support this but you can add an InitializingBean at application startup connecting to the database server using the administration account and the default Postgres database and issue a CREATE DATABASE if the application DB doesn't exist. – Vlad Mihalcea May 17 '15 at 03:20
Looks like this is the way to go. Thx. – Ueli Hofstetter May 17 '15 at 16:24
I upvoted your answer but in order for me to accept it, yout should add your comment about Postgres not supporting to create a new DB if it does not exist / to do this manually at startup. Because at it it stands, the answer is not really complete. – Ueli Hofstetter May 17 '15 at 22:33
2

@chuchikaeschtli: to be precise: no "full-blown" server based DBMS supports creating databases "on-the-fly" (when establishing the connection) if they do not exist. Neither Oracle (which has a **completely** different understanding of the term "database"), nor SQL Server, nor DB2 nor MySQL. H2 (and HSQLDB or Derby) being an in-memory, in-process file-based DBMS handles this differently because it was created with a different focus. – May 18 '15 at 08:55
@VladMihalcea Does use of the Flyway is any better than Liquibase? – Arefe Nov 30 '20 at 07:55
Both are fine. So, use whichever you like better. – Vlad Mihalcea Nov 30 '20 at 08:39
Does flyway support db creation (postgres) ? how ? – Alexander Argyriou Jul 20 '22 at 18:28
I am sorry I cannot understand what you mean, you said that we can use flyway for database creation. Schema <> DB in postgres, you can have a db with multiple schemas. I think this is not possible through flyway directly for postgres, you need a work around. Please explain how we can achieve that directly through flyway. thanks in advance – Alexander Argyriou Jul 21 '22 at 07:27
1

I also said `PostgreSQL doesn't support creating the database on-the-fly, from the connection URL`. The part about Flyway was to replace the `InitializingBean`, but I removed it because I don't think it could work. Instead, you could use Testcontainers. I updated the answer for that. – Vlad Mihalcea Jul 21 '22 at 07:30

Create PostgreSQL database on the fly using Hibernate even if the DB doesn't exist

1 Answers1

Linked