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.