59. Database initialization

An SQL database can be initialized in different ways depending on what your stack is. Or of course you can do it manually as long as the database is a separate process.

59.1 Initialize a database using JPA

JPA has features for DDL generation, and these can be set up to run on startup against the database. This is controlled through two external properties:

  • spring.jpa.generate-ddl (boolean) switches the feature on and off and is vendor independent.
  • spring.jpa.hibernate.ddl-auto (enum) is a Hibernate feature that controls the behavior in a more fine-grained way. See below for more detail.

59.2 Initialize a database using Hibernate

You can set spring.jpa.hibernate.ddl-auto explicitly and the standard Hibernate property values are none, validate, update, create-drop. Spring Boot chooses a default value for you based on whether it thinks your database is embedded (default create-drop) or not (default none). An embedded database is detected by looking at the Connection type: hsqldb, h2 and derby are embedded, the rest are not. Be careful when switching from in-memory to a “real” database that you don’t make assumptions about the existence of the tables and data in the new platform. You either have to set ddl-auto explicitly, or use one of the other mechanisms to initialize the database.

In addition, a file named import.sql in the root of the classpath will be executed on startup. This can be useful for demos and for testing if you are careful, but probably not something you want to be on the classpath in production. It is a Hibernate feature (nothing to do with Spring).

59.3 Initialize a database using Spring JDBC

Spring JDBC has a DataSource initializer feature. Spring Boot enables it by default and loads SQL from the standard locations schema.sql and data.sql (in the root of the classpath). In addition Spring Boot will load a file schema-${platform}.sql where platform is the vendor name of the database (hsqldb, h2, oracle, mysql, postgresql etc.). Spring Boot enables the failfast feature of the Spring JDBC initializer by default, so if the scripts cause exceptions the application will fail to start.

To disable the failfast you can set spring.datasource.continueOnError=true. This can be useful once an application has matured and been deployed a few times, since the scripts can act as “poor man’s migrations” — inserts that fail mean that the data is already there, so there would be no need to prevent the application from running, for instance.

59.4 Initialize a Spring Batch database

If you are using Spring Batch then it comes pre-packaged with SQL initialization scripts for most popular database platforms. Spring Boot will detect your database type, and execute those scripts by default, and in this case will switch the fail fast setting to false (errors are logged but do not prevent the application from starting). This is because the scripts are known to be reliable and generally do not contain bugs, so errors are ignorable, and ignoring them makes the scripts idempotent. You can switch off the initialization explicitly using spring.batch.initializer.enabled=false.

59.5 Use a higher level database migration tool

Spring Boot works fine with higher level migration tools Flyway (SQL-based) and Liquibase (XML). In general we prefer Flyway because it is easier on the eyes, and it isn’t very common to need platform independence: usually only one or at most couple of platforms is needed.