Automatically Create a Database Schema With Spring Boot

1. Introduction

Spring Boot works seamlessly with JPA making it easy to implement the data access layer in our applications. One of its powerful features is its ability to automatically create and manage database schemas based on our Java entity classes. With a few configurations, we can instruct Spring Boot to read our entity classes and automatically create or update the schema.

In this article, we’ll briefly talk about how we can leverage the power of Spring Boot and JPA to automatically create the database schema without having to write SQL statements. We’ll also see some common pitfalls to avoid during configuration.

2. Configuring for Auto Schema Creation

In this section, we’ll see the steps to configure Spring Boot for automatic database creation.

2.1. Adding the Required Dependencies

Before configuring our project for schema creation, we should ensure that our Spring Boot project includes the right dependencies.

First, we need Spring Data JPA for database interactions. It provides an abstraction layer over JPA:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

Then, we need to add the database-specific dependency to our project.

For H2 Database, we need to add the following dependency:

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
</dependency>

In the case of MySQL, the dependency would be as follows:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

and for PostgreSQL, we should add the following dependency:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
</dependency>

2.2. Configuring application.properties

Spring Boot uses the application.properties file to define configuration settings. This is where we include database connection details and other configurations.

Let’s see the configuration for our H2 database:

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.h2.console.enabled=true

# JPA Configuration
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto=update

For our MySQL, the configuration would be:

spring.datasource.url=jdbc:mysql://localhost:3306/mydatabase
spring.datasource.username=root
spring.datasource.password=password
spring.jpa.hibernate.ddl-auto=update
spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect

For our PostgreSQL, let’s configure:

spring.datasource.url=jdbc:postgresql://localhost:5432/mydatabase
spring.datasource.username=postgres
spring.datasource.password=password
spring.jpa.hibernate.ddl-auto=update
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect

2.3. Understanding spring.jpa.hibernate.ddl-auto Options

The key configuration option for automatic schema creation is spring.jpa.hibernate.ddl-auto. This setting controls how the database schema is managed when the application starts. Following, let’s understand the available options.

The none option means no schema generation or validation occurs. This is ideal when the database schema is already perfectly set up, and we don’t want Hibernate to make any changes to it.

The validate option checks if the existing database schema matches the entity classes. No changes will be made, but if there are discrepancies, such as a missing column, it will throw an error. This is useful for ensuring alignment without modification.

The update option modifies the database schema to match the entity classes. It adds new columns or tables but does not delete or alter existing structures. This is a safe choice when we need to evolve the schema without risking data loss.

The create option drops the entire database schema and recreates it every time the application starts. Use this option when we’re comfortable with losing and rebuilding the schema on each run.

We use the create-drop option to create the schema when the application starts and drops it when the application stops. This is handy for temporary environments or tests where persistence between runs is not required.

Finally, the drop option only drops the schema without recreating it. This is useful when we intentionally want to clean the entire database.

In production environments, developers often use the update option to ensure the schema evolves to match the application without risking the deletion of existing data.

2.4. Creating Entity Classes

Once the configuration is in place, we can create our entity classes. These classes represent the tables in our database and map directly to them.

Let’s see an example:

@Entity
public class Person {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    private String firstName;
    private String lastName;

//Setters and getters

}

The @Entity annotation marks the Person class as a JPA entity. This means it will be mapped to a corresponding database table, typically named “Person” unless otherwise specified.

The @Id annotation designates id as the primary key column. While the @GeneratedValue(strategy = GenerationType.IDENTITY) indicates that the database will auto-generate the primary key (e.g., an auto-incremented value) for new rows.

Additionally, id, firstName, and lastName represent the columns in the database table.

3. Testing Our Changes

Let’s test the functionality of saving and retrieving a Person entity using Spring Data JPA:

void whenSavingPerson_thenPersonIsPersistedCorrectly() {

    long countBefore = personRepository.count();
    assertEquals(0, countBefore, "No persons should be present before the test");

    Person person = new Person();
    person.setFirstName("John");
    person.setLastName("Doe");

    Person savedPerson = personRepository.save(person);

    assertNotNull(savedPerson, "Saved person should not be null");
    assertTrue(savedPerson.getId() > 0, "ID should be greater than 0"); // Check if ID was generated
    assertEquals("John", savedPerson.getFirstName(), "First name should be 'John'");
    assertEquals("Doe", savedPerson.getLastName(), "Last name should be 'Doe'");

}

This test ensures that a Person entity is correctly persisted in the database. It initially starts by checking that no Person records exist before the test, and then creates a new Person object with specific values. Next, after saving the entity using the repository, the test subsequently verifies that the saved entity is not null. It also tests if it has a generated ID greater than 0 and retains the correct first and last names.

4. Troubleshooting Common Issues

Configuring automatic schema creation in Spring Boot is generally straightforward, but sometimes issues can arise, and the schema may not generate as expected.

One common issue is that the system might fail to create tables due to naming conventions or database dialect problems. To avoid this, we need to ensure that the spring.jpa.database-platform property is properly set in the application.properties file.

Another useful tip is to enable Hibernate logging by setting the spring.jpa.show-sql=true property. This will print the SQL statements generated by Hibernate, which can help in debugging issues with schema creation.

It’s also important to make sure that our entity classes are in the same package or a sub-package relative to the class annotated with @EnableAutoConfiguration (usually the main application class). If Spring Boot cannot locate our entities, it won’t create the corresponding tables in the database.

Additionally, verify that the application.properties file is located in the correct directory, typically under src/main/resources. This ensures that Spring Boot can load our configurations properly.

We also need to be cautious about misconfiguring our database connection. If the connection settings are incorrect, Spring Boot might default to using an in-memory database like H2 or HSQLDB. Checking the logs for unexpected database connections can help prevent this issue.

Finally, When we use the default spring.datasource.* properties in our application.properties (or application.yml) file, Spring Boot auto-configuration will automatically detect these properties and configure the data source without any additional configuration class or Bean.

However, when we use a custom prefix like h2.datasource.*, Spring Boot’s default auto-configuration won’t automatically pick these properties up because it’s specifically looking for the spring.datasource.* prefix.

In this case, we need to manually configure a data source bean and tell Spring Boot to bind the custom properties using @ConfigurationProperties:

@Configuration
public class DataSourceConfig {
    
    @Bean
    @ConfigurationProperties(prefix = "h2.datasource")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }
}

5. Conclusion

In this tutorial, we’ve understood how to automatically create and update database schemas with Spring Boot. It’s a powerful feature that simplifies database management. With minimal configuration, we can keep our database schema in sync with our Java entities, ensuring that our application remains consistent and free from schema-related issues.