DEV Community

Cover image for Beyond DBUnit and Raw SQL: Type-Safe Database State in Java Tests
Yury
Yury

Posted on Originally published at Medium AI-assisted

Beyond DBUnit and Raw SQL: Type-Safe Database State in Java Tests

#java #database #testing

If you’ve spent any time writing integration tests in Java or Kotlin against real databases, you’ve likely hit one of these pain points:

  • DBUnit XML/YAML fixtures that rot silently until column renames break CI.
  • Hand-written SQL seeding scripts with zero autocomplete or compile-time safety.
  • Overloaded JPA/Hibernate entities dragged into simple test setups just to insert two rows.

Mocks hide database bugs, and fragile test data makes integration suites a nightmare to maintain.

Enter jdbscript

To solve this, I built jdbscript — a lightweight Java library (Apache 2.0) that treats database seeding like plain Java code with full compiler support.

Instead of writing raw SQL or XML, your database schema is represented as a plain Java or Kotlin interface. Dynamic proxies handle table cleanup, foreign key ordering, and runtime execution under the hood.

Here is what it looks like in practice:

// 1. Define your schema interface
public interface IAppSchema extends IDBSchema {
    IUserRecord users();
    IOrderRecord orders();
}

// 2. Create engine that handles db manipulation
IJDBEngine<IAppSchema> engine = JDBEngine.builder(IAppSchema.class)
    .dataSource(dataSource)
    .build();

// 3. Seed test fixtures cleanly
engine.resetDB(db -> {
    db.users().id(1L).username("alice").email("alice@example.com");
    db.users().id(2L).username("bob").email("bob@example.com");
    db.orders().id(101L).user_id(1L).total_amount(49.99);
});
Enter fullscreen mode Exit fullscreen mode

Full example

Key Highlights

  • Automatic Foreign Key Ordering & Cleanup: resetDB automatically cleans and inserts records in the correct dependency order so constraint violations don't break your suite.
  • Migration Testing: Verify Flyway or Liquibase scripts against real schemas deterministically.
  • Zero Framework Lock-in: Requires Java 17+ and a standard DataSource. Integrates seamlessly with JUnit 5, Testcontainers, and Spring Boot.
  • Broad Database Support: Validated in CI across PostgreSQL, MySQL, MariaDB, Oracle, SQL Server, Google Cloud Spanner, H2, CockroachDB, and DuckDB.

Give It a Spin

jdbscript v1.3.0 is live on Maven Central. (this version replaced old solution in a 9 years project)

There are 12 runnable examples (featuring Java & Kotlin with Spring Boot, Testcontainers, and Flyway) at jdbscript.org.

Check out the repo, try pointing it at your dev schema, and let me know how it fits into your testing workflow!

Top comments (0)