sqliteCreateFunction stopped working after laravel 12.21.*

[ruskiyos]

Laravel Version

12.21.0

PHP Version

8.4.14

Database Driver & Version

SQLite

Description

Since sqlite does not support the REGEXP function, the pdo allows you to create a custom function to handle the unsupported method.

// AppServiceProvider.php
public function register(): void
{
    if (DB::Connection() instanceof SQLiteConnection) {
        DB::connection()->getPdo()->sqliteCreateFunction('REGEXP', function ($pattern, $value) {
            mb_regex_encoding('UTF-8');

            $value = str_replace('[[:space:]]', '\s', $value);

            // workaround for escaping plus sign in regex pattern
            $pattern = str_replace('\\\\+', '\+', $pattern);

            return (false !== mb_ereg($pattern, $value)) ? 1 : 0;
        });
    }
}

This is a sample query that used to work.

$numericBrandCount = User::where('brand_id', 'REGEXP', '[0-9]+')->count();

After upgrading to laravel 12.22.* (or any version after that), the query breaks with the following error:

SQLSTATE[HY000]: General error: 1 no such function: REGEXP (Connection: sqlite, SQL: select count(*) as aggregate from "user" where "brand_id" REGEXP [0-9]+ and "user"."deleted_at" is null

If you roll back to laravel version 12.21.0, the query runs fine.

I have been unable to figure out what changed and therefore I am unable to create a Pull Request for it.

Steps To Reproduce

• Install laravel version 12.22.0 or higher
• define a sqlite custom function for REGEXP using sqliteCreateFunction in the register method of AppServiceProvider
• write a query that uses a REGEX expression
• run query against a sqlite database connection
• see no such function error

[crynobone]

Hey there, thanks for reporting this issue.

We'll need more info and/or code to debug this further. Can you please create a repository with the command below, commit the code that reproduces the issue as one separate commit on the main/master branch and share the repository here?

Please make sure that you have the latest version of the Laravel installer in order to run this command. Please also make sure you have both Git & the GitHub CLI tool properly set up.

laravel new bug-report --github="--public"

Do not amend and create a separate commit with your custom changes. After you've posted the repository, we'll try to reproduce the issue.

Thanks!

[ruskiyos]

I tried replicating it on a clean install and it works. I tried including all my composer dependencies, and it still works... However, when I try it on my repo, it doesn't work.

I cannot share my repo so I guess it's something with my setup (maybe a legacy configuration hanging around or something). I'll close the issue for now.

Thanks for looking into it!

[ruskiyos]

@crynobone I've been able to replicate the issue. I don't use github or their cli tools so I can't setup a repo for you, but it's very simple to replicate. The problem is that it seems the pdo looses context after the database gets refreshed.

I have a command that refreshes the database and runs migrations with seeders. One of the seeders queries the database with a regex query - this is what causes the issue. If I run the query outside the seeder, it works correctly. If I run as part of a command inside the seeder after the database is migrated, it fails. This works before laravel 12.21.*.

// AppServiceProvider.php
public function register(): void
{
    if (DB::Connection() instanceof SQLiteConnection) {
        DB::connection()->getPdo()->sqliteCreateFunction('REGEXP', function ($pattern, $value) {
            mb_regex_encoding('UTF-8');

            $value = str_replace('[[:space:]]', '\s', $value);

            // workaround for escaping plus sign in regex pattern
            $pattern = str_replace('\\\\+', '\+', $pattern);

            return (false !== mb_ereg($pattern, $value)) ? 1 : 0;
        });
    }
}

// database/migrations/2024_07_31_205841_create_users_table.php
<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class () extends Migration {
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('email', 150)->nullable()->unique();
            $table->string('name', 200)->nullable();
            $table->timestamps();
            $table->softDeletes();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('users');
    }
};

// app/Http/Models/User.php
<?php

namespace App\Http\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class User extends Model
{
    use SoftDeletes;
}

// app/Console/Commands/FakeCommand.php
<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;

class FakeCommand extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'app:fake-command';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Command description';

    /**
     * Execute the console command.
     */
    public function handle()
    {
        $this->call('migrate:fresh', ['--seed' => true]);
    }
}

// database/seeders/DatabaseSeeder.php
<?php

namespace Database\Seeders;

use App\Http\Models\User;
use Database\Seeders\Users\SampleDataUserSeeder;
use Illuminate\Database\Seeder;

class DatabaseSeeder extends Seeder
{
    /**
     * Seed the application's database.
     */
    public function run(): void
    {
        $count = User::where('name', 'REGEXP', "some-id-[0-9]+")->count();
    }
}

php artisan app:fake-command

  Dropping all tables .................................................................................................................. 6.37ms DONE

   INFO  Preparing database.  

  Creating migration table ............................................................................................................ 16.81ms DONE

   INFO  Running migrations.  

  2024_07_31_205841_create_user_table ................................................................................................. 12.74ms DONE


   INFO  Seeding database.  


In Connection.php line 826:
                                                                                                                                                                                                     
  SQLSTATE[HY000]: General error: 1 no such function: REGEXP (Connection: sqlite, SQL: select count(*) as aggregate from "user" where "name" REGEXP some-id-[0-9]+ and "user"."deleted_at" is null)  
                                                                                                                                                                                                     

In Connection.php line 406:
                                                              
  SQLSTATE[HY000]: General error: 1 no such function: REGEXP  

[crynobone]

Laravel needs to flush the database connection with the WAL journal mode: https://github.com/laravel/framework/pull/56368

You should use Illuminate\Database\Events\ConnectionEstablished event to register custom function such as above.