Separate foreign keys and relations

Foreign keys and Drizzle relations describe the same connection, users to notes, at two different layers. People mix them up all the time, so let’s be precise before we write any relation code.

The foreign key lives in the database

authorId: int('author_id').notNull().references(() => users.id) is the foreign key. It ends up in the migration as a FOREIGN KEY constraint, and SQLite enforces it on every write.

Its job is integrity. A note cannot point at a user who doesn’t exist. Delete a user and the onDelete: 'cascade' we chose removes their notes. This holds no matter who writes: your app, a seed script, a one-off SQL fix at 2am.

The relation lives in Drizzle

A relation is metadata you give Drizzle so its query API knows how to fetch related rows. “A user has many notes, through notes.authorId.” With that, you can ask for a user with their notes in one call and get a nested object back.

Its job is convenience. It does not create a constraint. It does not appear in a migration. Drop the relation and your database is exactly as safe as before; you’ve only lost a nice way to query.

Why the distinction matters

Imagine an import script that writes to SQLite directly, skipping Drizzle. Or an admin panel from another team. Or a second service sharing the database.

With the foreign key in place, a bad author_id from any of them fails with FOREIGN KEY constraint failed. With only a Drizzle relation, that row gets in, and your nested query later returns a note whose author is null, or crashes on it.

The database rule protects everyone. The application rule protects only code that goes through the application.

Keep both

Some people skip foreign keys because the relational query API works without them. Drizzle lets you do that. I don’t recommend it.

Keep the constraint. Add the relation on top for querying. The only reasonable exception is a distributed setup where the related row lives in another database, and then you need a different plan for integrity, not just a missing constraint.

See it for yourself

Take a disposable copy of the project and remove each layer in turn.

Remove .references() from the schema, generate and apply the migration, then insert a note with authorId: 999. It succeeds. That’s the hole a missing foreign key leaves.

Put it back, and instead skip the relation definition we write in the next lesson. Now db.query.users.findFirst({ with: { notes: true } }) doesn’t compile, because Drizzle doesn’t know how users and notes connect. Nothing is unsafe, it’s just inconvenient.

Two layers, two failure modes. Now you know which one you’re looking at.