Blog / CMS on Nuxt 4, MySQL 8 and Better Auth

Nuxt 4 (framework mode): full-stack Vue SSR: client under app/ (Vite), server under server/ (Nitro), API as server/api/*.post.ts Nitro route handlers.

MySQL 8 via Drizzle ORM and the mysql2 driver.

Better Auth: self-hosted auth running inside your app against your Postgres (Drizzle adapter).

Blog / CMS: authored posts with a draft→published→archived workflow, slug-keyed taxonomy (categories + tags via join), and moderated reader comments.

Database client

import { drizzle } from "drizzle-orm/mysql2";
import mysql from "mysql2/promise";

// ponytail: single module-level pool; the runtime + mysql2's pool handle concurrency,
// so no globalThis singleton dance needed.
const pool = mysql.createPool(process.env.DATABASE_URL!);

export const db = drizzle({ client: pool });

the posts table with slug-unique constraint, author FK into Better Auth's user, and a status/publishedAt pair that drives the published feed index

the slug-keyed categories and tags tables plus the post_tags join that attaches many tags to many posts

the comments table hanging off posts with a pending→approved→spam moderation status and per-post thread index

Client/server split: the DB client, Drizzle schema, records, and webhooks are server-side (server/). The `@/` alias is the client root (app/); server code reaches shared modules via Nuxt's `~~` rootDir alias (e.g. `~~/server/db/schema`).

The API layer is Nitro, Nuxt's server engine: endpoints are server/api/*.post.ts route handlers, and auth mounts as a Nitro catch-all that delegates to the auth library's framework-agnostic web handler.

Nuxt auto-imports components and composables at runtime, but the emitted server code imports h3 helpers (defineEventHandler, toWebRequest) EXPLICITLY — the one deliberate idiom trade so the handlers type-check under standalone tsc instead of relying on the auto-import magic.

Session gating runs in a Nitro server middleware (server/middleware/), which fires on every SSR and API request — the true security boundary, and a real server-side session check rather than a cookie-existence peek.

mysql2's pool multiplexes connections; drizzle-orm/mysql2 wraps it. One module-level pool is right for a serverless/edge app — the runtime and the pool handle concurrency.

MySQL has no row-level security: multi-tenant isolation is enforced in application code via the forOrg helper (src/lib/tenant.ts), not by the database. See the tenant-scoping section on SaaS pages.

Self-hosted: Better Auth owns the user/session/account/verification tables. This stack emits them (db/auth-schema.ts) and hands them to the Drizzle adapter, so app-type schemas can foreign-key `user` directly.

post_tags is keyed by a composite primary key on (post_id, tag_id) — the tagging identity used by application-level upserts. (Postgres also carries a redundant explicit unique on the same columns; MySQL relies on the composite PK alone.)

Comments default to 'pending' and require explicit promotion to 'approved'; the CHECK on both posts and comments uses text + CHECK rather than pgEnum so new statuses ship without an ALTER TYPE migration.

MySQL provides no row-level security. On MySQL, multi-tenant isolation is APP-ENFORCED via the forOrg helper (src/lib/tenant.ts), not database-enforced like Postgres RLS. Every org-scoped query MUST go through forOrg — a missed query leaks across tenants. Postgres cells enforce this in the database itself (RLS), so it holds even for a query that forgets to scope.

Blog / CMS

The publishing workflow is not a side field on this schema — it is the column the read path turns on. posts.status is text constrained by posts_status_check to 'draft', 'published' or 'archived', and idx_post_status_published indexes (status, published_at) in that order: equality on the status, then an ordered walk of the timestamp. The public index page therefore arrives newest-first with no sort step, the one read here where ordering comes free from the index, and the reason those two columns share a composite instead of sitting in separate indexes. published_at is deliberately not created_at. A draft has a creation time and a null published_at; the timestamp is written when the post goes live, which also lets an editor back-date or schedule by setting it directly.

Nothing at the database level ties the pair together — a row can be 'published' with a null published_at and the CHECK will accept it — so that invariant belongs to whatever handles the transition. Identity is doubled on purpose. slug is unique and is what the URL carries, but comments.post_id and post_tags.post_id both reference posts.id, the generated surrogate key. Renaming a slug rewrites one column and breaks no reference. Taxonomy comes in two shapes and only one of them is wired to posts. post_tags is the real join: a composite primary key on (post_id, tag_id), which makes re-saving a post's tag set an idempotent insert-on-conflict, with idx_post_tags_tag inverting it for a tag archive.

categories is the single-valued half: posts.category_id is a nullable FK onto it with idx_post_category behind it, and ON DELETE set null, so retiring a category unfiles its posts instead of deleting them. Comments land at 'pending' by default, with 'approved' and 'spam' as the other two states comments_status_check allows, so a comment stays invisible until someone promotes it. idx_comment_post keys on post_id alone, so a thread fetch is an index range and the approved-only filter is applied over the rows it returns.

Nuxt 4

Nuxt 4 in framework mode is the one stack here with two roots. Client code lives under app/ and is what `@/` points at (Vite, Vue single-file components); server code lives under server/ and is run by Nitro, Nuxt's server engine. The database layer is server-side, so initCode writes server/lib/db.ts and the schema, record modules and webhooks land under server/db/ and server/api/ — server modules reach each other through Nuxt's `~~` rootDir alias (`~~/server/lib/db`, `~~/server/db/schema`), never through `@/`. That split earns its keep with secrets: the Resend send client belongs to ~~/server/lib/email, and nothing under app/ can import it by accident. The API layer is Nitro rather than a React-shaped route file.

server/api/webhooks/polar.post.ts is a POST endpoint; auth mounts as the catch-all server/api/auth/[...all].ts, which adapts the H3 event with toWebRequest(event) and hands the resulting web Request to the auth library's framework-agnostic handler. Nuxt auto-imports defineEventHandler and its siblings at runtime, but the emitted server files import them from h3 explicitly — one deliberate idiom trade so every handler type-checks under standalone tsc. Session gating is a Nitro server middleware at server/middleware/auth.ts. It fires on every SSR render and every API request, filters on pathname prefixes (/dashboard, /settings), performs the real auth.api.getSession() lookup, and answers with sendRedirect(event, "/sign-in", 302). Because Nitro sits in front of both the rendered page and the endpoints, that is a genuine security boundary rather than a cheap pre-render bounce.

On the client, Vue does its own thing: the auth binding exposes signIn/signUp/useSession as Vue refs, screens are .vue components under app/pages/ (sign-in.vue, dashboard/[id].vue), chrome lives in app/components/ and app/layouts/, and SPA-side guards are app/middleware/*.ts. The design system is shadcn-vue on reka-ui — a real re-port, not the React components wearing new names — and it is checked with vue-tsc, since plain tsc cannot parse an SFC.

MySQL 8

MySQL 8 here is the mysql2 driver under Drizzle's mysql-core dialect: drizzle({ client: pool }) over one module-level mysql.createPool(DATABASE_URL). mysql2's pool multiplexes connections itself, so a single pool per module is the right shape — the runtime and the pool handle concurrency, with no globalThis singleton needed. The framework decides where that file lands (src/lib/db.ts on Next, app/lib/db.ts on React Router, server/lib/db.ts on Nuxt); the client text is the same in all three. The dialect's constraints show up directly in the column types.

MySQL cannot index a TEXT column without a prefix length, so anything that is a primary key, a UNIQUE, an index or a CHECK target is varchar with a declared length: ids are varchar(36) with no database default — the application generates them with crypto.randomUUID(), since there is no uuid type and no defaultRandom() — Better Auth's user.id and every FK pointing at it are varchar(255), an email or slug is varchar(255), a role or status varchar(32), a SHA-256 hex digest varchar(64). Free-form columns nobody indexes stay text. Timestamps are plain timestamp().defaultNow() without the withTimezone flag the Postgres bodies carry, and counters are bigint({ mode: "number" }). The operational difference that matters most: MySQL has no row-level security.

There is no policy layer to fall back on, so multi-tenant isolation is enforced in application code by forOrg(db, orgId) in src/lib/tenant.ts, which wraps each org-owned table's select/update/delete with a where on organization_id and throws on a missing orgId instead of quietly running unscoped. It is a real boundary only while every read and write goes through it — the shared plans catalog sits deliberately outside — and it is app-enforced, not database-enforced. Connections change with the deploy target: connectionLimit 2 per short-lived serverless instance, 10 in a long-running Node process, and on Cloudflare Workers mysql2 cannot run at all — there are no TCP sockets — so the edge client swaps to @planetscale/database over HTTP with drizzle-orm/planetscale-serverless.

Better Auth

Better Auth is a TypeScript library, not a service. The process that serves your pages is the process that hashes passwords and issues sessions, and the session rows sit in the same database as your application data. This fragment authors the four tables Better Auth expects — user, session, account, verification — into db/auth-schema.ts and passes that module to drizzleAdapter(db, { provider, schema: authSchema }) inside lib/auth.ts. Better Auth never creates tables at runtime; its CLI normally generates them, and authoring them here means the identity schema goes through the same migration proof as the app-type tables.

user.id is a bare text primary key (varchar(255) on MySQL, which cannot index TEXT without a prefix length) carrying no database default, because Better Auth generates the id and sends it in the insert. That is precisely what lets an app-type schema foreign-key user.id and cascade on delete. Session validation happens at two different strengths, deliberately. Next's src/proxy.ts calls getSessionCookie(request), which only asks whether the cookie is present: it runs at the edge, touches no database, and exists to bounce logged-out traffic before render. The authoritative check is auth.api.getSession({ headers }), and it runs inside the protected surface — requireUser() in src/lib/session.ts on Next, requireAuth(request) in app/lib/require-auth.ts on React Router, and the Nitro handler at server/middleware/auth.ts on Nuxt.

The last two do the real lookup on every guarded request, since neither framework has an edge proxy to peek with. The remaining emitted files are the mount: toNextJsHandler(auth) behind a [...all] route on Next, a resource route delegating to auth.handler on React Router, an h3 catch-all wrapping toWebRequest on Nuxt, plus a Vue auth client there. The auth instance imports the db client the framework already exported, so both share one pooled connection. What you inherit is ownership. Sessions join to your own tables, a user delete is a foreign-key cascade rather than a sync job, and drift arrives through your lockfile instead of a vendor's release notes.

The same ownership is the cost: password recovery only delivers if an email fragment is composed in — Better Auth's own server returns 400 "Reset password isn't enabled" until emailAndPassword.sendResetPassword is set — and rotating BETTER_AUTH_SECRET is yours to schedule.