2

I am unable to get the case insensitive search from the database using Sails.js V1.0 + Waterline ORM. I am using sails-postgresql adapter. The running environment is Heroku + Heroku PostgreSQL.

Is there any way to turn off the following setting in database adapter - For performance reasons, case-sensitivity of contains depends on the database adapter.

Tried the method:

Datastore configuration is:

default: {
  adapter: 'sails-postgresql',
  url: 'postgres://....',
  ssl: true,
  wlNext: {
    caseSensitive: true
  }
}

The code block is:

var meetings = await Meeting.find({
  select: ['id', 'uid', 'name', 'deleted', 'complete'],
  where: {
    owner: user.id,
    name: { contains: searchJson.name } : ""
  },
  skip: (inputs.page > 0) ? (inputs.page) : 0,
  limit: (inputs.limit > 0) ? (inputs.limit) : 10,
  sort: 'date DESC'
});
Binu Paul
  • 127
  • 12

3 Answers3

4

The easiest way I've found to handle this and case-insensitive unique indexes with PG/Sails is to use the citext column type instead of text/character varying types (compared to forcing everything to lowercase which stinks).

citext is a case insensitive text datatype. "Essentially, it internally calls lower when comparing values. Otherwise, it behaves almost exactly like text."

An example model attribute pulled from a working app:

username: {
  type: 'string',
  columnType: 'citext',
  required: true,
  unique: true,
  description: 'A users.. Username',
  // ...
},

According to this (somewhat irellevant) Heroku docs page this looks like it should work, but you may need to run create extension citext; on your database first.

nahanil
  • 522
  • 3
  • 9
  • 1
    What option do we have with Sails v1.2.4 and mongodb? – Codetard Aug 13 '20 at 13:11
  • Faced a similar issue, but also materialized views had dependencies on a table I wanted to change column type. In this case, you'll need to drop materialized views, then update the column of the table with the command below: "ALTER TABLE table_name ALTER COLUMN column_name TYPE citext;" and after that recreate materialized views that were dropped before. – Stepan Kovalyshyn Sep 08 '21 at 17:54
0

For MongoDB https://github.com/balderdashy/sails/issues/7014. From sails-mongo@1.2.0, you can chain on .meta({makeLikeModifierCaseInsensitive: true}) for a case-insensitive query. Example

await User.find(criteria).meta({makeLikeModifierCaseInsensitive: true});
Marcell
  • 101
  • 2
0

You can use native queries, for example:

const eventTypesRows = await Meeting.getDatastore()
  .sendNativeQuery(\`SELECT "name" FROM meeting WHERE LOWER(name) = LOWER($1)\`, [searchName]);
Artyom
  • 1
  • 1