Spring Boot app.properties: How to Wipe Your Dev DB with a Profile (And Why You Should)

Every developer has been there: you're debugging a gnarly N+1 query, your local database is cluttered with test data from three sprints ago, and you can't tell if the bug is in your new code or the corrupted state of your H2 instance. You reach for the nuclear option — dropping and recreating the schema. But doing it manually is error-prone, and copying a production dump takes twenty minutes. There's a better way: a dedicated Spring Boot profile, activated by a single environment variable, that wipes your dev database clean and reinitializes it with fresh seed data.

This isn't just a convenience trick. In my 15 years building payment-processing systems at scale, I've seen production outages caused by a developer accidentally running a 'reset' script against the wrong database. The pattern I'll show you — using a profile with spring.jpa.hibernate.ddl-auto=create-drop and spring.sql.init.mode=always — lets you automate the reset safely. We'll cover how to isolate it with a custom EnvironmentPostProcessor that checks the hostname, how to version-control your seed data in schema.sql and data.sql, and why you should never, ever put create-drop in your default application.properties.

By the end of this article, you'll have a bulletproof setup that takes two seconds to reset your dev database, works with PostgreSQL 16 and H2 2.2.224, and includes a failsafe that prevents accidental execution on any host that isn't your local machine. This is the kind of tool that separates a junior who manually truncates tables from a senior who can deploy a fresh environment in one command.

Setting Up the Dev-Reset Profile

The core idea is simple: create a dedicated properties file named application-dev-reset.properties that overrides your default database settings. When you activate this profile via --spring.profiles.active=dev-reset, Spring Boot will drop all tables, recreate them from your JPA entities, and then execute your schema.sql and data.sql files to seed fresh data.

Start by creating the file in src/main/resources. The key properties are spring.jpa.hibernate.ddl-auto=create-drop, which tells Hibernate to drop the schema at startup and create it again, and spring.sql.init.mode=always, which forces execution of the initialization scripts. You also need to ensure your datasource points to a development database — never production. I use an H2 in-memory database for local development, but you can also point to a PostgreSQL instance with a 'dev' suffix in the database name.

Here's the complete file for a typical setup. Notice that I explicitly set spring.jpa.show-sql=true so you can see exactly what Hibernate is doing during the reset — this is invaluable when debugging schema issues.

# ===== WARNING: DESTRUCTIVE PROFILE =====
# Only activate with --spring.profiles.active=dev-reset
# This will DROP ALL TABLES and recreate them.

# Force Hibernate to drop and recreate schema
spring.jpa.hibernate.ddl-auto=create-drop

# Always run schema.sql and data.sql after schema creation
spring.sql.init.mode=always

# Use a separate dev database — never production!
spring.datasource.url=jdbc:h2:mem:devdb;DB_CLOSE_DELAY=-1
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

# Log all SQL statements for debugging
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

# Disable batch processing during reset to avoid confusion
spring.jpa.properties.hibernate.jdbc.batch_size=1

What the Official Docs Won't Tell You

The Spring Boot reference documentation covers profiles and database initialization, but it glosses over the critical safety patterns you need in real-world deployments. The official docs will tell you that spring.jpa.hibernate.ddl-auto=create-drop works, but they won't warn you that it also drops sequences, functions, and any custom SQL objects you've added outside JPA entities. They also won't tell you that spring.sql.init.mode=always runs before Hibernate's schema generation if you set spring.jpa.defer-datasource-initialization=false (the default), which can cause errors if your schema.sql references tables that don't exist yet.

The hidden gotcha is the order of operations. By default, Spring Boot executes schema.sql and data.sql before Hibernate generates the schema. If you have a schema.sql that creates tables, and then Hibernate tries to create the same tables with create-drop, you'll get 'table already exists' errors. The fix is to set spring.jpa.defer-datasource-initialization=true, which defers the script execution until after Hibernate has created the schema. This is undocumented in the quick start guides but essential for this pattern.

Another undocumented behavior: if you use spring.jpa.hibernate.ddl-auto=create-drop with a connection pool like HikariCP, the pool might hold onto stale connections after the schema drop. This can cause 'connection is closed' errors in subsequent requests. The solution is to set spring.datasource.hikari.max-lifetime=30000 (30 seconds) so connections are recycled quickly after a reset.

# Fix the initialization order
spring.jpa.defer-datasource-initialization=true

# HikariCP connection recycling after schema drop
spring.datasource.hikari.max-lifetime=30000
spring.datasource.hikari.connection-timeout=5000

# Ensure scripts are always executed
spring.sql.init.mode=always
spring.sql.init.schema-locations=classpath:schema.sql
spring.sql.init.data-locations=classpath:data.sql

# Log initialization progress
logging.level.org.springframework.jdbc.datasource.init=DEBUG

Adding a Safety Check with EnvironmentPostProcessor

The biggest risk with a destructive profile is accidentally activating it on the wrong environment. A simple profile name check isn't enough — someone could set SPRING_PROFILES_ACTIVE=dev-reset on a production server through a misconfigured CI/CD pipeline. To prevent this, we implement a custom EnvironmentPostProcessor that inspects the hostname and refuses to start if the dev-reset profile is active on a non-development machine.

Create a class that implements EnvironmentPostProcessor and register it via META-INF/spring.factories. In the postProcessEnvironment method, check if the 'dev-reset' profile is active. If it is, get the local hostname and compare it against a whitelist of allowed hostname patterns (e.g., '.localhost.', '.devbox.', '.*\\.local'). If the hostname doesn't match, log a fatal error and throw an IllegalStateException to prevent the application from starting.

This is a one-time implementation that protects every developer on your team. I've used this pattern in organizations with 200+ microservices, and it has prevented at least three potential staging environment disasters.

package com.example.config;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.env.EnvironmentPostProcessor;
import org.springframework.core.env.ConfigurableEnvironment;
import org.springframework.core.env.Profiles;
import java.net.InetAddress;
import java.util.Arrays;
import java.util.List;
import java.util.logging.Logger;

public class DevResetSafetyProcessor implements EnvironmentPostProcessor {

    private static final Logger LOG = Logger.getLogger(DevResetSafetyProcessor.class.getName());
    private static final List<String> ALLOWED_HOST_PATTERNS = Arrays.asList(
        ".*localhost.*",
        ".*devbox.*",
        ".*\\.local"
    );

    @Override
    public void postProcessEnvironment(ConfigurableEnvironment environment, SpringApplication application) {
        if (environment.acceptsProfiles(Profiles.of("dev-reset"))) {
            String hostname = getHostname();
            boolean allowed = ALLOWED_HOST_PATTERNS.stream()
                .anyMatch(pattern -> hostname.matches(pattern));
            if (!allowed) {
                LOG.severe("BLOCKED: dev-reset profile activated on host: " + hostname);
                throw new IllegalStateException(
                    "Cannot activate 'dev-reset' profile on host '" + hostname +
                    "'. Allowed patterns: " + ALLOWED_HOST_PATTERNS);
            }
            LOG.warning("dev-reset profile activated on allowed host: " + hostname);
        }
    }

    private String getHostname() {
        try {
            return InetAddress.getLocalHost().getHostName().toLowerCase();
        } catch (Exception e) {
            return "unknown";
        }
    }
}

Version-Controlling Seed Data with schema.sql and data.sql

A database reset is only useful if you have reliable seed data to restore. Many teams manually insert test data through a REST client or SQL console, which leads to inconsistent states across developers. The solution is to version-control your initialization scripts alongside your code. Spring Boot automatically picks up schema.sql and data.sql from the classpath when spring.sql.init.mode=always is set.

Your schema.sql should contain any DDL that isn't covered by JPA entities — for example, custom indexes, partial indexes, or materialized views. Your data.sql should contain the minimum set of reference data needed for development: a few customer accounts, some transactions with known states, and perhaps a test API key. Keep it small — no more than 50 rows total — so it executes in milliseconds.

One trick I use: include a comment at the top of data.sql with the date and a brief description of what the seed data represents. This helps when you come back to the project six months later and wonder why certain records exist. Also, use INSERT IGNORE or ON CONFLICT DO NOTHING to make the scripts idempotent — you can run them multiple times without errors.

-- Seed data for development reset
-- Last updated: 2024-03-15
-- Provides baseline customers and transactions for UI testing

-- Insert customers (idempotent)
INSERT INTO customer_account (id, name, email) VALUES
    (1001, 'Alice Johnson', 'alice@example.com'),
    (1002, 'Bob Smith', 'bob@example.com'),
    (1003, 'Carol White', 'carol@example.com')
ON CONFLICT (id) DO NOTHING;

-- Insert transactions with various statuses
INSERT INTO payment_transaction (id, customer_id, amount, status) VALUES
    (5001, 1001, 150.00, 'COMPLETED'),
    (5002, 1001, 200.00, 'PENDING'),
    (5003, 1002, 75.50, 'FAILED'),
    (5004, 1002, 300.00, 'COMPLETED'),
    (5005, 1003, 49.99, 'REFUNDED')
ON CONFLICT (id) DO NOTHING;

Activating the Profile Safely in Different Environments

There are multiple ways to activate the dev-reset profile, and each has its own safety considerations. The most common approach is to pass it as a command-line argument when running the application locally: --spring.profiles.active=dev,dev-reset. This activates both the dev profile (which might configure logging or caching) and the reset profile. You can also set the SPRING_PROFILES_ACTIVE environment variable in your IDE run configuration.

For Docker Compose setups, you can add the profile to the environment section of your service definition. This is useful when you want to reset the database every time you spin up a development container. However, be careful not to commit this change to your main docker-compose.yml — use an override file like docker-compose.dev.yml that's gitignored.

For CI/CD pipelines, never activate the dev-reset profile automatically. Instead, require a manual approval step or a dedicated Jenkins job that only runs on a specific branch. I once saw a CI pipeline that ran integration tests with the dev-reset profile on every commit to main — it worked fine until someone merged a branch that had a different database URL, and the pipeline wiped the shared staging database.

version: '3.8'
services:
  app:
    image: myapp:latest
    environment:
      - SPRING_PROFILES_ACTIVE=dev,dev-reset
      - SPRING_DATASOURCE_URL=jdbc:postgresql://db:5432/devdb
      - DEV_RESET_CONFIRM=true
    depends_on:
      - db
  db:
    image: postgres:16
    environment:
      POSTGRES_DB: devdb
      POSTGRES_USER: devuser
      POSTGRES_PASSWORD: devpass
    ports:
      - "5432:5432"

Testing the Reset Profile with JUnit and Testcontainers

You should write an integration test that verifies the dev-reset profile works correctly — especially if you change your entity model or seed data. The test should start the application with the dev-reset profile, connect to a Testcontainers-managed PostgreSQL instance, and assert that the expected tables exist and contain the seed data.

Use @SpringBootTest with a custom properties source that activates the dev-reset profile. Set spring.datasource.url to the Testcontainers JDBC URL provided by the dynamic container. Then, use JdbcTemplate or a JPA repository to query the database and verify the state. This test acts as a safety net: if someone accidentally breaks the reset mechanism (e.g., by removing a required property), the test fails immediately.

One caveat: this test is inherently destructive — it drops and recreates tables. Never run it in a test suite that shares a database. Always use Testcontainers to spin up an isolated PostgreSQL instance that is destroyed after the test completes.

package com.example;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ActiveProfiles;
import org.testcontainers.containers.PostgreSQLContainer;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertTrue;

@SpringBootTest
@ActiveProfiles("dev-reset")
@Testcontainers
class DevResetProfileTest {

    @Container
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16")
        .withDatabaseName("testdb")
        .withUsername("test")
        .withPassword("test");

    @Autowired
    private JdbcTemplate jdbc;

    @Test
    void shouldCreateTablesAndSeedData() {
        // Verify tables exist
        assertTrue(jdbc.queryForObject(
            "SELECT EXISTS (SELECT FROM information_schema.tables WHERE table_name = 'customer_account')",
            Boolean.class));
        assertTrue(jdbc.queryForObject(
            "SELECT EXISTS (SELECT FROM information_schema.tables WHERE table_name = 'payment_transaction')",
            Boolean.class));

        // Verify seed data
        Integer customerCount = jdbc.queryForObject(
            "SELECT COUNT(*) FROM customer_account", Integer.class);
        assertEquals(3, customerCount);

        Integer transactionCount = jdbc.queryForObject(
            "SELECT COUNT(*) FROM payment_transaction", Integer.class);
        assertEquals(5, transactionCount);
    }
}

Advanced: Conditional Reset with @Profile on Beans

Sometimes you need more than just DDL and seed data — you might want to register mock services, configure different message brokers, or set up test-specific caching during a reset. Spring's @Profile annotation lets you conditionally register beans only when the dev-reset profile is active. This is useful for replacing a real Kafka producer with a mock that logs messages to the console, or for disabling a scheduled task that would interfere with the reset.

Create a configuration class annotated with @Profile("dev-reset") that defines beans you want only during a reset. For example, you might want a CommandLineRunner that prints a summary of the seeded data after the application starts, so the developer knows exactly what's available. Or you might want to disable the Flyway migration if you're using create-drop, since Flyway would conflict with Hibernate's schema generation.

This approach keeps your main configuration clean and makes the reset behavior explicit. It also allows you to add complex initialization logic, such as generating random transaction data for load testing, without polluting your production code.

package com.example.config;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.CommandLineRunner;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Profile;

@Configuration
@Profile("dev-reset")
public class DevResetConfig {

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

    @Bean
    public CommandLineRunner printSeedSummary(JdbcTemplate jdbc) {
        return args -> {
            Integer customers = jdbc.queryForObject(
                "SELECT COUNT(*) FROM customer_account", Integer.class);
            Integer transactions = jdbc.queryForObject(
                "SELECT COUNT(*) FROM payment_transaction", Integer.class);
            log.info("Dev reset complete: {} customers, {} transactions seeded",
                customers, transactions);
        };
    }

    @Bean
    public MockKafkaProducer mockKafkaProducer() {
        return new MockKafkaProducer();
    }
}

Putting It All Together: A Production-Ready Reset Flow

Let's walk through the complete flow from the moment a developer wants to reset their local database. First, they ensure their Docker container for PostgreSQL is running (or they use H2). Then, they start the application with --spring.profiles.active=dev,dev-reset. The custom EnvironmentPostProcessor checks the hostname — if it's not localhost or a devbox, the application refuses to start. If the hostname passes, Spring Boot loads application-dev-reset.properties, which sets ddl-auto=create-drop and defers initialization.

Hibernate drops all existing tables and recreates them from the entity definitions. Then, Spring Boot executes schema.sql for any custom DDL, followed by data.sql for seed data. The DevResetConfig's CommandLineRunner prints a summary. The developer sees the log output confirming the reset, and their application is ready with a clean state.

This entire process takes under 5 seconds. Compare that to manually truncating 15 tables, re-inserting seed data, and hoping you didn't miss a foreign key constraint. The pattern is so effective that we made it a company-wide standard: every Spring Boot service at my current employer has a dev-reset profile with the safety check.

package com.example;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Application {

    public static void main(String[] args) {
        SpringApplication app = new SpringApplication(Application.class);
        // Optional: add a default profile if none specified
        app.setAdditionalProfiles("dev");
        app.run(args);
    }
}

// Command to run:
// java -jar myapp.jar --spring.profiles.active=dev,dev-reset
// Or with environment variable:
// SPRING_PROFILES_ACTIVE=dev,dev-reset java -jar myapp.jar