A deeper dive into Sequelize queries: dynamic field names, the Op object, and raw SQL insertions

I recently built a MySQL database using the Sequelize ORM. Some of the querying functions I needed to run required some interesting syntax that I couldn't find specifically outlined in the Sequelize docs, or found the docs a little unclear on, so I thought I'd lay some of them out in this blog in case other people are looking for similar solutions.

Querying with dynamic field names

With Sequelize, you can query for specific field names dynamically. For example, if a user on your app would like to edit a section of their bio, instead of writing one function for every field, or passing back the whole bio along with the updated section every time there's an edit, you can write one function to update any given part of a user's bio in the database.

const updateUserBio = async (req, res) => {
    try {
        const { id } = req.params;
        const { name, bio, link_instagram, photo } = req.body;

        await Show.update(
            { name, bio, link_instagram, photo },
            {
                where: { id: id },
            })
        res.sendStatus(200);
    }
    catch (err) {
        console.log(err);
        res.sendStatus(400);
    }
}

Here, the front end needs to pass back all fields of the record every time I want to update even one section. Instead, I could grab the name of the field from the front end in addition to the new information, and query like so:

const updateUserBio = async (req, res) => {
    try {
        const { id } = req.params;
        const { fieldName, newInfo } = req.body;

        await User.update(
            { [fieldName]: newInfo },
            {
                where: { id },
            })
        res.sendStatus(200);
    }
    catch (err) {
        console.log(err);
        res.sendStatus(400);
    }
}

The above code is less redundant than the first example. It's also more elegant, more flexible, and reusable than creating an update function for each individual field of a given table.

Case-insensitive "contains" querying with the Op object

I wanted to allow a user to be able to just search for part of a field value and return a valid, case-insensitive result. (I've since learned about Fuse.js, a really cool library for "fuzzy" querying. But this was how I figured out some version of that before I'd heard of Fuse, and is a reasonable solution if you only want to return exact matches for searches, or don't want to install another dependency into your project.)

Sequelize comes with "operators," which are basically a big handful of comparator functions that live on its Op object. These are useful additions to your where clauses when you're looking for mathematical or comparator operations to run on your data.

One method in particular, Op.like, is really useful in conjunction with a value that looks something like this %{query}%, as below.

const searchBands = async (req, res) => {
    try {
        const { query } = req.params;
        const bands = await User.findAll({
                where: { 
                    { name: { [Op.like]: `%${query}%`} } 
                },
            })
        res.send(bands);
    }
    catch (err) {
        console.log(err);
        res.sendStatus(404)
    }

If I have three bands in my database that have names The Smiths, The Locksmiths, and The Smithies, a user query for "smith" would return all three of those results. Basically, Op.like plus that query field with the % returns results that contain whatever the passed in query is, AND it's case-insensitive. Sweet!

The Op object can be really useful in general. Here's an example of how to use it to inject AND and OR logic into your queries. Below, we're looking for records where either the nickname field includes the query AND the id_type is 2, OR records where the firstName field includes the query string AND the id_type is 2.

const searchBands = async (req, res) => {
    try {
        const { query } = req.params;
        const bands = await User.findAll({
                where: {
                    [Op.or]: [
                        {[Op.and]: [
                            { nickname: { [Op.like]: `%${query}%`} }, 
                            { id_type: 2 }
                        ]},
                        {[Op.and]: [
                            { firstName: { [Op.like]: `%${query}%`} }, 
                            { id_type: 2 }
                        ]}

                    ]
                }
            })
        res.send(bands);
    }
    catch (err) {
        console.log(err);
        res.sendStatus(404)
    }

Use raw SQL queries with your Sequelize

If you're comfortable with raw SQL and are using Sequelize, sometimes it might make the most sense for you to throw a raw SQL query in the mix. This can be done like so:

const { Sequelize } = require('sequelize');

const sequelize = new Sequelize(DB_NAME, DB_USER, DB_PASS, {
  dialect: 'mysql',
  host: DB_HOST,
  timestamps: false,
  pool: {
    max: 3,
    min: 0,
    idle: 10000
  },
});

const getBandFollowers = async (req, res) => {
    try {
        const { id } = req.params;
        const sql = `SELECT * FROM users WHERE id IN (
                        SELECT id_fan FROM fans_bands WHERE id_band = ?)`;
        const followers = await sequelize.query(sql, {
            replacements: [id]
        })
        res.status(200).send(followers);
    }
    catch () {
        res.sendStatus(400);
    }
}

You call the query on the sequelize connection that you've created. Write the raw query and pass it to sequelize.query. If you have replacements to pass in, simply add them as a second argument to the query function in an object with the key of replacements.

Comments

[Roberto Wiest]

Hello very good your article. How could I use the getBandFollowers function in an external file, for example, database.js would use the function to connect to the database and another controller.js file containing the getBandFollowers function. How would be the tulization of this function?

[Surendar]

Simply superb!