Skip schema.sql execution from test execution in spring boot

Question

I have below piece of SQL DDL in schema.sql under src/main/resources/.

ALTER TABLE `user` ADD INDEX IF NOT EXISTS `user_id_index`(user_name(30));

Now test cases are failing because it is trying to create the index in test execution where given table not existing in test DB config.

Below is my test DB config.

public DataSource dataSource() {
    DBConfigurationBuilder config = DBConfigurationBuilder.newBuilder();
    config.setPort(0);
    DB db = newEmbeddedDB(config.build());
    db.start();
    HikariConfig hikariConfig = new HikariConfig();
    hikariConfig.setJdbcUrl(config.getURL("unit-test"));
    return new HikariDataSource(hikariConfig) {
        @PreDestroy
        @SneakyThrows
        public void shutdown() {
            db.stop();
        }
    };
}

Test failure:

Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file: xyz/target/classes/schema.sql]: ALTER TABLE `user` ADD INDEX IF NOT EXISTS `user_id_index`(user_name(30)); nested exception is java.sql.SQLSyntaxErrorException: Table 'unit-test.user' doesn't exist
at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:622)
at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:254)
at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:49)
at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.runScripts(DataSourceInitializer.java:202)
at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer.createSchema(DataSourceInitializer.java:101)
at org.springframework.boot.autoconfigure.jdbc.DataSourceInitializerInvoker.afterPropertiesSet(DataSourceInitializerInvoker.java:63)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1847)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1784)
... 101 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: Table 'unit-test.user' doesn't exist
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)

How to skip the schema.sql is being executed in test execution? or any alterative way to try?

Answer 1

For now, added a table create statement just before the index creation to fix the test failures.

CREATE TABLE IF NOT EXISTS `user` (
  `id` varchar(255) NOT NULL,
  `user_name` text DEFAULT NULL,
   PRIMARY KEY (`id`)
)