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.
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.
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
, 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.
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 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).
Initialize a database
Spring Boot can automatically create the schema (DDL scripts) of your DataSource
and
initialize it (DML scripts): it loads SQL from the standard root classpath locations
schema.sql
and data.sql
, respectively. In addition Spring Boot will process the
schema-${platform}.sql
and data-${platform}.sql
files (if present), where platform
is the value of spring.datasource.platform
. This allows you to switch to database
specific scripts if necessary, 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. You can tune that using
spring.datasource.continue-on-error
.
In a JPA-based app, you can choose to let Hibernate create the schema or use
schema.sql but not both. Make sure to disable spring.jpa.hibernate.ddl-auto if you
chose the later.
|
You can also disable initialization by setting spring.datasource.initialize
to false
.
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
.
Use a higher-level database migration tool
Execute Flyway database migrations on startup
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 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:
flyway.locations=classpath:db/migration,filesystem:/opt/migration
You can also add a special {vendor}
placeholder to use vendor-specific scripts. Assume
the following:
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
.
If you want to make use of Flyway
callbacks, those scripts should also live in the classpath:db/migration folder.
|
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 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 flyway.locations
so that certain migrations
will only run when a particular profile is active. For example, in
application-dev.properties
you could set flyway.locations
to
classpath:/db/migration, classpath:/dev/db/migration
and migrations in dev/db/migration
will only run 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.
The master change log is by default read from db/changelog/db.changelog-master.yaml
but
can be set using 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 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.