Spring Boot Init Database

When we are developing Spring Boot applications it is a very common need to create tables and load into its data. Spring Boot provides an easy way to load initial SQL queries to insert registries in our database. Here we are going to understand how to initialize data with SQL files that Spring can load when it is starting.

Spring can load two files schema.sql and data.sql. These two files have to be in the resources folder.

Spring Boot Datasource initialization

The schema.sql file

We know that Spring can create tables using the entities class.

Please take a look at this article https://gustavopeiretti.com/spring-boot-database-connection-config/

But If we want to create tables using SQL sentences we can do it using the file schema.sql

Assume you have a User Entity

@Entity
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id;
    private String name;
    private String surname;
    private LocalDate birthDate;

    public User() {
    }

    // getters.. setters
}

For this entity, we need a table user with these attributes: id, name, surname, birth_date

So, ¿what do we have to do to create this table on our own?

We have to create a resources/schema.sql with the columns, type of data, and other definitions you need.

File resources/schema.sql

create table if not exists user
(
    id         bigint not null primary key,
    birth_date date not null,
    name       varchar(255) not null,
    surname    varchar(255) not null
);

The data.sql file

Spring Boot uses the data.sql file to run SQL queries when starting the application.

For example, we could have these queries in our resources/data.sql file to insert registries and populate the table.

INSERT INTO user (id, birth_date, name, surname)
VALUES (1, '1960-06-10', 'Paul', 'Hewson');
INSERT INTO user (id, birth_date, name, surname)
VALUES (2, '1960-03-13', 'Adam', 'Clayton');
INSERT INTO user (id, birth_date, name, surname)
VALUES (3, '1961-08-08', 'David', 'Evans');

Disable auto creation of tables

Be careful to add this property spring.jpa.hibernate.ddl-auto=none. Spring won’t use User.java class to create the table with this property with the value of none .

You can understand more about this property by visiting https://gustavopeiretti.com/spring-boot-database-connection-config/

With this property, the User.java entity class will not be used to create the tables. Our responsibility will be to create them manually or with the initialization using the SQL files.

The property indicates that we don’t want to use entity class to automatically create tables.


spring.jpa.hibernate.ddl-auto=none

Spring Boot Datasource initialization

Database initialization works with embedded databases like H2.

So, we need to add this property too, if we are using it for other non-embedded databases.

Enable SQL data source initialization

spring.datasource.initialization-mode=always

Spring Boot Datasource initialization

Starting our Spring Boot application

ok. Now, we are going to start our application to see the console.

Add this property to see data source logs with details.


logging.level.org.springframework.jdbc.datasource.init=DEBUG

As we begin our application, we are able to see this:

Spring Boot Datasource initialization

Conclusion:

Spring Boot provides an easy way to create schemas and populate tables. We can perform this data source creation and initialization with two files, schema, and data. Check the source code of this example here https://github.com/gustavopeiretti/spring-boot-examples

If you wish to send me any corrections or suggestions, please contact me.
Thanks for your feedback :)

See also