Can any one help to confirm sqlite not allow custom collation

@DhPandya Sorry, the link didn't help, i even can't migrate the tables

the story see here https://github.com/laravel/framework/issues/52045

@Tray2 If a string column is the default utf8mb4_unicode_ci and you set to ->unique()

when you insert some non-english string, it will cause duplicate entry, because utf8mb4_unicode_ci is case insensitive

you can try run a memory test, input this two string to a unique column, it will throw error duplicate entry

えりか
エリカ

actually that is two different words, so we need use utf8mb4_bin instead of utf8mb4_unicode_ci on a unique string column

@newbie360 Then you should set the whole database to that, rather than just a single column.

Or maybe use another RDBMS.

@Tray2 NO, MySql handle this is working good

$table->string('name')->collation('utf8mb4_bin')->unique();

the problem is Laravel 11 use the newest Sqlite version it can't handle the collation, if run memory sqlite test will throw an error

one of my old Laravel 10 project use the same migration code, running the same test is no error

Laravel 11 and Laravel 10 is use different Sqlite version, i guess Laravel haven't changed core code to handle the new version Sqlite

i don't want to change this to all migration, it is so ugly

            if (env('DB_CONNECTION') == 'mysql')
            {
                $table->string('name')->collation('utf8mb4_bin')->unique();
            }
            else if (env('DB_CONNECTION') == 'sqlite')
            {
                $table->binary('name')->unique();
            }

may be the best option for me is create another MySql database for testing, if the exists MySql database called demo, just create another db called demo_test

when use Pest, where i can put some code to create db demo_test before the test start and after test finish then drop the db?

@newbie360 Then use MySQL simple as that.

@newbie360 Laravel 11 uses SQLite by default to simplify booting up new projects, and for newcomers, as no additional DBMS installation is needed.

It is not a recommendation to use SQLite for new projects.

Not all DBMS share the same features, and if you are running a DBMS, like MySQL in production, and relies on features available only from them, you should consider using the same DBMS on your tests (like you figured out already).

@rodrigo.pedra

you should consider using the same DBMS on your tests

yup agreed, finally only changed phpunit.xml all working good

// .env
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=demo
DB_USERNAME=example
DB_PASSWORD=example

// phpunit.xml
<env name="DB_CONNECTION" value="mysql"/>
<env name="DB_DATABASE" value="demo_test"/>

// AppServiceProvider.php
    public function boot(): void
    {
        // Just in case someone need this
        ParallelTesting::tearDownProcess(function (int $token) {
            $parallelTestingDatabase = DB::getDatabaseName() . '_test_' . $token;
            DB::statement("DROP DATABASE IF EXISTS {$parallelTestingDatabase}");
        });
    }

command for daily usage

alias att="php artisan test"

above command will use database demo_test

alias attp="php artisan test --parallel --processes=2"

for my example, Pest parallel test will auto create the database called demo_test_test_1 and demo_test_test_2 base on the --processes number

i try to use memory engine, after read the https://dev.mysql.com/doc/refman/8.4/en/memory-storage-engine.html

forget it ;)