Route model binding works differently between MySQL and Postgres

Laravel does a lot of work to abstract database differences away, allowing us to think at a higher level when querying data.

But the reality is that subtle, yet important, differences remain. For example, recently a reader sent me a note about how route model binding works differently between MySQL and Postgres.

Let's say you have a typical users table with a big integer primary key called id.

And you have the following route, which uses route model binding to inject a User object into your controller action:

// routes/web.php
Route::get('/users/{user}', [UserController::class, 'show']);

An existing user will load as expected: /users/123.

A non-existent user will return a 404: /users/999999

But what happens if you give a non-integer in the URL, like /users/abc?

With MySQL, you get a 404, but with Postgres you get a 500 unhandled exception:

Invalid text representation: 7 ERROR: invalid input syntax for type bigint

That's a pretty big difference!

It's reasonable to ask: Do we care about this difference in behavior? And if so, how would we fix it?

Stay tuned for the next tip...

Here to help,

Joel

P.S. This is one example of why I'd never trust my tests to run with a different database than I use in production. Want help getting your tests running in a production-like environment?