Unit tests with JSON columns in SQLite

When doing any sort of development work I’ve always preferred working on an Ubuntu machine.
Doing so has helped me understand the servers that our production code has always been run on.

Towards the end of last year I started work on a new project that made heavy use of Spatie’s Event Projector package.
Without getting into the package too much, at its core it stores the payloads of all recorded events in a JSON column within a stored_events table.

I hadn’t needed to use JSON columns before and since they were a part of MySQL 5.7 which was released in 2015 I had assumed there’d be no issues with using them.

For the most part I was right, writing unit tests with Laravel’s build in tools has made me come to love writing tests and made TDD part of my daily workflow.
However when I tried to run the same tests within Gitlabs CI/CD pipelines I saw the following:

1
2
3
4
5
6
7

PHPUnit 7.5.6 by Sebastian Bergmann and contributors.

Runtime:       PHP 7.2.15
Configuration: /builds/secret-project/phpunit.xml

 ==> Tests\Unit\ExampleTest ✓ 
"{"errors":["SQLSTATE[HY000]: General error: 1 no such function: json_extract (SQL: select * from \"stored_events\" where \"event_class\" in (App\\Events\\ClaimReported, App\\Events\\ClaimUpdatedByApp, App\\Events\\ClaimCreated, App\\Events\\ClaimUpdated, App\\Events\\ClaimUploadCompleted) and \"id\" < 1 and json_extract(\"event_properties\", '$.\"claimUuid\"') = 0dab4335-c5e8-3564-9a0a-d0537cd697f4 order by \"id\" desc limit 1)"]}"