Spring Boot running data.sql before creating entities in presence of schema.sql

[ConsciousObserver]

I'm trying to insert some rows in my entities for testing on developer machine using H2 Database. I'm using data.sql for this.

It works fine, entities are created and then data.sql is run to insert data in the tables produced by the entities.

However I need to create some other tables for which there are no entity classes, so I'm using schema.sql for those. Here's the issue, as soon as I add schema.sql to the project, Spring Boot runs data.sql before creating entities, which ends in Table not found exception.

It works fine if I replace data.sql with import.sql, it's run after the entities are created.

However h2 configuration is supposed to run only when testing, for that I have a maven profile which activates a spring.datasource.platform = h2, to load schema-h2.sql and data-h2.sql. import.sql doesn't work with the platform.

So the issue is that data.sql is being run before entities are created only when schema.sql is present.

Here's the Github repository for reproducing the issue

Without any platform

https://github.com/ConsciousObserver/SpringBootSchemaSqlIssue.git

With h2 platform (It's another branch platform-h2 in the above repository)

https://github.com/ConsciousObserver/SpringBootSchemaSqlIssue/tree/platform-h2

Stackoverflow

http://stackoverflow.com/questions/43692115/spring-boot-running-data-sql-before-creating-entities-in-presence-of-schema-sql

Thanks

[adenix]

I'm having a similar issue where data-[platform].sql is being run before @Entity scan creates my tables. This causes QA and Production releases to have to be started twice.

Spring Boot Starter Version

<parent>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-parent</artifactId>
  <version>1.5.2.RELEASE</version>
  <relativePath/>
</parent>

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.pandera</groupId>
  <artifactId>ask-services</artifactId>
  <version>0.1.0a</version>
  <packaging>jar</packaging>

  <name>ask-services</name>
  <description>tbd</description>

  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.5.2.RELEASE</version>
    <relativePath/>
  </parent>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    <java.version>1.8</java.version>
  </properties>

  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-hateoas</artifactId>
    </dependency>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>

    <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <version>9.4-1201-jdbc41</version>
    </dependency>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-security</artifactId>
    </dependency>

    <dependency>
      <groupId>org.springframework.security.oauth</groupId>
      <artifactId>spring-security-oauth2</artifactId>
    </dependency>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-cache</artifactId>
    </dependency>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>

    <dependency>
      <groupId>org.springframework.restdocs</groupId>
      <artifactId>spring-restdocs-mockmvc</artifactId>
      <scope>test</scope>
    </dependency>

    <dependency>
      <groupId>com.h2database</groupId>
      <artifactId>h2</artifactId>
      <scope>test</scope>
    </dependency>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-devtools</artifactId>
      <optional>true</optional>
      <scope>runtime</scope>
    </dependency>

    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.16.8</version>
    </dependency>

    <dependency>
      <groupId>com.github.mifmif</groupId>
      <artifactId>generex</artifactId>
      <version>1.0.0</version>
    </dependency>
  </dependencies>

  <build>
    <finalName>ax-services</finalName>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
      </plugin>
    </plugins>
  </build>

</project>

[ConsciousObserver]

Are you also using schema.sql with data.sql ?

[philwebb]

Currently the schema.sql file is taken to bean that the entire database definition is being created by that file. It would be nice if we could change DataSourceInitializer so that the schema.sql file doesn't immediately trigger initialization. The ideal order is:

With hibernate:

• Run schema.sql
• Initialize Hibernate and allow it to create entity tables
• Run data.sql

Without hibernate:

• Run schema.sql
• Run data.sql

This are of the code is unfortunately quite difficult to change without causing side effects. I don't think we'll be able to do anything in the 1.5.x line.

I'd suggest you either try manually adding the create table commands for the entities in schema.sql or trying flyway for data population.

[adenix]

I was using both schema.sql and data.sql. My fix can be to just remove the schema.sql since I just had it there with a dummy command in case a team member needed to do manual schema updates.

[ConsciousObserver]

For now I have renamed the data-h2.sql to sample_data_h2.sql and I'm loading it using ScriptUtils in @PostConstruct method of a bean with conditional @Profile

@Configuration
@Profile("h2_in_memory")
public class InMemoryConfig {
	private final String SAMPLE_DATA = "classpath:sample_data_h2.sql";
	
	@Autowired
	private DataSource datasource;
	
	@PostConstruct
	public void loadIfInMemory() throws Exception {
		Resource resource = webApplicationContext.getResource(SAMPLE_DATA);
		ScriptUtils.executeSqlScript(datasource.getConnection(), resource);
	}
}

[psj258]

@ConsciousObserver, I changed schema.sql
from this:
create table test(id int(10), name varchar(10));
to this:
create table USER_DETAILS(id int(10), name varchar(10));
and database initialization worked as it was intended.

Due to log scripts execution order is correct:

2017-07-15 03:39:26.417  INFO 72712 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executing SQL script from URL [file:/path/to/project/SpringBootSchemaSqlIssue/target/classes/schema.sql]
2017-07-15 03:39:26.426  INFO 72712 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executed SQL script from URL [file:/path/to/project/SpringBootSchemaSqlIssue/target/classes/schema.sql] in 9 ms.
2017-07-15 03:39:28.420  INFO 72712 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executing SQL script from URL [file:/path/to/project/SpringBootSchemaSqlIssue/target/classes/data.sql]
2017-07-15 03:39:28.432  INFO 72712 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executed SQL script from URL [file:/path/to/project/SpringBootSchemaSqlIssue/target/classes/data.sql] in 12 ms.

Could you try it and give a response about results?

[ConsciousObserver]

@akanurlanopop , Thanks for response.

USER_DETAILS table is to be created by the com.test.UserDetails entity class.

I don't want to make an entry for it inside schema.sql as it can be generated by the JPA. That is normal behavior, however this doesn't work if I add custom tables (without JPA entities) to the schema.sql.

I hope it make things clearer.
I want to generate the tables which have JPA entities and use schema to create some extra tables.

If you make an entry of USER_DETAILS inside schema.sql then com.test.UserDetails entity won't be generating this table and If I make any changes to that entity, I would have to modify schema.sql as well.

[snicoll]

It looks like this issue would be addressed with what I am trying to do in #9528