数据库集成 Prisma

7.1 安装与初始化 Prisma

Prisma 是 Node.js/TypeScript 生态最流行的 ORM，提供类型安全的数据库查询、自动迁移和直观的 Schema 定义。

# 安装 Prisma
npm install prisma @prisma/client
npx prisma init --datasource-provider sqlite
# 或使用 PostgreSQL：
npx prisma init --datasource-provider postgresql
SHELL

初始化后，在 prisma/schema.prisma 中定义数据模型：

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "sqlite"
  url      = env("DATABASE_URL")
}

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String
  password  String   // bcrypt 哈希
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Post {
  id        String   @id @default(cuid())
  slug      String   @unique
  title     String
  content   String
  published Boolean  @default(false)
  authorId  String
  author    User     @relation(fields: [authorId], references: [id])
  createdAt DateTime @default(now())
}
PRISMA

# 执行数据库迁移
npx prisma migrate dev --name init

# 生成 Prisma Client（类型安全的数据库访问层）
npx prisma generate

# 打开 Prisma Studio（可视化数据库管理）
npx prisma studio
SHELL

7.2 Prisma Client 单例

在开发模式下，Vite/Node.js 的热重载会反复创建新的 Prisma Client 实例，导致连接数耗尽。需要用单例模式保证只创建一个实例。

// app/db.server.ts — Prisma Client 单例
import { PrismaClient } from "@prisma/client";

let db: PrismaClient;

declare global {
  var __db__: PrismaClient | undefined;
}

// 开发模式：复用 global 上的实例
if (process.env.NODE_ENV === "production") {
  db = new PrismaClient();
} else {
  if (!global.__db__) {
    global.__db__ = new PrismaClient();
  }
  db = global.__db__;
}

export { db };
TS

7.3 Model 层封装

将数据库操作封装成模型文件（以 .server.ts 结尾），Remix 保证这些文件只在服务端使用。

// app/models/post.server.ts
import { db } from "~/db.server";

export async function getPosts({
  page = 1,
  limit = 10,
  published = true,
} = {}) {
  const [posts, total] = await db.$transaction([
    db.post.findMany({
      where: { published },
      include: { author: { select: { name: true } } },
      orderBy: { createdAt: "desc" },
      skip: (page - 1) * limit,
      take: limit,
    }),
    db.post.count({ where: { published } }),
  ]);
  return { posts, total };
}

export async function getPost(slug: string) {
  return db.post.findUnique({
    where: { slug },
    include: { author: true },
  });
}

export async function createPost(data: {
  title: string; content: string; authorId: string;
}) {
  const slug = data.title
    .toLowerCase()
    .replace(/[^a-z0-9\u4e00-\u9fa5]/g, "-")
    .replace(/-+/g, "-");
  return db.post.create({ data: { ...data, slug } });
}

export async function updatePost(id: string, data: Partial<{ title: string; content: string; published: boolean }>) {
  return db.post.update({ where: { id }, data });
}

export async function deletePost(id: string) {
  return db.post.delete({ where: { id } });
}
TS

7.4 CRUD 完整路由实战

// app/routes/admin.posts.new.tsx — 创建文章
import { json, redirect, type ActionFunctionArgs } from "@remix-run/node";
import { Form, useActionData, useNavigation } from "@remix-run/react";
import { requireUser } from "~/session.server";
import { createPost } from "~/models/post.server";

export async function action({ request }: ActionFunctionArgs) {
  const user  = await requireUser(request);
  const form  = await request.formData();
  const title = form.get("title") as string;
  const content = form.get("content") as string;

  const errors: Record<string, string> = {};
  if (!title) errors.title = "标题不能为空";
  if (content.length < 100) errors.content = "正文至少100字";
  if (Object.keys(errors).length) return json({ errors }, { status: 422 });

  const post = await createPost({ title, content, authorId: user.id });
  return redirect(`/admin/posts/${post.id}/edit`);
}

export default function NewPost() {
  const actionData = useActionData<typeof action>();
  const nav = useNavigation();

  return (
    <Form method="post">
      <input name="title" placeholder="文章标题" />
      {actionData?.errors?.title && <p>{actionData.errors.title}</p>}
      <textarea name="content" rows={20} />
      {actionData?.errors?.content && <p>{actionData.errors.content}</p>}
      <button type="submit" disabled={nav.state === "submitting"}>
        {nav.state === "submitting" ? "保存中..." : "保存文章"}
      </button>
    </Form>
  );
}
TSX

7.5 Serverless 连接池注意事项

在 Serverless 环境（Vercel、Cloudflare Workers）中，每个函数调用可能创建新的数据库连接，导致连接数超限。需要使用连接池代理工具。

# 使用 Prisma Accelerate（无服务器连接池）
npm install @prisma/extension-accelerate

// app/db.server.ts（使用 Accelerate）
import { PrismaClient } from "@prisma/client/edge";
import { withAccelerate } from "@prisma/extension-accelerate";

export const db = new PrismaClient().$extends(withAccelerate());
TS