How to Set up Database Integration Tests in Vanilla PHP

In this guide I’ll show you how to run fast, isolated, high-quality Database Integration Tests in legacy or framework-less PHP projects. Only Doctrine or PDO needed, and a small but incredibly powerful trick used by many battle-tested frameworks across different programming languages ecosystems. One reason this is a very solid approach is that it provides the guarantees of real database integration tests — transactions, persisted data, and SQL queries hitting a real database — while keeping execution times extremely low. This makes it ideal for large test suites, continuous refactoring, and yes, even TDD, because it preserves your development flow through a fast feedback loop. Also, this approach works exceptionally well in legacy projects. Most legacy codebases lack a Testing Foundation. With this technique, you can introduce high-level database integration tests even into very old or badly coupled systems. Please note that before going 'all-in' into this approach, I've tried different alternatives, here are two of them: ❌ SQLite's In-Memory Database support SQLite's In-Memory Database support SQLite can run entirely in memory, meaning you can have a fully isolated database instance that lives only in RAM. For example, you could run your Database Integration Tests across 16 parallel processes, each with its own in-memory database. This is EXTREMELY fast — and a perfectly valid approach if SQLite is your primary database — but there’s a significant gap between SQLite and, for example, PostgreSQL. In behaviour, data types, and SQL semantics. MySQL is somewhat closer to SQLite, but still not equivalent. If your main database is other than SQLite and you choose this approach, you’ll need to limit your queries to the subset of features SQLite supports. And even then, there will always be a non-negligible mismatch, which may keep your confidence from reaching 100%. In PHP, you can set up SQLite's in-memory Database like this: $pdo = new PDO('sqlite::memory:'); $pdo = new PDO('sqlite::memory:'); Just be aware that with any in-memory database testing setup, you’ll need to recreate the schema for every run, as nothing is persisted. I suggest that you take a look at the official documentation for more details: php.net - PDO SQLite sqlite.org - In-Memory database php.net - PDO SQLite php.net - PDO SQLite php.net - PDO SQLite sqlite.org - In-Memory database sqlite.org - In-Memory database sqlite.org - In-Memory database ❌ Vimeo's In-Memory MySQL engine Vimeo's In-Memory MySQL engine I was genuinely surprised when I came across this project. And the good part is that I can speak from experience, having used it for a couple of months. Vimeo describes it as: A MySQL engine written in pure PHP. A MySQL engine written in pure PHP. To quickly illustrate the main idea: // use a class specific to your current PHP version (APIs changed in major versions) $pdo = new \Vimeo\MysqlEngine\Php8\FakePdo($dsn, $user, $password); // currently supported attributes $pdo->setAttribute(\PDO::ATTR_CASE, \PDO::CASE_LOWER); $pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false); // use a class specific to your current PHP version (APIs changed in major versions) $pdo = new \Vimeo\MysqlEngine\Php8\FakePdo($dsn, $user, $password); // currently supported attributes $pdo->setAttribute(\PDO::ATTR_CASE, \PDO::CASE_LOWER); $pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false); The library provides its own PDO implementation, which effectively acts as the interface to Vimeo’s MySQL engine under the hood. In theory, it works the same way as a regular PDO instance, and you can generally use it anywhere you would use native PDO. Some issues I've noticed, though: Limited capabilities. Its feature set is much smaller than native PDO. Only a few of the most common PDO attributes are supported. Unclear error messages. The library throws PHP-level exceptions instead of real SQL errors, and they’re often not very intuitive. For example, an SQL syntax error or assigning NULL to a non-nullable column produces a library-generated exception rather than the usual MySQL error message — and these can be confusing at first. Learning curve. It takes some time to become familiar with its error patterns before you can be truly productive. MySQL only. It only supports MySQL type-of databases, so it’s not suitable if your main database is PostgreSQL, SQLite, or anything else. Limited capabilities. Its feature set is much smaller than native PDO. Only a few of the most common PDO attributes are supported. Limited capabilities. Unclear error messages. The library throws PHP-level exceptions instead of real SQL errors, and they’re often not very intuitive. For example, an SQL syntax error or assigning NULL to a non-nullable column produces a library-generated exception rather than the usual MySQL error message — and these can be confusing at first. Unclear error messages. NULL Learning curve. It takes some time to become familiar with its error patterns before you can be truly productive. Learning curve. MySQL only. It only supports MySQL type-of databases, so it’s not suitable if your main database is PostgreSQL, SQLite, or anything else. MySQL only. On the other hand: It’s extremely fast — there’s no network or database server involved. It requires no infrastructure changes — you just need to recreate the schema each time since everything lives in memory. It’s reliable — having been used in production for years and backed by Vimeo. It’s extremely fast — there’s no network or database server involved. It’s extremely fast It requires no infrastructure changes — you just need to recreate the schema each time since everything lives in memory. It requires no infrastructure changes It’s reliable — having been used in production for years and backed by Vimeo. It’s reliable I recommend you to take a deeper look and see their real motivation: GitHub - vimeo/php-mysql-engine GitHub - vimeo/php-mysql-engine GitHub - vimeo/php-mysql-engine GitHub - vimeo/php-mysql-engine ✅ Transactional Database Integration Tests This is the chosen approach of this guide. As you may have noticed, all the previous options come with significant limitations. Unless you choose the SQLite in-memory approach and SQLite is your primary database, none of them provides a 100% trustworthy integration test. The only way to guarantee fully reliable tests is to interact with your database — the same your application uses. This approach does exactly that and works with any database system that supports transactions. The idea behind this technique is surprisingly simple. At its core, it looks like this: public function test_user_registration(): void { $pdo = new PDO(...);

// start transaction $pdo->beginTransaction();

// interact with the database performing real operations $stmt = $pdo->prepare('INSERT INTO users (name) VALUES (:name)'); $stmt->execute(['name' => 'John']);

// the user was inserted, we can do some assertions $this->assertCount(1, $pdo->query('SELECT * FROM users')->fetchAll());

// our test has finished, we roll back everything $pdo->rollBack(); } public function test_user_registration(): void { $pdo = new PDO(...);

// start transaction $pdo->beginTransaction();

// interact with the database performing real operations $stmt = $pdo->prepare('INSERT INTO users (name) VALUES (:name)'); $stmt->execute(['name' => 'John']);

// the user was inserted, we can do some assertions $this->assertCount(1, $pdo->query('SELECT * FROM users')->fetchAll());

// our test has finished, we roll back everything $pdo->rollBack(); } In other words: Starts a database transaction, performs a real insert, gets a real auto-incremented ID, and finally rolls everything back. Starts a database transaction, performs a real insert, gets a real auto-incremented ID, and finally rolls everything back. This is real database interaction with almost zero side effects — and, most importantly, enables a fast and reliable feedback loop that keeps your development flow smooth. The only noticeable side effect that I realized is that if you use auto-incremented IDs they will keep increasing. Modern testing setups wrap beginTransaction() and rollBack() inside methods such as setUp() and tearDown() which are specific to the Testing Frameworks, in this case, PHPUnit. But the underlying mechanism is exactly the same. beginTransaction() rollBack() setUp() tearDown() Also, you’ll probably want to separate your testing and development databases. If you mix them (use you development database for tests), your tests won’t start from a clean state, and you’ll eventually end up with incorrect assumptions and unreliable results. Who uses this approach? This technique is not new. In fact, it’s well-established and widely used across many battle-tested frameworks and tools in both PHP and non-PHP ecosystems. Frameworks like Ruby on Rails, Django (Python) and Spring Boot (Java) rely on the same idea: run each test inside a database transaction and roll it back at the end. Over the years, this pattern has proven to be one of the fastest, cleanest, and most reliable ways to write real database integration tests. Here are some well-known examples: Ruby On Rails Since the early versions of Rails (2005–2006, around its initial release), this mechanism has been supported. It has been part of Rails’ DNA from the very beginning. This approach allowed Rails applications to scale their test suites without suffering the performance penalties of repeatedly creating or truncating tables, and it helped popularize transactional testing patterns in many other frameworks. By default, Rails automatically wraps tests in a database transaction that is rolled back once completed. This makes tests independent of each other and means that changes to the database are only visible within a single test. By default, Rails automatically wraps tests in a database transaction that is rolled back once completed. This makes tests independent of each other and means that changes to the database are only visible within a single test. Reference: Ruby on Rails - Transactional Database Tests Reference: Ruby on Rails - Transactional Database Tests Ruby on Rails - Transactional Database Tests WordPress Since 2017, WordPress’ PHPUnit test suite has adopted this transactional approach: each test starts a MySQL transaction and rolls it back after execution. This ensures real SQL behavior while keeping the database clean between tests. Database modifications made during test, on the other hand, are not persistent. Before each test, the suite opens a MySQL transaction (START TRANSACTION) with autocommit disabled, and at the end of each test the transaction is rolled back (ROLLBACK). This means that database operations performed from within a test, such as the creation of test fixtures, are discarded after each test. Database modifications made during test, on the other hand, are not persistent. Before each test, the suite opens a MySQL transaction (START TRANSACTION) with autocommit disabled, and at the end of each test the transaction is rolled back (ROLLBACK). This means that database operations performed from within a test, such as the creation of test fixtures, are discarded after each test. START TRANSACTION and at the end of each test the transaction is rolled back ROLLBACK Reference: WordPress Handbook - Testing with PHPUnit Reference: WordPress Handbook - Testing with PHPUnit WordPress Handbook - Testing with PHPUnit Laravel The Illuminate\Foundation\Testing\RefreshDatabase Trait in Laravel also does exactly what we described. It wraps the test within a Database transaction, and rolls back everything at the end of the test. Illuminate\Foundation\Testing\RefreshDatabase The Illuminate\Foundation\Testing\RefreshDatabase trait does not migrate your database if your schema is up to date. Instead, it will only execute the test within a database transaction. Therefore, any records added to the database by test cases that do not use this trait may still exist in the database. The Illuminate\Foundation\Testing\RefreshDatabase trait does not migrate your database if your schema is up to date. Instead, it will only execute the test within a database transaction. Therefore, any records added to the database by test cases that do not use this trait may still exist in the database. Illuminate\Foundation\Testing\RefreshDatabase it will only execute the test within a database transaction Reference: Laravel - Resetting the Database after each test Reference: Laravel - Resetting the Database after each test Laravel - Resetting the Database after each test Symfony In the Symfony ecosystem, this approach is commonly implemented through the dama/doctrine-test-bundle, a bundle that — as of today — has more than 33 million downloads. dama/doctrine-test-bundle It is also one of the most decoupled, enterprise-grade solutions available. In practice, this means you can use the 'non-Symfony' part of this library in virtually any project, benefiting from the level of robustness and reliability that it has gained over the years. You might hesitate about the Doctrine requirement — but there’s an important reason for it. This whole approach relies on database transactions, and that raises an immediate question: what happens if your application performs nested transactions? what happens if your application performs nested transactions? This is exactly where the library shines. It handles transactional tests, even when your code opens its own transactions internally. Thanks to Doctrine’s DBAL middleware and its savepoint support, nested transactions work seamlessly on drivers such as PostgreSQL and MySQL. Reference: GitHub - dmaicher/doctrine-test-bundle Reference: GitHub - dmaicher/doctrine-test-bundle GitHub - dmaicher/doctrine-test-bundle GitHub - dmaicher/doctrine-test-bundle How to setup dmaicher/doctrine-test-bundle in your framework-agnostic project dmaicher/doctrine-test-bundle In this section, we’ll focus on how to configure this library for any framework-agnostic project — whether it’s a legacy codebase or a modern project where you intentionally chose to keep things minimal. As a matter of fact, I actually posted a question in library’s GitHub repository asking about this exact use case, and David Maicher, the official maintainer, was kind enough to help me through the details. What follows is essentially the result of that exchange. I actually posted a question in library’s GitHub repository I actually posted a question in library’s GitHub repository This assumes you already have a working Doctrine connection in place. Install the composer package: composer require dama/doctrine-test-bundle:^8.4 --dev composer require dama/doctrine-test-bundle:^8.4 --dev Step 1: Add library's PHPUnit extension to phpunit.xml phpunit.xml This is required so that PHPUnit automatically rolls back the database transaction after each test. Add the following block — or simply add the to your existing section if you already have one — to your phpunit.xml(or the one you use) file: phpunit.xml Step 2: Set up the Doctrine connection using the library’s components The following code shows what you would normally want to have in your “Doctrine connection” setup. The key parts are: the dama.connection_key parameter (it can be set to anything, but it must be present) adding the bundle’s DBAL Middleware calling setKeepStaticConnections(true) the dama.connection_key parameter (it can be set to anything, but it must be present) dama.connection_key adding the bundle’s DBAL Middleware calling setKeepStaticConnections(true) setKeepStaticConnections(true) If you miss any of these, the integration test won't work. use Doctrine\ORM\Configuration; use Doctrine\ORM\ORMSetup; use Doctrine\DBAL\Connection; use Doctrine\DBAL\DriverManager;

function getDoctrineConnection(Environment $environment): Connection { $parameters = [ 'driver' => 'pdo_pgsql', 'host' => '127.0.0.1', 'user' => 'postgresql', 'password' => 'postgresql', 'dbname' => 'app_prod', ];

// this is not relevant for this example, but if you use Doctrine, you probably use the ORM too. // And this is just to make it more similar to your context. $config = ORMSetup::createAttributeMetadataConfiguration( paths: [$domainEntitiesPath], isDevMode: $environment->not(Environment::production), );

// you will probably want to have a check similar to this if ($environment->is(Environment::testing)) { // also, you probably want to switch to an empty, different database for testing! $parameters['dbname'] = 'app_tests';

// set a connection key $parameters['dama.connection_key'] = 'anything-is-ok',

// add the DBAL middleware $config->setMiddlewares([ new \DAMA\DoctrineTestBundle\Doctrine\DBAL\Middleware(), ]);

// keep static connections across tests \DAMA\DoctrineTestBundle\Doctrine\DBAL\StaticDriver::setKeepStaticConnections(true); }

return DriverManager::getConnection($parameters, $config); } use Doctrine\ORM\Configuration; use Doctrine\ORM\ORMSetup; use Doctrine\DBAL\Connection; use Doctrine\DBAL\DriverManager;

function getDoctrineConnection(Environment $environment): Connection { $parameters = [ 'driver' => 'pdo_pgsql', 'host' => '127.0.0.1', 'user' => 'postgresql', 'password' => 'postgresql', 'dbname' => 'app_prod', ];

// this is not relevant for this example, but if you use Doctrine, you probably use the ORM too. // And this is just to make it more similar to your context. $config = ORMSetup::createAttributeMetadataConfiguration( paths: [$domainEntitiesPath], isDevMode: $environment->not(Environment::production), );

// you will probably want to have a check similar to this if ($environment->is(Environment::testing)) { // also, you probably want to switch to an empty, different database for testing! $parameters['dbname'] = 'app_tests';

// set a connection key $parameters['dama.connection_key'] = 'anything-is-ok',

// add the DBAL middleware $config->setMiddlewares([ new \DAMA\DoctrineTestBundle\Doctrine\DBAL\Middleware(), ]);

// keep static connections across tests \DAMA\DoctrineTestBundle\Doctrine\DBAL\StaticDriver::setKeepStaticConnections(true); }

return DriverManager::getConnection($parameters, $config); } This is it. You don’t need anything else. This is a complete example of how a Database Integration Test now looks: use PHPUnit\Framework\TestCase;

class SomeTest extends TestCase { public function test_doctrine_connection(): void { $connection = getDoctrineConnection(Environment::testing);

// Insert a real row into the database $connection->insert('users', [ 'name' => 'John', ]);

// Fetch the last inserted ID $userId = $connection->lastInsertId();

// Verify the row exists $name = $connection->fetchOne( 'SELECT name FROM users WHERE id = :id', ['id' => $userId] );

$this->assertEquals('John', $name);

// No cleanup needed — everything will be rolled back automatically } } use PHPUnit\Framework\TestCase;

class SomeTest extends TestCase { public function test_doctrine_connection(): void { $connection = getDoctrineConnection(Environment::testing);

// Insert a real row into the database $connection->insert('users', [ 'name' => 'John', ]);

// Fetch the last inserted ID $userId = $connection->lastInsertId();

// Verify the row exists $name = $connection->fetchOne( 'SELECT name FROM users WHERE id = :id', ['id' => $userId] );

$this->assertEquals('John', $name);

// No cleanup needed — everything will be rolled back automatically } } I hope this post helped you understand how to perform real database integration tests in PHP without relying on any framework. Share your thoughts Did you find this approach useful? Would you like to hear about other variations? If you tried it or ran into anything unexpected, I'd be happy hear how it went — your experience helps keep this post accurate and helpful for others. And if you’re applying this technique in a real project — especially a legacy one — feel free to share your story. This can encourage others to adopt it as well. Special thanks to David Maicher (@dmaicher), the maintainer of the doctrine-test-bundle project, for helping clarify how to use the library in a framework-agnostic context. David Maicher (@dmaicher) David Maicher (@dmaicher) doctrine-test-bundle