Designing a Flexible URL & Content Model

Website Rebuild with Astro

Chapter 2 · Designing a Flexible URL & Content Model

Every sibling course reached the same design independently: a real parent_id foreign key plus a precomputed full_path string, kept in sync. This chapter reaches it a fifth time — with Drizzle ORM, a genuinely new TypeScript-first ORM not yet used anywhere on this site, deliberately not a second course built on Prisma.

The Self-Referencing Schema

// db/schema.ts import { mysqlTable, int, varchar, text, AnyMySqlColumn } from 'drizzle-orm/mysql-core'; import { relations } from 'drizzle-orm'; export const pages = mysqlTable('pages', { id: int('id').primaryKey().autoincrement(), slug: varchar('slug', { length: 255 }).notNull(), fullPath: varchar('full_path', { length: 1024 }).notNull().unique(), title: varchar('title', { length: 255 }).notNull(), body: text('body'), parentId: int('parent_id').references( (): AnyMySqlColumn => pages.id, { onDelete: 'restrict' } ), }); export const pagesRelations = relations(pages, ({ one, many }) => ({ parent: one(pages, { fields: [pages.parentId], references: [pages.id], relationName: 'parentChild', }), children: many(pages, { relationName: 'parentChild' }), }));
A real TypeScript wrinkle, unique to a schema-as-real-code ORM
parentId's own references() call needs a function returning pages.id, not a direct reference — because pages can't be referenced from inside its own definition while TypeScript is still evaluating it. Prisma's own schema.prisma never hits this, since it's a separate declarative DSL file, not real TypeScript being type-checked as it's written. This is a genuine, small cost of Drizzle's own "the schema is real code" design.

Verifying onDelete: 'restrict' — Which Layer, Confirmed

Matches Laravel's layer, not Rails' or Django's
Drizzle's references(..., { onDelete: 'restrict' }) generates a genuine SQL FOREIGN KEY ... ON DELETE RESTRICT clause in the migration itself — a real, database-enforced constraint, exactly like Laravel's own restrictOnDelete(). This is a different layer entirely from Rails' own dependent: :restrict_with_error (an application-level ActiveRecord callback) or Django's own PROTECT (an ORM-level check before deletion) — a raw SQL DELETE bypassing Drizzle entirely would still be refused here, the same guarantee Laravel's own database constraint provides.

No Lifecycle Hooks — A Genuine, Significant Difference

// db/pages.ts import { db } from './client'; import { pages } from './schema'; import { eq } from 'drizzle-orm'; async function computeFullPath(slug: string, parentId: number | null): Promise<string> { if (!parentId) return slug; const [parent] = await db.select().from(pages).where(eq(pages.id, parentId)); return `${parent.fullPath}/${slug}`; } export async function createPage(slug: string, title: string, parentId: number | null) { const fullPath = await computeFullPath(slug, parentId); return db.insert(pages).values({ slug, title, fullPath, parentId }); }
Not a gap — a deliberate design choice, worth naming honestly
Every ORM used across this series so far — ActiveRecord, Eloquent, Django's own ORM, even Prisma via its own middleware — offers some form of lifecycle hook or callback for exactly this "recompute a derived field whenever a row is saved" job. Drizzle offers none — it's deliberately a thin, close-to-SQL query builder, not a framework with model-level magic. computeFullPath has to be called explicitly by every function that creates or updates a page — there's no before_save-equivalent silently doing it automatically. This is a real, significant architectural difference, not a smaller version of the same idea.

Five ORMs, Compared Honestly

DjangoLaravelRailsAstro (Drizzle)
Delete-protection layerApplication-level (PROTECT)Database-level (restrictOnDelete())Application-level (dependent: :restrict_with_error)Database-level (onDelete: 'restrict')
Lifecycle hooks?Yes — signalsYes — model eventsYes — callbacks (before_save)No — explicit helper functions only

Migrations

npx drizzle-kit generate npx drizzle-kit migrate

drizzle-kit generate diffs schema.ts against the current migration history and writes a new SQL migration file; drizzle-kit migrate applies it.

Hands-On Exercises

Exercise 1

Define the self-referencing pages table in Drizzle, including the function-based workaround needed for parentId's own circular reference to pages.id.

📄 View solution
Exercise 2

Write and test computeFullPath(), confirming it correctly builds a nested path for a page with a real parent, and returns the bare slug for a page with no parent.

📄 View solution
Exercise 3

Confirm onDelete: 'restrict' is a real database-level constraint by attempting a raw SQL DELETE (bypassing Drizzle entirely) against a page that still has children, and observing the database itself refuse it.

📄 View solution

Chapter 2 Quick Reference

  • references((): AnyMySqlColumn => pages.id, ...) — the function-based workaround for a self-referencing foreign key
  • onDelete: 'restrict' — a real database-level constraint, matching Laravel's own layer
  • No lifecycle hooks — Drizzle's own deliberate, minimal design; full_path is computed by an explicit helper, not a callback
  • drizzle-kit generate / migrate — Drizzle's own migration workflow
  • Next chapter: Routing — Astro's Own Catch-All, Now Database-Backed