Sails.js upgrade to v1 reverse case sensitive queries

Question

After upgrading to sails v1 all the requests in the controllers became case sensitive.

Although this is expected, commented here: https://sailsjs.com/documentation/concepts/models-and-orm/models#?case-sensitivity, I would like to have case insensitive behavior.

In my queries this is a problem and I am not able to figure out a way to make it NON case sensitive again. I am using MongoDB in production.

Any kind of help or suggestion would be much appreciated.

Charbonneau

Luckily my DB data is lowercase so I ended up lower-casing all entries coming from the front end – Charbonneau

Answer 1

For MongoDB we need to do a native mongo query to get case-insensitive:

const collection = Pet.getDatastore().manager.collection(Pet.tableName);
const res = await collection.find({ name: { $regex: /blue/, $options: 'i' } });
const dataWithObjectIds = await res.toArray();
const dataWithIds = JSON.parse(JSON.stringify(rawDataArr).replace(/"_id"/g, '"id"'));

See here for more on native mongo query - https://mcmap.net/q/1924626/-sailsjs-use-mongodb-without-orm

Striped

Having a blanket "replace _id with id" seems to have a fairly high risk of replacing more than intended. – Guillory

@Guillory I agree - if any other ideas I would jump for them. To be safer about it I'm double quoting the term and the replacement "_id" and "id". – Striped

or you can add case insensitive index on the field? – Analyzer

@VikasKumar can you show an example of this with the example I shared above please? – Striped

Answer 2

As the sails docs you linked specify, you should do this in the database:

Most databases are case sensitive by default but in the rare cases where they aren't and you would like to change that behavior you must modify the database to do so.

Since you're using MongoDB, that means creating a case insensitive index:

db.collection.createIndex({ key: 1 }, {
    collation: {
        locale: 'en',
        strength: 1
      }
})

Valorie

Thanks! I was playing around with this too but seems I am missing something in sails. I am doing the following ` var db = Model.getDatastore().manager; db.collection('Model').createIndex( { key: 1}, { collation: { locale: 'en', strength: 1 } } ); db.collection('Model').find({ "field": input }).limit(5).collation( { locale: 'en', strength: 1 } ).toArray(function(err, records) { sails.log(records)` but records is empty. Seems that when I do this change in the controller(before I had Model.find({}) ) I can't get any record even if it is in the DB – Charbonneau

I haven't actually tried this myself, so the answer could just be wrong. It seems adding a collation complicates queries a bit – Valorie

Maybe ask a new question with specifics on case-insensitive MongoDB queries. The Sails.js reference might scare off the mongo folks – Valorie