Using Grails With Apache Derby For Embedded Database Testing

An embedded database can make Grails tests quicker, more portable and easier to run on a developer workstation. Apache Derby is a practical choice when a test suite needs a relational database without requiring a separate server, Docker container or managed cloud instance. The database runs inside the test process, creates its files locally and can be removed after the test run.

This approach suits Java developers moving into Groovy and Grails because Derby supports familiar SQL and JDBC concepts while keeping the setup relatively small. The examples below focus on integration testing, test data isolation, configuration by environment and the small operational details that help a build behave consistently on laptops, CI agents and Australian development teams working across Sydney, Melbourne, Brisbane or Perth.

Why Derby Fits A Grails Test Environment

Apache Derby has two useful modes: embedded mode, where the application loads the database engine in the same JVM, and network server mode, where Derby accepts connections over a port. For Grails integration tests, embedded mode is usually the simpler option. There is no database service to start, no credentials to distribute and no port conflict when several builds run on the same machine.

Grails applications commonly use GORM with Hibernate. Derby can act as the JDBC database behind Hibernate, allowing domain classes, constraints, relationships and transactional behaviour to be tested against a real relational engine. That gives a more realistic result than relying exclusively on mocked repositories or in-memory collections.

A Derby test database does not need to imitate production in every detail. If the production system uses PostgreSQL, MySQL or Microsoft SQL Server, database-specific queries and migration scripts still deserve separate verification. Derby is most valuable for testing Grails application behaviour: persistence rules, service transactions, validation, controllers that load domain data and scheduled jobs that update records.

Australian teams often value this low-maintenance setup when developers work from home or across offices. A new team member in Adelaide can clone the project and run the test suite without requesting access to a shared database in Sydney. It also avoids dependence on office VPNs, which can be inconvenient during a short pairing session or an unstable NBN connection.

For practical Grails lessons, Grails Example tutorials provide useful background on application structure, commands and framework conventions. Those fundamentals make the database configuration easier to understand because the test environment is an extension of the normal Grails application lifecycle rather than a separate testing product.

Adding The Derby Driver And Test Configuration

The exact dependency syntax depends on the Grails version and build system. A Grails application using Gradle can declare Derby in build.gradle. In many projects, the dependency is placed on the runtime classpath because the application needs the JDBC driver when the test environment starts:

dependencies {
    runtimeOnly 'org.apache.derby:derby:10.17.1.0'
}

Use a Derby version compatible with the Java version supported by the project. Older Grails applications may use runtime instead of runtimeOnly, while some Hibernate integrations expect an additional Derby tools or network-client module. Check the project’s existing dependency conventions before changing the configuration, and keep the version in dependency management where possible.

The test environment can define a separate datasource in grails-app/conf/application.yml. A file-based embedded database is convenient because it survives for the duration of the test run and can be inspected while diagnosing a failure:

environments:
    test:
        dataSource:
            dbCreate: create-drop
            pooled: false
            url: jdbc:derby:build/test-db;create=true
            driverClassName: org.apache.derby.jdbc.EmbeddedDriver
            username: app
            password: app
            dialect: org.hibernate.dialect.DerbyTenSevenDialect

The dialect name may vary with the Hibernate and Derby versions in use. Some Grails releases use a different Hibernate dialect class, so an application should follow the dialect documented for its dependency combination. A wrong dialect can produce confusing schema-generation errors even when the JDBC driver is installed correctly.

A memory-backed URL can reduce filesystem cleanup:

url: jdbc:derby:memory:testdb;create=true

However, file-based storage is often easier to inspect. The build/test-db directory can be removed by Gradle’s clean task or by a test fixture after a failed run. Never place this database under a source-controlled directory, and avoid pointing the test profile at a developer’s normal Derby database. A mistaken dbCreate setting can destroy useful local data.

Building Reliable Grails Integration Tests

A Grails integration test loads the application context and can exercise GORM, dependency injection and transactions together. A simple specification might create a domain object, save it and query it through a service:

import grails.testing.mixin.integration.Integration
import grails.gorm.transactions.Rollback
import spock.lang.Specification

@Integration
@Rollback
class CustomerServiceSpec extends Specification {

    CustomerService customerService

    void "it stores a customer in Derby"() {
        when:
        def customer = customerService.register("Amelia", "amelia@example.com")

        then:
        customer.id
        Customer.count() == 1
    }
}

The exact test annotations differ between Grails generations. Older applications may use @TestFor, @Mock or legacy integration-test conventions, while newer versions commonly use grails.testing packages. The important point is that Derby must be available when the Grails application context creates the datasource and Hibernate session factory.

@Rollback is useful because each test can run within a transaction that is rolled back after execution. This gives tests a clean logical state without recreating the schema for every method. It does not replace good fixture design: non-transactional work, asynchronous jobs and explicit commits can still leave records behind. When a test deliberately verifies commit behaviour, clean the relevant data in a controlled setup or teardown method.

Use builders or fixture helpers for repeatable test data. A customer specification should not depend on a record inserted by another specification, and tests should not rely on execution order. Unique emails, deterministic dates and explicit relationship setup make failures easier to reproduce. This is particularly useful when a CI pipeline runs in UTC while developers in Australia inspect failures during AEST or AEDT business hours.

Derby also exposes SQL and schema differences that a mock cannot reveal. It can identify invalid column mappings, incorrect join assumptions, missing indexes in a query plan and constraints that are not represented in a unit test. Keep fast unit tests for pure business rules, then reserve Derby-backed integration tests for persistence and application-context behaviour.

Managing Schema State, Fixtures And Derby Files

dbCreate: create-drop is convenient for an isolated test database. Grails creates the schema when the test environment starts and drops it when the session factory shuts down. This is appropriate for many local and CI runs, although a failed or forcibly terminated process can leave Derby files behind. A later run may then report that the database already exists or is locked.

A reliable cleanup routine should stop the test process normally and remove the generated database directory when necessary. Do not delete Derby files while the JVM still has an open connection. On Windows workstations, file locks can remain visible for a short time after a test runner stops; on macOS and Linux, the same problem may appear as a stale lock or permission error.

For teams using database migrations, test the migrations rather than allowing Hibernate to silently create a schema. Tools such as Liquibase or the Grails database-migration plugin can apply versioned changes to a clean Derby database. This verifies that a new developer, a CI agent or a production deployment can reproduce the intended schema sequence.

There is an important distinction between schema portability and production equivalence. Derby has its own reserved words, data types, identity behaviour and SQL restrictions. A migration that works in Derby may still fail in PostgreSQL. Keep a second test stage against the production database engine for vendor-specific SQL, while using Derby for fast feedback on the majority of persistence workflows.

Useful fixture practices include:

• Create required records inside each specification.
• Keep generated database directories outside source control.
• Use explicit cleanup for non-transactional operations.
• Test migrations separately from Hibernate auto-creation.

When several builds run at once, give each process a unique database path. A CI job number or temporary directory can be included in the JDBC URL. Without isolation, two parallel Gradle jobs may open the same Derby files and fail with a lock exception. Parallel execution is common in hosted build systems, so this detail matters even if a laptop runs the suite sequentially.

Diagnosing Failures And Connecting The Workflow

The first diagnostic step is to confirm that the Derby driver is on the runtime classpath used by the test task. A ClassNotFoundException for EmbeddedDriver usually indicates a dependency scope or version problem. A “database already exists” message often points to a stale directory, while a lock error usually means another JVM still owns the files.

Enable SQL logging only while investigating, because verbose output can make a CI log difficult to read. Hibernate SQL and parameter logging can show whether the application is issuing the expected insert, update or join. Derby’s error message may appear several layers below a Grails exception, so inspect the root cause rather than stopping at the first HibernateException.

A useful workflow starts with a unit test, continues with a Derby integration test and finishes with a production-engine test where required. Run the fast tests during ordinary development, then execute the full suite before a merge. In a local Australian team, a developer in Melbourne might run the same Gradle command as a teammate in Brisbane, with no shared database state affecting either result.

The Masha Leilm resource can sit alongside framework documentation as part of a broader learning workflow, especially when developers are building confidence with Java, Groovy and web application concepts. Keep the project’s own README explicit about the Grails version, Java version, Derby dependency, test command and cleanup path so the setup remains understandable months after it was created.

A small checklist helps distinguish application defects from environment defects:

• Confirm the Java and Grails versions used by CI.
• Verify the JDBC URL and Derby driver class.
• Remove stale database files after interrupted runs.
• Re-run the failing specification in isolation.

Derby testing also benefits from a clear boundary around external services. Do not make a database integration test depend on a live payment gateway, email provider or Australian address-validation service. Stub those integrations while allowing Derby to verify the local transaction. This keeps the test repeatable and avoids charges or unexpected network calls during a build.

A useful next step is to document the test profile and add one representative specification for each important persistence pattern: a simple save, a relationship query, a validation failure and a transactional rollback. Review the setup during code changes, and use the contact page when a Grails-specific example or clarification is needed. A well-defined embedded database workflow gives developers fast feedback while preserving a clear path to testing against the production database engine before release.