DEV Community: Filis Futsarov

How to set up Database Integration Tests in vanilla PHP

Filis Futsarov — Sat, 29 Nov 2025 16:37:00 +0000

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 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:');

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:

❌ 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.

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);

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.

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.

I recommend you to take a deeper look and see their real motivation:

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();
}

In other words:

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.

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.

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.

Reference: 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.

Reference: 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.

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.

Reference: 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.

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?

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

How to setup `dmaicher/doctrine-test-bundle` in your framework-agnostic project

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.

This assumes you already have a working Doctrine connection in place.

Install the composer package:

composer require dama/doctrine-test-bundle:^8.4 --dev

Step 1: Add library's PHPUnit extension to `phpunit.xml`

This is required so that PHPUnit automatically rolls back the database transaction after each test.

Add the following <extensions> block — or simply add the <bootstrap class> to your existing <extensions> section if you already have one — to your phpunit.xml (or the one you use) file:

<?xml version="1.0" encoding="UTF-8"?>
<phpunit>
    <!-- ...other stuff ... -->
    <extensions>
        <bootstrap class="DAMA\DoctrineTestBundle\PHPUnit\PHPUnitExtension"/>
    </extensions>
</phpunit>

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)

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);
}

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
    }
}

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.

How to automatically decrypt a LUKS LVM setup on boot with a USB

Filis Futsarov — Wed, 25 Jun 2025 10:07:01 +0000

This guide walks you through a robust procedure to auto-decrypt a LUKS-on-LVM setup at boot with a USB key. It assumes you already have your system set up with LUKS encryption on LVM.

The core idea is simple: keep a dedicated USB stick at home to unlock your system effortlessly, and leave it behind whenever you head out, so your machine stays securely encrypted on the go.

I extensively tested the procedure in multiple VMs and on real-world installs of Ubuntu 24.04 and Pop!_OS 22.04. Until reaching that point, I repeatedly broke my VM while trying outdated guides, AI-generated suggestions, and other unreliable sources—until I found my own way and then passed it to real setups. Here’s what I tried (and what finally worked with some customization):

❌ initramfs hooks
❌ udev scripts
❌ dracut modules
✅ cryptsetup using keyscript option (handled by initramfs-tools)

This solution relies on the keyscript option supported by initramfs-tools (not by cryptsetup itself), which allows delegating the decryption process to a custom script in a higher-level and safer way than using low-level initramfs hooks — although the option is defined in /etc/crypttab and passed to the initramfs layer, cryptsetup itself only accepts raw key data.

Internally, initramfs-tools handles keyscript by executing the specified script and piping its output to cryptsetup, effectively feeding the keyfile through standard input.

Most scripts I came across failed to handle this process in a fault-tolerant way: instead of falling back to the familiar "Enter password" screen when any kind of error happens, they leave you with a broken system.

That’s why I wrote a script that does things properly. It mounts your USB, attempts to load the keyfile up to three times, and if it still fails, gracefully falls back to the standard password prompt you're used to.

Before proceeding, ⚠️ make a full disk backup using Clonezilla (not just the partition) and test this solution in a VM running the same Ubuntu version as yours. This ensures you're familiar with the process and minimizes risk — I personally broke my VM several times during testing for writing this post, but snapshots made rollback easy.

The only requirement for this solution to work is support for the keyscript parameter, which has been available in initramfs-tools since at least Ubuntu 16.04.

✅ Tested on:

Ubuntu 22.04 and 24.04
Pop!_OS 22.04

These cover cryptsetup versions from 2.4.3 to 2.7.0. You can verify your version with:

cryptsetup --version

1. Creating a secure keyfile

This command creates a 4MB keyfile filled with cryptographically secure binary data (non-ASCII characters), providing high entropy per byte — making brute-force attacks significantly harder.

💡 Tip: Save the keyfile directly to the USB drive that you'll use to decrypt your setup.

dd if=/dev/urandom of=/media/usb/secure.key bs=1M count=4 iflag=fullblock

2. Downloading the unlock script

We'll need the unlock script for the keyscript parameter in the crypttab entry.

In systemd-based setups (e.g. Pop!_OS), the keyscript must be placed inside /lib/cryptsetup/scripts. In non-systemd setups (e.g. Ubuntu), it can technically reside anywhere — but placing it in /lib/cryptsetup/scripts ensures compatibility across systems.

sudo wget -O /lib/cryptsetup/scripts/keyscript.sh \
  https://raw.githubusercontent.com/filisko/cryptsetup-usb-keyscript/main/src/keyscript.sh

⚠️ It's critical that the script is owned by root and has execution permissions:

sudo chown root:root /lib/cryptsetup/scripts/keyscript.sh
sudo chmod 755 /lib/cryptsetup/scripts/keyscript.sh

3. Finding your USB's UUID

We need USB's UUID to tell cryptsetup where to find the keyfile.

sudo blkid

There will be many lines like the following one. We're interested in the UUID="81D6-413D" part.

/dev/sda1: UUID="81D6-413D" BLOCK_SIZE="512" TYPE="vfat" PARTUUID="c371356e-01"

3. Updating `/etc/crypttab` entries

To enable automatic decryption, we need to edit /etc/crypttab and provide:

The LUKS device name (already there).
The UUID of the LUKS-encrypted root partition (already there).
The full keyfile path, using the USB device's UUID: /dev/disk/by-uuid/81D6-413D:/secure.key (must be set).
The necessary options for cryptsetup to invoke the keyscript (must be set).

This tells the system where to find the keyfile and how to decrypt at boot.

sudo nano /etc/crypttab

It’s very likely that your system has an entry like this by default (note luks):

dm_crypt-0 UUID=XXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXX none luks

I suggest that you save it somewhere or comment it putting # at the beginning of the line.

The first thing that you see in the line is LUKS' device name that we will also need later to add the keyfile inside, so also save it. Common names are:

dm_crypt-0 / cryptdrive (Ubuntu)
cryptdata (Pop!_OS)

⚠️ It's crucial to preserve the original device name (e.g.: dm_crypt-0), UUID, and exact field spacing to avoid boot issues.

Replace (not add, duplicate UUIDs or device names ºare not allowed) your entry to match the changes of this entry:

dm_crypt-0 UUID=XXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXX /dev/disk/by-uuid/81D6-413D:/secure.key luks,discard,cipher=aes-xts-plain64,size=256,hash=sha1,keyscript=/lib/cryptsetup/scripts/keyscript.sh,tries=4

⚠️ The tries=4 option is mandatory — it ensures the system runs the script 3 times and gives it a chance to gracefully fall back on the 4th try. That 4th attempt is where the script prompts the user to manually enter the password, in case:

The USB isn't connected or could not be properly mounted.
The keyfile isn't found.
The keyfile is invalid.

Double-check the USB UUID and keyscript path, especially if you changed the script location.

4. Get LUKS' device path

With LUKS' device name (e.g.: dm_crypt-0) from the crypttab entry in previous step, we will get its device path to add the keyfile into it.

sudo cryptsetup status dm_crypt-0

The line that we need from the output is: device: /dev/nvme0n1p3 where /dev/nvme0n1p3 is the device path that we need.

/dev/mapper/dm_crypt-0 is active and is in use.
 type:    LUKS2
 cipher:  aes-xts-plain64
 keysize: 512 bits
 key location: keyring
 device:  /dev/nvme0n1p3
 sector size:  512
 offset:  32768 sectors
 size:    1993975808 sectors
 mode:    read/write

5. Add the keyfile to LUKS device

Here we need to set LUKS' device path that we got in the previous step and specify the keyfile that we generated at the very beginning, in step 1.

This will require an existing passphrase to add the new keyfile:

sudo cryptsetup luksAddKey /dev/nvme0n1p3 /media/usb/secure.key

If "nothing happens" (it actually returns a shell success code) then it worked.

6. Test the keyfile

To test that the previous step was done correctly, we can do the following:

sudo cryptsetup luksOpen --test-passphrase --key-file /media/usb/secure.key /dev/nvme0n1p3

If it works, it won't show anything (only return success in the terminal). If it fails, it will show:

No key available with this passphrase.

7. Update initramfs

The last step to set everything up is updating initramfs. Initramfs is a temporary file system with a mini-Linux inside used only during boot. After boot everything is deleted.

Updating initramfs will add to the initramfs image the updated version of /etc/crypttab together with the unlock script that we've previously downloaded inside /lib/cryptsetup.

Warning: If you see any errors while running the command, it's better to revert to your original /etc/crypttab entry and post a comment in the comments section below this post.

This may take around 1 min:

sudo update-initramfs -u

To check that our script was added inside initramfs we can run:

sudo lsinitramfs /boot/initrd.img-XXXXXXXX-generic | grep 'keyscript.sh'

lsinitramfs lists the files of initramfs' compressed image. So all the files of the mini-Linux will be listed there.

And something like this should be printed in our console (you can Ctrl-C to cancel the grep after finding it):

usr/lib/cryptsetup/scripts/keyscript.sh

8. Reboot

This is the last necessary step.

Simply reboot the USB and your setup should be automatically decrypted! 🎉🥳

sudo reboot

If all went well, please comment below with your Ubuntu (or other distro) version and your cryptsetup version.

9. Logs

Something cool that I figured out is a way to log messages inside initramfs. Usually this is almost impossible because, as I said earlier, initramfs (a temporary filesystem) is cleaned up right after boot, so anything written anywhere will be removed.

I realized that logs can be sent directly to the kernel using /dev/kmsg, so that later on, after boot, you can grep logs doing the following:

sudo dmesg | grep 'keyscript.sh'

This is pretty cool because either on success or failure (you had to manually enter the password) you can check the logs.

Logs when using the USB key normally:

[    8.199775] keyscript.sh: Attempt #1
[    8.213481] keyscript.sh: Using keyfile: /mnt/unlock-usb/secure.key

Logs when USB device was not found or simply choosing to manually introduce the password

[    3.258277] keyscript.sh: Attempt #1
[    6.271922] keyscript.sh: Device for decryption not found: /dev/disk/by-uuid/81D6-413D
[    6.941412] keyscript.sh: Proceeding to ask for manually entering the password

Logs when plugging in a USB without the keyfile

[    3.234567] keyscript.sh: Attempt #1
[    6.274412] keyscript.sh: Keyfile not found at: /mnt/unlock-usb/secure.key
[    6.954763] keyscript.sh: Proceeding to ask for manually entering the password

Logs when the USB could not be mounted on boot

[    3.112245] keyscript.sh: Attempt #1
[    6.514122] keyscript.sh: Failed to mount device: /dev/disk/by-uuid/81D6-413D at /mnt/unlock-usb
[    6.613212] keyscript.sh: Proceeding to ask for manually entering the password

Logs when the keyfile is located but incorrect

[    8.082461] keyscript.sh: Attempt #1
[    8.096964] keyscript.sh: Using keyfile: /mnt/unlock-usb/secure.key
[   13.501285] keyscript.sh: Attempt #2
[   13.511812] keyscript.sh: Using keyfile: /mnt/unlock-usb/secure.key
[   18.349567] keyscript.sh: Attempt #3
[   18.361704] keyscript.sh: Using keyfile: /mnt/unlock-usb/secure.key
[   23.205031] keyscript.sh: Max retries (3) reached. Proceeding to ask for manually entering the password.

Share thoughts!

Would it be interesting to consider any USB as valid? Or it's better to restrict it by UUID as we're doing already?

Special thanks to Guilhem Moulin, from the official Cryptsetup Team at Debian, for helping me clear up some doubts about the behavior of cryptsetup.

Say thanks if you made it, or help me keep this post updated with new versions of Ubuntu or cryptsetup.