database rollback support for the testsuite

[Treggats]

Description We've added support for multiple database dialects. However, this CI still only runs on SQLite. Which is fine at first, but if we want to fully support multiple database dialects then we need to use them in the CI as well.

Setting up the CI to use those can be done pretty easily, but the testsuite at the moment isn't ready for this.

The issue When running tests with SQLite I believe that the database is fully destroyed and created again. This is, however, not the case for the other dialects. With which we run into test failures, because the table it is trying to create already exists from a previous test.

[brendt]

Why don't we spin up an individual action using a matrix? That way we always start from a clean slate?

[Treggats]

The different databases aren't the issue. Use the MySQL driver for the tests are run the testsuite. That bests demonstrates the issue

[aidan-casey]

With FrameworkIntegrationTestCase, the SQLite database is deleted and re-copied every test run. Nothing is currently happening with other dialects like Laravel supports with the RefreshDatasebase trait.

I think our default stance should be to not refresh the database on every test case, as that will impact performance. Obviously we do need to support this though, since you will need to do it for tests around migrations, etc.

This probably goes hand-in-hand with a refactor of the test helpers.

[Treggats]

I think our default stance should be to not refresh the database on every test case

I agree, only on the parts where the tables are being recreated. what data is stored inside them is of less concern I gather

[brendt]

Gotcha! Ok let's take this into account for alpha2 :)

Edit: I guess it's pretty important for the multi-database setup, so let's do it now. I agree with Aidan that no rollback would be preferable, but we'd probably need to rewrite our tests (because we migrate in every one of them). We could work around that by changing the FrameworkIntegrationTest::migrate function, so that it keeps track of migrations, but still, I think it'll lead to a lot of edge cases.

The first step would be to have a complete rollback, and then figure out how to improve it from there.

[brendt]

I'm gonna look into this

[brendt]

Rollback in itself works, although there are still several errors on MySQL and Postgres. Gonna make a new issue for it