SQLite, case sensitivity, and Laravel testing

Gotta have a hero image. Thanks AI!

Imagine you have a bunch of records like this:

$cars = [
  [
    'model' => 'Countach',
    'registration' => '7075GHX'
  ],
  [
    'model' => 'Mini',
    'registration' => '9643tls'
  ],
  [
    'model' => 'Mustang',
    'registration' => '8862MBA'
  ]
];

Your user can search for cars by registration. It doesn’t matter whether they search for 8862MBA or 8862mba, they're going to find the Mustang, right?

Yes, that’s right. In MySQL, and in MSSQL at least. But then, when you write your tests, you find that you don’t necessarily find the Mustang. Because you’re using SQLite as your testing database, and SQLite uses case-sensitive matches.

You may have already found that the ‘where’ filter in Laravel collections is case-sensitive, so though you can query the database successfully in a case-insensitive manner, you can’t then filter the resulting collection the same way (though here’s a simple Collection macro that provides case-insensitive filtering).

So you’re left with the icky situation that a where in an Eloquent query works differently to a where in a Collection, except where it doesn't.

So I guess you can try remember to/get your team to always remember to change the case of this kind of supplied data before storing it in the database (😂) or get them to use raw SQL in where clauses (eg whereRaw('UPPER(REGISTRATION) = ?', strtoupper($registration)) 💀).

SQlite supports a collate clause in its CREATE TABLE syntax, but this isn't supported in Laravel's Database schema driver for SQLite.

If there is any interest, I may look into implementing it.