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:Jump to solution
```tsx
const savedReportDB = await prisma.reportDatabse.create({
data: {
userId,
generatedFor: yesterday,...
16 Replies
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")
}
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,
},
});
@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 } },
},
});
But, savedWeight is an array.
saved** are array's
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 } },
},
});
Got prisma error
Yeah I updated the code
Solution
^^ If they're all arrays
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 })),
},
},
});
Thanks, let's check if they work a lot man i ask one last favor?
https://app.serverlessq.com/
i am using this for cron, how can i make this a private request
tried to log headers, but x-api-key is not present there
You want to limit access to the endpoint with an api key?
Yes
You can use a middleware
I'll send an example, hold on
Something like this should work
/* 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);
Ok trying this
yup that worked