Database Initialization

An SQL database can be initialized in different ways depending on what your stack is. Of course, you can also do it manually, provided the database is a separate process. It is recommended to use a single mechanism for schema generation.

Initialize a Database Using Hibernate

You can set spring.jpa.hibernate.ddl-auto to control Hibernate’s database initialization. Supported values are none, validate, update, create, and create-drop. Spring Boot chooses a default value for you based on whether you are using an embedded database. An embedded database is identified by looking at the Connection type and JDBC url. hsqldb, h2, or derby are embedded databases and others are not. If an embedded database is identified and no schema manager (Flyway or Liquibase) has been detected, ddl-auto defaults to create-drop. In all other cases, it defaults to none.

Be careful when switching from in-memory to a ‘real’ database that you do not 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.

You can output the schema creation by enabling the org.hibernate.SQL logger. This is done for you automatically if you enable the debug mode.

In addition, a file named import.sql in the root of the classpath is executed on startup if Hibernate creates the schema from scratch (that is, if the ddl-auto property is set to create or create-drop). This can be useful for demos and for testing if you are careful but is probably not something you want to be on the classpath in production. It is a Hibernate feature (and has nothing to do with Spring).

Initialize a Database Using Basic SQL Scripts

Spring Boot can automatically create the schema (DDL scripts) of your JDBC DataSource or R2DBC ConnectionFactory and initialize its data (DML scripts).

By default, it loads schema scripts from optional:classpath*:schema.sql and data scripts from optional:classpath*:data.sql. The locations of these schema and data scripts can be customized using spring.sql.init.schema-locations and spring.sql.init.data-locations respectively. The optional: prefix means that the application will start even when the files do not exist. To have the application fail to start when the files are absent, remove the optional: prefix.

In addition, Spring Boot processes the optional:classpath*:schema-${platform}.sql and optional:classpath*:data-${platform}.sql files (if present), where ${platform} is the value of spring.sql.init.platform. This allows you to switch to database-specific scripts if necessary. For example, you might choose to set it to the vendor name of the database (hsqldb, h2, oracle, mysql, postgresql, and so on).

By default, SQL database initialization is only performed when using an embedded in-memory database. To always initialize an SQL database, irrespective of its type, set spring.sql.init.mode to always. Similarly, to disable initialization, set spring.sql.init.mode to never. By default, Spring Boot enables the fail-fast feature of its script-based database initializer. This means that, if the scripts cause exceptions, the application fails to start. You can tune that behavior by setting spring.sql.init.continue-on-error.

Script-based DataSource initialization is performed, by default, before any JPA EntityManagerFactory beans are created. schema.sql can be used to create the schema for JPA-managed entities and data.sql can be used to populate it. While we do not recommend using multiple data source initialization technologies, if you want script-based DataSource initialization to be able to build upon the schema creation performed by Hibernate, set spring.jpa.defer-datasource-initialization to true. This will defer data source initialization until after any EntityManagerFactory beans have been created and initialized. schema.sql can then be used to make additions to any schema creation performed by Hibernate and data.sql can be used to populate it.

The initialization scripts support -- for single line comments and /* */ for block comments. Other comment formats are not supported.

If you are using a higher-level database migration tool, like Flyway or Liquibase, you should use them alone to create and initialize the schema. Using the basic schema.sql and data.sql scripts alongside Flyway or Liquibase is not recommended and support will be removed in a future release.

If you need to initialize test data using a higher-level database migration tool, please see the sections about Flyway and Liquibase.

Initialize a Spring Batch Database

If you use Spring Batch, it comes pre-packaged with SQL initialization scripts for most popular database platforms. Spring Boot can detect your database type and execute those scripts on startup. If you use an embedded database, this happens by default. You can also enable it for any database type, as shown in the following example:

  • Properties

  • YAML

spring.batch.jdbc.initialize-schema=always
spring:
  batch:
    jdbc:
      initialize-schema: "always"

You can also switch off the initialization explicitly by setting spring.batch.jdbc.initialize-schema to never.

Use a Higher-level Database Migration Tool

Spring Boot supports two higher-level migration tools: Flyway and Liquibase.

Execute Flyway Database Migrations on Startup

To automatically run Flyway database migrations on startup, add the appropriate Flyway module to your classpath. In-memory and file-based databases are supported by org.flywaydb:flyway-core. Otherwise, a database-specific module is required. For example, use org.flywaydb:flyway-database-postgresql with PostgreSQL and org.flywaydb:flyway-mysql with MySQL. See the Flyway Documentation for further details.

Typically, migrations are scripts in the form V<VERSION>__<NAME>.sql (with <VERSION> an underscore-separated version, such as ‘1’ or ‘2_1’). By default, they are in a directory called classpath:db/migration, but you can modify that location by setting spring.flyway.locations. This is a comma-separated list of one or more classpath: or filesystem: locations. For example, the following configuration would search for scripts in both the default classpath location and the /opt/migration directory:

  • Properties

  • YAML

spring.flyway.locations=classpath:db/migration,filesystem:/opt/migration
spring:
  flyway:
    locations: "classpath:db/migration,filesystem:/opt/migration"

You can also add a special {vendor} placeholder to use vendor-specific scripts. Assume the following:

  • Properties

  • YAML

spring.flyway.locations=classpath:db/migration/{vendor}
spring:
  flyway:
    locations: "classpath:db/migration/{vendor}"

Rather than using db/migration, the preceding configuration sets the directory to use according to the type of the database (such as db/migration/mysql for MySQL). The list of supported databases is available in DatabaseDriver.

Migrations can also be written in Java. Flyway will be auto-configured with any beans that implement JavaMigration.

FlywayProperties provides most of Flyway’s settings and a small set of additional properties that can be used to disable the migrations or switch off the location checking. If you need more control over the configuration, consider registering a FlywayConfigurationCustomizer bean.

Spring Boot calls Flyway.migrate() to perform the database migration. If you would like more control, provide a @Bean that implements FlywayMigrationStrategy.

Flyway supports SQL and Java callbacks. To use SQL-based callbacks, place the callback scripts in the classpath:db/migration directory. To use Java-based callbacks, create one or more beans that implement Callback. Any such beans are automatically registered with Flyway. They can be ordered by using @Order or by implementing Ordered.

By default, Flyway autowires the (@Primary) DataSource in your context and uses that for migrations. If you like to use a different DataSource, you can create one and mark its @Bean as @FlywayDataSource. If you do so and want two data sources (for example by retaining the main auto-configured DataSource), remember to set the defaultCandidate attribute of the @Bean annotation to false. Alternatively, you can use Flyway’s native DataSource by setting spring.flyway.[url,user,password] in external properties. Setting either spring.flyway.url or spring.flyway.user is sufficient to cause Flyway to use its own DataSource. If any of the three properties has not been set, the value of its equivalent spring.datasource property will be used.

You can also use Flyway to provide data for specific scenarios. For example, you can place test-specific migrations in src/test/resources and they are run only when your application starts for testing. Also, you can use profile-specific configuration to customize spring.flyway.locations so that certain migrations run only when a particular profile is active. For example, in application-dev.properties, you might specify the following setting:

  • Properties

  • YAML

spring.flyway.locations=classpath:/db/migration,classpath:/dev/db/migration
spring:
  flyway:
    locations: "classpath:/db/migration,classpath:/dev/db/migration"

With that setup, migrations in dev/db/migration run only when the dev profile is active.

Execute Liquibase Database Migrations on Startup

To automatically run Liquibase database migrations on startup, add the org.liquibase:liquibase-core to your classpath.

When you add the org.liquibase:liquibase-core to your classpath, database migrations run by default for both during application startup and before your tests run. This behavior can be customized by using the spring.liquibase.enabled property, setting different values in the main and test configurations. It is not possible to use two different ways to initialize the database (for example Liquibase for application startup, JPA for test runs).

By default, the master change log is read from db/changelog/db.changelog-master.yaml, but you can change the location by setting spring.liquibase.change-log. In addition to YAML, Liquibase also supports JSON, XML, and SQL change log formats.

By default, Liquibase autowires the (@Primary) DataSource in your context and uses that for migrations. If you need to use a different DataSource, you can create one and mark its @Bean as @LiquibaseDataSource. If you do so and want two data sources (for example by retaining the main auto-configured DataSource), remember to set the defaultCandidate attribute of the @Bean annotation to false. Alternatively, you can use Liquibase’s native DataSource by setting spring.liquibase.[driver-class-name,url,user,password] in external properties. Setting either spring.liquibase.url or spring.liquibase.user is sufficient to cause Liquibase to use its own DataSource. If any of the three properties has not been set, the value of its equivalent spring.datasource property will be used.

See LiquibaseProperties for details about available settings such as contexts, the default schema, and others.

You can also use a Customizer<Liquibase> bean if you want to customize the Liquibase instance before it is being used.

Use Flyway for Test-only Migrations

If you want to create Flyway migrations which populate your test database, place them in src/test/resources/db/migration. A file named, for example, src/test/resources/db/migration/V9999__test-data.sql will be executed after your production migrations and only if you’re running the tests. You can use this file to create the needed test data. This file will not be packaged in your uber jar or your container.

Use Liquibase for Test-only Migrations

If you want to create Liquibase migrations which populate your test database, you have to create a test changelog which also includes the production changelog.

First, you need to configure Liquibase to use a different changelog when running the tests. One way to do this is to create a Spring Boot test profile and put the Liquibase properties in there. For that, create a file named src/test/resources/application-test.properties and put the following property in there:

  • Properties

  • YAML

spring.liquibase.change-log=classpath:/db/changelog/db.changelog-test.yaml
spring:
  liquibase:
    change-log: "classpath:/db/changelog/db.changelog-test.yaml"

This configures Liquibase to use a different changelog when running in the test profile.

Now create the changelog file at src/test/resources/db/changelog/db.changelog-test.yaml:

databaseChangeLog:
  - include:
      file: classpath:/db/changelog/db.changelog-master.yaml
  - changeSet:
      runOrder: "last"
      id: "test"
      changes:
        # Insert your changes here

This changelog will be used when the tests are run and it will not be packaged in your uber jar or your container. It includes the production changelog and then declares a new changeset, whose runOrder: last setting specifies that it runs after all the production changesets have been run. You can now use for example the insert changeset to insert data or the sql changeset to execute SQL directly.

The last thing to do is to configure Spring Boot to activate the test profile when running tests. To do this, you can add the @ActiveProfiles("test") annotation to your @SpringBootTest annotated test classes.

Depend Upon an Initialized Database

Database initialization is performed while the application is starting up as part of application context refresh. To allow an initialized database to be accessed during startup, beans that act as database initializers and beans that require that database to have been initialized are detected automatically. Beans whose initialization depends upon the database having been initialized are configured to depend upon those that initialize it. If, during startup, your application tries to access the database and it has not been initialized, you can configure additional detection of beans that initialize the database and require the database to have been initialized.

Detect a Database Initializer

Spring Boot will automatically detect beans of the following types that initialize an SQL database:

If you are using a third-party starter for a database initialization library, it may provide a detector such that beans of other types are also detected automatically. To have other beans be detected, register an implementation of DatabaseInitializerDetector in META-INF/spring.factories.

Detect a Bean That Depends On Database Initialization

Spring Boot will automatically detect beans of the following types that depends upon database initialization:

If you are using a third-party starter data access library, it may provide a detector such that beans of other types are also detected automatically. To have other beans be detected, register an implementation of DependsOnDatabaseInitializationDetector in META-INF/spring.factories. Alternatively, annotate the bean’s class or its @Bean method with @DependsOnDatabaseInitialization.