Define users and notes

A Drizzle schema is TypeScript code that describes your tables. It is the single source both the ORM and the migration tool read. It is not a substitute for thinking about the database design, though. You still decide what’s required, what’s unique, and what points at what.

Our app has users and notes. A user has an email and a display name. A note has a title, a body, and an author. Create src/db/schema.ts:

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(''),
})

sqliteTable() takes the table name and an object of columns. int() and text() are the SQLite column types. When you don’t pass a name, the column is named after the key, so email becomes email. For authorId I pass 'author_id' explicitly, because I want camelCase in TypeScript and snake_case in the database.

What each modifier does

.notNull() makes the column required. .unique() adds a unique constraint, so two users can’t share an email. .default('') gives the body a value when the insert omits it.

.references(() => users.id) is the foreign key. It tells SQLite that author_id must match an existing users.id. The arrow function is there so the two tables can reference each other without import-order problems.

Two layers of protection

TypeScript now knows the shape of both tables. Try inserting a note without authorId and the editor complains before you run anything.

SQLite enforces the same rules at runtime: NOT NULL, UNIQUE, and the foreign key. Node’s SQLite driver turns foreign key enforcement on by default, so an insert with an unknown author fails with FOREIGN KEY constraint failed.

We want both layers. TypeScript catches your mistakes while you code. The database catches everyone else’s: a seed script, an admin tool, a colleague running SQL by hand.

Relations are a different thing

Later we add Drizzle relations to fetch a user together with their notes. A relation is a convenience for querying. It does not create a foreign key or appear in a migration. Only .references() does that. We get the database rules right first, then add the conveniences on top.

Nothing has reached the database yet. The schema is just TypeScript until we run a migration, which is what the next lessons are about.