Problem with email uniqueness on user registration

@Sinnbeck It's just me playing around with the database. I haven't changed anything.

The email column type in the users table is

character varying(255) NOT NULL

No, the app is not in production yet.

@Laralex Ok. It might be related to the mysql running on your system. Windows? How is it installed?

It is also possible that you didnt set the column to unique in the migration? Just tested in my own database by duplicating username (which is unique)

Error synchronizing data with database

Reason:
SQL Error [1062] [23000]: (conn=116) Duplicate entry 'FOO' for key 'users_username_unique'

@Sinnbeck I'm running PostgreSQL. The app is on a live server. I haven't forgot unique() in the users table migration file.

$table->string('email')->unique();

@Laralex Ah postgresSQL might be different :) I havent been able to find any articles regarding it, but my guess is that you should be able to make it case insensitive somehow. Maybe someone who actually uses postgres can answer better (I only use mysql, mariadb and mssql)

@Sinnbeck Then I'll wait for other responses. Thanks!

@Laralex Think I found something http://shuber.io/case-insensitive-unique-constraints-in-postgres/

Currently searching for tricks to make it citext without a manual migration query

@Sinnbeck I have no idea how to use a mutator/accessor :(

@Laralex They are easy to add https://laravel.com/docs/8.x/eloquent-mutators#accessors-and-mutators

@Laralex just try to google something like "Laravel Accessor Mutator" - and the first link will be https://laravel.com/docs/9.x/eloquent-mutators#accessors-and-mutators

@Sinnbeck Well, okay.

User Model

public function getEmailAttribute($value) {
        return strtolower($value);
}

But how is that gonna help me to prevent users to register with the same email with changed uppercase letters?

@Laralex use a mutator not an accessor https://laravel.com/docs/9.x/eloquent-mutators#defining-a-mutator

Anyway If you use mass asignments with create() it wont work (you have to convert to lowercase manually in that cases)

@okusax app/Actions/Fortify/CreateNewUser.php

User::create([
       'name' => $input['name'],
       'email' => $input['email'],
       'password' => Hash::make($input['password'])
]);

@Laralex so this should make sure they are always lowercase :)

User::create([
       'name' => $input['name'],
       'email' => strtolower($input['email']),
       'password' => Hash::make($input['password'])
]);

@Laralex

define:

// User.php

public function setEmailAttribute($value)
{
    $this->attributes['email'] = strtolower($value);
}

every time you assign the user email, it will be lowercased:

$user = new User();
$user->name = $input['name'];
$user->password = Hash::make($input['password']);
$user->email = $input['email']; // will be converted to [email protected] on the database
$user->save(); // when saved of course

if you use create() I think you should do the conversion manually:

User::create([
       'name' => $input['name'],
       'email' => strtolower($input['email']),
       'password' => Hash::make($input['password'])
]);

There is an old topic about this issue ( https://github.com/laravel/framework/issues/9430 ) Some people manage the unique validation in a Request Validation (ensuring that always your laravel application would be dealing with a lowercase email: https://github.com/laravel/framework/issues/9430#issuecomment-274482274 )

Others recommends to use a middleware to alter that kind of data to lowercase if detected https://github.com/laravel/framework/issues/9430#issuecomment-354729914

Maybe you can try that options too and check what works best for you.

@okusax Okay, nice. But how do I make this message

appear like this

(as a part of $errors variable, something like putting this validation in the controller's $request->validate() method)

@Laralex are you using Fortify? You can define your own authentication callback where you can modify the email input (strtolower) so comparison will match your stored data

https://laravel.com/docs/9.x/fortify#customizing-user-authentication

@tykus Useful answers as always.. Thanks, worked!