Performant randomised fetching in Prisma

  • Prisma
  • Node.js
Have you ever needed to fetch some random rows from a database in Prisma? What if you can't just select random ID's because some rows have been deleted? And what if you have a large database? And what if performance is critical? There are many ways to tackle this problem, each with their own merits. I'm just going to present you with the option I chose.
If you have ever used Prisma before, you will know how amazing it is. Coming from a background in frontend development, Prisma really helped me to dip my toes in backend development. For this particular problem, it was of course not an option to make more than 1 database request for a collection of random rows. Thankfully, Prisma lets you specify an array of ID's to be fetched in 1 request. 
Since some rows have been deleted, we first need an array of available ID's. And obviously, we don't want to fetch the rows themselves just to find out their ID's. The solution I chose is to keep another Prisma model to keep track of available ID's. This meant I could afford to take a portion of the ID's since the payload will be significantly smaller than the actual rows themselves. 
So first, we take a random segment  of available ID's, maybe 500 rows long. Then, we use random indexes to select and push random row ID's into a random ID's array. Once we have that array, all we have to do is pass it to Prisma and let it work it's magic. I guess the only caveat to this approach is that the rows will not be spread over the entire table and will instead be contained within a single random segment. 
const getRandomPosts = async (count, usedPostIds = []) => {
  const pageSize = 500;
  const availableIds = await this.prisma.posts.count();
  const pageCount = Math.ceil(availableIds / pageSize);
  const randomPage = Math.floor(Math.random() * pageCount);
  const availablePostIds = await this.prisma.postsAvailableIds.findMany({
    skip: randomPage * pageSize,
    take: pageSize,
    select: {
      id: true,
    },
    where: {
      id: {
        notIn: usedPostIds,
      },
    },
  });
  const idArray = availablePostIds.map((post) => post.id);
  const randomIds = [];
  for (let i = 0; i < count; i++) {
    const randomIndex =
      getRandomIndex(idArray, [randomIds, usedPostIds]) ??
      Math.floor(Math.random() * idArray.length);
    randomIds.push(idArray[randomIndex]);
    idArray.splice(randomIndex, 1);
  }
  const posts = await this.prisma.posts.findMany({
    where: {
      id: {
        in: randomIds,
      },
    },
  });
  return posts ?? [];
};