Add MySQL Functions to SQLite in Laravel Tests

Source: Dev.to

Problem

When running tests with SQLite, MySQL‑specific functions such as FIELD cause a no such function error. SQLite does not include MySQL’s built‑in functions.

Example Code

Route

Route::get('/', function () {
    return User::query()
        ->orderByRaw('FIELD(id, 3, 5, 4, 1, 2)')
        ->get();
});

Test

namespace Tests\Feature;

use App\Models\User;
use Illuminate\Foundation\Testing\RefreshDatabase;
use Tests\TestCase;

class ExampleTest extends TestCase
{
    use RefreshDatabase;

    public function test_sql_function(): void
    {
        User::factory()->count(5)->create();

        $data = $this->get('/')->assertStatus(200)->collect();

        self::assertEquals([3, 5, 4, 1, 2], $data->pluck('id')->toArray());
    }
}

Fix: Register a Custom SQLite Function

PHP’s SQLite PDO driver allows you to define custom functions. Add the definition in the base TestCase setUp method:

namespace Tests;

use Illuminate\Database\SQLiteConnection;
use Illuminate\Foundation\Testing\TestCase as BaseTestCase;
use Illuminate\Support\Facades\DB;

abstract class TestCase extends BaseTestCase
{
    use CreatesApplication;

    protected function setUp(): void
    {
        parent::setUp();

        $connection = DB::connection();

        if (is_a($connection, SQLiteConnection::class)) {
            $connection->getPdo()->sqliteCreateFunction(
                'FIELD',
                static fn($id, ...$array) => array_search($id, $array)
            );
        }
    }
}

Result

SQLite now recognizes the FIELD function, and the test passes. The same approach can be used to add other MySQL‑specific functions needed for testing.

Originally published at .