JDBScriptDatabase Test State Setup

Why JDBScript? ​

Setting up and verifying relational database fixtures in Java tests has traditionally been painful and fragile. JDBScript bridges this gap:

• Compile-Time Checking
  • Problem: Raw SQL strings and XML/JSON datasets fail at runtime when schemas change, offering zero compiler feedback or IDE safety.
  • Solution: Schemas are defined as typed Java interfaces. Renaming a column or changing a type triggers compile errors immediately.
• No XML or Raw SQL
  • Problem: DbUnit-style XML datasets are tedious to maintain, difficult to refactor, and decouple data definitions from your code.
  • Solution: Construct test datasets using fluent Java syntax, full IDE autocomplete, reusable fixtures, and smart default generators.
• Automatic FK-Ordering
  • Problem: Inserting or cleaning up dependent records requires manually managing topological order to avoid foreign key constraint violations.
  • Solution: JDBScript introspects foreign key metadata automatically, guaranteeing correct insertion order and safe reverse teardown.
• Multi-DBMS Compatibility
  • Problem: Raw SQL fixtures and DB-specific tools break when running tests across different DBMSs (e.g., in-memory databases locally vs PostgreSQL/Oracle in CI).
  • Solution: A single unified Java fixture runs transparently across 12+ supported databases including PostgreSQL, MySQL, Oracle, SQL Server, SQLite, DuckDB, and H2 with automatic dialect and sequence handling.

JDBScript vs DbUnit ​

While DbUnit established dataset-driven testing in Java, maintaining external XML/YAML datasets imposes recurring maintenance overhead as applications evolve.

Quickstart ​

Add the jdbscript dependency to your project's test scope:

<dependency>
    <groupId>org.jdbscript</groupId>
    <artifactId>jdbscript</artifactId>
    <version>1.3.0</version>
    <scope>test</scope>
</dependency>

testImplementation("org.jdbscript:jdbscript:1.3.0")

testImplementation 'org.jdbscript:jdbscript:1.3.0'

Code Example ​

1. Declare Your Schema Interface ​

Define lightweight interfaces extending IDBSchema and IDBRecord to reflect your tables and columns:

import org.jdbscript.IDBSchema;
import org.jdbscript.IDBSchema.IDBRecord;

public interface IAppSchema extends IDBSchema {
    IUserRecord users();
    IOrderRecord orders();

    interface IUserRecord extends IDBRecord {
        IUserRecord id(Long id);
        IUserRecord username(String username);
        IUserRecord email(String email);
        IUserRecord active(Boolean active);
    }

    interface IOrderRecord extends IDBRecord {
        IOrderRecord id(Long id);
        IOrderRecord user_id(Long userId);
        IOrderRecord total_amount(Double amount);
    }
}

2. Seed and Reset Test State with resetDB ​

Initialize JDBEngine and wipe tables & insert fixtures in a single fluent call:

import org.jdbscript.JDBEngine;
import org.jdbscript.IJDBEngine;
import javax.sql.DataSource;

DataSource dataSource = getDataSource();
IJDBEngine<IAppSchema> engine = JDBEngine.builder(IAppSchema.class)
    .dataSource(dataSource)
    .build();

// Wipes schema tables and inserts records in FK-safe order
engine.resetDB(db -> {
    db.users().id(1L).username("alice").email("[email protected]").active(true);
    db.users().id(2L).username("bob").email("[email protected]").active(false);
    db.orders().id(1001L).user_id(1L).total_amount(59.99);
});

3. Verify Database State with assertDB ​

Assert that expected records exist or do not exist using the same intuitive syntax:

// Assert that records exist in the database
engine.assertDBHas(db -> {
    db.users().username("alice").active(true);
    db.orders().user_id(1L).total_amount(59.99);
});

// Assert that records do not exist
engine.assertDBHasNot(db -> {
    db.users().username("charlie");
});

Code Recipes & Examples ​

Runnable, self-contained example projects demonstrating real-world usage patterns across different stacks:

Core Patterns & Fixture Setup ​

• Quickstart (01-quickstart) — Standard JUnit 5 Arrange-Act-Assert lifecycle with type-safe schema fixtures.
• RecordTools & Defaults (03-recordtools-defaults) — Avoid dummy fixture boilerplate by populating only the columns relevant to your test while smart defaults automatically populate non-essential NOT NULL columns.
• Class Scripts & Composition (02-class-scripts-and-include) — Avoid copy-pasting shared test data by composing modular baseline scripts, and perform type-safe state transitions mid-test.
• Scripting Power (04-scripting-power) — Generate large-scale test datasets in a few lines of Java code without bloated fixture files or flaky, non-deterministic random data.
• Interleaved Insertion (06-insert-power) — Append new records mid-test with insertDB to simulate incoming events and state progressions without wiping existing data.

Schema & Data Migration Testing ​

• Liquibase Data Migration (09-liquibase-data-migration) — Verify column backfills and transformations by seeding pre-migration shape and asserting post-migration state.
• Flyway Data Migration (10-flyway-data-migration) — Test Flyway version migrations step-by-step with intermediate state assertions.

Advanced Modeling ​

• Domain DSLs & Aggregates (11-domain-dsl-and-helpers) — Seed complex multi-table business entities cleanly by extending schemas with domain helper methods via engine.as(...).
• Custom Converters (05-custom-converters) — Mapping domain value objects (e.g., Money) to JDBC columns via IJDBTypeConverter.

Frameworks & Ecosystem ​

• Spring Boot (08-springboot) — Seed and reset database state in @SpringBootTest integration tests via auto-configured DataSource.
• Testcontainers (07-testcontainers) — Manage fixture state and test data seamlessly in Testcontainers (e.g. PostgreSQL) via standard DataSource integration.
• Kotlin DSL (12-kotlin-dsl) — Write concise, type-safe database schemas and test fixtures in Kotlin test suites.