从Mongoose转MySQL：Drizzle ORM+PlanetScale多对多关系实现求助

1. Schema 定义 #

以下是适配 PlanetScale 的多对多关系 Schema，包含 credits、categories 主表，以及关联两者的中间表 credits_categories：

import { mysqlTable, varchar, int, primaryKey } from "drizzle-orm/mysql-core";
import { relations } from "drizzle-orm";

// Credits 主表
export const credits = mysqlTable("credits", {
  id: int("id").autoincrement().primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  description: varchar("description", { length: 1000 }),
  // 可根据需求添加其他字段（如创建时间、状态等）
});

// Categories 主表
export const categories = mysqlTable("categories", {
  id: int("id").autoincrement().primaryKey(),
  name: varchar("name", { length: 255 }).notNull().unique(),
  slug: varchar("slug", { length: 255 }).notNull().unique(),
  // 可根据需求添加其他字段
});

// 多对多中间表（联合主键确保关联唯一）
export const creditsCategories = mysqlTable("credits_categories", {
  creditId: int("credit_id")
    .notNull()
    .references(() => credits.id),
  categoryId: int("category_id")
    .notNull()
    .references(() => categories.id),
}, (table) => ({
  pk: primaryKey({ columns: [table.creditId, table.categoryId] }),
}));

// 定义表间关联关系
export const creditsRelations = relations(credits, ({ many }) => ({
  categories: many(creditsCategories),
}));

export const categoriesRelations = relations(categories, ({ many }) => ({
  credits: many(creditsCategories),
}));

export const creditsCategoriesRelations = relations(creditsCategories, ({ one }) => ({
  credit: one(credits, {
    fields: [creditsCategories.creditId],
    references: [credits.id],
  }),
  category: one(categories, {
    fields: [creditsCategories.categoryId],
    references: [categories.id],
  }),
}));

注：如果使用 PlanetScale 推荐的 UUID 作为主键，可将表中 id 字段替换为：varchar("id", { length: 36 }).primaryKey().$defaultFn(() => crypto.randomUUID())，同时中间表的关联字段也要对应改为 varchar 类型。

2. 多对多查询示例 #

基于上述 Schema，以下是常用的多对多关联查询代码：

查询单个 Credit 及其关联的所有 Categories #

import { db } from "./db"; // 你的 Drizzle 数据库实例
import { credits, categories, creditsCategories } from "./schema";
import { eq } from "drizzle-orm";

async function getCreditWithCategories(creditId: number) {
  const rawCredit = await db.query.credits.findFirst({
    where: eq(credits.id, creditId),
    with: {
      categories: {
        with: {
          category: true,
        },
      },
    },
  });

  // 整理结果，直接返回关联的分类数组
  return rawCredit ? {
    ...rawCredit,
    categories: rawCredit.categories.map(item => item.category)
  } : null;
}

查询单个 Category 及其关联的所有 Credits #

async function getCategoryWithCredits(categoryId: number) {
  const rawCategory = await db.query.categories.findFirst({
    where: eq(categories.id, categoryId),
    with: {
      credits: {
        with: {
          credit: true,
        },
      },
    },
  });

  return rawCategory ? {
    ...rawCategory,
    credits: rawCategory.credits.map(item => item.credit)
  } : null;
}

查询所有 Credits 并附带各自的 Categories #

async function getAllCreditsWithCategories() {
  const rawCredits = await db.query.credits.findMany({
    with: {
      categories: {
        with: {
          category: true,
        },
      },
    },
  });

  return rawCredits.map(credit => ({
    ...credit,
    categories: credit.categories.map(item => item.category)
  }));
}

新增 Credit 与 Category 的关联 #

async function linkCreditToCategory(creditId: number, categoryId: number) {
  await db.insert(creditsCategories).values({
    creditId,
    categoryId,
  });
}

内容的提问来源于stack exchange，提问作者oliverkeenanlyu