Select, filter, order, and limit

A query that lists things needs three parts: a filter, a stable order, and a limit. db.select().from(notes) with nothing else returns every note of every user. That’s not a harmless default, it’s a bug waiting for the table to grow.

Here is the query for “one user’s newest notes”, which is the query we designed the index for:

import { and, desc, eq, isNull } from 'drizzle-orm'

const rows = await db
  .select({ id: notes.id, title: notes.title })
  .from(notes)
  .where(and(eq(notes.authorId, userId), isNull(notes.archivedAt)))
  .orderBy(desc(notes.createdAt), desc(notes.id))
  .limit(20)

Let’s take it apart.

Filter with operators, not strings

eq(), and(), isNull() are functions from drizzle-orm. eq(notes.authorId, userId) becomes "notes"."author_id" = ? with userId bound as a parameter. You never type a condition as text, so you can’t misspell a column and you can’t inject anything.

isNull(notes.archivedAt) keeps only notes that were never archived. Don’t write eq(notes.archivedAt, null). In SQL, NULL is not equal to anything, not even to NULL. That’s three-valued logic: a comparison can be true, false, or unknown. IS NULL is the only correct check, and isNull() is how you write it.

Order with a tie-breaker

desc(notes.createdAt) puts the newest first. But created_at has second precision, and two notes saved in the same second would come back in whatever order SQLite feels like. Add desc(notes.id) as a second key and the order is fixed: same input, same output, every time. Without it, paging through results can show a note twice or skip one.

Select only what you need

The object passed to .select() does two things. It decides which columns SQLite reads, and it becomes the TypeScript type of each row. Here, rows is { id: number, title: string }[]. Try to access rows[0].body and the compiler stops you.

This matters for privacy as much as for speed. If a list screen doesn’t show note bodies, don’t select them. Data you never load can’t leak through a log or a serializer.

Look at the SQL

Call .toSQL() on the query before await and you see what Drizzle sends:

select "id", "title" from "notes"
where (("notes"."author_id" = ?) and (("notes"."archived_at" is null)))
order by "notes"."created_at" desc, "notes"."id" desc
limit ?

Two parameters: the user ID and 20. Drizzle adds more parentheses than you’d write by hand, but the query is readable, bounded, and exactly what we asked for.

Try this on your own: seed 25 notes for one user, several with the same timestamp, and fetch the first 20. Then use .offset(20) for the second page. Run it a few times. The order never changes, thanks to the tie-breaker.