Define users and notes

A Drizzle schema is executable TypeScript that describes database tables. It is not a replacement for database design.

Create src/db/schema.ts. Users have an email and display name. Notes belong to a user through authorId. The foreign key protects the relationship even when another script writes directly to SQLite.

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

export const users = sqliteTable('users', {
  id: int().primaryKey({ autoIncrement: true }),
  email: text().notNull().unique(),
  name: text().notNull(),
})

export const notes = sqliteTable('notes', {
  id: int().primaryKey({ autoIncrement: true }),
  authorId: int('author_id').notNull().references(() => users.id),
  title: text().notNull(),
  body: text().notNull().default(''),
})

TypeScript now knows which fields exist and whether they can be null. SQLite still enforces NOT NULL, uniqueness, and the foreign key at runtime. We want both layers because either one can be bypassed independently.

A relation used for convenient fetching does not create a database foreign key. The .references() call does. We will add Drizzle relations later, after the database rules are correct.

Add the two tables, then intentionally remove authorId from a note value and inspect the TypeScript error. Later, after migrating, attempt an invalid author ID and save the database error too.