[FIXED] Spring-Boot execute data.sql in one profile only

Issue

I have this application where using a profile "default" it connects to a PostgreSQL database and do migrations using Flyway.

I want to create another profile called "devEmbeddedCreate" where I need to use an embedded database server (h2), create the database using spring.jpa.hibernate.ddl-auto=create-drop in the application.properties file and execute a differently called "data.sql" script to initialize some tables.

If I leave the script with "data.sql" file name, it gets executed every time the application starts. That's something I don't want to happen, I need it to be executed only in a certain profile.

Things I've tried:

1. The documentation mentions there can be a schema-${platform}.sql file and you can define the platform using spring.datasource.platform in the config. The problem it doesn't work with a data-${platform}.sql file. (here)

2. Created a EmbeddedDatabaseBuilder. The problem is when I use it, it doesn't automatically create the database and only apply the specified script. Couldn't find a way to create the database automatically as spring.jpa.hibernate.ddl-auto=create-drop does. (here and here)

3. Looking for a way to transform an XML config to Java-based configuration found a way to create the database and all. After a lot of tweaking and changing to work in memory, it looked promising but haven't been able to find out why the database get closed (and erased all its structure) while starting up (here)

There must be a simpler way to just say "hey spring... run on strartup this data-devEmbeddedCreate.sql script when my profile is devEmbeddedCreate, right?

Solution

You were on the right track with your approach 1), but you should set datasource platform via spring.datasource.platform, not spring.jpa.database-platform. The script execution functionality is not JPA-specific.

You can also manually specify which SQL script files get executed by setting the spring.datasource.schema property. This is an excerpt from org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration file in 1.0.2.RELEASE:

String schema = this.datasourceProperties.getProperty("schema");
if (schema == null) {
    schema = "classpath*:schema-"
            + this.datasourceProperties.getProperty("platform", "all")
            + ".sql,classpath*:schema.sql,classpath*:data.sql";
}

As you can see, the set of files specified in the documentation is only used if you don't specify your own list.

Answered By - kryger