2.02 第二、三部分：数据库模型和迁移

很好，让我们继续进行数据库模型和迁移的设置。

第二部分：数据库模型和迁移

1. 在 src/db/schema.ts 中定义数据库模型：

import { sqliteTable, text, integer } from "drizzle-orm/sqlite-core";
 
export const users = sqliteTable("users", {
  id: integer("id").primaryKey(),
  username: text("username").notNull().unique(),
  password: text("password").notNull(),
  email: text("email").notNull().unique(),
  createdAt: integer("created_at", { mode: "timestamp" }).notNull().defaultNow(),
});
 
export const posts = sqliteTable("posts", {
  id: integer("id").primaryKey(),
  title: text("title").notNull(),
  content: text("content").notNull(),
  authorId: integer("author_id").notNull().references(() => users.id),
  createdAt: integer("created_at", { mode: "timestamp" }).notNull().defaultNow(),
  updatedAt: integer("updated_at", { mode: "timestamp" }).notNull().defaultNow(),
});

2. 在 src/db/index.ts 中设置数据库连接：

import { drizzle } from "drizzle-orm/libsql";
import { createClient } from "@libsql/client";
 
const client = createClient({
  url: process.env.DATABASE_URL!,
  authToken: process.env.DATABASE_AUTH_TOKEN,
});
 
export const db = drizzle(client);

3. 创建迁移文件：

bun run drizzle-kit generate:sqlite

4. 在 src/index.ts 中添加迁移逻辑：

import { migrate } from "drizzle-orm/libsql/migrator";
import { db } from "./db";
 
// ... 其他导入
 
// 在应用启动时运行迁移
await migrate(db, { migrationsFolder: "./drizzle" });
 
// ... 其他应用逻辑

现在我们已经设置了数据库模型和迁移。接下来，让我们开始后端 API 开发。

第三部分：后端 API 开发

1. 在 src/routes/auth.ts 中创建认证路由：

import { Elysia } from "elysia";
import { jwt } from "@elysiajs/jwt";
import { db } from "../db";
import { users } from "../db/schema";
import { eq } from "drizzle-orm";
 
export const authRoutes = new Elysia()
  .use(
    jwt({
      name: "jwt",
      secret: process.env.JWT_SECRET!,
    })
  )
  .post("/login", async ({ body, jwt, set }) => {
    const { username, password } = body;
    const user = await db.select().from(users).where(eq(users.username, username)).get();
 
    if (!user || user.password !== password) {
      set.status = 401;
      return { error: "Invalid credentials" };
    }
 
    const token = await jwt.sign({ id: user.id, username: user.username });
    return { token };
  })
  .post("/register", async ({ body, set }) => {
    const { username, password, email } = body;
    try {
      const newUser = await db.insert(users).values({ username, password, email }).returning().get();
      set.status = 201;
      return { message: "User created successfully", user: newUser };
    } catch (error) {
      set.status = 400;
      return { error: "Username or email already exists" };
    }
  });

2. 在 src/routes/posts.ts 中创建博客文章路由：

import { Elysia } from "elysia";
import { db } from "../db";
import { posts } from "../db/schema";
import { eq } from "drizzle-orm";
 
export const postRoutes = new Elysia()
  .get("/posts", async () => {
    return await db.select().from(posts).all();
  })
  .get("/posts/:id", async ({ params }) => {
    const post = await db.select().from(posts).where(eq(posts.id, parseInt(params.id))).get();
    return post || { error: "Post not found" };
  })
  .post("/posts", async ({ body }) => {
    const { title, content, authorId } = body;
    const newPost = await db.insert(posts).values({ title, content, authorId }).returning().get();
    return newPost;
  })
  .put("/posts/:id", async ({ params, body }) => {
    const { title, content } = body;
    const updatedPost = await db
      .update(posts)
      .set({ title, content, updatedAt: new Date() })
      .where(eq(posts.id, parseInt(params.id)))
      .returning()
      .get();
    return updatedPost || { error: "Post not found" };
  })
  .delete("/posts/:id", async ({ params }) => {
    await db.delete(posts).where(eq(posts.id, parseInt(params.id)));
    return { message: "Post deleted successfully" };
  });

3. 在 src/index.ts 中整合所有路由：

import { Elysia } from "elysia";
import { html } from "@elysiajs/html";
import { authRoutes } from "./routes/auth";
import { postRoutes } from "./routes/posts";
import { migrate } from "drizzle-orm/libsql/migrator";
import { db } from "./db";
 
// 运行迁移
await migrate(db, { migrationsFolder: "./drizzle" });
 
const app = new Elysia()
  .use(html())
  .use(authRoutes)
  .use(postRoutes)
  .get("/", () => "Welcome to the Blog API")
  .listen(3000);
 
console.log(`Server is running at ${app.server?.hostname}:${app.server?.port}`);

这就是后端 API 开发的基本结构。接下来，我们将继续前端页面和组件的开发。需要我继续吗？