Change database schema used by Spring Boot
JavaSpringSpring BootJava Problem Overview
How do I specify database schema used by Spring Boot? I am using default hibernate (=default) and postgres (but i hoping for a generic solution). I know how to specify JDBC URL:
spring.datasource.url=jdbc:postgresql:db_name
But unfortunately postgresql does not allow to specify schema in JDBC URL. I know that there is hibernate property hibernate.default_schema
, so I was hoping that one of the following properties will work:
hibernate.default_schema=schema
spring.hibernate.default_schema=schema
spring.jpa.hibernate.default_schema=raw_page
But unfortunately neither of them seems to have any result.
Java Solutions
Solution 1 - Java
Use for application.properties
:
spring.jpa.properties.hibernate.default_schema=your_scheme
OR for application.yaml
:
spring:
jpa:
properties:
hibernate.default_schema: your_scheme
From the Spring Boot reference guide:
> all properties in spring.jpa.properties.*
are passed through as normal JPA properties (with the prefix stripped) when the local EntityManagerFactory
is created
See http://docs.spring.io/spring-boot/docs/current/reference/htmlsingle/#howto-configure-jpa-properties
For a full list of available properties see http://docs.spring.io/spring-boot/docs/current/reference/htmlsingle/#howto-configure-jpa-properties
Solution 2 - Java
It depends on the DataSource implementation which property has to be used to set the default schema (reference). With HikariDataSource for example spring.jpa.properties.hibernate.default_schema
is ignored and you have to set
spring.datasource.hikari.schema=schema
See the complete list of HikariCP configuration parameters here.
Solution 3 - Java
spring:
jpa:
properties:
hibernate:
default_schema: your_schema_name
Solution 4 - Java
spring.jpa.properties.hibernate.default_schema=your_scheme
OR
spring: jpa: properties: hibernate.default_schema: your_scheme
With HikariDataSource for example spring.jpa.properties.hibernate.default_schema is ignored and you have to set too
spring.datasource.hikari.schema=your_scheme
Solution 5 - Java
I was hitting error: Cannot acquire connection from data source org.postgresql.util.PSQLException: ERROR: unsupported startup parameter: search_path
Solution: application-xyz_dev.yml
url: jdbc:postgresql://localhost:8080/your_database?search_path=your_scheme&stringtype=unspecified
spring: jpa: properties: hibernate.default_schema: your_scheme