CODEWITHSUNDEEP
postgresqlsamplingprisma
Aerodynamika
Aerodynamika

Reputation: 8413

How to sample a PostgreSQL database using Prisma?

Suppose I have several millions of statements in my PostgreSQL database and I want to get only 10000 of them. But not the first 10000, rather, a random selection of 10000 (it would be best if I could also choose the logic, e.g. select every 4th statement).

How could I do this using Prisma, or — if it's not possible using Prisma — using a good old PostgreSQL request?

For now, I'm using this code to limit the number of results I'm getting:

const statements = await this.prisma.statement.findMany({
      where: {
        OR: conditions,
      },
      orderBy: {
        createdAt: 'asc',
      },
      take: 10000,
    });

This will use the conditions I have, then order them in ascending order, and "take" or limit the first 10000 results.

What could I use in place of the "take" or what request I could make directly in PostgreSQL to randomly sample my DB for records?

Upvotes: 3

Views: 998

Answers (2)

Rob
Rob

Reputation: 1

To reiterate Nurul's response, this isn't supported in Prisma.

If you have some field that auto-increments like an id you could likely generate an array of randomly selected ids and then query for those. This may not be ideal for a larger sample of 100,000 or 1,000,000 but in the case of 100 to 10,000 I think this could be a viable fallback solution until functionality is supported

const idsArray = [5, 9, 13, 2, 18]; // Random array of IDs

const statements = await this.prisma.statement.findMany({
    where: {
        id: {
            in: idsArray,
        },
    },
});

Upvotes: 0

Nurul Sundarani
Nurul Sundarani

Reputation: 7618

Prisma doesn't natively support fetching random data as of now.

There is a Feature Request that discusses the exact same scenario as you need.

The alternative could be to use queryRaw for raw database access and use PostgreSQL's random function as described in the above mentioned Feature Request.

Upvotes: 1

Related Questions