SQL Initialisation Scripts
Spring Boot can run schema.sql and data.sql at start-up; learn when that is handy, why it is off for real databases, and how it interacts with Hibernate.
src/main/resources/schema.sql # runs first
src/main/resources/data.sql # runs second
spring.sql.init.mode=embedded|always|never
spring.jpa.defer-datasource-initialization=true # run after Hibernate DDLBefore you reach for Flyway, Spring Boot has a simpler mechanism: put schema.sql and data.sql at the root of the classpath (src/main/resources) and Boot runs them against the DataSource at start-up. Schema scripts run first, then data scripts. There is no history table and no versioning: the scripts run in full every time the application starts.
When they run
The switch is spring.sql.init.mode:
| Value | Effect |
|---|---|
embedded (default) | Only for embedded databases such as H2 |
always | Every database, including PostgreSQL |
never | Scripts are ignored |
The default is the safety net. Developers often copy mode=always from a tutorial into the main application.properties, and then a data.sql full of sample rows runs against production on the next deploy. Put always in a profile-specific file if you need it at all.
Because they run every time
Write scripts that survive a second run: CREATE TABLE IF NOT EXISTS, and inserts guarded with ON CONFLICT DO NOTHING on PostgreSQL. Otherwise the second start fails, because a failing statement stops start-up by default. spring.sql.init.continue-on-error=true exists, but it also hides real mistakes, so prefer idempotent SQL.
With JPA
By default the scripts run before Hibernate initialises. If Hibernate creates the tables (ddl-auto=create-drop in a demo) and data.sql inserts into them, the inserts fail because the tables do not exist yet. Set spring.jpa.defer-datasource-initialization=true to run the scripts after Hibernate. Mixing the two is fine for a prototype and a poor idea for anything long-lived.
Vendor-specific scripts
Set spring.sql.init.platform=postgresql and Boot also looks for schema-postgresql.sql and data-postgresql.sql. You can point at other files with schema-locations and data-locations.
Where they fit
Use scripts for demos, H2-backed experiments and seed data in local development. Once a database holds data you care about, you need history and ordered, one-time changes: that is Flyway's job. Spring Boot's documentation advises against using the scripts alongside Flyway, so pick one per database. In tests, prefer @Sql on the test class or method, which runs a script for that test only.
Example
-- src/main/resources/schema.sql
CREATE TABLE IF NOT EXISTS room (
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE,
capacity INT NOT NULL
);
-- src/main/resources/data.sql
INSERT INTO room (name, capacity) VALUES ('Oak', 2) ON CONFLICT (name) DO NOTHING;
INSERT INTO room (name, capacity) VALUES ('Birch', 4) ON CONFLICT (name) DO NOTHING;
INSERT INTO room (name, capacity) VALUES ('Cedar', 6) ON CONFLICT (name) DO NOTHING;
When to use it
- Spin up an H2 demo for a workshop with a few sample rooms already loaded.
- Seed a local PostgreSQL started by Docker Compose with test guests, but only under the local profile.
- Load a small fixture for one integration test with @Sql instead of sharing global seed data.
More examples
Seed data only in the local profile
The main application.yaml keeps the default, so production never runs the seed file. Moving the file out of the default data.sql name is a second guard: even if mode is set to always elsewhere, Boot will not find it by accident. The scripts run before Hibernate starts, so ddl-auto=validate checks the schema they created instead of changing it.
# application-local.yaml (activated with spring.profiles.active=local)
spring:
sql:
init:
mode: always
platform: postgresql
schema-locations: classpath:seed/local-schema.sql
data-locations: classpath:seed/local-data.sql
continue-on-error: false
jpa:
hibernate:
ddl-auto: validate
Hibernate creates tables, data.sql fills them
Without the defer flag, data.sql runs first and fails with table not found. With an embedded H2 URL the default mode already runs the script, so no mode setting is needed. Treat this setup as a demo: there is no record of how the schema changed over time.
# A throwaway H2 demo: Hibernate builds the schema from the entities
spring.datasource.url=jdbc:h2:mem:demo
spring.jpa.hibernate.ddl-auto=create-drop
# Run data.sql after Hibernate has created the tables
spring.jpa.defer-datasource-initialization=true
A script per test with @Sql
@JdbcTest starts only the JDBC slice with an embedded database, and each test runs in a transaction that is rolled back afterwards, so the inserted rooms never leak into the next test. By default a method-level @Sql replaces the class-level one, which would skip the CREATE TABLE and fail the inserts; @SqlMergeMode(MERGE) runs the class script first, then the method's. @Sql can point at script files too; inline statements keep a small fixture next to the assertion that depends on it.
import static org.assertj.core.api.Assertions.assertThat;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.jdbc.test.autoconfigure.JdbcTest;
import org.springframework.jdbc.core.simple.JdbcClient;
import org.springframework.test.context.jdbc.Sql;
import org.springframework.test.context.jdbc.SqlMergeMode;
@JdbcTest
@SqlMergeMode(SqlMergeMode.MergeMode.MERGE)
@Sql(statements = "CREATE TABLE IF NOT EXISTS room (id BIGINT PRIMARY KEY, name VARCHAR(100), capacity INT)")
class RoomQueriesTest {
@Autowired
JdbcClient jdbc;
@Test
@Sql(statements = {
"INSERT INTO room VALUES (1, 'Oak', 2)",
"INSERT INTO room VALUES (2, 'Cedar', 6)"})
void findsLargeRooms() {
long large = jdbc.sql("SELECT count(*) FROM room WHERE capacity >= 4")
.query(Long.class)
.single();
assertThat(large).isEqualTo(1);
}
}
Discussion