84

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,995
  • 2
  • 16
  • 27

5 Answers5

142

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
  • 115,695
  • 22
  • 220
  • 224
  • 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
  • 5
    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
27

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
  • 767
  • 1
  • 6
  • 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
    I cannot see why said JPA option would be 'ignored' if using Hikari as it works at the JDBC level and has no knowledge if JPA. Setting `spring.jpa.properties.hibernate.default_schema` means Hibernate will generate SQL with each object qualified by a schema name (unless already qualified explicitly). Therefore, it will be irrelevant to set a (default) schema name on the JDBC connection itself (i.e. via Hikari). You can say that the two are alternatives. Doing it via Hibernate is a lot better as not every database supports the notion of a default schema on a connection. – peterh Jul 27 '22 at 07:34
  • @peterh you're right, it's ugly to be forced to use connection pool specific properties, but as stated `spring.jpa.properties.hibernate.default_schema` did not work. I do not think that a proper solution for Hibernate would be to prefix each object with the schema name - considering native queries. – vboerchers Sep 08 '22 at 15:37
  • @vboerchers. You got a point on native queries. The `hibernate.default_schema` only applies to SQL generated by Hibernate. After your comment I no longer know what is preferable. I guess it depends. :-) On a plain vanilla Spring Boot application I still cannot replicate that use of Hikari would mean that `spring.jpa.properties.hibernate.default_schema` is ignored. – peterh Sep 09 '22 at 11:57
  • I swear that at some point, my application worked using hibernate.default_schema, and started ignoring this property when I migrate from SB 3.0.6 to SB 3.1.1.. Or was it when I introduced flyway to my project ? No idea, but that wasn't clear from the logs or documentation. Anyway, thanks for the information. – Adrien H Jun 24 '23 at 14:57
1
spring:
  jpa:
    properties:
      hibernate:
        default_schema: your_schema_name
harun ugur
  • 1,718
  • 18
  • 18
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
  • 71
  • 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
  • 1,115
  • 1
  • 7
  • 3