如何将含多对多关联的Prisma PostgreSQL数据库完整导出为CSV

问题描述 #

使用Prisma创建了包含多个多对多关联的PostgreSQL数据库，希望将根表（thing）及关联表（relationOne、relationTwo）中的statOne/statTwo等数据导出为单个CSV文件，但当前导出无法包含关联信息。附Prisma模型示例及pgAdmin生成的导出SQL，询问该需求是否可行。

Prisma模型示例 #

model thing {
  relationOne relations[]
  relationTwo relations[]

  statOne Int
  statTwo Int
}

model relationOne {
  assignedThing thing[]

  statOne String
  statTwo String
}

model relationTwo {
  assignedThing thing[]

  statOne String
  statTwo String
}

pgAdmin生成的导出SQL #

copy public.model (id, movement, toughness, save, wounds, leadership, "objectiveControl", "invulnerableSave", "factionKeywords", keywords, "modelName", "unitAssignedId", points, "degradeText") TO '/home/mnemonicds/Documents/testExport' DELIMITER ',' CSV QUOTE '"' ESCAPE '''';

可行性结论及实现方案 #

该需求完全可行，以下是两种落地方案：

方案一：直接用PostgreSQL的COPY结合JOIN与聚合函数 #

Prisma会为多对多关联自动生成中间表（如thing_relationOne、thing_relationTwo），通过LEFT JOIN关联根表、中间表及关联表，再用STRING_AGG将同一根表对应的多条关联数据合并为单个字段，最终导出为CSV：

COPY (
  SELECT
    t.id,
    t.statOne AS thing_statOne,
    t.statTwo AS thing_statTwo,
    STRING_AGG(DISTINCT r1.statOne, ';') AS relationOne_statOne,
    STRING_AGG(DISTINCT r1.statTwo, ';') AS relationOne_statTwo,
    STRING_AGG(DISTINCT r2.statOne, ';') AS relationTwo_statOne,
    STRING_AGG(DISTINCT r2.statTwo, ';') AS relationTwo_statTwo
  FROM public.thing t
  LEFT JOIN public.thing_relationOne tr1 ON t.id = tr1."thingId"
  LEFT JOIN public.relationOne r1 ON tr1."relationOneId" = r1.id
  LEFT JOIN public.thing_relationTwo tr2 ON t.id = tr2."thingId"
  LEFT JOIN public.relationTwo r2 ON tr2."relationTwoId" = r2.id
  GROUP BY t.id, t.statOne, t.statTwo
) TO '/home/mnemonicds/Documents/testExport_with_relations' DELIMITER ',' CSV HEADER QUOTE '"' ESCAPE '''';

• 说明：LEFT JOIN确保保留所有根表数据，即使无关联记录；STRING_AGG用指定分隔符（如;）合并关联字段，避免CSV列数混乱；GROUP BY按根表主键聚合，保证每行对应一条根表数据。

方案二：通过Prisma查询后代码生成CSV #

在代码中使用Prisma的include选项查询根表及所有关联数据，再将结果转换为CSV格式（需借助第三方库如csv-writer）：

const { PrismaClient } = require('@prisma/client');
const fs = require('fs');
const createCsvWriter = require('csv-writer').createObjectCsvWriter;

const prisma = new PrismaClient();

async function exportToCsv() {
  // 查询根表及所有关联数据
  const things = await prisma.thing.findMany({
    include: {
      relationOne: true,
      relationTwo: true,
    },
  });

  // 转换关联数组为分隔字符串
  const transformedData = things.map(thing => ({
    id: thing.id,
    thing_statOne: thing.statOne,
    thing_statTwo: thing.statTwo,
    relationOne_statOne: thing.relationOne.map(r => r.statOne).join(';'),
    relationOne_statTwo: thing.relationOne.map(r => r.statTwo).join(';'),
    relationTwo_statOne: thing.relationTwo.map(r => r.statOne).join(';'),
    relationTwo_statTwo: thing.relationTwo.map(r => r.statTwo).join(';'),
  }));

  // 配置CSV写入器并导出
  const csvWriter = createCsvWriter({
    path: 'testExport_with_relations.csv',
    header: [
      { id: 'id', title: 'ID' },
      { id: 'thing_statOne', title: 'thing_statOne' },
      { id: 'thing_statTwo', title: 'thing_statTwo' },
      { id: 'relationOne_statOne', title: 'relationOne_statOne' },
      { id: 'relationOne_statTwo', title: 'relationOne_statTwo' },
      { id: 'relationTwo_statOne', title: 'relationTwo_statOne' },
      { id: 'relationTwo_statTwo', title: 'relationTwo_statTwo' },
    ],
  });

  await csvWriter.writeRecords(transformedData);
  console.log('CSV导出完成');
}

// 执行导出并处理错误
exportToCsv()
  .catch(e => console.error(e))
  .finally(async () => await prisma.$disconnect());

• 说明：此方案更灵活，适合需要对数据进行复杂处理（如过滤、格式转换）的场景；需提前安装依赖：npm install @prisma/client csv-writer，并确保Prisma客户端已生成。

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