Social network on Next.js 16 (App Router), MySQL 8 and Better Auth

Next.js 16 App Router: file-based routing, server components, and the Edge proxy (Next 16's renamed middleware).

MySQL 8 via Drizzle ORM and the mysql2 driver.

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

Social network: public profiles with @-handles, a directed follow graph, an authored-post feed, and per-post like edges.

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 });

one public profile per Better Auth user, with a unique @-handle and optional bio/avatar

directed follower→following edges with a composite unique that prevents duplicate follows

authored feed posts keyed to a Better Auth user, indexed per-author for profile timeline queries

post↔user like edges with a composite unique enforcing one like per user per post

Auth runs in proxy.ts (Next 16's renamed middleware) on the Edge runtime: it gates on the session cookie's presence only — full session validation happens in Server Components and route handlers, not in the proxy.

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.

Both the follow edge (follower_id + following_id composite PK + unique) and the like edge (post_id + user_id composite PK + unique) carry explicit unique constraints — the constraint is the identity, not just a performance index.

Better Auth's user.id is text, not uuid; all four tables match that type on their FK columns rather than recasting, so joins never cross a type boundary.

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.

Social network

Better Auth's user row is the account; profiles is the face that account shows. The split is deliberate: profiles.user_id is a unique text FK, so the mapping is strictly one-to-one, but posts.author_id, follows.follower_id, follows.following_id and post_likes.user_id all reference user.id rather than profiles.id. Authorship is keyed to the account, while the handle, bio and avatar are a separate row you join in for display. Every one of those FKs cascades on delete, so removing an account clears its profile, both directions of its follow edges, its posts and its likes in a single statement — there is no orphan sweep to write. The graph itself is two edge tables, and both make the edge its own identity.

follows has no surrogate key: (follower_id, following_id) is the primary key, and follows_follower_following_unique restates the same rule. You cannot follow the same account twice, and direction matters — following back inserts a second, separate row. post_likes is shaped identically, with (post_id, user_id) as the composite primary key plus post_likes_post_user_unique. Neither table caches a count; follower totals and like totals are aggregates over the edges themselves. The cost of that shape is worth knowing before you build on it. A home feed is fan-out-on-read: take the follower-side prefix of the follows primary key to get everyone a viewer follows, then read each author through idx_post_author.

posts carries only idx_post_author — no (author_id, created_at) composite and no index on created_at alone — so newest-first ordering is a sort on top of the index hit, which is fine at seed scale and the first thing to revisit under load. Likes read better: (post_id, user_id) leads with post_id, so counting a post's likes and answering whether the current viewer already liked it both ride the same key, and idx_post_likes_user runs the relation backwards for a liked-posts page. handle is unique and separately covered by idx_profile_handle, which is what keeps an /@name route a single-row lookup.

Next.js 16 (App Router)

Next 16's App Router keeps the whole application under src/: the scaffold runs create-next-app with --src-dir and --import-alias @/*, so `@/` resolves to src/* and the verified files land on top of a stock project rather than replacing it. initCode writes the database client to src/lib/db.ts, then appends the auth fragment's own files — identity tables at src/db/auth-schema.ts, the auth instance at src/lib/auth.ts, a catch-all handler under src/app/api/auth/, and src/proxy.ts. Each file has exactly one owner; the framework never re-emits the ones auth brought. Server code has two shapes here and they are not interchangeable. A Server Component runs on the server and imports { db } from "@/lib/db" directly, so a page can await a Drizzle query with no API route in between.

Anything that needs a URL — an OAuth callback, a Polar or Clerk webhook, a mutation posted from the client — is a route handler at src/app/api/<path>/route.ts exporting GET or POST. Session checking is split across two tiers on purpose. src/proxy.ts (Next 16's rename of middleware.ts; under --src-dir it must sit beside src/app or Next silently ignores it) runs on the Edge runtime with a config.matcher listing the guarded prefixes — /dashboard/:path* and /settings/:path* out of the box. It only asks whether a session cookie exists and redirects to /sign-in when it does not: no database round trip at the edge. The authoritative check is auth.api.getSession() inside the Server Component or route handler that actually reads rows.

What that means when you build on it: widening the protected surface is a one-line change to the matcher array, but the proxy is not the security boundary — a request carrying any session cookie reaches the page, and the page decides. Keep the real check next to the data. The UI overlay follows the same split, with auth screens under src/app/(auth)/ and the shell and dashboard under src/app/(app)/.

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.