Selecting a random record (libSQL SQLite)

I have a table of questions and I would like to select a single random record to create a quiz. In raw SQL I would use

SELECT * FROM questions ORDER BY RANDOM() LIMIT(1)

SELECT * FROM questions ORDER BY RANDOM() LIMIT(1)

I have no idea how to model this into a drizzle query as there only seem to be asc and desc methods on orderBy - I am using libSQL Thanks

2 Replies

Mykhailo•7mo ago

Hey @danfascia! You can try this:

import { products } from '../drizzle/schema';
import { sql } from 'drizzle-orm';

await db
    .select()
    .from(products)
    .orderBy(sql`RANDOM()`)
    .limit(1);

import { products } from '../drizzle/schema';
import { sql } from 'drizzle-orm';

await db
    .select()
    .from(products)
    .orderBy(sql`RANDOM()`)
    .limit(1);

You can read more about sql operator in the docs.

danfasciaOP•7mo ago

Thank you that's perfect and it works! Great tip

Gaming

Programming

Selecting a random record (libSQL SQLite)