Recent projects each used Prisma, Drizzle, or skipped the ORM entirely and wrote raw SQL.

The same AI makes completely different mistakes on each of the three—that surprised me quite a bit… On Prisma it writes “the correct way from the previous major version”; on Drizzle it gets stuck on types and API signatures; on raw SQL it has no idea what limitations that driver has.

Here I’ll note down a few fallback practices I use along the schema-to-migration chain—just notes for myself.

Prisma 7 / Drizzle 0.45 / Next 16. If the versions don’t line up, all the approaches below will change.

A few spots in Prisma 7 where AI is guaranteed to write it wrong

The CLAUDE.md:18-25 in that acg-site repo has a section literally titled “Key Version Differences (Common AI Mistakes)”. Of the six bullets, only one is Prisma-related:

The Prisma 7 generator is "prisma-client" (not "prisma-client-js"), import path @/generated/prisma/client

One bullet crammed in two things. The ❌→✅ comparison table at the end of .cursor/rules/library-versions.mdc in the same repo splits it into items 3 and 4, writing it out separately again (that repo maintains three sets of convention files at once—I did the cost accounting for writing it that way in the post about convention files, so here I’ll only pick the data-layer-related ones).

It looks redundant, but it’s actually very necessary. Because these two things aren’t a case of “AI can’t do it” but of “AI remembers the old one”.

If you don’t hard-code it, it will guess from memory 100% of the time—it never fails.

I’ll add one of my own: since Prisma 7, the connection string moved from the schema to prisma.config.ts, and the validation in that file is usually written to throw unconditionally.

That’s how prisma.config.ts:5-8 in vns-next looks:

const url = process.env.DATABASE_URL;
if (!url) {
  throw new Error("DATABASE_URL is not defined");
}

The consequence is that during the Docker build you must feed in a placeholder value, even though prisma generate never connects to a database at all—that’s where those two lines of SKIP_ENV_VALIDATION=1 plus a fake connection string in the image come from; I pasted the original text in the self-hosting post.

What you need to note is the direction of causality: the Dockerfile isn’t written wrong—it’s the unconditional throw in prisma.config.ts that ropes the build phase in too.

Any library that does validation at the top level of a config file has this problem; Prisma is just the one I ran into recently.

These all fall under “the training data has the old syntax”. Writing them into CLAUDE.md is the highest-value kind of convention—one line saves you half an hour.

db push can’t create native SQL indexes

This pitfall isn’t the AI’s fault, but the AI won’t proactively warn you about it either.

The search in vns-next uses the pg_trgm extension plus three GIN indexes: fuzzy title, alias array containment, fuzzy tag name. prisma db push won’t create any of these for you—it only syncs what can be expressed in the schema.

So I put them separately in a scripts/db/indexes.sql, with the first line of comments at the top reading “Prisma schema cannot declare trgm/expression indexes; run manually after db:push”.

More importantly, how to verify. The line recorded in the body of commit 35659c4 is “pg_trgm search (EXPLAIN verified index usage)”.

A query running successfully doesn’t mean it used the index—a full table scan also returns correct results, just slowly. For this kind of thing you have to look at the execution plan, not at whether the endpoint returns 200.

A few friction points on the Drizzle side

The thdl side uses Drizzle + postgres.js, and the friction points are a whole different batch.

Connection pool

Dev hot reload keeps creating new connections over and over and leaking them all; you have to attach a globalThis singleton.

This is a pitfall very familiar to Prisma users; switching to Drizzle you still have to hand-write it once (src/lib/db/index.ts:8-13):

declare global {
  var __thdl_pg: ReturnType<typeof postgres> | undefined;
}

const client = globalThis.__thdl_pg ?? postgres(connectionString, { max: 10 });
if (process.env.NODE_ENV !== "production") globalThis.__thdl_pg = client;

Enum columns meet searchParams

What comes up from the URL is always string, while the pgEnum column is a union of 9 literals—there’s no clean conversion path in between.

In the end the code was written like this (src/app/(site)/resources/page.tsx:21):

cat ? eq(resources.category, cat as "music") : undefined

It just picks an arbitrary member and casts to it. It works, but it throws away the validation responsibility entirely; to actually do it properly you’d have to pass it through zod’s enum first.

I’ll jot this down for now, haven’t touched it…

Version differences again

The second parameter of Drizzle’s table definition changed starting from 0.36 to return an array instead of an object. thdl has 0.45.2 installed; see src/lib/db/schema.ts:113-117 for the syntax:

(t) => [
  index("resources_status_created_idx").on(t.status, t.createdAt),
  index("resources_category_idx").on(t.category),
]

The AI will most likely write it as => ({ ... }) for you—same nature as those two Prisma points. When checking the changelog, remember not to look at the wrong version; you won’t find this one in 0.45.

And one more that’s not a code problem

What I run locally is pnpm db:push; the steps the README gives everyone else are also pnpm db:push; and in the drizzle/ directory there’s still just one baseline migration to this day.

package.json:14 does define db:migrate, but grepping the whole repo turns up no call site. The deploy side is even more blunt: deploy/ contains nothing but a Caddyfile and a systemd unit, ExecStart is just node server.js, and the database is never touched at all.

In other words, there’s no versioned record of how the schema evolved step by step; it all depends on push aligning things on the spot.

The database is still small, so pushing all at once really is fine for now. But once the table structure gets complicated, or a second person actually has to deploy a copy, that debt comes due… I know it, I just haven’t gotten to it yet.

Wiring the ORM and the auth library takes manual alignment

thdl uses better-auth’s drizzleAdapter, which expects four tables: user / session / account / verification.

But the table names in your own schema aren’t necessarily those, so you have to map them by hand (src/lib/auth.ts:8-16):

database: drizzleAdapter(db, {
  provider: "pg",
  schema: {
    user: schema.users,
    session: schema.sessions,
    account: schema.accounts,
    verification: schema.verifications,
  },
}),

The AI doesn’t get this wrong because it’s dumb, but because it has no way to know out of thin air what your tables are called. This is exactly the kind of part where a human has to pin things down first and only then let it do the wiring.

A fossil worth mentioning in passing: the allowlist in that repo’s .claude/settings.local.json has two commands back to back — delete the old auth library’s catch-all route directory, create the new auth library’s catch-all route directory.

An entire library swap got recorded in full like that, and in the end the route file is only 4 lines long. Hilarious.

Migration scripts must be idempotent, and they need unit tests

When vns-next migrated from the old Hugo site to Postgres, the first line of the header comment in scripts/migrate-content.ts states the strategy: “Idempotent and re-runnable: upsert Work/Page by primary key; deleteMany DownloadLink then insert; clear then insert SiteLink/TeamMember/DlEntry.”

If it dies partway through, you just run it again — that premise made the whole migration far easier.

I pulled the parsing logic into a standalone module, scripts/lib/parse.ts, with 31 test cases across 6 describe blocks; the acceptance line in the body of commit 35659c4 is simply “all 31 tests green.”

The scariest thing in data migration isn’t a crash, it’s a silent error. A field shifted out of place, a date off by a day — everything runs green and you still can’t see it. Pure functions plus unit tests are the only thing holding the line.

The timezone pitfall deserves its own section. The default schema in js-yaml parses a timezone-less string like date: 2024-07-31 03:56:40 straight into Date, computed as UTC, and the timezone is wrong right there.

The fix is to switch to JSON_SCHEMA so it stays a string, then parse it yourself with +08:00. The comment in scripts/lib/parse.ts:12-13 reads:

js-yaml is a dependency of gray-matter, so we reuse it directly here.
Using JSON_SCHEMA keeps date: 2024-07-31 03:56:40 as a string (the default schema parses it into a Date as UTC, which gets the timezone wrong)

The record counts need to be checkable too. After the migration, the body of commit 35659c4 recorded work=145 / tag=247 / download_link=2307 / page=22 / dl_entry=89.

deploy/README.md:35 picked the first three and wrote them into the import notes, with a verification SQL given separately on line 48: after import, select count(*) from work should be 145. With that one line, re-running it on another machine feels safe.

One last point that’s easy to overlook: encode/decode functions shared between migration scripts and the frontend must be pulled out into a zero-dependency module.

The header of vns-next’s src/lib/download-label.ts:1-3 says:

/**
 * DownloadLink.label 的编码/解码 —— 纯函数、零依赖
 *（迁移脚本与前端共用；不要在这里引入任何 node/解析库依赖）
 */

It started with commit 404a769: “extract splitLabel into a zero-dependency module to keep gray-matter out of the client bundle.”

Once a shared function casually imports a parsing library, it crawls all the way into the client bundle through the import chain, and nothing can stop it.

When you don’t use an ORM

Another project skipped the ORM entirely and just writes raw SQL through a serverless Postgres HTTP driver.

The complexity you save gets handed straight back to your migration scripts:

  • That driver executes only one statement at a time, so the migration script has to implement its own semicolon-splitting state machine, and also handle dollar-quoted blocks and semicolons inside strings
  • Bulk writes must be combined into a single unnest upsert. Under an HTTP driver every statement is a separate network round trip, and once the row count grows the latency gets ugly
  • Migrations use plain filename ordinals and re-run in full every time, relying on the SQL being idempotent — there isn’t even a migration state table

That last one is actually the most worry-free. With no state table, there’s no “state table doesn’t match the real schema” problem — the hardest kind to track down.

A few habits I have now

ApproachWhat it solvesFrequency
Lock down the schema before letting AI write queriesStops it from guessing fields from memory★★★★★
Migration scripts must be idempotent and re-runnableIf it fails midway, you can just run it again★★★★★
Extract parsing/encoding into pure functions with unit testsSilent errors are the scariest thing in data migrations★★★★
Keep native indexes in a separate SQL fileNeither push nor generate will create them for you★★★
Feed a fake connection string at build timeThe Docker build layer has no database★★★

That’s it for now — I’ll add more next time I switch ORMs~