Inspect SQL, plans, and data

Type-safe SQL can still be slow SQL. Types prove the shape of a query, not its cost. When something is slow, or you want to know whether an index earns its keep, you need evidence. Here are the three sources I use, in order.

The generated SQL

Start with .toSQL() on the query, as we did in the select lesson. Read the statement and its parameters. Very often a “slow query” turns out to be a query that fetches far more than intended, and the SQL makes that obvious.

The query plan

SQLite can tell you how it intends to run a statement, without running it. Prefix the SQL with explain query plan. We use the sql template and db.all(), since this is raw SQL:

import { sql } from 'drizzle-orm'

const plan = db.all<{ detail: string }>(sql`explain query plan
  select id, title from notes
  where author_id = ${userId} and archived_at is null
  order by created_at desc, id desc
  limit 20`)

console.log(plan.map(row => row.detail))

With the composite index from the schema lesson in place:

[ 'SEARCH notes USING INDEX notes_author_created_idx (author_id=?)' ]

SEARCH ... USING INDEX means SQLite jumps to this user’s rows through the index, and because the index is also ordered by created_at, it reads them in the right order.

Now drop the index and ask again:

[ 'SCAN notes', 'USE TEMP B-TREE FOR ORDER BY' ]

SCAN notes means it reads the entire table. USE TEMP B-TREE FOR ORDER BY means it then sorts everything in a temporary structure. With a hundred notes, you won’t feel it. With a million, you will. That’s the plan change the index buys, in two lines you can paste into a pull request.

Enough data to matter

A plan tells you the strategy. Timing tells you the cost, and timing on twenty rows is meaningless. Every strategy is fast on twenty rows.

Before comparing, seed a realistic amount: a few thousand notes across a few hundred users, with timestamps spread out. Then time the query with and without the index. Keep the numbers next to the plan. The next person who wonders “do we need this index?” gets an answer instead of an opinion.

Look at the rows with Studio

npx drizzle-kit studio opens a browser UI that reads your drizzle.config.ts and lets you browse and edit tables. During development it’s the fastest way to check that a migration produced the columns you expected, or that a seed script did what you meant.

Be careful with it against anything shared. Studio has the full authority of the credentials in your config. If that’s a production connection string, one accidental edit is a production edit. Point it at a local or disposable database, and keep production access for reviewed migrations.

Repeat the plan comparison on your own project for the query you designed your index for. Save both plans, write one sentence explaining the difference in plain words, and you have documentation that’s actually verifiable.