Using SQLite with Kahlan in PHP
I’ve been using Kahlan for testing a bit recently, I really like the describe-it style. I had a project where I needed to write an SQLite implementation of a repository and I wanted to use Kahlan to test the implementation, here’s how I did it.
Note: I’m considering extracting this out into a package, but it’s not much work to implement and I’m not sure how generic I can make it…
How it started
My file structure looked something like this:
├── data
│ └── db.sqlite
├── spec
│ ├── SqliteTaskRepositorySpec.php
│ └── db
│ ├── fixtures
│ │ └── tasks.sql
│ ├── loader.php
│ └── reset.sql
├── src
│ ├── Repository
│ │ ├── SqliteTaskRepository.php
│ │ └── TaskRepository.php
└── vendor
I was using /data/db.sqlite
for the application, and wanted a way to test that the SQLite implementation of a repository I was writing for a simple Task-list application (to be used later this year in a workshop) was working correctly.
What I wanted
In Kahlan, I wanted something simple to work with when describing specs, I wanted to be able to reset the database before each spec, as well as load fixtures into the database easily inside a spec (as each spec would potentially require different data in the database).
I wanted something like the following in /spec/SqliteTaskRepositorySpec.php
:
<?php
describe('SqliteTaskRepository', function() {
beforeEach(function() {
resetDB(); // reset our database before each test
});
it('gets all tasks from the database', function() {
load('tasks'); // load fixtures inside this test
// do stuff
});
});
How I did it
I setup composer to autoload the /spec/db/loader.php
file:
{
"autoload": {
"psr-4": {
"App\\": "src/"
},
"files": [
"spec/db/loader.php"
]
},
}
Then inside that file, I created a couple of functions:
<?php
/**
* Resets the database that's in the Kahlan container under the `db.sqlite` key.
*
* To use this, specify a file called `reset.sql` within the `/spec/db` folder of your project
* When this function is called, it'll bind a new PDO instance into the Kahlan container under `db.sqlite`
* It will then set that DB to whatever is in the `reset.sql` file
*
* @param string $db
*/
function resetDB($db = ':memory:')
{
\Kahlan\box('db.sqlite', new PDO("sqlite:{$db}"));
$sql = file_get_contents(__DIR__ . '/reset.sql');
\Kahlan\box('db.sqlite')->exec($sql);
}
/**
* Loads a fixture file into the database that's in the Kahlan container under the `db.sqlite` key.
*
* @param string $fixture
*/
function load(string $fixture)
{
$sql = file_get_contents(__DIR__ . "/fixtures/{$fixture}.sql");
\Kahlan\box('db.sqlite')->exec($sql);
}
It uses the \Kahlan\box()
function, which acts as a very basic container to store our PDO object.
How I use it
Using the resetDb()
function without a parameter will create an in-memory database, but if you wanted one on disk, you could easily pass the absolute path to it and it’ll use that instead.
My /spec/db/reset.sql
looks like the following:
DROP TABLE IF EXISTS Tasks;
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE "Tasks" (
"id" TEXT PRIMARY KEY ASC NOT NULL UNIQUE,
"name" TEXT,
"added_on" INTEGER DEFAULT CURRENT_TIME,
"complete" INTEGER DEFAULT 0
);
PRAGMA writable_schema=OFF;
COMMIT;
With loading fixtures, you can then create a file within /spec/db/fixtures
and call the name of that file (without the .sql
extension) to load that fixture into the database.
My /spec/db/fixtures/tasks.sql
fixture file looks like the following:
INSERT INTO Tasks (id, name, added_on)
VALUES
('095ae2fb-cf7b-4dcd-829f-e10e3ce84a3d', 'Task 1', '2016-10-05 03:04:05'),
('cfa02e73-d510-4c09-8bb5-e46c9c806b9b', 'Task 2', '2016-10-06 02:03:04'),
('b1b0c0ce-cc9c-458e-b648-de19e0aac496', 'Task 3', '2016-10-07 01:02:03');
UPDATE Tasks SET complete = 1 WHERE id = 'b1b0c0ce-cc9c-458e-b648-de19e0aac496';
Now, whenever I call load('tasks');
in a spec, it’ll execute that fixture file in the database and I’ll have tasks in SQLite that I can work with.
And because the PDO object with my SQLite database is in Kahlan’s container, I could do something like the following to set up my repository to use it:
<?php
describe('SqliteTaskRepository', function() {
beforeEach(function() {
resetDB();
$this->repo = new SqliteTaskRepository(\Kahlan\box('db.sqlite'));
});
});
Now, I can ensure that my SQLite repository actually does what it should, happy days.