Can Spring Data JPA automatically create database in PostgreSQL?

Question

I'm using Spring Boot, Spring Data JPA and PostgreSQL.

A want to persist User entity to database db_example.

@Entity
@Table(name = "my_user") // user is a reserved keyword in PostgreSQL
public class User {
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Integer id;  
    private String name;    
    private String email;

    // getters and setters       
}

My application.properties is quite standart:

spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

spring.jpa.hibernate.show-sql=true
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=create

spring.datasource.url=jdbc:postgresql://localhost:5432/db_example
spring.datasource.username=springuser
spring.datasource.password=ThePassword

Is it possible to make database created while Spring Boot Application started?

In my case I got org.postgresql.util.PSQLException: FATAL: database "db_example" does not exist

You need to manually create the `db_example` database on your PostgreSQL server and then this should work — Alan Kavanagh, Feb 25 '19 at 14:00
Even if it could: do not do that. Creating a database requires an account with superuser privileges. And your application should **not** use such a database user to begin with that is a huge security risk. After all that is a one-time operation when you setup your server. And what's wrong with using the default database `postgres`? — , Feb 25 '19 at 14:03
@a_horse_with_no_name, actually I used to such behavior of MongoDB, it can create database automatically. I'm planning to create a few Spring Boot Microservices, which will store some data in PostgreSQL in a separate databases. So I wanted to automate this process. — Andrew, Feb 25 '19 at 14:14
Well, this is not how Postgres (or most relational databases for that matter) works. Creating a database is a task done during the installation and configuration of the database server, not something you do "ad hoc" when starting an application. — , Feb 25 '19 at 14:15
@a_horse_with_no_name What is wrong with this approach for development purposes? — Pijotrek, Feb 25 '19 at 14:19
@Andrew set those 2 properties in your `application.properties` file: `spring.jpa.generate-ddl=true` `spring.jpa.hibernate.ddl-auto=create` (or create-drop or update) — Pijotrek, Feb 25 '19 at 14:20
@Pijotrek: because the database user you use during development should not have more privileges than the one used in production. — , Feb 25 '19 at 14:20
It sounds a bit paranoid to me, but that's only my opinion and I'm not an expert, rather a beginner. Obviously for production purposes I completely agree, but speeding up the development process is a good thing. I can't imagine asking DBA to do some changes in schema every time I change the field in my entity POJO... — Pijotrek, Feb 25 '19 at 14:22
@Pijotrek: I already have set this properties in my `application.properties`, as I pointed out in my question. Unfortunately it does't help - got `org.postgresql.util.PSQLException: FATAL: database "db_example" does not exist` — Andrew, Feb 25 '19 at 14:24
@Pijotrek: for a controlled deployment of schema changes, tools like Liquibase or Flyway are a much better solution — , Feb 25 '19 at 18:40

score 1 · Answer 1 · answered Feb 25 '19 at 18:17

So i configured this manually through application instead of using application.yml default properties here, In addition to those you just need to add the below one.

For more info docs

jpaProps.put("javax.persistence.schema-generation.database.action", "update");

Can Spring Data JPA automatically create database in PostgreSQL?

1 Answers1