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
ok
ok
ok
not ok
# FAIL: 3 != 2
1..4
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.