2024-03-07 Don't review Prisma Migration generated SQL

---

因为大量熟练使用 database tools，如：Prisma 和 Ecto，我已经不能熟练写出相对复杂 SQL 了。😭

目前，我在设计和开发一个能源回收和调度系统，因为我是一个以“懒”作为美德的程序员，我想直接使用 Supabase 提供的 Auth 服务。

但是我发现在 Prisma schema 中实现的 many-to-many 关系，无法优雅地使用 supabase-js 查询。

于是我研究了一下原因，不看不知道，一看吓一跳。Prisma Migration 生成的 many-to-many SQL 好复杂，完全违反直觉，反正我 review 困难。

下面是一个 role:permission many-to-many 的例子：

// prisma.schema
model Permission {
  id    Int    @id @default(autoincrement())
  roles Role[]
}

model Role {
  id          Int          @id @default(autoincrement())
  permissions Permission[]
}

The SQL was generated by Prisma Migration:

-- CreateTable
CREATE TABLE "_PermissionToRole" (
    "A" INTEGER NOT NULL,
    "B" INTEGER NOT NULL
);

-- CreateIndex
CREATE UNIQUE INDEX "_PermissionToRole_AB_unique" ON "_PermissionToRole"("A", "B");

-- CreateIndex
CREATE INDEX "_PermissionToRole_B_index" ON "_PermissionToRole"("B");

-- AddForeignKey
ALTER TABLE "_PermissionToRole" ADD CONSTRAINT "_PermissionToRole_A_fkey" FOREIGN KEY ("A") REFERENCES "Permission"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "_PermissionToRole" ADD CONSTRAINT "_PermissionToRole_B_fkey" FOREIGN KEY ("B") REFERENCES "Role"("id") ON DELETE CASCADE ON UPDATE CASCADE;

上面 SQL 看的我一愣一愣的。而且上面 SQL 生成的关系在 supabase-js 查询语句很丑。

const { data, error } = await supabase.from("Role").select(`*, _PermissionToRole(
  A: Permission (*)
)`);

于是我凭着记忆手写了下面这段 SQL。

create table role (
  "id" serial primary key,
);

create table permission (
  "id" serial primary key,
);

create table members (
  "role_id" int references role,
  "permission_id" int references permission,
  primary key (role_id, permission_id)
);

经过测试，我手写的 SQL 可以使用 supabase-js 进行关联查询，代码如下：

- const { data, error } = await supabase.from("Role").select(`*, _PermissionToRole(
-   A: Permission (*)
- )`);

+ const {data, error} = await supabase.from("role").select("*, permission(*)")

那么，今后我会手写 SQL 管理 migrations 吗？

答：不会，如果是团队合作的 JS 项目，我依然会使用 Prisma Migration，因为复杂的关系和限制手写 SQL 成本太高了。工作量、易出错、缺少检查工具。

所以，由于种种原因，我的结论：

Don't review Prisma Migration generated SQL, focus on prisam.schema.

TJ 2024-03-07

如果是非团队项目 or 非 JS 项目

我会使用 Ecto 管理 migrations。

Ecto 生成的 many-to-many SQL 简洁又准确

create table(:role_permission, primary_key: false) do
  add :role_id, references(:role), primary_key: true
  add :permission_id, references(:permission), primary_key: true
end

-- Table Definition
CREATE TABLE "public"."role_permission" (
    "role_id" int8 NOT NULL,
    "permission_id" int8 NOT NULL,
    CONSTRAINT "role_permission_role_id_fkey" FOREIGN KEY ("role_id") REFERENCES "public"."role"("id"),
    CONSTRAINT "role_permission_permission_id_fkey" FOREIGN KEY ("permission_id") REFERENCES "public"."permission"("id"),
    PRIMARY KEY ("role_id","permission_id")
);

对比 Prsima 生成的 SQL，高下立判。