Database Initialization in Spring Boot

When working with Spring Boot and relational databases, initializing your database schema and loading seed data is a common requirement. Database Initialization in Spring Boot is easy using two files:

schema.sql: Used to create tables and structures
data.sql: Used to insert initial data

This post explains how to use them, how Spring Boot runs them, and best practices for both development and production. Lets see the techniques for Database Initialization in Spring Boot.

🧠 Why Use `schema.sql` and `data.sql`?

Purpose	File	Example
Define database structure	`schema.sql`	CREATE TABLE …
Insert seed or test data	`data.sql`	INSERT INTO …

These files are automatically executed by Spring Boot when the application starts.

🛠️ How It Works

Spring Boot auto-detects and executes:

schema.sql: before Hibernate (DDL)
data.sql: after Hibernate (DML)

🔄 Execution Order:

If Hibernate DDL is disabled, schema.sql creates the schema.
Then, data.sql inserts data.
Hibernate DDL can override schema if set.

🔧 Configuration Options

By default, Spring Boot will load these files from:

src/main/resources/schema.sql

src/main/resources/data.sql

You can control execution using properties in application.properties:

spring.sql.init.mode=always

spring.sql.init.platform=h2

spring.sql.init.schema-locations=classpath:schema.sql

spring.sql.init.data-locations=classpath:data.sql

📁 Directory Structure

src/

└── main/

└── resources/

├── application.properties

├── schema.sql

└── data.sql

✍️ Example: `schema.sql`

CREATE TABLE users (

id BIGINT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(100),

email VARCHAR(100) UNIQUE

);

✍️ Example: `data.sql`

INSERT INTO users (name, email) VALUES (‘Alice’, ‘alice@example.com’);
INSERT INTO users (name, email) VALUES (‘Bob’, ‘bob@example.com’);

🧪 Verify with H2 Console

If using H2 for development, enable the console:

spring.h2.console.enabled=true

spring.h2.console.path=/h2-console

Visit http://localhost:8080/h2-console to browse your schema.

⚠️ Common Pitfalls

Issue	Cause
Tables not created	Hibernate overrides schema
Data not loaded	`spring.sql.init.mode` not set to `always`
Syntax error	SQL dialect mismatch
Duplicate inserts	`data.sql` runs every time app restarts

🧠 When to Use `schema.sql` and `data.sql`

Use Case	Recommendation
Local development	✅ Use both for quick testing
Integration testing	✅ Use with embedded DB
Production	❌ Avoid. Use Liquibase/Flyway instead

🔄 Alternative: Hibernate Auto DDL

spring.jpa.hibernate.ddl-auto=create

Options:

none
create
create-drop
update
validate

Drawbacks:

Not suitable for production
Can cause data loss

🚀 Best Practices

Best Practice	Why
Use only for development or test	Not safe for production
Place files in `resources/`	Required for auto-detection
Add `spring.sql.init.mode=always`	Ensures consistent behavior
Use `insert ... if not exists` in `data.sql`	Avoids errors on rerun
For production, use Liquibase or Flyway	Handles migrations safely

🧹 Summary

schema.sql: defines schema, runs before Hibernate
data.sql: loads initial data, runs after Hibernate
Configure with spring.sql.init.* properties
Great for testing, not suitable for production