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.
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.
You can set spring.jpa.hibernate.ddl-auto explicitly and the standard Hibernate property
values are none, validate, update, create, create-drop. Spring Boot chooses a
default value for you based on whether it thinks your database is embedded: default to
create-drop if no schema manager has been detected, none in all other cases. 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.
![[Note]](images/note.png) | Note |
|---|---|
You can output the schema creation by enabling the |
In addition, a file named import.sql in the root of the classpath will be 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 probably not something you want to be on the classpath in production. It
is a Hibernate feature (nothing to do with Spring).
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 the schema-${platform}.sql
and data-${platform}.sql files (if present), where
platform is the value of spring.datasource.platform, e.g. you might choose to set
it to the vendor name of the database (hsqldb, h2, oracle, mysql,
postgresql etc.). Spring Boot enables the fail-fast feature of the Spring JDBC
initializer by default, so if the scripts cause exceptions the application will fail
to start. The script locations can be changed by setting spring.datasource.schema and
spring.datasource.data, and neither location will be processed if
spring.datasource.initialize=false.
To disable the fail-fast you can set spring.datasource.continue-on-error=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.
If you want to use the schema.sql initialization in a JPA app (with
Hibernate) then ddl-auto=create-drop will lead to errors if
Hibernate tries to create the same tables. To avoid those errors set
ddl-auto explicitly to "" (preferable) or "none". Whether or not you use
ddl-auto=create-drop you can always use data.sql to initialize new
data.
If you are using Spring Batch then 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 are using an embedded database this happens by default. You can also enable it for any database type:
spring.batch.initialize-schema=always
You can also switch off the initialization explicitly using
spring.batch.initialize-schema=never.
Spring Boot supports two higher-level migration tools: Flyway and Liquibase.
To automatically run Flyway database migrations on startup, add the
org.flywaydb:flyway-core to your classpath.
The migrations are scripts in the form V<VERSION>__<NAME>.sql (with <VERSION> an
underscore-separated version, e.g. ‘1’ or ‘2_1’). By default they live in a folder
classpath:db/migration but you can modify that using spring.flyway.locations. You can
also add a special {vendor} placeholder to use vendor-specific scripts. Assume the
following:
spring.flyway.locations=db/migration/{vendor}Rather than using db/migration, this configuration will set the folder to use according
to the type of the database (i.e. db/migration/mysql for MySQL). The list of supported
database are available in DatabaseDriver.
See also the Flyway class from flyway-core for details of available settings like schemas
etc. In addition Spring Boot provides a small set of properties in
FlywayProperties
that can be used to disable the migrations, or switch off the location checking. Spring
Boot will call 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
folder. To use Java-based callbacks, create one or more beans that implement
FlywayCallback or, preferably, extend BaseFlywayCallback. Any such beans will be
automatically registered with Flyway. They can be ordered using @Order or by
implementing Ordered.
By default Flyway will autowire the (@Primary) DataSource in your context and
use that for migrations. If you like to use a different DataSource you can create
one and mark its @Bean as @FlywayDataSource - if you do that remember to create
another one and mark it as @Primary if you want two data sources.
Or you can use Flyway’s native DataSource by setting spring.flyway.[url,user,password]
in external properties.
There is a Flyway sample so you can see how to set things up.
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 will only be run when your
application starts for testing. If you want to be more sophisticated you can use
profile-specific configuration to customize spring.flyway.locations so that certain
migrations will only run when a particular profile is active. For example, in
application-dev.properties:
spring.flyway.locations=classpath:/db/migration,classpath:/dev/db/migrationWith that setup, migrations in dev/db/migration will only run when the dev profile is
active.
To automatically run Liquibase database migrations on startup, add the
org.liquibase:liquibase-core to your classpath.
The master change log is by default read from db/changelog/db.changelog-master.yaml but
can be set using spring.liquibase.change-log. In addition to YAML, Liquibase also
supports JSON, XML, and SQL change log formats.
By default Liquibase will autowire the (@Primary) DataSource in your context and use
that for migrations. If you like to use a different DataSource you can create one and
mark its @Bean as @LiquibaseDataSource - if you do that remember to create another one
and mark it as @Primary if you want two data sources. Or you can use Liquibase’s native
DataSource by setting spring.liquibase.[url,user,password] in external properties.
See
LiquibaseProperties
for details of available settings like contexts, default schema etc.
There is a Liquibase sample so you can see how to set things up.