Spring Boot application doesn't create schemas for JPA @Table annotation

Question

I want to have tables located in different database schemas. But unfortunately, I can't achieve this with Spring Boot. Here steps to reproduce it.

1. Create a new Spring Boot project on http://start.spring.io version 2.0.5 (with derby and PostgreSQL dependencies)

2. Create simple entity

@Entity
@Table(name = "my_table")
public class MyTable {
    @Id Integer id;
}

3. Add only next property to the application.properties with value 'update' or 'create' (if you try 'create-drop' then you get another error described here: https://github.com/spring-projects/spring-boot/issues/7706#issuecomment-268798059). Now Derby datasource will be used by default.

spring.jpa.hibernate.ddl-auto=create

4. Run a generated test or main class. Be sure all works fine.

5. Modify the entity, add attribute schema to the @Table annotation. Now the entity looks like:

@Entity
@Table(name = "my_table", schema = "my_schema")
public class MyTable {
    @Id Integer id;
}

6. Run a test (or main class). This time I get an error while Spring Boot initialization process "java.sql.SQLSyntaxErrorException: Schema 'MY_SCHEMA' does not exist":

Full log listing is available here: https://gist.github.com/asaushkin/8d767c92b2e7025dd359f7be43eefdd6

7. Check on PostgreSQL. This error reproduces on a PostgreSQL instance too. Without the 'schema' attribute Spring Boot app runs perfect, but as soon as this attribute appears on the @Table annotation the exceptions are thrown.

Full log is here: https://gist.github.com/asaushkin/dd0d677964556bf943c4f013d4785372

My question is: why are schemas not created by Spring Boot?

These options can't resolve this issue too:

spring.jpa.properties.javax.persistence.schema-generation.create-database-schemas=true
spring.jpa.properties.hibernate.hbm2dll.create_namespaces=true

Links

1. https://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/Hibernate_User_Guide.html#configurations-hbmddl
2. https://docs.spring.io/spring-boot/docs/current/reference/html/howto-data-access.html#howto-configure-jpa-properties

Update (11 March 2019):

I've just check the current behavior of the issue. I wonder, but currently with Derby driver all works fine and the table is created with the specified schema. But in PostgreSQL an error continues exists.

Generated SQL (for PostgreSQL) is:

create table my_schema.my_table (id int4 not null, primary key (id))

Answer 1

Rename spring.jpa.properties.javax.persistence.schema-generation.create-database-schemas to spring.jpa.properties.javax.persistence.create-database-schemas. In other words, remove '.schema-generation'.

I just had the same problem not with PostgreSQL but H2 - schemas weren't being created. But, as I've discovered, the problem is not with H2 (or, likely, PostgreSQL) but, rather, Hibernate (it deviates from the standard, regarding that nomenclature). That likely means that this solution will work for you too.

Answer 2

Check that are you specifying the database dialect in the application.properties file or not for more check this thread.

Unable to get spring boot to automatically create database schema

Answer 3

I had the same problem with PostgreSQL and JPA (ERROR o.h.e.jdbc.spi.SqlExceptionHelper - ERROR: relation "schema.table" does not exist) and I figured out this solution.

In your entities classes, add escape characters \", between database element´s name. For instance:

Use this form:

@Table(name = "\"USUARIO\"", schema="\"INVENTARIODB\"")

Rather than a typical way

@Table(name = "USUARIO", schema="INVENTARIODB")

The same applies for columns names

@Column(name = "\"ID\"", nullable = false, updatable = false)    
private Long id;

Rather than

@Column(name = "ID", nullable = false, updatable = false)    
private Long id;

UPDATE:

I discovered the reason that was causing the problem. I used Valentina Studio to create my DB, if I use capital letters (MYTABLE), instead lower-case letters (mytable) to create my tables, I had to use double quotes inside SQL statements. This is because PostgreSQL is case sensitive. If you can´t change your database then use my last solution. Also is a good idea to enable spring.jpa.show-sql=true property, so you can see hibernate´s queries and know what´s going on.