Sequelize Scopes Implementation

Understanding the Problem

We need to implement different types of Sequelize scopes to query a database of musical instruments and stores. The requirements include:

Implementing a default scope to exclude createdAt and updatedAt fields
Creating named scopes for different instrument types (keyboard, string, woodwind)
Implementing a named function scope for dynamic routes to filter by storeId and sort alphabetically
Bonus: Adding a scope to filter instruments by both storeId and type

The goal is to return specific subsets of data while excluding unnecessary fields, and in some cases, sorting the results.

Devising a Plan

Add default scopes to both Instrument and Store models to exclude createdAt and updatedAt
Add named scopes for each instrument type in the Instrument model
Create a named function scope that takes a storeId parameter to filter instruments
Modify the route handlers in app.js to use these scopes
For the bonus, create another named function scope that takes both storeId and type parameters
Implement the route handler for the bonus challenge

Carrying Out the Plan

Step 1: Add Default Scopes

Let's start by adding default scopes to both models to exclude the createdAt and updatedAt fields.

File: instrument.js

'use strict';
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class Instrument extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      // define association here
      Instrument.belongsTo(models.Store)
    }
  }
  Instrument.init({
    name: DataTypes.STRING,
    type: DataTypes.STRING,
    storeId: DataTypes.INTEGER
  }, {
    sequelize,
    modelName: 'Instrument',
    // Add default scope to exclude createdAt and updatedAt
    defaultScope: {
      attributes: { exclude: ['createdAt', 'updatedAt'] }
    }
  });
  return Instrument;
};

File: store.js

'use strict';
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class Store extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      // define association here
      Store.hasMany(models.Instrument, { foreignKey: 'storeId' });
    }
  }
  Store.init({
    name: DataTypes.STRING,
    location: DataTypes.STRING
  }, {
    sequelize,
    modelName: 'Store',
    // Add default scope to exclude createdAt and updatedAt
    defaultScope: {
      attributes: { exclude: ['createdAt', 'updatedAt'] }
    }
  });
  return Store;
};

Step 2: Add Named Scopes for Instrument Types

Now, let's add named scopes to filter instruments by type (keyboard, string, woodwind).

order: [['name', 'ASC']] } }, // Bonus: Combined scope for both store and type storeAndType(storeId, type) { return { where: { storeId: storeId, type: type }, include: { model: sequelize.models.Store }, order: [['name', 'ASC']] } } } }); return Instrument; };

Step 3: Modify Route Handlers in app.js

Now let's update the route handlers in app.js to use our scopes:

File: app.js (updated routes)

// STEP 2: Implement named scopes to their respective routes
app.get('/instruments/keyboard', async (req, res, next) => {
    const keyboards = await Instrument.scope('keyboard').findAll();
    res.json(keyboards);
});

app.get('/instruments/string', async (req, res, next) => {
    const strings = await Instrument.scope('string').findAll();
    res.json(strings);
});

app.get('/instruments/woodwind', async (req, res, next) => {
    const woodWinds = await Instrument.scope('woodwind').findAll();
    res.json(woodWinds);
});

// STEP 3 CHALLENGE: Implement the named function scopes to their dynamic routes
app.get('/stores/:storeId/instruments', async (req, res, next) => {
    const storeId = req.params.storeId;
    const filterStoreInstruments = await Instrument.scope({ method: ['store', storeId] }).findAll();
    res.json(filterStoreInstruments);
});

app.get('/stores/:storeId/instruments/:type', async (req, res, next) => {
    const storeId = req.params.storeId;
    const type = req.params.type;
    const filteredInstruments = await Instrument.scope(
        { method: ['storeAndType', storeId, type] }
    ).findAll();
    res.json(filteredInstruments);
});

Bonus Challenge: Dynamic Type Route

For the bonus challenge to support any instrument type dynamically, we would add another route:

File: app.js (bonus route)

// BONUS: Route to handle any instrument type dynamically
app.get('/instruments/:type', async (req, res, next) => {
    const type = req.params.type;
    const instruments = await Instrument.scope({ method: ['ofType', type] }).findAll();
    res.json(instruments);
});

Looking Back and Learning

What are Sequelize Scopes?

Scopes in Sequelize allow you to define commonly used queries that you can easily reference later. They are similar to "views" in SQL databases but more flexible since they can be combined and chained.

Types of Scopes We Used:

Default Scope: Applied automatically to every query unless explicitly overridden. We used this to exclude the timestamp fields.
Named Scopes: Predefined scopes that can be referenced by name (like 'keyboard', 'string').
Dynamic/Function Scopes: Scopes that accept parameters to customize the query (like filtering by storeId).

Real-World Applications

Scopes are extremely useful in real-world applications:

E-commerce: Filtering products by category, price range, or availability
Content Management: Showing only published articles or filtering by author
User Management: Filtering active users, admins, or users with specific permissions

Best Practices

Keep your default scope minimal - don't exclude too much data by default
Use named scopes for commonly used filters
Use function scopes for dynamic queries that require parameters
Consider scopes that can be composed (chained together) for more complex queries

Common Mistakes to Avoid

Forgetting that default scopes apply to all queries - sometimes you might need to use unscoped() to get all fields
Creating too many specific scopes instead of using function scopes with parameters
Not considering the performance implications of complex scopes, especially those with includes (joins)

Complete Solution Files

Updated instrument.js

'use strict';
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class Instrument extends Model {
    static associate(models) {
      Instrument.belongsTo(models.Store)
    }
  }
  Instrument.init({
    name: DataTypes.STRING,
    type: DataTypes.STRING,
    storeId: DataTypes.INTEGER
  }, {
    sequelize,
    modelName: 'Instrument',
    defaultScope: {
      attributes: { exclude: ['createdAt', 'updatedAt'] }
    },
    scopes: {
      keyboard: {
        where: {
          type: 'keyboard'
        }
      },
      string: {
        where: {
          type: 'string'
        }
      },
      woodwind: {
        where: {
          type: 'woodwind'
        }
      },
      // Bonus scope for any instrument type
      ofType(type) {
        return {
          where: {
            type: type
          }
        }
      },
      // Store filter with sorting
      store(storeId) {
        return {
          where: {
            storeId: storeId
          },
          include: {
            model: sequelize.models.Store
          },
          order: [['name', 'ASC']]
        }
      },
      // Combined store and type filter
      storeAndType(storeId, type) {
        return {
          where: {
            storeId: storeId,
            type: type
          },
          include: {
            model: sequelize.models.Store
          },
          order: [['name', 'ASC']]
        }
      }
    }
  });
  return Instrument;
};

Updated store.js

'use strict';
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class Store extends Model {
    static associate(models) {
      Store.hasMany(models.Instrument, { foreignKey: 'storeId' });
    }
  }
  Store.init({
    name: DataTypes.STRING,
    location: DataTypes.STRING
  }, {
    sequelize,
    modelName: 'Store',
    defaultScope: {
      attributes: { exclude: ['createdAt', 'updatedAt'] }
    }
  });
  return Store;
};

Updated app.js (route handlers only)

// List of all the instruments in the database
app.get('/instruments', async (req, res, next) => {
    const allInstruments = await Instrument.findAll();
    return res.json(allInstruments);
});

// Keyboard instruments route
app.get('/instruments/keyboard', async (req, res, next) => {
    const keyboards = await Instrument.scope('keyboard').findAll();
    res.json(keyboards);
});

// String instruments route
app.get('/instruments/string', async (req, res, next) => {
    const strings = await Instrument.scope('string').findAll();
    res.json(strings);
});

// Woodwind instruments route
app.get('/instruments/woodwind', async (req, res, next) => {
    const woodWinds = await Instrument.scope('woodwind').findAll();
    res.json(woodWinds);
});

// Dynamic type route (BONUS)
app.get('/instruments/:type', async (req, res, next) => {
    const type = req.params.type;
    const instruments = await Instrument.scope({ method: ['ofType', type] }).findAll();
    res.json(instruments);
});

// Filter by store ID and sort alphabetically
app.get('/stores/:storeId/instruments', async (req, res, next) => {
    const storeId = req.params.storeId;
    const filterStoreInstruments = await Instrument.scope({ method: ['store', storeId] }).findAll();
    res.json(filterStoreInstruments);
});

// Filter by store ID and instrument type
app.get('/stores/:storeId/instruments/:type', async (req, res, next) => {
    const storeId = req.params.storeId;
    const type = req.params.type;
    const filteredInstruments = await Instrument.scope(
        { method: ['storeAndType', storeId, type] }
    ).findAll();
    res.json(filteredInstruments);
});

// List of all stores
app.get('/stores', async (req, res, next) => {
    const allStores = await Store.findAll();
    res.json(allStores);
});