In Memory SQLite with Pest, Phinx and Eloquent
The application
I’ve mentioned in a previous post that I’m currently building an application on top of Slim 4.
Slim is a bare bones, micro framework, so I need to pick and choose the third party libraries I work with to build out the application.
In this case I’m running Pest as the test framework, Cake’s Phinx for database migrations, and Laravel’s Eloquent ORM.
This post details how I worked through setting up a testing database.
Setting up SQLite for Tests
I wanted to start adding some tests which need to have a database in play, so I decided to set up SQLite - this is a bit more lightweight than using a second fully fledged mysql database and is ideal to use as a test database. Getting this up and running initially was straightforward enough.
First I added the setup foo to the phinx config file:
test:
adapter: sqlite
name: "%%PHINX_CONFIG_DIR%%/test"
charset: utf8
And created a new .env.test file for my main app to parse. I’m using vlucas\dotenv.
APP_ENV='test'
DB_DRIVER='sqlite'
DB_NAME=/absolute/path/to/project/root/test.sqlite3
Then in my Pest bootstrap file, something like this.
use Dotenv\Dotenv;
// load the test dotenv
try {
$dotenv = Dotenv::createImmutable(__DIR__ . '/../', '.env.test');
$dotenv->load();
} catch (Exception $e) {
// ignore
}
// load the main app
require_once __DIR__ . '/../bootstrap/bootstrap.php';
This makes sure .env.test loads before the .env in the main bootstrap. Since it’s immutable the test configuration items take precedence.
I also needed to update my Connection class to handle the “sqlite” DB_DRIVER value and pass the necessary configuration into Eloquent’s Capsule, but we’re not so concerned about that for this post.
So far so good, I can run ./tools/phinx migrate -e test and all the tables get created as expected, and I can roll them back with ./tools/phinx rollback -e test -t 0
When migrated, Pest now uses the SQLite database connection, and I can start writing tests.
Setup Migrations & Teardown Rollbacks
However, it would be great if the migrations could be migrated and rolled back automatically rather than having to manually run these on the command line before and after running tests.
No stress, let’s leverage the Phinx Manager and write a migrator class to handle that programmatically.
<?php
declare(strict_types=1);
namespace App\Database;
use Phinx\Config\Config;
use Phinx\Migration\Manager;
use Symfony\Component\Console\Input\ArgvInput;
use Symfony\Component\Console\Output\BufferedOutput;
use Symfony\Component\Yaml\Yaml;
class Migrator
{
/**
* @var string
*/
public const PHINX_CONFIG_FILE = __DIR__ . '/../../phinx.yml';
/** @var Manager */
protected $phinxApplication;
public function __construct()
{
if (!file_exists(self::PHINX_CONFIG_FILE)) {
throw new ConfigException('Phinx configuration file not found: ' . self::PHINX_CONFIG_FILE . '. You probably need to initialise phinx');
}
$fileContents = file_get_contents(self::PHINX_CONFIG_FILE);
if (!$fileContents) {
throw new ConfigException('Phinx configuration file is empty');
}
$config = Yaml::parse($fileContents);
$config = new Config($config, self::PHINX_CONFIG_FILE);
$this->phinxApplication = new Manager($config, new ArgvInput(), new BufferedOutput());
}
public function migrate(string $environment): bool
{
logger()->debug($this->getOutput());
// run migrations
$this->phinxApplication->migrate($environment);
logger()->debug($this->getOutput());
return true;
}
/**
* CAREFUL!!! This method will empty the database.
*/
public function rollback(string $environment): bool
{
$this->phinxApplication->rollback($environment, 0);
logger()->debug($this->getOutput());
return true;
}
private function getOutput(): string
{
/** @var BufferedOutput */
$output = $this->phinxApplication->getOutput();
return $output->fetch();
}
}
It’s a little weird that we have to pass in Symfony Console inputs/outputs to get this working, but Phinx is built primarily as a cli command so its implementation is coupled to that use case. Here we’re essentially using the ArgvInput as a dummy, and the BufferedOutput to get the result back as a string which we can then just log out.
Cool, we can now add a set up migration and tear down rollback over in any tests that need it.
beforeAll(function () {
app()->get(Migrator::class)->migrate('test');
});
afterAll(function () {
app()->get(Migrator::class)->rollback('test');
});
We’ll also test the Migrator itself for good measure. This test itself is fairly simple, but it should at least alert us to any exceptions that occur.
<?php
use App\Database\Migrator;
use App\Models\User;
test('migrates the dababase', function () {
$migrator = app()->get(Migrator::class);
$result = $migrator->migrate('test');
// the migration method ran
expect($result)->toBe(true);
// users tables exist
expect(fn () => User::all())->not->toThrow(Exception::class);
});
test('rolls back the dababase', function () {
$migrator = app()->get(Migrator::class);
$result = $migrator->rollback('test');
// the rollback method ran
expect($result)->toBe(true);
// users table does not exist
expect(fn () => User::all())->toThrow(Exception::class);
});
Ok this is all going well so far - my tests are all green and working as they should.
In Memory SQLite
However I know that using in memory SQLite should be way faster than file-based, and once I have more tests, that’s going to start to matter. No worries, both Phinx and Eloquent support in memory SQLite, so we might as well sort that out now. We just need to set the database name to “:memory:”. Let’s update phinx.yml
test:
adapter: sqlite
name: ":memory:"
charset: utf8
and .env.test
APP_ENV='test'
DB_DRIVER='sqlite'
DB_NAME=':memory:'
And then run our tests again. But… uh-oh, that didn’t work!
• Tests\Database\MigratorTest > migrates the dababase
Expecting Closure Object (...) not to throw 'Exception'.
at tests/Database/MigratorTest.php:10
6▕ test('migrates the dababase', function () {
7▕ $migrator = app()->get(Migrator::class);
8▕ $result = $migrator->migrate('test');
9▕ expect($result)->toBe(true);
➜ 10▕ expect(fn () => User::all())->not->toThrow(Exception::class);
11▕ });
What could be going on here? Let’s look at the underlying exception.
SQLSTATE[HY000]: General error: 1 no such table: users (SQL: select * from "users")
So by the time we get to Pest, the migrations have seemingly vanished. It turns out that since the Phinx Migration and Eloquent Capsule are using different Database connections, the in-memory SQlite instance is not available to Eloquent at all, and this is why our tests are failing.
We need to somehow get them using the same connection. The most sensible option would be for us to be able to pass the Eloquent Capsule connection to Phinx.
This is where Phinx’s Custom Adapters come in. Let’s make a new TestSqLiteAdapter based on Phinx’s default and over-ride the connect method.
<?php
declare(strict_types=1);
namespace App\Database;
use Illuminate\Database\Capsule\Manager as Capsule;
use Phinx\Db\Adapter\SQLiteAdapter;
final class TestSqLiteAdapter extends SQLiteAdapter
{
public function connect()
{
// this ensures that our test database uses the same (eloquent capsule) connection so that it's available to our test suite
if ($this->connection === null) {
$this->setConnection(Capsule::connection()->getPdo());
}
}
}
Then let’s register our adapter somewhere so that Phinx knows it exists - for now I’ll just put it in the Migrator’s construct method ahead of the Phinx Manager set up, since that’s the only place I actually need this functionality for now (I won’t be needing it on the command line to run my main migrations for example)
<?php
use Phinx\Db\Adapter\AdapterFactory;
...
AdapterFactory::instance()->registerAdapter('testsqlite', TestSqLiteAdapter::class);
Awesome, we can now update our phinx.yml to use our new adapater.
test:
adapter: testsqlite
name: ":memory:"
charset: utf8
And… we’re back. Our tests are all green again and super speedy!
Improvements
I then went on to make some further improvements, mainly in terms of moving to a PHP based Phinx config rather than yaml. This enabled me to include .env values so they don’t have to be duplicated. I wound up taking away the responsibility for environments from Phinx completely and delegating everything to APP_ENV. My .env.test now looks like this:
APP_ENV='test'
DB_DRIVER='sqlite'
PHINX_ADAPTER='testsqlite'
DB_NAME=':memory:'
And my phinx config like this:
<?php
use Dotenv\Dotenv;
if (!env('APP_ENV')) {
$dotenv = Dotenv::createImmutable(__DIR__);
$dotenv->load();
}
return
[
'paths' => [
'migrations' => '%%PHINX_CONFIG_DIR%%/src/Database/Migrations',
'seeds' => '%%PHINX_CONFIG_DIR%%/db/seeds',
],
'environments' => [
'default_migration_table' => 'phinxlog',
'default_environment' => 'main',
'main' => [
'adapter' => env('PHINX_ADAPTER', env('DB_DRIVER', 'mysql')),
'host' => env('DB_HOST', 'localhost'),
'name' => env('DB_NAME'),
'user' => env('DB_USER'),
'pass' => env('DB_PASSWORD'),
'port' => env('DB_PORT', '3306'),
'charset' => 'utf8',
],
],
'version_order' => 'creation',
];
Phinx now has just one default main environment, with Dotenv managing which values Phinx actually gets. We’ve added in one extra value PHINX_ADAPTER which will take precedence over DB_DRIVER for Phinx only, Eloquent will carry on using DB_DRIVER. In the case of SQLite, we will ignore the host, user, pass and port values so it won’t matter if those are set by our main .env file.
Much cleaner all round!
Pick & mix frameworks ftw
Using something like Slim is obviously more challenging than simply reaching for an opinionated framework like Laravel, but I really like this approach - it teaches you about framework internals and forces you to make more deliberate decisions when implementing new features.