Provide easy way to clear / repopulate a database in tests without restarting the application

[geoand]

Description
Users often want to clear and repopulate their database before every test.
We should provide some out of the box functionality to make that easy when Hibernate and / Flyway or Liquibase are used.

Implementation ideas
@famod IIRC, you already had something like this?

[famod]

In my previous project we used database-native features to import/export dumps:

• H2 (which we used in most tests): SCRIPT ... (https://www.h2database.com/html/commands.html#script)
• PostgreSQL: CREATE DATABASE dump USING TEMPLATE ...; (https://www.postgresql.org/docs/10/sql-createdatabase.html)

We triggered those via QuarkusTestBefore/AfterEachCallback. If interested I can try to get the permission to contribute the code (shouldn't be a problem, I think).

Although we were also using Liquibase for the general database init and productive migrations in that project, we did not use it for database resets in tests.
We did that in a couple of previous projects and sooner or later it always reached a point where it became unbearably slow as our migrations grew. It certainly depenends on the structure and size of your application, though.

I've never used Flyway. Might be much faster.
See also https://github.com/radcortez/flyway-junit5-extensions by @radcortez.

FTR: https://quarkusio.zulipchat.com/#narrow/stream/187030-users/topic/Clear.20H2.20database.20between.20tests

[radcortez]

If there is interest, we can pull some of the stuff I have in the JUnit + Flyway Extension.

I actually wanted to have it in the Flyway project, but their test annotations are heavily tied to Spring, so I had to write new ones, more generic. This extension is not tied to Quarkus in anyway, so it works as long as you can provide the Datasource that you want to clear / repopulate.

If we make it (or pull it to be part of Quarkus) we can integrate it a little bit better. Anyway, I usually point users to that extension when they request something similar :)

[geoand]

I'm plus one for having a Quarkus specific - more tightly integrated way of doing what your extension does

[famod]

@radcortez I suppose as a user of such a Flyway-based reset feature I would need to write Flyway-compatible migrations?
What if I want to use Liquibase or yet another tool or home grown solution for production? Wouldn't I end up maintaining two sets of migrations/db scripts?

[radcortez]

@radcortez I suppose as a user of such a Flyway-based reset feature I would need to write Flyway-compatible migrations?

Well, yes it is the junit-flyway-extension :)

What if I want to use Liquibase or yet another tool or home grown solution for production? Wouldn't I end up maintaining two sets of migrations/db scripts?

Ideally, you should be using some sort of tool to handle your database schema / migrations, being Liquibase / Flyway or something else. In that case, it makes sense that the same tool is applied and used into your tests. This allows you to reuse whatever scripts you require to migrate your prod databases, and at the same time test these scripts.

I think that we may need to provide ways for these tools to hook up into tests and do their thing. We can certainly provide some generic way, but again, if I'm already using one of these tools and have a bunch os script, I don't want to write new schemas / statements in different way just for testing.

[geoand]

Looking at your extension @radcortez, I think we should pretty much copy what you did for Quarkus with the change being that it would be super tightly integrated.

[famod]

Wouldn't mixing flyway and liquibase be a bit confusing?

[geoand]

Mixing? How so?

[radcortez]

Looking at your extension @radcortez, I think we should pretty much copy what you did for Quarkus with the change being that it would be super tightly integrated.

Sure. Feel free to copy what you want. Or if you prefer, I can pick this up and do it.

[geoand]

Go ahead :)

[famod]

@geoand

Mixing? How so?

junit-flyway-extension is based on flyway, so what does that mean if I'm using liquibase to manage my migrations (in general, not just for test)?

[radcortez]

I guess we just write a similar one for liquibase. Or ideally, try to come up with a way that is able to use one or the other transparently in the API.

[overheadhunter]

@radcortez I would love to see junit-flyway-extension adapter integrated with Quarkus!

As at the time of writing this, @FlywayTest doesn't reset the database in nested tests yet, I thought it would be worth pointing out that Quarkus' support for @Nested is currently not working properly, either (see #19845). So you might want to keep an eye on this nesting issue as well, as it may affect your extension.

[radcortez]

@radcortez I would love to see junit-flyway-extension adapter integrated with Quarkus!

I'm happy to integrate it directly, I just need to find the time to do it :)

As at the time of writing this, @FlywayTest doesn't reset the database in nested tests yet

Ah, I can probably look into this and fix it in the extension directly. Feel free to report any bugs there for now.

[gastaldi]

If you're using Flyway, you just need to have your tests to extend an abstract class like the following:

import javax.inject.Inject;

import org.flywaydb.core.Flyway;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;

public abstract class BaseTest {

    @Inject
    Flyway flyway;

    @BeforeEach
    void migrate() {
        flyway.migrate();
    }

    @AfterEach
    void clean() {
        flyway.clean();
    }
}

[Manfred73]

I tried the BaseTest approach by injecting Flyway, but unfortunately @QuarkusIntegrationTest does not allow to use @Inject.

org.junit.platform.commons.JUnitException: @Inject is not supported in @NativeImageTest and @QuarkusIntegrationTest tests. Offending field is com.mycompany.integrationtests.TestIntegrationBase.flyway

I've also tried adding @TestTransaction to each integration test class, but that doesn't help.

I'm using h2 database for testing (stored to filestystem).

[gastaldi]

Instead of injecting, you may probably lookup the Flyway instance using Arc.container() but I haven't tested TBH

[Manfred73]

I see that I have a dependency quarkus-arc included. Is that sufficient?
Do you have an example on how that should be done?

[gastaldi]

Arc.container().instance(Flyway.class) should return the Flyway managed object

[Manfred73]

When I do that in a @beforeeach, Arc.container() returns null.

[gastaldi]

Ah my bad. Probably because integration tests don't run in the same instance as the app. Try creating a JAX-RS endpoint and invoking it in your test using RedtAssured, similar to what's done in https://github.com/quarkusio/quarkus/blob/main/integration-tests/flyway/src/main/java/io/quarkus/it/flyway/FlywayFunctionalityResource.java

[Manfred73]

OK, I'll have a look at that.

Initially I was not using flyway at all (since we're using another framework within the company to deal with db changes). I was looking for a way to create the schema based on a schema.sql instead of using the entities (as we do with spring), but couldn't find a way to do that with Quarkus (#30193), except for using the Flyway approach.

So for now I'm using flyway so I get the correct schema creation.

[Manfred73]

Implementing a FlywayResource with a migrate and clean endpoint did the trick, although it's a bit cumbersome that you have to implement a specific resource for this.

The thing I'm not sure about is that we're now adding something to the production code only for testing purposes. Also we should add restrictions that not just anybody can call these endpoints. And of course also add unittests for the newly introduced code ;-)

I now have a FlywayResource as such:

@Path("/v1/flyway")
public class FlywayResource {

	@Inject
	Flyway flyway;

	@Inject
	FlywayModelCreator flywayModelCreator;

	@GET
	@Path("migrate")
	@Consumes(MediaType.APPLICATION_JSON)
	@Produces(MediaType.APPLICATION_JSON)
	public FlywayModel migrate() {
		flyway.migrate();
		return flywayModelCreator.createMigrateResult(
				Objects.requireNonNull(flyway.info().current().getVersion(), "Version is null! Migration was not applied"));
	}

	@GET
	@Path("clean")
	@Consumes(MediaType.APPLICATION_JSON)
	@Produces(MediaType.APPLICATION_JSON)
	public FlywayModel clean() {
		final var cleanResult = flyway.clean();
		return flywayModelCreator.createCleanResult(cleanResult);
	}
}

The FlywayModelCreator:

@ApplicationScoped
public class FlywayModelCreator {

	public FlywayModel createMigrateResult(MigrationVersion migrationVersion) {
		return FlywayModel.builder().version(migrationVersion.toString()).build();
	}

	public FlywayModel createCleanResult(CleanResult cleanResult) {
		return FlywayModel.builder().schemasCleaned(cleanResult.schemasCleaned).schemasDropped(cleanResult.schemasDropped).build();
	}
}

And the model itself:

@Getter
@Builder(toBuilder = true)
@EqualsAndHashCode
@AllArgsConstructor
@NoArgsConstructor
@RegisterForReflection
@Jacksonized
@JsonPropertyOrder({
		"version",
		"schemasCleaned",
		"schemasDropped"
})
public class FlywayModel implements Serializable {

	@Serial
	private static final long serialVersionUID = 5294631637987570767L;

	private String version;

	@Singular("schemaCleaned")
	private List<String> schemasCleaned;

	@Singular("schemaDropped")
	private List<String> schemasDropped;
}

And then an integrationtest as such:

class CommandResourceSuccessTest extends TestIntegrationBase {

     // some test here which writes to database
}

And the base class:

class TestIntegrationBase {

	@BeforeEach
	void migrate() {
		given().contentType(ContentType.JSON).when().get("/v1/flyway/migrate").then().statusCode(200);
	}

	@AfterEach
	void clean() {
		given().contentType(ContentType.JSON).when().get("/v1/flyway/clean").then().statusCode(200);
	}

Mind though that we seem no longer be able to use the @TestHTTPEndpoint on the integration test itself as such:

@TestHTTPEndpoint(CommandResource.class)
class CommandResourceSuccessTest extends TestIntegrationBase {

     // some test here which writes to database
}

because this will mess up the flyway path. If the baseUrl for the CommandResource would be "/v1/command", then the flyway url would become "/v1/command/v1/flyway" instead of "/v1/flyway".

So I ended up removing the @TestHTTPEndpoint and explicitly set the url in the post/get, etc.:

class CommandResourceSuccessTest extends TestIntegrationBase {

	@Test
	void expect_success_response {
                // GIVEN
		final var myObject = MyObject.toBuilder().firstName("John").lastName("Doe").build();

		// WHEN / THEN
		final var result = given()
				.contentType(ContentType.JSON)
				.body(myObject)
				.when()
				.post("/v1/command")     // explicitly set base url without using @TestHTTPEndpoint
				.then()
				.statusCode(400)
				.extract()
				.jsonPath();
		// some assertions
	}
}

[Manfred73]

Ah my bad. Probably because integration tests don't run in the same instance as the app. Try creating a JAX-RS endpoint and invoking it in your test using RedtAssured, similar to what's done in https://github.com/quarkusio/quarkus/blob/main/integration-tests/flyway/src/main/java/io/quarkus/it/flyway/FlywayFunctionalityResource.java

Indeed: https://stackoverflow.com/questions/71022392/quarkus-quarkustest-vs-quarkusintegrationtest

[gastaldi]

You can also create an integration-tests module and put that resource there to avoid polluting your production code

[Manfred73]

Would there be any other way to clear de database before/after running each QuarkusIntegrationTest?

The above with Flyway works, but unfortunately we can no longer use Flyway. The company uses it's own tooling for rolling out db changes.
At the moment we have a real db2 database which we also want to use for our tests (instead of using H2).

For simple @QuarkusTest (or even unit tests without using @QuarkusTest), where we test the repository, we can clear the database ourselves since we can inject some SessionFactory in the test and do something like this:

/**
 * <pre>
 * The property {@code quarkus.hibernate-orm.sql-load-script=./import.sql} only works in combination with the property
 * {@code quarkus.hibernate-orm.database.generation=drop-and-create}.
 *
 * Since we now use a real db2 database instead of H2 in-memory (and db changes are propagated by the company tool),
 * we can no longer use Flyway to drop and create schema. The db user used in the application doesn't have the correct 
 * rights for doing so, so we can only delete the contents. The rollout of db changes is a separate process.
 *
 * In the {@code @BeforeEach} setup we load the {@code import.sql} ourselves which has native delete and insert 
 * statements.
 * In the {@code @AfterEach} we load the {@code delete.sql} which will clean the database.
 * </pre>
 */
@QuarkusTest
@Transactional
class MyRepositoryTest {

    @Inject
    MyRepository repository;

    @Inject
    SessionFactory sessionFactory;

    @BeforeEach
    void setup() {
        try (final var ss = sessionFactory.openStatelessSession()) {
            final var sqlStatements = Arrays.stream(ResourceReader.readFileContentFromClassLoaderResourceAsString("./import.sql").split(";"))
                    .filter(item -> !item.trim().isBlank())
                    .toList();
            sqlStatements.forEach(item -> ss.createNativeQuery(item).executeUpdate());
        }
    }

    @AfterEach
    void teardown() {
        try (final var ss = sessionFactory.openStatelessSession()) {
            final var sqlStatements = Arrays.stream(ResourceReader.readFileContentFromClassLoaderResourceAsString("./delete.sql").split(";"))
                    .filter(item -> !item.isBlank())
                    .toList();
            sqlStatements.forEach(item -> ss.createNativeQuery(item).executeUpdate());
        }
    }

Obviously we cannot do the above in a @QuarkusIntegrationTest because of the beforementioned reasons (not being able to use @Inject in @QuarkusIntegrationTest).

So my question: are there alternatives for clearing the db contents before/after eacht QuarkusIntegrationTest?

[radcortez]

For integration tests, you need to create the datasource directly and then run the scripts (kind of like Flyway does).

[Manfred73]

I came up with a different approach: we already had some delete resource which just deletes all records from the database, so I call that in the @beforeeach and @AfterEach of the integration test to clean up (no need to inject anything in the integration test in this case, just call the resource with RestAssured).

These delete resources are now in the production code as well though, but we only need that for testing purposes. Next step is to take out these delete resources into a separate module which we don't deploy to production. Just like Gastaldi recommended to do for the FlywayResource mentioned earlier.