How to add a case-insensitive unique constraint in a SequelizeJS Model when using Postgresql?

Question

var Model = sequelize.define('Company', {
  name: {
    type: DataTypes.STRING,
    unique: true
  }
}

In the above example, unique: true is case sensitive. It allows both "sample" and "Sample" to be saved in the db. Is there a built-in way to do this in sequelize without having to write a custom validator?

Answer 1

I'd suggest adding a functional unique index as seen here: http://www.postgresql.org/message-id/[email protected]

var Model = sequelize.define('Company', {
  name: {
    type: DataTypes.STRING
  }
}, 
{
  indexes: [
    { 
      unique: true,   
      name: 'unique_name',  
      fields: [sequelize.fn('lower', sequelize.col('name'))]   
    }
  ]
});

Answer 2

01AUG2022 update. I stumbled across this while trying to solve the same problem and I come with good news! As long as you're using Postgres or SQLite you can set your data type to type: DataTypes.CITEXT and your values will retain their case in the database while also giving a Validation error if you try to add another entry regardless of case.

TLDR: testuser and TestUser will throw a validation error if the column type is CITEXT.