Drizzle Team•4mo ago

Best way to select 10 random rows from a table?

Hey, I have a need to select 10 random rows from a table in my postgres db. One option I've been looking at is the tsm_system_rows extension, but I'm not 100% sure what the best way to to install an postgres extension w/drizzle is (the docs say there is no specific way) This the best answer I've gotten so far, but it seems like it might not be the most performant as the table gets larger

db.select({ id: item.id })
  .from(item)
  .where(eq(item.isRandomizable, false))
  .orderBy(sql`RANDOM()`)
  .limit(10);

db.select({ id: item.id })
  .from(item)
  .where(eq(item.isRandomizable, false))
  .orderBy(sql`RANDOM()`)
  .limit(10);

1 Reply

Mario564•4mo ago

Hi there. I personally recommend handling the randomization in the backend, like generating random integers with TS and using inArray(item.id, randomIds) (that's just an idea, could be different depending on your needs) If you want to use the extension you mentioned, then you can create a blank migration running npx drizzle-kit generate --custom and add the query/statement to add the extension:

CREATE EXTENSION tsm_system_rows;

CREATE EXTENSION tsm_system_rows;

Gaming

Programming

Best way to select 10 random rows from a table?