Put rules in the schema

Validation in your application gives users friendly error messages. Constraints in the database protect the data when some other code path forgets to validate. You want both, and the rules that must never be broken belong in the schema.

Let’s finish the notes table. We add a creation timestamp, decide what happens to notes when a user is deleted, and add one index:

import { index, int, sqliteTable, text } from 'drizzle-orm/sqlite-core'
import { sql } from 'drizzle-orm'

export const notes = sqliteTable('notes', {
  id: int().primaryKey({ autoIncrement: true }),
  authorId: int('author_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  title: text().notNull(),
  body: text().notNull().default(''),
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
}, table => [
  index('notes_author_created_idx').on(table.authorId, table.createdAt),
])

Three new things here.

A default the database fills in

createdAt defaults to the SQL expression CURRENT_TIMESTAMP. Wrapping it in the sql tag passes it through as-is, so SQLite sets the time on insert. Your code never has to remember to do it. SQLite stores it as text like 2026-09-07 21:01:45.

Delete behavior is a product decision

onDelete: 'cascade' means deleting a user also deletes their notes. For a notes app that’s reasonable: notes without an owner are useless.

For invoices or audit records it would be a disaster. There, you’d want 'restrict', which refuses to delete a user who still has rows pointing at them. Drizzle can’t make this call for you. Write the option down on purpose, don’t accept whatever the default is.

One index, for one named query

The third argument to sqliteTable() returns an array of extra table-level items. Here it’s a composite index on authorId and createdAt.

I add an index only when I can name the query it serves. Ours is “one user’s notes, newest first”. That query filters on author_id and sorts on created_at, so an index on both columns, in that order, lets SQLite jump straight to the rows.

Every index makes some read faster and every write a little slower, because SQLite has to update it. Don’t sprinkle them everywhere. Start from a real filter and sort, add the index, then check the query plan once there’s data. We do exactly that in the inspection lesson, where the plan changes from SCAN notes to SEARCH notes USING INDEX notes_author_created_idx.

The uniqueness rule stays

users.email keeps its .unique() from the previous lesson. Application code will check emails too, to show a nice message. But the constraint is what guarantees no duplicates exist, whatever path wrote the row.

Before generating the migration, write one sentence for each constraint and for the index, saying which rule it protects or which query it serves. If you can’t write the sentence, you probably don’t need it.