How to Select Multiple Random Records in Sequelize.js

Sequelize is a popular ORM for Node.js that supports multiple SQL dialects. When working with large datasets, you might sometimes need to retrieve a subset of records in a random order – for example, to display random user profiles or products. This article will walk you through the steps to select multiple random records using Sequelize, providing basic to advanced examples.

Getting Started

To begin, ensure you have Sequelize set up in your project. If not, you can install it using npm or yarn:

npm install sequelize
// OR
yarn add sequelize

You also need to set up a connection to your database and define your models as per your schema.

Basic Random Selection

The simplest way to fetch random records is by using the ORDER BY RANDOM() clause (or a similar function, depending on your database system). In Sequelize, you can order your query results randomly like this:

const YourModel = require('./models/YourModel');

async function fetchRandomRecords() {
  const records = await YourModel.findAll({
    order: Sequelize.literal('RAND()'), // Use RANDOM() for PostgreSQL
    limit: 5
  });
  return records;
}

Random Selection with Conditions

Sometimes you might want to fetch random records based on a certain condition. For instance, selecting random active users:

async function fetchRandomActiveUsers() {
  const activeUsers = await YourModel.findAll({
    where: {
      status: 'active'
    },
    order: Sequelize.literal('RAND()'),
    limit: 5
  });
  return activeUsers;
}

Efficient Random Selection

Using ORDER BY RANDOM() might not be efficient for large datasets. In such cases, you can use a different strategy such as randomly generating a set of IDs to fetch. Here’s how you can do that:

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

async function fetchEfficientRandomRecords() {
  const count = await YourModel.count();
  const randomIds = Array.from({ length: 5 }, () => Math.floor(Math.random() * count) + 1);

  const records = await YourModel.findAll({
    where: {
      id: {
        [Op.in]: randomIds
      }
    }
  });

  return records;
}

Advanced Techniques

In databases like PostgreSQL, you can use table sampling to fetch random records more efficiently. Sequelize doesn’t have built-in support for this, but you can still execute raw SQL queries:

async function fetchSampledRandomRecords() {
  const records = await sequelize.query(
    'SELECT * FROM "YourModel" TABLESAMPLE SYSTEM (1) LIMIT 5',
    { model: YourModel }
  );
  return records;
}

Conclusion

This tutorial provided various methods for selecting multiple random records with Sequelize. Remember that while ORDER BY RANDOM() is straightforward, it may not be the most performance-friendly option for larger tables. Use advanced techniques such as random ID selection or table sampling for more efficiency. Always test different strategies and keep in mind the size and nature of your dataset when choosing the best approach.