sql - Creating temporary database that works across maven test phases?

Question

I've joined a project that has a lot of files with SQL statements for creating a database that is used for integration testing.

I'm wondering how I can use these files to create a database for unit testing (using java and maven).

I can create a HSQL in-memory database for each unit test, or even use the spring jdbc embedded-database feature, but there's so many SQL statements to execute in the test setup that this is not scalable.

So I'd like to create a temporary database (that loads the SQL statements) at the start of the maven test phase, have the unit tests access this temporary database and perform various operations, then delete the temporary database at the end of the maven test phase.

I've looked at sql-maven-plugin which would allow me to do the test phase executions, but I'm not sure how to configure a temporary database that will be available across all unit tests. There's no server to connect to, and in-memory database will not work across multiple unit tests (I assume).

One option could be to use a unique temporary file, e.g. specifying the JDBC driver URL as jdbc:hsqldb:file:/path/to/temporary/file, but I'm not sure how to generate a unique temporary file in maven.

Any suggestions on how to do this, or if there's a better approach to take?

Update: I decide to use a file-based database created in target/db directory. I use the maven clean plugin to remove the target/db directory before tests are run, and the maven sql plugin to create the database from scripts.

Answer 1

For this case I have created the derby-maven-plugin. It's available from Maven Central, so you don't need to add any extra repositories or anything.

You could use it like this:

    <project ...>
        <build>
            <plugins>
                <plugin>
                    <groupId>org.carlspring.maven</groupId>
                    <artifactId>derby-maven-plugin</artifactId>
                    <version>1.8</version>
                    <configuration>
                        <basedir>${project.build.directory}/derby</basedir>
                        <port>1527</port>
                    </configuration>
                    <executions>
                        <execution>
                            <id>start-derby</id>
                            <phase>pre-integration-test</phase>
                            <goals>
                                <goal>start</goal>
                            </goals>
                        </execution>
                        <execution>
                            <id>stop-derby</id>
                            <phase>post-integration-test</phase>
                            <goals>
                                <goal>stop</goal>
                            </goals>
                        </execution>
                    </executions>
                </plugin>
            </plugins>
        </build>
    </project>

For more info you can also check the USAGE.