Theo's Typesafe Cult•14mo ago

Could someone help me with one to many relation in prisma?

I have a cron job that create a list of records from different tables. Those table items need to be linked (have foreign key) with record of tables. Following is my Prisma schema and the cron api.

Solution:

```tsx const savedReportDB = await prisma.reportDatabse.create({ data: { userId, generatedFor: yesterday,...

Jump to solution

16 Replies

albatroz•14mo ago

model User {
    id              String          @id @default(uuid())

    WeightDatabase        WeightDatabase[]
    LiterDatabase         LiterDatabase[]
    PieceCountingDatabase PieceCountingDatabase[]
    GradeDatabase         GradeDatabase[]
    ReportDatabse         ReportDatabse[]
    AlertDatabase         AlertDatabase[]

    GradeSystemTemplate   GradeSystemTemplate[]
    PieceCountingTemplate PieceCountingTemplate[]
    AlertSystemTemplate   AlertSystemTemplate[]
}


model ReportDatabse {
    reportId     String   @id @unique @default(uuid())
    user         User     @relation(fields: [userId], references: [id])
    userId       String
    generatedFor DateTime

    savedWeight           WeightDatabase[]
    savedLiter            LiterDatabase[]
    savedPieceCounting    PieceCountingDatabase[]
    savedGrades           GradeDatabase[]
    AlertDatabase         AlertDatabase[]
    ReportDatabseAverages ReportDatabaseAverages?

    @@index([userId, generatedFor(sort: Asc), reportId], name: "reportDatabase_comp_key")
}

model ReportDatabaseAverages {
    id       String        @id @unique @default(uuid())
    report   ReportDatabse @relation(fields: [reportId], references: [reportId])
    reportId String        @unique

    savedWeightAverage Float? @default(0)
    savedLiterAverage  Float? @default(0)
}

model User {
    id              String          @id @default(uuid())

    WeightDatabase        WeightDatabase[]
    LiterDatabase         LiterDatabase[]
    PieceCountingDatabase PieceCountingDatabase[]
    GradeDatabase         GradeDatabase[]
    ReportDatabse         ReportDatabse[]
    AlertDatabase         AlertDatabase[]

    GradeSystemTemplate   GradeSystemTemplate[]
    PieceCountingTemplate PieceCountingTemplate[]
    AlertSystemTemplate   AlertSystemTemplate[]
}


model ReportDatabse {
    reportId     String   @id @unique @default(uuid())
    user         User     @relation(fields: [userId], references: [id])
    userId       String
    generatedFor DateTime

    savedWeight           WeightDatabase[]
    savedLiter            LiterDatabase[]
    savedPieceCounting    PieceCountingDatabase[]
    savedGrades           GradeDatabase[]
    AlertDatabase         AlertDatabase[]
    ReportDatabseAverages ReportDatabaseAverages?

    @@index([userId, generatedFor(sort: Asc), reportId], name: "reportDatabase_comp_key")
}

model ReportDatabaseAverages {
    id       String        @id @unique @default(uuid())
    report   ReportDatabse @relation(fields: [reportId], references: [reportId])
    reportId String        @unique

    savedWeightAverage Float? @default(0)
    savedLiterAverage  Float? @default(0)
}

model WeightDatabase {
    weight   Float
    weightId String @id @default(uuid())

    createdAt DateTime @default(now())
    user      User     @relation(fields: [userId], references: [id])
    userId    String   @unique

    ReportDatabse         ReportDatabse? @relation(fields: [reportDatabseReportId], references: [reportId])
    reportDatabseReportId String?

    @@index([createdAt(sort: Asc), userId], name: "weightDatabase_comp_key")
}

model PieceCountingDatabase {
    pieceId           String @id @default(uuid())
    itemName          String @db.VarChar(255)
    singlePieceWeight Float
    itemsCounted      Float

    createdAt DateTime @default(now())
    user      User     @relation(fields: [userId], references: [id])
    userId    String   @unique

    ReportDatabse         ReportDatabse? @relation(fields: [reportDatabseReportId], references: [reportId])
    reportDatabseReportId String?

    @@index([createdAt(sort: Asc), userId], name: "pieceCountingDatabase_comp_key")
}

model GradeDatabase {
    itemName         String
    gradeId          String @id @default(uuid())
    gradeName        String
    gradeUpperLimit  Float
    gradeLowerLimit  Float
    gradedItemWeight Float

    createdAt DateTime @default(now())
    user      User     @relation(fields: [userId], references: [id])
    userId    String   @unique

    ReportDatabse         ReportDatabse? @relation(fields: [reportDatabseReportId], references: [reportId])
    reportDatabseReportId String?

    @@index([createdAt(sort: Asc), userId], name: "gradeDatabase_comp_key")
}

model WeightDatabase {
    weight   Float
    weightId String @id @default(uuid())

    createdAt DateTime @default(now())
    user      User     @relation(fields: [userId], references: [id])
    userId    String   @unique

    ReportDatabse         ReportDatabse? @relation(fields: [reportDatabseReportId], references: [reportId])
    reportDatabseReportId String?

    @@index([createdAt(sort: Asc), userId], name: "weightDatabase_comp_key")
}

model PieceCountingDatabase {
    pieceId           String @id @default(uuid())
    itemName          String @db.VarChar(255)
    singlePieceWeight Float
    itemsCounted      Float

    createdAt DateTime @default(now())
    user      User     @relation(fields: [userId], references: [id])
    userId    String   @unique

    ReportDatabse         ReportDatabse? @relation(fields: [reportDatabseReportId], references: [reportId])
    reportDatabseReportId String?

    @@index([createdAt(sort: Asc), userId], name: "pieceCountingDatabase_comp_key")
}

model GradeDatabase {
    itemName         String
    gradeId          String @id @default(uuid())
    gradeName        String
    gradeUpperLimit  Float
    gradeLowerLimit  Float
    gradedItemWeight Float

    createdAt DateTime @default(now())
    user      User     @relation(fields: [userId], references: [id])
    userId    String   @unique

    ReportDatabse         ReportDatabse? @relation(fields: [reportDatabseReportId], references: [reportId])
    reportDatabseReportId String?

    @@index([createdAt(sort: Asc), userId], name: "gradeDatabase_comp_key")
}

And this is how handler looks like

    const AlertDatabase = await prisma.alertDatabase.findMany({
      where: {
        userId,
        createdAt: {
          gte: yesterday,
          lt: new Date(),
        },
      },
    });

    const savedReportDB = await prisma.reportDatabse.create({
      data: {
        userId,
        generatedFor: yesterday,
        savedWeight: { connect: savedWeight },
        savedLiter: { connect: savedLiter },
        savedPieceCounting: { connect: savedPieceCounting },
        savedGrades: { connect: savedGrades },
        AlertDatabase: { connect: AlertDatabase },
      },
    });

    console.log(
      "===> ~ file: reportDatabase.ts:121 ~ savedReportDB:",
      savedReportDB,
    );

    await prisma.reportDatabaseAverages.create({
      data: {
        reportId: savedReportDB.reportId,
        savedWeightAverage,
        savedLiterAverage,
      },
    });

    const AlertDatabase = await prisma.alertDatabase.findMany({
      where: {
        userId,
        createdAt: {
          gte: yesterday,
          lt: new Date(),
        },
      },
    });

    const savedReportDB = await prisma.reportDatabse.create({
      data: {
        userId,
        generatedFor: yesterday,
        savedWeight: { connect: savedWeight },
        savedLiter: { connect: savedLiter },
        savedPieceCounting: { connect: savedPieceCounting },
        savedGrades: { connect: savedGrades },
        AlertDatabase: { connect: AlertDatabase },
      },
    });

    console.log(
      "===> ~ file: reportDatabase.ts:121 ~ savedReportDB:",
      savedReportDB,
    );

    await prisma.reportDatabaseAverages.create({
      data: {
        reportId: savedReportDB.reportId,
        savedWeightAverage,
        savedLiterAverage,
      },
    });

But it's not connected to any of the records

albatroz•14mo ago

abcdef•14mo ago

@Albatros Try to change it to

const savedReportDB = await prisma.reportDatabse.create({
  data: {
    userId,
    generatedFor: yesterday,
    savedWeight: { connect: { id: savedWeight.id } },
    savedLiter: { connect: { id: savedLiter.id } },
    savedPieceCounting: { connect: { id: savedPieceCounting.id } },
    savedGrades: { connect: { id: savedGrades.id } },
    AlertDatabase: { connect: { id: AlertDatabase.id } },
  },
});

const savedReportDB = await prisma.reportDatabse.create({
  data: {
    userId,
    generatedFor: yesterday,
    savedWeight: { connect: { id: savedWeight.id } },
    savedLiter: { connect: { id: savedLiter.id } },
    savedPieceCounting: { connect: { id: savedPieceCounting.id } },
    savedGrades: { connect: { id: savedGrades.id } },
    AlertDatabase: { connect: { id: AlertDatabase.id } },
  },
});

albatroz•14mo ago

But, savedWeight is an array. saved** are array's

albatroz•14mo ago

abcdef•14mo ago

const savedReportDB = await prisma.reportDatabse.create({
  data: {
    userId,
    generatedFor: yesterday,
    savedWeight: { connect: savedWeight.map(({ id }) => ({ id })) },
    savedLiter: { connect: { id: savedLiter.id } },
    savedPieceCounting: { connect: { id: savedPieceCounting.id } },
    savedGrades: { connect: { id: savedGrades.id } },
    AlertDatabase: { connect: { id: AlertDatabase.id } },
  },
});

const savedReportDB = await prisma.reportDatabse.create({
  data: {
    userId,
    generatedFor: yesterday,
    savedWeight: { connect: savedWeight.map(({ id }) => ({ id })) },
    savedLiter: { connect: { id: savedLiter.id } },
    savedPieceCounting: { connect: { id: savedPieceCounting.id } },
    savedGrades: { connect: { id: savedGrades.id } },
    AlertDatabase: { connect: { id: AlertDatabase.id } },
  },
});

@Albatros

albatroz•14mo ago

albatroz•14mo ago

Got prisma error

abcdef•14mo ago

Yeah I updated the code

Solution

abcdef•14mo ago

const savedReportDB = await prisma.reportDatabse.create({
  data: {
    userId,
    generatedFor: yesterday,
    savedWeight: {
      connect: savedWeight.map((weight) => ({ weightId: weight.weightId })),
    },
    savedLiter: {
      connect: savedLiter.map((liter) => ({ literId: liter.literId })),
    },
    savedPieceCounting: {
      connect: savedPieceCounting.map((piece) => ({ pieceId: piece.pieceId })),
    },
    savedGrades: {
      connect: savedGrades.map((grade) => ({ gradeId: grade.gradeId })),
    },
    AlertDatabase: {
      connect: AlertDatabase.map((alert) => ({ alertId: alert.alertId })),
    },
  },
});

const savedReportDB = await prisma.reportDatabse.create({
  data: {
    userId,
    generatedFor: yesterday,
    savedWeight: {
      connect: savedWeight.map((weight) => ({ weightId: weight.weightId })),
    },
    savedLiter: {
      connect: savedLiter.map((liter) => ({ literId: liter.literId })),
    },
    savedPieceCounting: {
      connect: savedPieceCounting.map((piece) => ({ pieceId: piece.pieceId })),
    },
    savedGrades: {
      connect: savedGrades.map((grade) => ({ gradeId: grade.gradeId })),
    },
    AlertDatabase: {
      connect: AlertDatabase.map((alert) => ({ alertId: alert.alertId })),
    },
  },
});

^^ If they're all arrays

albatroz•14mo ago

Thanks, let's check if they work Praype

a lot man thankyou

i ask one last favor? https://app.serverlessq.com/ i am using this for cron, how can i make this a private request

albatroz•14mo ago

tried to log headers, but x-api-key is not present there

abcdef•14mo ago

You want to limit access to the endpoint with an api key?

albatroz•14mo ago

Yes

abcdef•14mo ago

You can use a middleware I'll send an example, hold on

/* Middleware - middleware/validateApiKey.ts */
export const validateApiKey = (handler) => async (req, res) => {
  const apiKey = req.headers["x-api-key"];

  if (!apiKey) {
    return res.status(401).json({ message: "Missing API key" });
  }

  const expectedApiKey = process.env.API_KEY;

  if (apiKey !== expectedApiKey) {
    return res.status(401).json({ message: "Invalid API key" });
  }

  return handler(req, res);
};



/* pages/api/route.ts */
import { validateApiKey } from "middleware/validateApiKey";

async function handler(req: NextApiRequest, res: NextApiResponse) {
  // ...
}

export default validateApiKey(handler);

/* Middleware - middleware/validateApiKey.ts */
export const validateApiKey = (handler) => async (req, res) => {
  const apiKey = req.headers["x-api-key"];

  if (!apiKey) {
    return res.status(401).json({ message: "Missing API key" });
  }

  const expectedApiKey = process.env.API_KEY;

  if (apiKey !== expectedApiKey) {
    return res.status(401).json({ message: "Invalid API key" });
  }

  return handler(req, res);
};



/* pages/api/route.ts */
import { validateApiKey } from "middleware/validateApiKey";

async function handler(req: NextApiRequest, res: NextApiResponse) {
  // ...
}

export default validateApiKey(handler);

Something like this should work

albatroz•14mo ago

Ok trying this yup that worked

Gaming

Programming

Could someone help me with one to many relation in prisma?