Create Postgres database if it doesn't exist when running spring boot application

Question

I'm using PostgreSQL and spring-boot-2.0.1 and want my app to create the database if it doesn't exist. I have the below options in my application.properties

spring.jpa.hibernate.ddl-auto=update
spring.jpa.generate-ddl=true
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQL94Dialect

spring.datasource.driverClassName=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/mydb
spring.datasource.username=postgres
spring.datasource.password=123

with the above i'm getting error:

Caused by: org.postgresql.util.PSQLException: FATAL: database "mydb" does not exist

any idea what i'm missing?

the generate-ddl configuration only acts on tables and not on DB; if the DB doesn't exist it won't be created — Angelo Immediata, Jul 04 '18 at 15:01

score 4 · Accepted Answer · answered Jul 04 '18 at 19:02

Since all Postgres installations come with a default database, technically it should be possible to connect to it at the very beginning (when the application starts) and then to call

CREATE DATABASE

This is something you could do programmatically before spring initializes, but after the properties are read, for example in Environment Post Processor

The so-early invocation is required because you probably want to take advantage of the properties read by spring boot on one hand, but by the time the spring boot beans will start to get the newly created database in the "operational" state.

So, technically its possible. However, I can't see a real use-case for this scenario. If it's for tests, then you can perfectly use the default Postgres database, that was mentioned in the very beginning of the question.

If it's for production environment - usually DBAs won't allow that, because in order to be able to run that CREATE DATABASE statement, the user that connects to Postgres (spring boot application in this case) should have a very "strong" createdb priviledge, something that DBA won't really want to share.

score 4 · Answer 2 · answered Sep 10 '22 at 04:03

Spring Boot

Postgres does not support createDatabaseIfNotExist=true So you can try a similar way and it worked for me see Screenshot

@SpringBootApplication
public class SpringSecurityJwtApplication{

    public static void main(String[] args) {
        Logger logger = LoggerFactory.getLogger(SpringSecurityJwtApplication.class);
        Connection connection = null;
        Statement statement = null;
        try {
            logger.debug("Creating database if not exist...");
            connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/", "postgres", "postgres");
            statement = connection.createStatement();
            statement.executeQuery("SELECT count(*) FROM pg_database WHERE datname = 'database_name'");
            ResultSet resultSet = statement.getResultSet();
            resultSet.next();
            int count = resultSet.getInt(1);

            if (count <= 0) {
                statement.executeUpdate("CREATE DATABASE database_name");
                logger.debug("Database created.");
            } else {
                logger.debug("Database already exist.");
            }
        } catch (SQLException e) {
            logger.error(e.toString());
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                    logger.debug("Closed Statement.");
                }
                if (connection != null) {
                    logger.debug("Closed Connection.");
                    connection.close();
                }
            } catch (SQLException e) {
                logger.error(e.toString());
            }
        }
        SpringApplication.run(SpringSecurityJwtApplication.class, args);
    }
}

score 3 · Answer 3 · edited Apr 11 '22 at 09:27

3

You can create a database if it doesn't exist with spring boot. At least with MySQL, we do:

spring.datasource.url=jdbc:mysql://localhost:3306/mydb?createDatabaseIfNotExist=true

So it should be the same with PostgreSQL:

spring.datasource.url= jdbc:postgresql://localhost:5432/mydb?createDatabaseIfNotExist=true

This doesn't work with PostgreSQL.

edited Apr 11 '22 at 09:27

Tobias S.

21,159
4
27
45

answered Jul 04 '18 at 18:49

ISlimani

1,643
14
16

34

Thank you for your answer but this won't work for postgres. it only works with mysql – Ahmed Lotfy Jul 04 '18 at 19:46
5

As mentioned, this solution doesn't work for Postgres. – Arefe Nov 30 '20 at 07:51
2

Unfortunately, createDatabaseIfNotExist won't work for postgres. – ilkayaktas Feb 14 '22 at 07:18

score 0 · Answer 4 · answered Apr 04 '21 at 10:59

0

Short answer:

It cannot be done like the way we do in MySQL you need to find a way to do it yourself out of Spring Configuration

answered Apr 04 '21 at 10:59

Ahmed Lotfy

1,065
2
15
33

score 0 · Answer 5 · answered Apr 11 '23 at 15:13

A solution would be to add the script for database creation ... even though, you don't have "if not exist" in postgres. Here is how i solved the issue with a docker file, but you can do a sql script in postgres i guess. I put this inside initdb.sh ...

set -e
export PGPASSWORD=$POSTGRES_PASSWORD;
psql -v ON_ERROR_STOP=1 --username "root" --dbname "mydb" <<-EOSQL
  SELECT 'CREATE DATABASE mydb' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'mydb')\gexec
  GRANT ALL PRIVILEGES ON DATABASE mydb TO root;
  \connect mydb root
EOSQL

Create Postgres database if it doesn't exist when running spring boot application

5 Answers5

Linked