How to Load Initial Data with data.sql and schema.sql in Spring Boot - When to Use Flyway/Liquibase Instead

ローカルで動かすときに… — here’s the English translation of the article body:

---

When running locally, you want the test data to already be loaded the moment the app starts up. You place a data.sql file with that goal in mind, yet for some reason it never runs. Or worse, the app fails to even start, throwing a “table does not exist” error. This is a stumble that nearly everyone hits at least once when they first start working with Spring Boot.

In this article, we’ll sort out where to place schema.sql / data.sql, how to configure them, and the pitfalls around execution order relative to Hibernate (JPA)—getting you to the point where you can diagnose “data.sql doesn’t run” on your own. Along the way, we’ll also lay out the criteria for when to hand production schema management over to Flyway/Liquibase instead.

The Roles of schema.sql and data.sql

These two files have distinct roles, just as their names suggest.

• schema.sql … DDL. Where you write table and index definitions
• data.sql … DML. Where you seed initial data with INSERT and the like

If you place both directly under src/main/resources (the classpath root), Spring Boot automatically finds and runs them on startup. The convenience here is that they work with no special configuration.

-- src/main/resources/schema.sql
CREATE TABLE IF NOT EXISTS book (
  id     BIGINT PRIMARY KEY,
  title  VARCHAR(200) NOT NULL,
  author VARCHAR(100)
);

-- src/main/resources/data.sql
INSERT INTO book (id, title, author) VALUES (1, '吾輩は猫である', '夏目漱石');
INSERT INTO book (id, title, author) VALUES (2, '走れメロス', '太宰治');

The first thing to keep in mind is that this is strictly a mechanism intended for seeding data in development and testing. It has no version control and no incremental application, so it’s not suited for managing schema changes in production. We’ll come back to that point in the latter half.

Placement and Naming Conventions

The default file names are schema.sql and data.sql, and their location is directly under src/main/resources. First, check that you haven’t strayed from this.

When you want to switch scripts per DB platform, you can use the naming convention data-${platform}.sql. The value you specify in spring.sql.init.platform gets substituted into ${platform}.

spring.sql.init.platform=postgresql

With this setting, schema-postgresql.sql and data-postgresql.sql are read. This is handy in situations where the DDL dialect differs between, say, H2 and PostgreSQL.

If you want to change the location from the default, specify the path explicitly.

spring.sql.init.schema-locations=classpath:db/schema.sql
spring.sql.init.data-locations=classpath:db/data.sql

What spring.sql.init.mode Means

The most common cause of “data.sql doesn’t run” is this spring.sql.init.mode. It has three possible values.

• embedded … Runs only for embedded databases like H2 or HSQLDB (default)
• always … Always runs, regardless of the database type
• never … Performs no initialization at all

The key point is that the default is embedded. In other words, when you’re connected to an external database like MySQL or PostgreSQL, the scripts won’t run unless you specify otherwise. If your initial data stopped loading the moment you swapped out the database—even though it worked fine on your local H2—this is the cause.

If you want the scripts to run against an external DB as well, explicitly set always.

spring.sql.init.mode=always

Conversely, when you want to stop initialization partway through, setting it to never disables it entirely.

Watch Out for Execution Order Relative to Hibernate

If you’re using JPA (Hibernate), there’s one more major pitfall: execution order.

Since Spring Boot 2.5, SQL script initialization runs before Hibernate’s table generation (ddl-auto). Taken at face value, this means data.sql runs before Hibernate has created the tables. And naturally, that results in a failure like:

org.springframework.jdbc.BadSqlGrammarException: ... Table "BOOK" not found

a “table does not exist” error. Having entity definitions but no tables is a sign that this ordering isn’t lining up.

If you want to run data.sql against tables that Hibernate created, add a setting that shifts initialization to after Hibernate.

spring.jpa.defer-datasource-initialization=true

There are truly many cases where the only issue is forgetting this single line. When you want to seed tables auto-generated from JPA entities, you should consider it essentially mandatory. For the JPA entity definitions themselves, see also the article JPA Entity Relationship Mapping.

Conflicts from Using ddl-auto and schema.sql Together

Another common pattern is defining tables in both ddl-auto and schema.sql.

ddl-auto=create or update has Hibernate generate DDL from your entities. If you then write a CREATE TABLE for the same table in schema.sql, the definitions become duplicated—prone to conflicting, or having one overwrite the other, leading to unexpected behavior.

The way to avoid this is simple: consolidate the responsibility for schema definition into one or the other.

• If you manage the schema with schema.sql, set ddl-auto=none to stop Hibernate’s auto-generation
• If you leave table generation to Hibernate, don’t use schema.sql—consolidate on data.sql plus defer-datasource-initialization=true

For a setup that uses schema.sql with an external DB, you can write it like this in YAML.

spring:
  jpa:
    hibernate:
      ddl-auto: none
  sql:
    init:
      mode: always
      schema-locations: classpath:schema.sql
      data-locations: classpath:data.sql

If you decide up front which side owns the schema, this kind of confusion almost never occurs.

”data.sql Doesn’t Run” Checklist

When you get stuck, work through these from the top.

1. You’re on an external DB but haven’t set spring.sql.init.mode=always … The default embedded won’t run on an external DB. This is the most frequent culprit.
2. You forgot to add spring.jpa.defer-datasource-initialization=true … It runs before Hibernate and fails with “table not found.”
3. The file name or location is wrong … Is data.sql directly under src/main/resources, and is the spelling correct?
4. A syntax error on the SQL side … Missing semicolons, DB dialect mismatches, and so on. Check the logs for errors.
5. You’ve ended up with a duplicate definition alongside ddl-auto … The conflict pattern from the previous section.

Nearly every case falls into one of these five. If you read the logs carefully, you can see which phase is failing—whether it’s a missing table or a syntax error.

When to Use Flyway/Liquibase Instead

As convenient as schema.sql / data.sql are, they have a major weakness: no version control, no incremental application, no rollback. Since they assume you re-run everything from scratch every time, they aren’t suited to an operation where you evolve the schema bit by bit in production.

For that, simply hand things over to a migration tool. Sorting it out by use case, it breaks down like this.

Use case	Recommended approach
Seeding data for local development and testing	schema.sql / data.sql
Managing production schema change history	Flyway or Liquibase
Wanting to write it intuitively in SQL	Flyway
Wanting DB-independent management in XML/YAML	Liquibase

Roughly speaking, it’s standard SQL initialization for development data you can throw away without trouble, and Flyway/Liquibase for the production schema you keep growing. The concrete syntax for each is covered in Database Migration with Flyway and Database Migration with Liquibase, so give those a read if you’re considering a production-oriented setup.

Incidentally, in the context of preparing test data, Repository Slice Testing with @DataJpaTest and Safely Switching Environment-Specific Configuration with Spring Boot Profiles—which covers switching configuration per environment—also pair well.

Summary

schema.sql / data.sql aren’t scary once you understand the key points to nail down. For an external DB, use mode=always; when combining with Hibernate, use defer-datasource-initialization=true; and decide whether ddl-auto or schema.sql owns the schema. These three points resolve the majority of stumbles.

On top of that, don’t burden this mechanism with production schema change management. If you separate the roles—standard SQL initialization for development seed data, Flyway/Liquibase for production migrations—your project won’t break down as it grows.