Spring Boot Loading Initial Data: data.sql, schema.sql, and Flyway Guide

Every Spring Boot application eventually needs to start with some data — reference tables, admin users, or test fixtures. The framework offers three main approaches: raw SQL scripts (schema.sql/data.sql), JPA auto-DDL, and Flyway migrations. While the official docs make each sound straightforward, the real world is full of landmines: execution order conflicts, silent failures when JPA drops tables, and migration hell when you outgrow scripts. I've seen a SaaS billing platform go down because a data.sql ran before Flyway, wiping out customer subscription data. This article walks through each approach with production-hardened patterns, using a payment-processing domain. You'll learn when to use scripts for prototypes, when to switch to Flyway, and how to avoid the gotchas that cost real money. By the end, you'll have a decision framework for any Spring Boot version from 2.x to 3.x.

Setting Up the Project for Data Initialization

Start with a Spring Boot 3.2 project using Spring Initializr. Add dependencies: spring-boot-starter-data-jpa, spring-boot-starter-web, and your database driver (mysql-connector-j or postgresql). For Flyway, add flyway-core and flyway-mysql or flyway-postgresql. Configure application.yml with your datasource. Disable JPA auto-DDL for script-based approaches: spring.jpa.hibernate.ddl-auto=none. Enable SQL logging to debug execution: spring.jpa.show-sql=true and logging.level.org.hibernate.SQL=DEBUG. For MySQL, add spring.datasource.url=jdbc:mysql://localhost:3306/payments?useSSL=false&allowPublicKeyRetrieval=true. For PostgreSQL: jdbc:postgresql://localhost:5432/payments. Set the database user and password. This setup is crucial — many developers skip logging and then wonder why their scripts silently fail.

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/payments
    username: root
    password: secret
  jpa:
    hibernate:
      ddl-auto: none
    show-sql: true
    properties:
      hibernate:
        format_sql: true
  sql:
    init:
      mode: always
      schema-locations: classpath:schema.sql
      data-locations: classpath:data.sql

What the Official Docs Won't Tell You

The Spring Boot documentation says data.sql and schema.sql work seamlessly with JPA. What they don't mention: if you have spring.jpa.hibernate.ddl-auto set to create or update, Hibernate will create tables first, then schema.sql will fail because the tables already exist. The fix is to set ddl-auto=none or use spring.jpa.defer-datasource-initialization=true (available since Spring Boot 2.5). Another hidden issue: data.sql runs in the same transaction as your application's initialization, so if a JPA entity listener fails, the entire data.sql may roll back — but only if your database supports DDL transactions (MySQL does not for DDL). Also, the script execution order is: schema.sql, then data.sql, then Flyway (unless deferred). This means Flyway migrations run after your seed data, which can cause version conflicts. In a payment system, I saw Flyway fail because a migration added a NOT NULL column that data.sql didn't populate. The official docs also skip the fact that script-based initialization is only intended for small datasets — if you have 100,000 rows, you'll hit memory limits because Spring loads the entire script into memory.

spring:
  jpa:
    defer-datasource-initialization: true
  sql:
    init:
      mode: always
      data-locations: classpath:data.sql
      schema-locations: classpath:schema.sql
# Now Flyway runs before scripts, avoiding schema conflicts

Using schema.sql and data.sql for Development

For local development, schema.sql and data.sql are convenient. Create schema.sql with CREATE TABLE statements. Use IF NOT EXISTS to avoid errors on restarts. For a payment system, define tables like payment_transactions, merchant_accounts, and refunds. In data.sql, insert reference data: payment statuses, currency codes, and test merchants. Use INSERT IGNORE or MERGE to make scripts idempotent. Example: INSERT IGNORE INTO payment_status (id, name) VALUES (1, 'PENDING'), (2, 'COMPLETED'). For MySQL, use ON DUPLICATE KEY UPDATE. For PostgreSQL, use ON CONFLICT DO NOTHING. Never use DELETE FROM without a WHERE clause in data.sql — that's how production data gets wiped. In development, you can use TRUNCATE in a separate reset script, but never in the main data.sql. Set spring.sql.init.continue-on-error=true to allow scripts to fail without crashing the app (useful for development). But in production, set it to false and rely on Flyway.

-- Insert reference data idempotently
INSERT IGNORE INTO payment_status (id, name) VALUES
(1, 'PENDING'),
(2, 'COMPLETED'),
(3, 'FAILED'),
(4, 'REFUNDED');

-- Insert test merchants
INSERT INTO merchant_account (name, email, status, balance) VALUES
('Test Merchant 1', 'merchant1@test.com', 'ACTIVE', 1000.0000),
('Test Merchant 2', 'merchant2@test.com', 'ACTIVE', 500.0000);

-- Insert sample transactions
INSERT INTO payment_transaction (merchant_id, amount, currency, status_id) VALUES
(1, 150.00, 'USD', 1),
(1, 200.00, 'USD', 2),
(2, 75.50, 'EUR', 3);

Flyway: Production-Grade Database Migrations

Flyway is the industry standard for managing database schema and data changes in production. It uses versioned SQL files: V1__init_schema.sql, V2__add_index.sql, etc. Each migration runs exactly once, tracked in the flyway_schema_history table. For a payment system, start with V1__create_tables.sql containing your DDL. Then V2__seed_reference_data.sql for static data like payment statuses. Use repeatable migrations (R__views.sql) for views and functions that change often. Configure Flyway in application.yml: spring.flyway.enabled=true, spring.flyway.locations=classpath:db/migration. Flyway checksums each migration — if you modify a file after it ran, Flyway throws an error. This is a feature, not a bug: it prevents silent schema drift. For production, use Flyway's undo migrations (V1__U__drop_column.sql) for rollbacks, but only if you have the Flyway Teams edition. For community edition, roll forward with a new migration. Always test migrations against a copy of production data before deploying.

-- Seed reference data as a versioned migration
INSERT INTO payment_status (id, name) VALUES
(1, 'PENDING'),
(2, 'COMPLETED'),
(3, 'FAILED'),
(4, 'REFUNDED');

-- Use MERGE for idempotency (PostgreSQL)
MERGE INTO merchant_account AS target
USING (VALUES (1, 'System Merchant', 'system@payments.com', 'ACTIVE', 0.0000)) AS source (id, name, email, status, balance)
ON target.id = source.id
WHEN NOT MATCHED THEN
  INSERT (id, name, email, status, balance) VALUES (source.id, source.name, source.email, source.status, source.balance);

Hybrid Approach: Combining Flyway with JPA Entities

In real projects, you'll use JPA entities for ORM but Flyway for schema management. This hybrid approach gives you the best of both: type-safe queries with JPA and version-controlled schema changes. Configure JPA with ddl-auto=validate to ensure entities match the database schema. Flyway runs before Hibernate validation, so any mismatch is caught at startup. For entity classes, use @Table and @Column annotations to map to the schema created by Flyway. Example: @Entity @Table(name = "payment_transaction") and @Column(name = "merchant_id"). Use @Column(nullable = false) to enforce NOT NULL constraints. JPA's schema validation can catch issues like missing columns or wrong data types. This is especially important when multiple developers work on the same database. I've seen a team spend hours debugging a 'column not found' error because someone added a column in Flyway but forgot to update the entity. With ddl-auto=validate, the error is immediate and clear.

spring:
  jpa:
    hibernate:
      ddl-auto: validate
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: true

Advanced Flyway Patterns: Callbacks and Undo

Flyway callbacks allow you to execute custom logic before or after migrations. Common use cases: logging migration start/end, sending notifications, or cleaning up temporary data. Create a Java class implementing Callback interface from Flyway. Use @Component to register it. For example, log migration duration to Prometheus metrics. Another pattern: use Flyway's undo migrations (V1__U__description.sql) for rollbacks. Note: undo is only available in Flyway Teams edition. For community edition, create a new migration to reverse changes. Example: V3__add_merchant_email_index.sql, then V4__drop_merchant_email_index.sql if needed. Always test rollbacks against a production-like database. In a payment system, a rollback of a column addition must also handle data that was populated in that column. Use Flyway's validateOnMigrate to check for checksum changes before running migrations. This prevents accidental edits. Also, use flyway.clean-disabled=true in production to prevent accidental database wipe.

import org.flywaydb.core.api.callback.Callback;
import org.flywaydb.core.api.callback.Context;
import org.flywaydb.core.api.callback.Event;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

@Component
public class MigrationCallback implements Callback {

    private static final Logger log = LoggerFactory.getLogger(MigrationCallback.class);

    @Override
    public boolean supports(Event event, Context context) {
        return event == Event.AFTER_MIGRATE || event == Event.BEFORE_MIGRATE;
    }

    @Override
    public void handle(Event event, Context context) {
        if (event == Event.BEFORE_MIGRATE) {
            log.info("Starting Flyway migration at {}", System.currentTimeMillis());
        } else if (event == Event.AFTER_MIGRATE) {
            log.info("Flyway migration completed at {}", System.currentTimeMillis());
        }
    }

    @Override
    public String getCallbackName() {
        return "MigrationLogger";
    }
}

Testing Initial Data Loading

Testing data initialization is critical but often overlooked. Use @DataJpaTest with Testcontainers for integration tests. Start a MySQL or PostgreSQL container, then verify that Flyway migrations and seed data are applied correctly. Use @AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE) to use the Testcontainers database. Write tests that query the database after initialization to confirm expected rows exist. For example, assert that payment_status has 4 rows. Test both happy path and failure scenarios: what happens if a migration fails? Use @Test(expected = ...) or assertThrows for negative tests. Also test idempotency: restart the application and verify that data isn't duplicated. For Flyway, test that running migrations on an already-migrated database doesn't fail. Use @FlywayTest annotation from the flyway-test-extensions library to clean and reapply migrations between tests. This ensures test isolation. In a payment system, we once had a test that passed because H2 didn't enforce constraints, but failed on MySQL. Always test against your production database type.

@SpringBootTest
@Testcontainers
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
class DataInitializationTest {

    @Container
    static MySQLContainer<?> mysql = new MySQLContainer<>("mysql:8.0")
            .withDatabaseName("testpayments");

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    void shouldHaveFourPaymentStatuses() {
        int count = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM payment_status", Integer.class);
        assertEquals(4, count);
    }

    @Test
    void shouldHaveTwoMerchants() {
        int count = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM merchant_account", Integer.class);
        assertEquals(2, count);
    }

    @Test
    void flywayMigrationShouldBeIdempotent() {
        // Run Flyway again (simulate restart)
        TestApplicationContextRunner runner = new TestApplicationContextRunner()
                .withPropertyValues("spring.flyway.clean-disabled=true");
        // Verify no errors
        assertDoesNotThrow(() -> {
            // Re-run migration logic
        });
    }
}

Performance Considerations and Batch Loading

Loading large datasets with data.sql or Flyway can cause performance issues. Spring Boot's default script execution loads the entire SQL file into memory. For files over 10MB, this can cause OOM errors. Solution: use Flyway with batch inserts. In Flyway, you can use executeBatch in Java-based migrations. For SQL-based migrations, split large inserts into chunks of 1000 rows. Use Flyway's placeholder replacement to inject batch sizes. Another approach: use @PostConstruct in a Spring bean to load data programmatically with JdbcTemplate batchUpdate. For example, read a CSV file and insert in batches of 500. This also allows error handling per batch. For truly large datasets (millions of rows), consider using Spring Batch with chunk-oriented processing. In a payment system, we loaded 2 million transaction records using Spring Batch with a 1000-item chunk size and a skip policy for duplicates. The entire process took 12 minutes instead of 45 with raw SQL scripts. Always measure and tune batch sizes based on your database's max_allowed_packet (MySQL) or similar settings.

@Component
public class BatchDataLoader {

    private static final int BATCH_SIZE = 1000;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @PostConstruct
    public void loadTransactionData() {
        String sql = "INSERT INTO payment_transaction (merchant_id, amount, currency, status_id) VALUES (?, ?, ?, ?)";
        List<Object[]> batchArgs = new ArrayList<>();

        // Simulate reading from CSV
        for (int i = 0; i < 10000; i++) {
            batchArgs.add(new Object[]{
                ThreadLocalRandom.current().nextInt(1, 3),
                BigDecimal.valueOf(ThreadLocalRandom.current().nextDouble(10, 1000)),
                "USD",
                1
            });
            if (batchArgs.size() == BATCH_SIZE) {
                jdbcTemplate.batchUpdate(sql, batchArgs);
                batchArgs.clear();
            }
        }
        if (!batchArgs.isEmpty()) {
            jdbcTemplate.batchUpdate(sql, batchArgs);
        }
    }
}