Database pre-population

As you probably know, the Spring framework provides a lot of tools for relational database management. Spring Boot can create databases needed by given entities on its own, and Spring Data JPA repositories make data management a lot easier. But sometimes it is useful to launch SQL scripts before the application runs, for example, for database pre-population or detailed database schemas setting with SQL tools. In this topic, you will learn how to do it and find some examples of using the new features.

Defining a database schema

schema.sql is the script defining the database structure (schema). It includes the name of a database, its fields, relations to other databases, and other information.
For example, let's imagine that we are developing an app for a car shop. It would need a database so let's create one:

CREATE TABLE car (
    id IDENTITY PRIMARY KEY,
    model VARCHAR(50),
    price INT
);

You should put this script in the resources folder with the name schema.sql to make it launch during the Spring application building. You can change the location inside the resources folder and the name of the file with help of the change in the application.properties file. For example, if you want your file to be in a data folder and to have the name myschema.sql, write:

spring.sql.init.schema-locations=classpath:data/myschema.sql

Script with data

data.sql is the script that allows you to pre-populate your database with some data according to the schema. Let's continue with our car database example and add a few rows to it:

INSERT INTO car(model, price) VALUES('Mazda', 50000);
INSERT INTO car(model, price) VALUES('Opel', 10000);
INSERT INTO car(model, price) VALUES('Volvo', 25000);

The rules are pretty similar to the schema ones: you should put the script in the resources folder. And also if you want to change the name and location of your script, there is a special property you can use:

spring.sql.init.data-locations=classpath:data/mydata.sql

Spring Boot and SQL scripts initialization

Let's create an object that repeats the structure of our database. As you may already know, Spring Boot can initialize a database by the given entity on its own. To generate a database like the one from our example above, you can use the following entity realization:

@Entity
@Table(name = "car")
public class Car {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    private String model;
    private int price;

    //getters, setters, and constructors...
}

@Entity
@Table(name = "car")
data class Car(
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    var id: Long = 0,
    var model: String = "",
    var price: Int = 0
)

Let's imagine the following: Spring Boot creates a table called Car. After that, the SQL script schema.sql launches and tries to do the same. Of course, this situation will lead to an error. So, SQL scripts and Spring Boot could have the same task simultaneously resulting in some unexpected behavior of an application. In order to prevent this, you need to configure application.properties correctly.

Application.properties settings

Some of the properties depend on which type of database we have: embedded or external. In short, embedded DB is integrated into the application you are developing or executing. The external database exists separately from any given application and needs its own server to operate. The H2 database can work in both modes. It depends on its URL. The database is external if you define the file to store it in ( spring.datasource.url=jdbc:h2:file:~/testdb ). To use an embedded database you should replace the file with the mem parameter in the URL ( spring.datasource.url=jdbc:h2:mem:testdb).

The first property we will talk about is spring.sql.init.mode. With its help, you can determine whether Spring launches data and schema scripts during application building or not. Three options are possible: always, never, and embedded. The last option is the default one. It means that SQL scripts will be launched only if your database is embedded. Let's look back at our example. If we have an external database and don't turn on this property, Spring just doesn't launch our SQL scripts and we don't have any databases in the app.

Spring Boot can use different ORMs to work with databases. Hibernate is the most popular one. It has its own property spring.jpa.hibernate.ddl-auto, which defines how exactly Hibernate creates and modifies database tables. There are five possible options: create, create-drop, update, validate, and none. Let's understand the most popular ones. If you want Spring Boot to generate databases on its own, you should choose the create option. Create-drop option is convenient for testing since Spring Boot creates databases and deletes them after the application stops. If you want Spring Boot and Hibernate not to generate anything and give this job to SQL scripts, the none option is the right choice. It is the default option for external databases.

During the application building stage, SQL scripts launch before Spring Boot generates databases by entities. This can become a problem if you want to use data.sql without schema.sql because there will be no tables to fill. You can change it by setting the property spring.jpa.defer-datasource-initialization to the true value.

When you run your application several times, you need to remember that creating a table that already exists will cause an error. Let's consider this problem with an example. Imagine you have spring.sql.init.mode=always in the application.properties and schema.sql in the resources folder. When you run your application for the second time, an error will occur. There are several ways to fix it. First, you may use different settings of application.properties for the next launches. In our example, you may use spring.sql.init.mode=never for them. Also, you can solve this problem with the help of SQL tools. You may delete the table if there is one by adding the following line to the beginning of schema.sql:

DROP TABLE IF EXISTS tableName

@Sql annotation

Another method of database pre-population is the @Sql annotation. It is used for the testing of applications. Let's understand how it works with the help of our little car application.

Let's create a resources folder in the test directory and copy our SQL scripts there. We also need to implement a JPA repository for data management. You can see the project structure in this screenshot:

Now we have everything set up to code our test class! The @Sql annotation will be responsible for the database pre-population. Here is an example of a possible test class.

@SpringBootTest
@Sql({"/schema.sql", "/data.sql"})
public class BootDatabasePrepopulationTests {

    @Autowired
    private CarRepository carRepository;

    @Test
    public void fillingTableTest() {
        assertEquals(carRepository.findAll().size(), 3);
    }
}

@SpringBootTest
@Sql("/schema.sql", "/data.sql")
class BootDatabasePrepopulationTests(@Autowired private val carRepository: CarRepository) {
    
    @Test
    fun fillingTableTest() {
        assertEquals(carRepository.findAll().size(), 3)
    }
}

Now let's consider using several methods in the test class. SQL scripts of class launches for each method over again. The @Sql annotation can be also defined for a separate method. In that case, it overrides the class annotation. However, you can change the Spring's behavior. The @SqlMergeMode annotation can let the method scripts launch after the class ones.

With help of the @Sql annotation, you can also change local configuration (how we run and parse SQL scripts) by the config attribute. Also, you can specify when to execute the scripts (before or after the method) with executionPhase. The scripts attribute defines paths to SQL scripts that will be launched. Finally, you can declare inline SQL statements to execute by the statements attribute.

Now let's consider an example. We have added two scripts to the test's resources folder: clean.sql which drops the table and moreData.sql which adds another three rows to the database. In the second method, I launched moreData.sql and checked whether the data was added. You can see the implementation of the tests below:

@SpringBootTest
@Sql({"/schema.sql", "/data.sql"})
@Sql(scripts = "/clean.sql", executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD)
public class BootDatabasePrepopulationTests {

    @Autowired
    private CarRepository carRepository;

    @Test
    public void fillingTableTest() {
        assertEquals(carRepository.findAll().size(), 3);
    }

    @Sql("/moreData.sql")
    @SqlMergeMode(SqlMergeMode.MergeMode.MERGE)
    @Test
    public void addingDataToTableTest() {
        assertEquals(carRepository.findAll().size(), 6);
    }
}

@SpringBootTest
@Sql("/schema.sql", "/data.sql")
@Sql(scripts = ["/clean.sql"], executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD)
class BootDatabasePrepopulationTests(@Autowired private val carRepository: CarRepository) {

    @Test
    fun fillingTableTest() {
        assertEquals(carRepository.findAll().size(), 3)
    }

    @Sql("/moreData.sql")
    @SqlMergeMode(SqlMergeMode.MergeMode.MERGE)
    @Test
    fun addingDataToTableTest() {
        assertEquals(carRepository.findAll().size(), 6)
    }
}

Try experimenting (for example, adding tests with other repository methods or changing the settings of the @Sql annotation) with this code to better understand the usage of the @Sql annotation.

Conclusion

There are three methods of database preparation:

• the automated one with Spring Boot entities;

• the launch of schema and data SQL scripts from resources;

• with the help of the @Sql annotation, which is used for testing purposes.

These methods can work both separately and together if the configuration is correct. Pre-population of databases is a widespread task in web application development, so it's an important know-how.