A common debate in the community is whether you should use SQLite or the same database that you use in production for your tests; assuming you don't use SQLite in production.

From the conversations I've been involved in, the argument stems around wanting to get the most confidence that you can from your tests. For some this means that they want to run their tests against the same database that they use in local/production environments. By using SQLite, the fear is that you could unknowingly introduce a bug due to differences in the database engines/drivers.

This article isn't about arguing which approach is better; I've done both across commercial and personal projects and there are valid arguments on both sides. That said, I've never actually come across a bug that was caused by using a different SQL engine for my tests... that I know of.

In any case, I've found that using an in-memory SQLite database for my tests has offered me the greatest performance. And maybe you also like using SQLite, but you've come across a situation where you need to use a MySQL or Postgres function that doesn't exist in SQLite and so you end up with errors like:

Illuminate\Database\QueryException : SQLSTATE[HY000]: General error: 1 no such function: concat (SQL: ...)

Illuminate\Database\QueryException : SQLSTATE[HY000]: General error: 1 no such function: st_distance (SQL: ...)

Illuminate\Database\QueryException : SQLSTATE[HY000]: General error: 1 no such function: regexp (SQL: ...)

Illuminate\Database\QueryException : SQLSTATE[HY000]: General error: 1 no such function: {whatever function you used} (SQL: ...)

Dang! SQLite doesn't support that function I want to use, so I'll have to use the real database driver for my tests...

Not quite! You can patch the missing functions into SQLite using the PDO::sqliteCreateFunction method.

Patching Missing Functions

You can patch missing functions by using something like the following:

1use Illuminate\Support\Facades\DB;
2 
3DB::connection()->getPdo()->sqliteCreateFunction('name-of-function', function ($argument) {
4    return // PHP code that "simulates" or "represents" the function...;
5}, 1);

Breaking this down, sqliteCreateFunction takes 3 arguments; the name of the function, the closure/function to execute, and the number of arguments that the function takes.

In the closure, you'd put whatever PHP code you need to simulate the function you want to use. The arguments to the closure are whatever the function needs to execute. These can be column values or arbitrary values that you pass in.

Let's take a look at some common functions that I've come across in my travels.

Distance

point

1use Illuminate\Support\Facades\DB;
2 
3DB::connection()->getPdo()->sqliteCreateFunction('point', function ($lat, $lon) {
4    return "{$lat},{$lon}";
5}, 2);
6 
7// To run it
8DB::statement("SELECT point(column1, column2) FROM table");
9DB::statement("SELECT point(-27.470125, 153.021072) FROM table");

st_distance

 1use Illuminate\Support\Facades\DB;
 2 
 3DB::connection()->getPdo()->sqliteCreateFunction('st_distance', function ($origin, $destination) {
 4    [$originLatitude, $originLongitude] = explode(',', $origin);
 5    [$destinationLatitude, $destinationLongitude] = explode(',', $destination);
 6    return sqrt(pow($originLatitude - ($destinationLatitude), 2) + pow($originLongitude - ($destinationLongitude), 2)) * 111;
 7}, 2);
 8 
 9// To run it
10DB::statement("SELECT st_distance(point(origin_lat, origin_lon), point(destination_lat, destination_lon)) FROM table");
11DB::statement("SELECT st_distance(point(-27.470125, 153.021072), point(-33.865143, 151.209900)) FROM table");

Math Functions

acos

1use Illuminate\Support\Facades\DB;
2 
3DB::connection()->getPdo()->sqliteCreateFunction('acos', function ($number) {
4    return acos($number);
5}, 1);
6 
7// To run it
8DB::statement("SELECT acos(column) FROM table");
9DB::statement("SELECT acos(1) FROM table");

degrees

1use Illuminate\Support\Facades\DB;
2 
3DB::connection()->getPdo()->sqliteCreateFunction('degrees', function ($number) {
4    return rad2deg($number);
5}, 1);
6 
7// To run it
8DB::statement("SELECT degrees(column) FROM table");
9DB::statement("SELECT degrees(1) FROM table");

cos

1use Illuminate\Support\Facades\DB;
2 
3DB::connection()->getPdo()->sqliteCreateFunction('cos', function ($number) {
4    return cos($number);
5}, 1);
6 
7// To run it
8DB::statement("SELECT cos(column) FROM table");
9DB::statement("SELECT cos(1) FROM table");

radians

1use Illuminate\Support\Facades\DB;
2 
3DB::connection()->getPdo()->sqliteCreateFunction('radians', function ($number) {
4    return deg2rad($number);
5}, 1);
6 
7// To run it
8DB::statement("SELECT radians(column) FROM table");
9DB::statement("SELECT radians(1) FROM table");

sin

1use Illuminate\Support\Facades\DB;
2 
3DB::connection()->getPdo()->sqliteCreateFunction('sin', function ($number) {
4    return sin($number);
5}, 1);
6 
7// To run it
8DB::statement("SELECT sin(column) FROM table");
9DB::statement("SELECT sin(1) FROM table");

Miscellaneous

concat

1use Illuminate\Support\Facades\DB;
2 
3DB::connection()->getPdo()->sqliteCreateFunction('concat', function (...$items) {
4    return array_reduce($items, fn ($carry, $item) => $carry . $item);
5});
6 
7// To run it
8DB::statement("SELECT concat(column1, column2) FROM table");
9DB::statement("SELECT concat('foo', ' ', 'bar') FROM table");

regexp

 1use Illuminate\Support\Facades\DB;
 2 
 3DB::connection()->getPdo()->sqliteCreateFunction('regexp', function ($pattern, $string) {
 4    if (preg_match('/' . $pattern . '/', $string)) {
 5        return true;
 6    }
 7 
 8    return false;
 9}, 2);
10 
11// To run it
12DB::statement("SELECT regexp(column1, 'search string') FROM table");
13DB::statement("SELECT regexp('pattern', 'string') FROM table");

Hopefully, you're getting the gist of how these work and can apply them to your own needs if you run into a similar situation.

That said, if you get into a pickle and can't figure out how to patch a particular function you're using, hit me up on Twitter @jryd_13 or via the contact form and I'll see if I can help.

Re-using these patched functions

If I only need to patch a function once or twice, I'll usually just add the code as a private or protected method to the test class. However, if I find that I need to patch a function across multiple test classes then I'll consider extracting this to a trait. If you don't like extracting it to traits and/or find you need to import the patched functions into quite a few of your test classes then I'll consider putting the patched functions into a protected method on the base test class.

As a trait

 1// tests/MocksStDistance.php
 2namespace Tests;
 3 
 4use Illuminate\Support\Facades\DB;
 5 
 6trait MocksStDistance
 7{
 8    public function mockStDistance()
 9    {
10        DB::connection()->getPdo()->sqliteCreateFunction('point', function ($lat, $lon) {
11            return "{$lat},{$lon}";
12        }, 2);
13 
14        DB::connection()->getPdo()->sqliteCreateFunction('st_distance', function ($origin, $destination) {
15            [$originLatitude, $originLongitude] = explode(',', $origin);
16            [$destinationLatitude, $destinationLongitude] = explode(',', $destination);
17            return sqrt(pow($originLatitude - ($destinationLatitude), 2) + pow($originLongitude - ($destinationLongitude), 2)) * 111;
18        }, 2);
19    }
20}
21 
22// tests/Feature/ExampleTest.php
23namespace Tests\Feature;
24 
25class ExampleTest
26{
27    use MocksStDistance;
28 
29    public function test_it_finds_the_nearest_park()
30    {
31        $this->mockStDistance();
32 
33        // ...
34    }
35}

Via the base test class

 1// tests/TestCase.php
 2namespace Tests;
 3 
 4use Illuminate\Foundation\Testing\TestCase as BaseTestCase;
 5 
 6abstract class TestCase extends BaseTestCase
 7{
 8    use CreatesApplication;
 9 
10    protected function mockRegex()
11    {
12        DB::connection()->getPdo()->sqliteCreateFunction('regexp', function ($pattern, $string) {
13            if (preg_match('/' . $pattern . '/', $string)) {
14                return true;
15            }
16 
17            return false;
18        }, 2);
19    }
20}
21 
22// tests/Feature/ExampleTest.php
23namespace Tests\Feature;
24 
25class ExampleTest
26{
27    public function test_it_filters_for_parks_by_the_supplied_search_term()
28    {
29        $this->mockRegex();
30 
31        // ...
32    }
33}

By patching in the missing functions, you don't have to ditch SQLite for your tests when you run into use-cases where you need to use functions that SQLite doesn't natively provide. Nor do you need to feel constrained that you can't use these functions because you're using SQLite.

---

I hope you found this useful. If you have any feedback, thoughts, or different approaches to this then I'd love to hear from you; I'm @jryd_13 on Twitter.

Thanks!