How to use Lowercase function in Sequelize Postgres

Using the LOWER() function in PostgreSQL within Sequelize primarily involves incorporating specific functions into queries to process data. Sequelize, as an ORM (Object-Relational Mapping tool), offers a straightforward method to integrate native SQL functions, such as PostgreSQL's LOWER() function.

Basic Usage

When you need to apply the LOWER() function to a specific field in a query, you can use Sequelize's sequelize.fn method. This method enables you to invoke native database functions. Here is a basic example, assuming you have a User model (User), and you want to find all users with a username (username) of lowercase 'john_doe'.

javascript
const { User } = require('../models'); // Import User model

async function findUserByUsername(username) {
  return await User.findAll({
    where: {
      // Use sequelize.fn to call the LOWER function
      username: sequelize.where(
        sequelize.fn('LOWER', sequelize.col('username')), 
        sequelize.fn('LOWER', username)
      )
    }
  });
}

// Call the function
findUserByUsername('JOHN_DOE').then(users => {
  console.log(users); // Output query results
});

Explanation

In the above code:

• sequelize.fn('LOWER', sequelize.col('username')): This converts each value of the username column to lowercase.
• sequelize.fn('LOWER', username): This converts the input username parameter value to lowercase.
• sequelize.where: This is Sequelize's function for constructing conditions, comparing the two LOWER()-processed values.

Advanced Usage

If you need to use LOWER() in more complex queries, such as joins or sorting, Sequelize also supports it.

Using in Sorting

Suppose you want to sort users based on the lowercase version of their username:

javascript
async function getUsersOrderedByUsername() {
  return await User.findAll({
    order: [
      [sequelize.fn('LOWER', sequelize.col('username')), 'ASC']
    ]
  });
}

// Call the function
getUsersOrderedByUsername().then(users => {
  console.log(users); // Output sorted user list
});

Notes

• When using native SQL functions, especially in web applications, ensure your inputs are secure to prevent SQL injection attacks.
• When using sequelize.fn and sequelize.col, verify that referenced column and table names are correct to avoid runtime errors.

By using the above methods, you can flexibly utilize PostgreSQL's LOWER() function in Sequelize, whether in simple queries, sorting, or complex queries.