Programmatic database resets

Posted: 19 August 2026 - Reading time: 6 minutes

Automated testing is good. This is, I hope, not a controversial statement. Small, isolated tests are also good. This should also not be a controversial statement.

But sometimes, tests will need to talk to the database. It may be too difficult to mock, or you may be using an expensive-to-load fixture for an integration test. That's OK, and those tests absolutely have their place.

But it's also nice to not have to rebuild the world for such tests. Enter, trivially simple transaction-based isolation.

The use case

Consider a test method that creates a new User, then creates a Project, and then assigns it to that user. Then it asserts that the "assign User to Project" operation worked by looking directly in the database.

This is a perfectly reasonable integration test. However, it means at least two database writes (or more, depending on your data model), which has two main drawbacks:

Writing to disk costs time. For a single test you probably won't notice, but doing the same over 500 tests (especially if you're using data providers) it becomes very noticeable.
Your database is now in an unknown state afterward. That means the dummy data you just tested with is now part of the database for the next test that runs, making it unreliable.

We could address point 2 by wiping the whole database between each test. However, that can also be quite slow, especially if the schema has to be rebuilt. It's even worse if you need to have some existing fixture data in the database (say, the User roles are already defined, or Project categories, etc.). Every test now has some large number of additional writes that need to happen before you can even get started! That's clearly doubleplusungood.

The tool

Instead, we can leverage features of PHPUnit to avoid that issue entirely.

PHPUnit has a nice but under-utilized feature where you can tag an arbitrary method with #[Before] or #[After] to make it run before/after every test method in that class. It's a more flexible, superior alternative to the setUp() and tearDown() methods, which at this point you should never use. There's also #[BeforeClass] and #[AfterClass], which do exactly what you'd expect.

One clever use of that is to wrap every test in a transaction. Assuming you're using Doctrine, you could have code something like this:

class SomethingTest extends TestCase
{
    private Connection $conn;

    #[Before(20)]
	public function setupDoctrine(): void
	{
	      $connectionParams = /* ... */ ;
	      $this->conn = DriverManager::getConnection($connectionParams);
    }
    
    #[Before(10)]
    public function startTransaction(): void
    {
        self::$conn->beginTransaction();
    }
	 
    #[After]
    public function endTransaction(): void
    {
        self::$conn->rollBack();
    }
    
    // Test methods here.
}

(If you're not using Doctrine, then the same logic applies, just spelled differently for your DBAL.)

Now, every test begins by creating a database connection to use for the test, then starting a transaction. After every test, the transaction is rolled back. No data is ever written to the database.

Of course, repeating that in every test class is wasteful, so let's break it out to some traits.

trait SetupDoctrine
{
    // ...
}

trait TransactionIsolation
{
    use SetupDoctrine;
    
    #[Before]
    public function startTransaction(): void
    {
        self::$conn->beginTransaction();
    }
	 
    #[After]
    public function endTransaction(): void
    {
        self::$conn->rollBack();
    }
}

class SomethingTest extends TestCase
{
    use SetupDoctrine;
    use TransactionIsolation;
    
    // Test methods here.
}

A trait that gets used multiple times like SetupDoctrine here is fine; the engine realizes that and politely just includes it once.

What's the benefit? When a transaction is active, the database never actually writes to disk. It just holds the changes in memory but allows reads within the same transaction to access the newly changed data. That eliminates the cost of disk writes.

When the transaction is rolled back, any pending changes are discarded. The existing data on-disk is never modified. That means every test starts with the same known fixture state. (Which, presumably, you've set up in some other way beforehand. That's a topic for another post.)

Moreover, multiple transactions can easily be open at the same time. That means two different tests can run in parallel (using paratest or similar) and work with the same database, without bumping into each other or creating race conditions. Score!

In my anecdotal experience, this approach can speed up large test suites by a noticeable amount. The amount will vary widely with the details of your test suite.

Limitations

Like any technique, this one has limitations. There are two main ones that I have run into.

MySQL DDL

On MySQL and MariaDB, DDL statements (those that modify the schema) automatically trigger a transaction commit. That means this technique will not work for any test that needs to modify the schema. Fortunately, that's usually a relatively small subset of tests. PostgreSQL does not have this limitation.

Single Connection Only

If any of your tests directly or indirectly trigger writes to a different database connection (which I have run into in some legacy systems), or writes to a non-SQL database like Redis or OpenSearch or whatnot, then those connections will, naturally, be unaffected by the transaction. Depending on the details of what you're doing, it may be possible to use a similar technique for those other services. Or possibly not, and they'll need manual cleanup/reset between tests, too. Again, it will depend heavily on your specific test cases.

Conclusion

It's not perfect, but for the common case a simple transaction trait is a quick, easy, and safe way to speed up your tests and make them more reliable at the same time. And that's always a win in my book.