[SOLVED] Sequelize case-sensitive queries

Sequelize case-sensitive queries

I'm using sequelize and want to know, how to send case-sensitive queries to my DB.

I have table users with a column Login.

And when I send request, (data.Login = '***0xwraith***'), sequelize finds me user with login 0xWraith. But I want the logins 0xWraith and 0xwraith be distinct and separate.

This is the code I use:

let db_user = await db.users.findOne({
            where: {
                Login: data.login
            }
        });

Solution

MySQL's string comparison is case insensitive by default.

If you would like to have case sensitive query just for this query, you can add BINARY keyword before the comparison to enable it.

SELECT * FROM users WHERE BINARY(`Login`) = '0xWraith';

You can write the query in Sequelize as following.

db.users.findOne({
    where: sequelize.where(sequelize.fn('BINARY', sequelize.col('Login')), data.login)
})

If you want to enable the case sensitivity as a table level, read more about it here https://dev.mysql.com/doc/refman/8.0/en/case-sensitivity.html.