Load Initial Data with SQL Scripts in Spring Boot

When performing integration testing, we may require to load initial data into in-memory database before executing the tests. This helps in writing tests that depend on pre-existing data, and we do not want to mock the database interaction for some reason.

In this tutorial, we will learn to execute SQL scripts and load data during application startup while integration testing using @SpringBootTest annotation.

1. Default Behavior

By default, Spring Boot uses hibernate to manage persistence. Note that, for some features, hibernate provides more fine-grained control over spring boot configurations.

1.1. Default Schema Creation

When using in-memory databases, Spring boot automatically creates the schema for the JPA entities as part of autoconfiguration. All such default created tables are empty with no records in them.

For the Item entity, a table with the name “ITEM” be created in the database.

@Entity
@Table(name = "ITEM")
public class Item {

  @Id
  @GeneratedValue(strategy = IDENTITY)
  private Integer id;

  @Column(nullable = false)
  private String name;
}

We can verify the SQL logs in the console:

drop table if exists item cascade 

create table item (
   id integer generated by default as identity,
    name varchar(255) not null,
    primary key (id)
)

1.2. Default Data Initialization with ‘import.sql’

In addition, a file named import.sql in the root of the classpath is executed on startup. Any SQL statements written in this file will be executed on startup.

INSERT INTO ITEM (name) VALUES ('Books');
INSERT INTO ITEM (name) VALUES ('Games');

We can verify the loaded data using a simple test:

@Test
void testImportSqlRecords() {

  Optional<Item> item = itemRepository.findByName("Books");

  Assertions.assertNotNull(item.get().getId());
  Assertions.assertEquals("Books", item.get().getName());
}

2. Disable Default Schema Generation and Use SQL Scripts

Instead of the default schema created, we can generate the schema and initialize data using the schema.sql and data.sql files placed in the ‘/src/main/resources‘ or ‘/test/resource‘ folder.

2.1. Schema.sql

Make sure to disable spring.jpa.hibernate.ddl-auto if we want to use only the schema.sql file. Refer to the latest spring boot properties if there is a property name change.

spring.jpa.hibernate.ddl-auto = none

Now we can place the DDL statements in schema.sql file which will be executed on application startup.

DROP TABLE ITEM IF EXISTS;

CREATE TABLE ITEM (
    id   INTEGER      NOT NULL AUTO_INCREMENT,
    name VARCHAR(128) NOT NULL,
    PRIMARY KEY (id)
);

2.2. Data.sql

We can add the DML statements in data.sql file. Note that we have disabled the default schema generation, so import.sql file will not be executed.

INSERT INTO ITEM (name) VALUES ('Mobiles');
INSERT INTO ITEM (name) VALUES ('Laptops');

We can verify the schema and data loading with a test:

@Test
void testDataSqlRecords() {

  Optional<Item> item = itemRepository.findByName("Laptops");

  Assertions.assertNotNull(item.get().getId());
  Assertions.assertEquals("Laptops", item.get().getName());
}

3. Execute Custom DML Scripts using @Sql Annotation

3.1. Default Location for SQL Scripts

Spring data provides the capability to add SQL scripts that will be executed just before a test class or method. We only need to create the file with the exactly same name as the test class or test method, and place it in the classpath at the exact package location.

For example, for the testMethodNameScript(), we need to place the AppTest.testMethodNameScript.sql at the directory location '/resources/com/howtodoinjava/demo/'.

Do not forget to annotate the test with @Sql annotation.

@SpringBootTest
public class AppTest {

  @Test
  @Sql
  void testMethodNameScript() {

    Optional<Item> item = itemRepository.findByName("Spices");

    Assertions.assertNotNull(item.get().getId());
    Assertions.assertEquals("Spices", item.get().getName());
  }
}

The SQL file location is ‘/resources/com/howtodoinjava/demo/AppTest.testMethodNameScript.sql’.

INSERT INTO ITEM (name) VALUES ('Tickets');
INSERT INTO ITEM (name) VALUES ('Spices');

3.2. Custom Location

We can also provide the custom location of SQL scripts to execute in the @Sql annotation if we do not want to put the file in the default location discussed above. The following test will search the SQL file in location ‘/resources/sql’.

@Test
@Sql("classpath:sql/testMethodNameScript.sql")
void testMethodNameScript() {

  //...
}

4. Load Multiple SQL Files using Properties Configuration

There is one more way to provide the DDL and DML scripts using the properties configuration. Do not forget to disable the default initialization using by setting spring.jpa.hibernate.ddl-auto to none.

In the following configuration, we have placed the custom-schema.sql and custom-data.sql in the /resources/sql directory.

spring.jpa.hibernate.ddl-auto=none

spring.sql.init.schema-locations=sql/custom-schema.sql
spring.sql.init.data-locations=sql/custom-data.sql

We can provide multiple SQL files from different locations for both properties.

spring.sql.init.data-locations=sql/custom-data-1.sql, sql/custom-data-2.sql

Interestingly enough, we are also allowed to use ANT-style pattern matching in the file names. In the following example, the Spring JDBC will load all SQL files with name matching the pattern ‘custom-data-*.sql‘ inside the ‘/resources/sql‘ directory.

spring.sql.init.data-locations=sql/custom-data-*.sql

5. Conclusion

In this Spring boot tutorial, we learned to initialize an embedded database and load initial data for integration testing purposes. Note that this post mainly talks about configurations used for an embedded datasource.

Note that basic datasource initialization is enabled only for embedded databases and will turn off as soon as we switch a production database and we will need to change the property values of spring.sql.init.mode to always, first.

Happy Learning !!

Source code on Github