Paginate and use an ORM deliberately

Any list that grows needs pagination. And the first rule of pagination is ordering. Every paginated query must order by something stable and unique. order by created_at alone is not stable when two notes share a timestamp. Add the primary key as a tiebreaker, or page boundaries become random.

Offset pagination drifts

Offset pagination is the easy one:

select id, title, created_at from notes
where user_id = ?
order by created_at desc, id desc
limit 10 offset 20;

It works, until the data moves. If a new note arrives while a user is reading page 2, everything shifts by one and page 3 repeats an item they already saw. Offsets also get slower as they grow, because the database still walks past every skipped row.

Cursor pagination holds still

Cursor pagination fits long or changing lists better. Instead of a page number, the client sends the sort values of the last item it saw:

select id, title, created_at from notes
where user_id = ? and (created_at, id) < (?, ?)
order by created_at desc, id desc
limit 10;

The query says “the next 10 older than this exact position”. New rows cannot shift the window. And the index from the earlier lessons serves it at the same cost on page 2 and on page 200.

Drizzle is a tool, not a shield

Drizzle lets you define the schema in TypeScript and write typed queries with autocomplete:

const rows = await db.select().from(notes)
  .where(eq(notes.userId, userId))
  .orderBy(desc(notes.createdAt), desc(notes.id))
  .limit(10)

I like this. No typos in raw strings, and a column rename becomes a compile error.

But the deployed database still runs SQL. It still needs indexes, constraints and a migration history. The ORM does not add an index you never created. A Drizzle query that produces SCAN notes is exactly as slow as the handwritten version.

Be careful with the wiring too. drizzle-kit generates SQL migration files into a directory, and Wrangler applies them. The migrations_dir in wrangler.jsonc must point where drizzle-kit writes, so check both configs against the current D1 and Wrangler docs. Two tools each half-managing migrations is how schemas drift.

Try this on the notes app: add cursor pagination to the list endpoint. Then compare the SQL Drizzle emits with your handwritten version, and run both through EXPLAIN QUERY PLAN. They should use the same index.