How to use Lowercase function in Sequelize Postgres

Question

I am trying to use the lowercase function to do string searching in Sequelize. I manage to do it using the ilike. My question is how to use the lowercase function in this scenario?

The findAll using ilike is as following:

Db.models.Person.findAll(where: {firstName: {$ilike: `somename`}});

How do I change it to lower(firstname) = lower('somename');

Answer 1

PostgreSQL generally uses case-sensitive collations. This means data that appears in each column is compared literally against your query.

You have several options:

---

1. Follow Ben's answer, and wrap your wrap columns and database in a sequelize.fn('lower') call.

Pros: No database changes.

Cons: You need to remember to use it for every query. Foregoes indexes (unless you've already created a functional index) and scans tables sequentially, resulting in slower look-ups with large tables. Quite verbose code.

---

2. Use ILIKE, to case-insensitively match a pattern

To find the name exactly:

Db.models.Person.findAll(where: {firstName: {$iLike: 'name'}});

To find a fragment, which may be contained within arbitrary characters:

Db.models.Person.findAll(where: {firstName: {$iLike: '%name%'}});

Pros: Easy to remember. No Sequelize function wrappers - it's a built-in operator, so syntax is neater. No special indexes or database changes required.

Cons: Slow, unless you start messing with extensions like pg_trgm

---

3. Define your text columns with the citext type, which implicitly compares lowercase

Defining your column types as 'citext' (instead of text or character varying) has the same practical effect of turning this:

select * from people where name = 'DAVID'

to this...

select * from people where LOWER(name) = LOWER('DAVID')

The PostgreSQL documentation shows this as an example of how to create your table with the citext type:

CREATE TABLE users (
    nick CITEXT PRIMARY KEY,
    pass TEXT   NOT NULL
);

INSERT INTO users VALUES ( 'larry',  md5(random()::text) );
INSERT INTO users VALUES ( 'Tom',    md5(random()::text) );
INSERT INTO users VALUES ( 'Damian', md5(random()::text) );
INSERT INTO users VALUES ( 'NEAL',   md5(random()::text) );
INSERT INTO users VALUES ( 'Bjørn',  md5(random()::text) );

SELECT * FROM users WHERE nick = 'Larry';

TL;DR basically swap out your "text" columns for "citext".

The citext module comes bundled with PostgreSQL 8.4, so there's no need to install any extensions. But you do need to enable it on each database you use it with the following SQL:

CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;

And then in your Sequelize definitions, define a type attribute:

// Assuming `Conn` is a new Sequelize instance
const Person = Conn.define('person', {
  firstName: {
    allowNull: false,
    type: 'citext' // <-- this is the only change
  }
});

Then your searches against that column will always be case-insensitive with regular where = queries

Pros: No need to wrap your queries in ugly sequelize.fn calls. No need to remember to explicitly lowercase. Locale aware, so works across all character sets.

Cons: You need to remember to use it in your Sequelize definitions when first defining your table. Always activated - you need to know that you'll want to do case insensitive searching when defining your tables.