Update one owned note

The most expensive line you can forget in an update is .where(). Without it, db.update(notes).set({ title: 'Hello' }) renames every note in the table. Drizzle runs it without complaint, because it’s valid SQL.

So the first rule is: always filter. The second rule, for data that belongs to a user, is: filter on ownership too.

Here is how I update a note:

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

const [updated] = await db
  .update(notes)
  .set({ title: 'A better title' })
  .where(and(eq(notes.id, noteId), eq(notes.authorId, userId)))
  .returning()

The condition has two parts. eq(notes.id, noteId) picks the note. eq(notes.authorId, userId) requires that it belongs to the current user. Both must be true, or nothing changes.

One statement, not three

The tempting alternative is to select the note, check note.authorId === userId in JavaScript, then update. Three steps, and a gap between them. Another request could delete the note or change the owner in that gap.

The single conditional update has no gap. The database evaluates the ownership check and the write together. It is also less code.

Read the result

.returning() gives back the updated rows. When the array is empty, updated is undefined. That happens in two cases: the note doesn’t exist, or it exists but belongs to someone else.

I return the same response for both, typically a 404. If you answer “not yours” for the second case, you’ve just confirmed to an attacker that note 4821 exists. Not knowing is the safer answer.

When updated is a row, you have the new state, no second query needed.

What Drizzle can and can’t check

TypeScript will stop you from setting a column that doesn’t exist, or from putting a string in authorId. Good.

It cannot know where userId came from. If you read it from the request body instead of the verified session, the ownership check is theater: the client picks the value it’s checked against. Authorization is your job, at the edge of the application. Drizzle only guarantees that the SQL says what your code says.

The three cases

Run the update as the note’s owner, as a different signed-in user, and with an ID that doesn’t exist. Then select the note and check the title.

Only the first run returns a row and changes the title. The other two return undefined and leave the database as it was.

Turn that into a test. Write it so that removing eq(notes.authorId, userId) makes it fail. That test is the cheapest insurance you’ll ever buy against someone “simplifying” the condition next year.