Tutorials/Testing the data layer

Testing your data layer with @StormTest

A data-layer test usually starts with setup: a DataSource, schema scripts, wiring. Storm's test module replaces that with one annotation, and lets you assert on the SQL a block of code ran.

Series · The Storm way4 min readKotlin

01The task

Test repository and query code against a real schema, fast enough to run on every build, without starting a dependency-injection container or writing connection code in every test class.

02One annotation

@StormTest creates an in-memory H2 database, runs your schema and data scripts once per class, and injects test method parameters. It needs no Spring context and no base class:

OwnerRepositoryTest.kt Kotlin · storm-test
1
2
3
4
5
6
7
8
9
10
@StormTest(scripts = ["/schema.sql", "/data.sql"])
class OwnerRepositoryTest {

    @Test
    fun `finds owners by last name`(orm: ORMTemplate) {
        val owners = orm.entity(Owner::class).findAll(Owner_.lastName eq "Smith")

        owners.size shouldBe 2
    }
}

Parameters resolve by type: ORMTemplate, DataSource, SqlCapture, or any type with a static of(DataSource) factory. Each test runs in a transaction that is rolled back afterwards, so every test starts from the state the scripts created and count assertions can be exact. A transaction { } block inside a test commits and rolls back as usual, on a savepoint. All of a test's connections share that one transaction, so a transaction that begins on another thread while one is open fails and names rollback = false, the setting for tests that need real concurrent transactions.

03Assert the SQL, not only the rows

A test that checks results passes as long as the rows are right, however many queries produced them. SqlCapture records every statement a block runs, with its operation, bound parameters, origin and database time, so the query count becomes an assertion:

PetQueryTest.kt Kotlin · storm-test
1
2
3
4
5
6
7
8
@Test
fun `pet list loads owners in the same query`(orm: ORMTemplate, capture: SqlCapture) {
    val pets = capture.execute { orm.entity(Pet::class).findAll() }

    pets.forEach { render(it.owner.lastName) }   // the owner came with the pet
    capture.count(Operation.SELECT) shouldBe 1   // a second query fails the test
    capture.count(Operation.UPDATE) shouldBe 0   // a read writes nothing
}

"No N+1", "this service only reads" and "the batch runs one statement" each become one line that fails the build when the code changes. count(Origin.FETCH) counts the statements that resolved references, the ones that grow with the number of rows.

The test log is worth reading too. Every @StormTest reports all advice categories, so the log names statements worth changing, such as a distinct over a to-many join or an entity past the design budget, while the test still passes. Statement advice explains how to read it.

04The same tests against PostgreSQL

H2 keeps the loop fast, but it accepts SQL your production database rejects and behaves differently on upserts, sequences, identity columns and keyword escaping. The database attribute runs a test class on the real database, in a Testcontainers container that every test class of the run shares:

PostgresOwnerTest.kt Kotlin · storm-test
1
2
3
4
5
6
7
8
@StormTest(database = TestDatabase.POSTGRESQL, scripts = ["/schema.sql", "/data.sql"])
class PostgresOwnerTest {

    @Test
    fun `finds owners by last name`(orm: ORMTemplate) {
        // the same test body, run on PostgreSQL
    }
}

Scripts, parameter injection, SqlCapture and per-test rollback work as on H2, so both suites share their test code. Each class gets a new, empty database inside the shared container, so the scripts run without drop guards. Add org.testcontainers:testcontainers-postgresql and the PostgreSQL driver to the test classpath; H2 tests need nothing new. MySQL, MariaDB, SQL Server and Oracle work the same way, and the Spring Boot @DataStormTest slice takes the same attribute.

05Keep going

The reference documentation covers the mechanics in depth: