When running tests with SQLite, MySQL-specific functions like FIELD throw a no such function error.
Why It Fails
Each database has different built-in functions. SQLite doesn’t have MySQL’s FIELD function. Suppose your code has this query:
1
2
3
| Route::get('/', function() {
return User::query()->orderByRaw('FIELD(id, 3, 5, 4, 1, 2)')->get();
});
|
It works fine with MySQL, but tests using SQLite will fail:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| 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());
}
}
|
Add It with sqliteCreateFunction
PHP’s SQLite PDO supports custom functions. Just add it in TestCase’s setUp:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
| 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)
);
}
}
}
|
Now SQLite recognizes the FIELD function and the test passes. You can use the same approach to add other MySQL-specific functions.
