In Spring Boot 2, how can I auto-generate my database and also record the schema generation commands to a file?

Question

I'm using Spring Boot 2.1 with Java 11. I am using Maven to build my artifacts. When running locally, I like the Spring JPA directives that let me create the database automatically ...

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.ddl-auto=update
spring.jpa.hibernate.show-sql=true

I also like the directives that let me auto-create files ...

spring.jpa.properties.javax.persistence.schema-generation.create-source=metadata
spring.jpa.properties.javax.persistence.schema-generation.scripts.action=update
spring.jpa.properties.javax.persistence.schema-generation.scripts.create-target=update.sql

However when I combine both in my src/main/resources/application.properties ...

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.ddl-auto=update
spring.jpa.hibernate.show-sql=true
spring.jpa.properties.javax.persistence.validation.mode=none

spring.datasource.url=jdbc:postgresql://${PG_DB_HOST:localhost}:5432/${PG_DB_NAME}

spring.datasource.username=${PG_DB_USER}
spring.datasource.password=${PG_DB_PASS}

spring.jpa.properties.javax.persistence.schema-generation.create-source=metadata
spring.jpa.properties.javax.persistence.schema-generation.scripts.action=update
spring.jpa.properties.javax.persistence.schema-generation.scripts.create-target=update.sql

it seems the "spring.jpa.properties.javax.persistence" take precedence and my schema changes are not auto-run against the database. Is there a way to configure things such that both happen -- the changes get recorded to a file and automatically run against my database?

Answer 1

Add the database.action with javax.persistence as follows which will update the database schema according to models as explained in Database Schema Creation

application.properties

spring.jpa.properties.javax.persistence.schema-generation.database.action=update

Also recommended to change (Deprecated) PostgreSQLDialect dialect with PostgreSQL82Dialect or according to the version you are using. Dailects

application.properties

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.ddl-auto=update
spring.jpa.hibernate.show-sql=true
spring.jpa.properties.javax.persistence.validation.mode=none

spring.datasource.url=jdbc:postgresql://${PG_DB_HOST:localhost}:5432/${PG_DB_NAME}

spring.datasource.username=${PG_DB_USER}
spring.datasource.password=${PG_DB_PASS}

spring.jpa.properties.javax.persistence.schema-generation.database.action=update
spring.jpa.properties.javax.persistence.schema-generation.create-source=metadata
spring.jpa.properties.javax.persistence.schema-generation.scripts.action=update
spring.jpa.properties.javax.persistence.schema-generation.scripts.create-target=update.sql