How to get your DB Tables automatically created with Spring Boot

A common requirement when you are developing and testing your application is to use create and drop your database schema at start up. Let’s learn how to do that in a simple Spring Boot application.

Configuration steps for automatic Table generation

Here is a sample application.properties configuration that will let Hibernate create the Database tables out of your Entity beans:

spring.datasource.url=jdbc:mysql://localhost:3306/mysqltutorial?useSSL=false
spring.datasource.username=root
spring.datasource.password=
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto = create

Here are the possible values for hibernate.hbm2ddl.auto :

• validate: validate the schema, makes no changes to the database.
• update: update the schema.
• create: creates the schema, destroying previous data
• create-drop: drop the schema at the end of the session

Additionally, if you want to log the schema creation, include these properties as well in your application.properties file:

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

On the other hand, if you are using a YAML file for your configuration, you can use the following application.yml as a reference:

spring:
  jpa:
    database: POSTGRESQL
    show-sql: true
    hibernate:
      ddl-auto: create-drop
  datasource:
    platform: postgres
    url: jdbc:postgresql://localhost:5432/myschema
    username: foo
    password: bar
    driverClassName: org.postgresql.Driver

Automatic schema creation does not work?

If you are unable to see the database schema generation, check the following points:

1. Are you including the configuration file in the right place? In a Maven project, you should place your application.properties (or equivalent) in src/main/resources folder.
2. Your Entity classes should be in the same or in a sub-package relative to where you have you class with @EnableAutoConfiguration. If that’s not trye, your Spring Boot application simply cannot detect them and hence will not create your Database tables.
   • As workaround, you can use @EntityScan annotation in the main class, specifying the Entity you want to save or package too. See the following example:

@SpringBootApplication
@EnableConfigurationProperties
@EntityScan(basePackages = {"com.acme.model"})  // force scan JPA entities
public class Application {

  private static ConfigurableApplicationContext applicationContext;

  public static void main(String[] args) {
    Application.applicationContext = SpringApplication.run(Application.class, args);
  }
}

Delegate schema creation to the Database

For some databases, it is possible to delegate the creation of the schema you are using to the Database itself, via the JDBC Driver.

For example, if you are using MySQL Database, you could use the createDatabaseIfNotExist property as follows:

spring.datasource.url=jdbc:mysql://localhost:3306/sampledb?createDatabaseIfNotExist=true&useUnicode=true&characterEncoding=utf-8&autoReconnect=true

A complete example

If you want to check a complete example of a Spring Boot application which uses automatic schema creation with a PostgreSQL database, we recommend checking this article which is also backed on GitHub by source code: Spring Boot JPA application with PostgreSQL

Found the article helpful? if so please follow us on Socials