Spring Boot Persistence: Design, Migrate, Test

Updated 6 November 2026 · Standard formula, shown below

This article explains how to design, migrate, and test persistence in Spring Boot for production use. You will learn the core concepts, practical steps, and common pitfalls, with concrete examples you can apply immediately.

Background: What Persistence Means in Spring Boot

Spring Boot simplifies database access by auto-configuring data sources, connection pools, and ORM tools. The default stack uses Spring Data JPA (Hibernate) for relational databases, but you can also use JDBC templates, MyBatis, or reactive drivers. The key design decision is choosing between an object-relational mapping (ORM) approach and a plain SQL approach. ORM maps Java entities to tables and handles relationships, while SQL gives you full control over queries. Many production systems use a hybrid: JPA for CRUD and native queries for complex reporting.

How to Design Your Persistence Layer

Start by defining your domain model as plain Java classes (entities). Annotate them with @Entity, specify the table name, and set primary keys with @Id and @GeneratedValue. Use @Column to define column names, lengths, and nullability. For relationships, use @OneToMany, @ManyToOne, and @ManyToMany, but be careful with lazy loading and cascade options.

Use @Version for optimistic locking to prevent lost updates.
Define repositories as interfaces extending JpaRepository or CrudRepository.
Add custom query methods using derived names or @Query annotations.
Set spring.jpa.hibernate.ddl-auto=none in production to avoid schema changes from entities.

For multi-tenant applications, consider separate schemas or a tenant discriminator column. For high-read systems, add read replicas and configure routing datasources. Always separate read and write operations if possible.

How to Migrate Your Database Schema

Schema migration is the controlled process of changing your database structure over time. The two most common tools are Flyway and Liquibase. Flyway uses versioned SQL scripts (e.g., V1__create_users.sql) that run in order. Liquibase uses XML, YAML, or JSON changelogs. Both integrate with Spring Boot via spring.flyway.enabled=true or spring.liquibase.enabled=true.

Add the dependency (e.g., org.flywaydb:flyway-core).
Place migration scripts in src/main/resources/db/migration (Flyway) or db/changelog (Liquibase).
Write incremental changes: never edit an applied migration; create a new one.
Test migrations on a staging database that mirrors production data volume.
Use spring.flyway.baseline-on-migrate=true when adopting Flyway on an existing database.

For zero-downtime deployments, use expand-and-contract: add new columns first, deploy code, then backfill and remove old columns in a later release. Avoid destructive operations like dropping tables in the same release that changes code.

How to Test Persistence for Production

Testing is critical because database bugs often appear only with real SQL and data. Use the following layers:

Unit tests: Test repository methods with an in-memory database like H2. Annotate with @DataJpaTest to load only JPA components.
Integration tests: Use Testcontainers to run a real database (PostgreSQL, MySQL) in Docker. This catches dialect-specific issues.
Migration tests: Run Flyway/Liquibase against a clean database and verify the schema matches your entities.
Performance tests: Execute queries with realistic data volumes to detect N+1 problems or missing indexes.

Example of a @DataJpaTest:

@RunWith(SpringRunner.class)
@DataJpaTest
public class UserRepositoryTest {
    @Autowired
    private UserRepository userRepository;

    @Test
    public void testFindByEmail() {
        User user = new User("alice@example.com");
        userRepository.save(user);
        Optional<User> found = userRepository.findByEmail("alice@example.com");
        assertThat(found).isPresent();
    }
}

For integration tests with Testcontainers, add @SpringBootTest and @Testcontainers annotations. Always clean up test data between runs to avoid flaky tests.

Common Problems and How to Avoid Them

One frequent issue is the N+1 query problem: fetching a list of entities and then accessing a lazy relationship for each row, causing many SQL queries. Fix it with @EntityGraph or a join fetch in JPQL. Another issue is using ddl-auto=update in production, which can alter tables unexpectedly. Always set it to none and rely on migrations.

Connection pool exhaustion happens when connections are not released. Use HikariCP (default) with sensible maximum pool size (e.g., 10-20 per instance). Set timeouts for idle connections. Also, beware of long-running transactions that hold connections and locks.

Database-specific SQL can break when switching databases. Use JPQL or Spring Data projections instead of native queries unless necessary. If you must use native SQL, test on the same database as production.

Choosing Between JPA and JDBC

JPA is productive for CRUD and simple relationships, but it adds complexity and performance overhead. JDBC with JdbcTemplate gives you full control and is faster for bulk operations. Consider your team’s skills and the application’s read/write ratio. For microservices with simple data needs, JPA is often sufficient. For high-performance analytics or complex SQL, use JDBC or MyBatis.

You can also mix both: use JPA for transactional writes and JDBC for read-only queries. Spring Boot allows both in the same application. Just ensure transaction boundaries are clear.

Costs and Performance Considerations

Persistence design affects infrastructure costs. Inefficient queries increase CPU and I/O, leading to larger database instances. Use indexes on columns used in WHERE and JOIN clauses. Monitor slow query logs. Consider caching with Redis or Spring Cache for read-heavy workloads, but be careful with cache invalidation.

Connection pool size should be tuned based on concurrent requests and database limits. Too many connections waste memory; too few cause timeouts. Start with 10 per instance and adjust based on load testing.

Safety and Data Integrity

Always use transactions for multi-step writes. Annotate service methods with @Transactional and set rollback rules. Use optimistic locking (@Version) to prevent concurrent updates. For financial data, consider pessimistic locking with SELECT FOR UPDATE.

Encrypt sensitive data at rest and in transit. Use environment variables or a secret manager for database credentials. Never log SQL statements containing sensitive data. Validate all input to prevent SQL injection, even when using JPA (use parameter binding).

Frequently Asked Questions

What is the difference between Flyway and Liquibase?

Flyway uses SQL scripts with a simple versioning scheme; Liquibase uses XML/YAML/JSON changelogs that abstract database-specific SQL. Flyway is easier to start, while Liquibase offers more control and can generate changes for multiple databases. Choose based on your team’s preference and database portability needs.

Should I use `ddl-auto=update` in production?

No. It can alter your schema automatically, which is risky in production. Use none and manage schema changes with migration tools. This ensures changes are reviewed and versioned.

How do I test migrations without affecting production?

Run migrations on a staging database that mirrors production structure and data. Use Testcontainers in CI to test against a real database. You can also use Flyway’s clean and migrate on a disposable database.

What is the N+1 query problem and how do I fix it?

It occurs when you load a list of entities and then access a lazy relationship for each one, causing one query per row. Fix by using @EntityGraph to eager fetch the relationship in a single query, or use a join fetch in JPQL.

Can I use multiple databases in one Spring Boot application?

Yes, you can configure multiple datasources and assign them to different repositories. Use @Primary for the main one and define separate DataSource beans. Ensure transaction managers are properly configured per datasource.

How do I handle database connection failures gracefully?

Use connection pool settings like connection-timeout and validation-timeout. Implement retry logic with Spring Retry for transient failures. Also, use health indicators to check database status and fail fast if the database is down.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.