PostgreSQL typically uses case-sensitive mappings. This means that the data that appears in each column is compared literally with your query.
You have several options:
1. Follow Ben's answer and wrap your columns and database in a call to sequelize.fn('lower') .
Pros: no changes to the database.
Cons: you need to remember to use it for every request. Foregoes indexes (if you have not already created a functional index) and consecutively scan tables, which leads to a slower scan with large tables. Pretty detailed code.
2. Use ILIKE to insensitive to pattern
To find the name exactly:
Db.models.Person.findAll(where: {firstName: {$iLike: 'name'}});
To find a fragment that can be contained within arbitrary characters:
Db.models.Person.findAll(where: {firstName: {$iLike: '%name%'}});
Pros: easy to remember. No Sequelize wrapper is a built-in statement, so the syntax is more accurate. No special indexes or database changes are required.
Cons: Slowly unless you start messing with extensions like pg_trgm
3. Define your text columns with a citext type that implicitly compares lowercase
Defining column types as "citext" (instead of text or character varying ) has the same practical effect:
select * from people where name = 'DAVID'
to that...
select * from people where LOWER(name) = LOWER('DAVID')
The PostgreSQL documentation shows this as an example of how to create a table of type citext:
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 replaces your text columns with citext.
The citext module comes bundled with PostgreSQL 8.4, so there is no need to install any extensions. But you need to include it in every database that you use with the following SQL:
CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;
And then in the Sequelize definitions, define the 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 search queries against this column will always be case insensitive with regular queries where =
Pros: No need to wrap your queries in ugly sequelize.fn calls. No need to remember clearly lowercase letters. Knowledge of the locale, therefore, works in all character sets.
Cons: You must remember to use it in your Sequelize definitions when you first define your table. Always activated - you need to know that when defining tables, you will need to do a case-insensitive search.