don't worry, it's probably fine

sqlite-unit - a test framework for SQLite queries

16 Mar 2021

tdd sqlite testing

I wrote some extensions to SQLite as part of Advent of Code 2020, adding functionality to solve specific problems such as splitting a string, multiplying all the values in a column.

I’d hacked together some light tests because the desire to do Test-Driven Development has wedged itself into my brain since I started working in software. These were unsophisticated and just consisted of running a particular SQL query and using Bash to check the outputs.

Despite promising myself I’d go back and make something better, I never did.

✨Until Now✨

I used SQLite’s astoundingly well-designed API and documentation to rig up a tiny testing framework for the outputs of queries.

The intention was to provide a few helper functions that I could use within SQLite itself rather than outside the process - I decided on assert_equal, assert_null, and assert_not_null so that I could write queries like:

select assert_equal(some_value, some_other_value) from test_inputs;

I also wanted to make it compatible with the Test-Anything Protocol and pipe it into consumers to get nice output. It’s not fully compliant yet - it doesn’t support named tests although there’s no reason I couldn’t extend it, and for implementation reasons the number of tests is at the end of the output rather than the beginning.

Example tests

create table input(a);
insert into input values (1), (2), (3);

with testcases(actual, expected) as (
    select count(*), 3 from input
    union all

    select sum(a), 6 from input
    union all

    select min(a), 1 from input
    union all

    select max(a), 2 from input -- wrong, it's 3

select assert_equal(actual, expected) from testcases;

Example output

not ok
# FAIL: 3 != 2

I’d be very surprised if anyone other than myself ended up using it - it was a bit of fun, and an opportunity to get deeper into SQLite’s extensions API.

The code is on GitHub as sqlite-unit, issues and pull requests accepted.