sails.jswaterlineheroku-postgressails-postgresql

How can I use the Find method to perform a case insensitive search on the database field?


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'
});

Solution

  • 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.