Designing a Flexible URL & Content Model

Website Rebuild with Express

Chapter 2 · Designing a Flexible URL & Content Model

A sixth arrival at the same design every sibling course reached independently — a real parent_id foreign key plus a precomputed full_path string. This time, written directly in SQL, with no ORM standing between the code and the schema at all.

The Schema, By Hand

-- schema.sql CREATE TABLE pages ( id INT AUTO_INCREMENT PRIMARY KEY, slug VARCHAR(255) NOT NULL, full_path VARCHAR(1024) NOT NULL UNIQUE, title VARCHAR(255) NOT NULL, body TEXT, parent_id INT, FOREIGN KEY (parent_id) REFERENCES pages(id) ON DELETE RESTRICT );
Closing the loop on Astro's own Drizzle finding
The Astro rebuild's own Chapter 2 needed a function-based workaround — references((): AnyMySqlColumn => pages.id, ...) — specifically because pages couldn't be referenced from inside its own definition while TypeScript was still evaluating it. Raw SQL DDL has no such problem: FOREIGN KEY (parent_id) REFERENCES pages(id) reads perfectly naturally, with no special syntax at all. That earlier workaround wasn't a fundamental complexity of self-referencing tables — it was an artifact of representing the schema as TypeScript code, evaluated top to bottom. Plain SQL shows that clearly.

The Connection

// db.js const mysql = require('mysql2/promise'); const pool = mysql.createPool({ uri: process.env.DATABASE_URL, charset: 'utf8mb4', }); module.exports = pool;

ON DELETE RESTRICT: No Ambiguity Left

Laravel's restrictOnDelete() and Astro's Drizzle onDelete: 'restrict' were both verified as real database-level constraints — but each still required checking, since an ORM sits between the code and the schema and could, in principle, have implemented it at either layer. There's no such question here: with no ORM present at all, ON DELETE RESTRICT is a raw SQL clause, enforced by the database engine directly. This is the cleanest, most unambiguous version of the same finding in the whole series.

Computing full_path, By Hand

// lib/pages.js async function computeFullPath(pool, slug, parentId) { if (!parentId) return slug; const [rows] = await pool.query('SELECT full_path FROM pages WHERE id = ?', [parentId]); return `${rows[0].full_path}/${slug}`; } module.exports = { computeFullPath };
No lifecycle hooks — even more obviously true here
Drizzle had no lifecycle hooks, requiring an explicit helper function to compute full_path. With no ORM present at all, that's not even a design choice to note anymore — there's nothing that could have provided a hook in the first place. computeFullPath is simply called directly, every time, by whatever code creates or updates a page.

Hands-On Exercises

Exercise 1

Write the raw CREATE TABLE schema.sql, and confirm it applies successfully via the mysql CLI or mysql2 directly.

📄 View solution
Exercise 2

Confirm the self-referencing FOREIGN KEY needs no special workaround in raw SQL, contrasting this directly with Drizzle's own TypeScript circular-reference issue from the Astro rebuild's Chapter 2.

📄 View solution
Exercise 3

Write and test computeFullPath(), confirming correct behavior both for a root page (no parent) and a nested page (a real parent already in the database).

📄 View solution

Chapter 2 Quick Reference

  • FOREIGN KEY (parent_id) REFERENCES pages(id) — no workaround needed, unlike Drizzle's own TypeScript-specific issue
  • ON DELETE RESTRICT — the cleanest, most unambiguous database-level constraint in the whole series
  • computeFullPath — a plain parameterized query, called explicitly, with no ORM lifecycle hook to lean on
  • Next chapter: Routing — Express's Own Wildcard, Hand-Wired