Select, filter, order, and limit

A useful collection query needs a filter, stable order, and limit. Returning every row is not a harmless default.

Use operators such as eq() instead of writing condition strings. Select only the columns the caller needs, order newest first, and add the ID as a tie-breaker when timestamps can match.

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)

Use isNull() for null checks rather than copying = NULL SQL. SQL null has three-valued logic, so ordinary equality is not the right comparison.

The selected object shape becomes the result type. This is useful for privacy too: if a list does not need note bodies, do not select and move them through the application.

Seed 25 notes with repeated timestamps, then request the first two pages using a bounded limit. Save the generated SQL and prove the order stays deterministic across repeated runs.