72

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.

Paperback Writer
  • 1,835
  • 2
  • 16
  • 26

5 Answers5

129

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

M. Deinum
  • 104,041
  • 21
  • 200
  • 207
  • 1
    It seems that I missed this part in guide. I am not sure why ddl-auto is changed by `spring.jpa.hibernate.ddl-auto` but schema is changed by 'spring.jpa.properties.hibernate.default_schema'. Still it works as described in guide, so thank! – Paperback Writer Jun 18 '14 at 06:54
  • The *spring.jpa.hibernate.x* vs *spring.jpa.properties.hibernate.x* is really confusing. The *spring.jpa.properties.hibernate.* worked for me. – Raf Nov 11 '17 at 16:47
  • 4
    pay attention that the schema name must be in lowercase – ihebiheb Dec 18 '17 at 22:57
  • The full list of available properties is [here](http://docs.jboss.org/hibernate/core/4.3/devguide/en-US/html_single/#d5e4971). – Zhichang Yu Mar 04 '19 at 14:42
20

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.

vboerchers
  • 660
  • 1
  • 5
  • 16
  • Indeed i had the ikari datasource set up and it would not get the schema through `spring.jpa.properties.hibernate.default_schema` but it worked fine with the ikari way. – Jib'z Apr 28 '20 at 12:40
1
spring:
  jpa:
    properties:
      hibernate:
        default_schema: your_schema_name
harun ugur
  • 1,513
  • 14
  • 17
1

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

Bob
  • 61
  • 4
  • spring.datasource.hikari.schema=your_scheme, In my case this is also not working. I have added this in application.properties still hitting error. PSQLException: ERROR: relation "result_details" does not exist Position: 16 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440) – Anup Singh Jan 15 '22 at 08:38
1

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

Anup Singh
  • 965
  • 6
  • 3