Best Practices for Updating Nested Relations in Prisma with NestJS and SQL Server
Hello! I'm working on a project using NestJS, Prisma, and SQL Server, and I need help with updating a complex model with many relations. Here’s the context:
I have the following models with relationships:
Models in Prisma Schema:
Question:
How can I efficiently update these models in a single query or within a transaction? The update should handle creating, deleting, and updating nested related objects. I currently do not have services or controllers for the related models, but I can create them if necessary.
Thank you for any guidance!
UserConfig
|- Preferences? // 1:1
| |- NotificationSettings[] // 1:N
| | `- EmailSettings? // 1:1
| `- ThemeSettings? // 1:1
`- AccountSettings? // 1:1
`- PrivacySettings[] // 1:N
UserConfig
|- Preferences? // 1:1
| |- NotificationSettings[] // 1:N
| | `- EmailSettings? // 1:1
| `- ThemeSettings? // 1:1
`- AccountSettings? // 1:1
`- PrivacySettings[] // 1:N
model UserConfig {
id Int @id @default(autoincrement())
userId Int @unique
preferences Preferences?
accountSettings AccountSettings?
}
model Preferences {
id Int @id @default(autoincrement())
userConfigId Int @unique
notificationSettings NotificationSettings[]
themeSettings ThemeSettings?
userConfig UserConfig @relation(fields: [userConfigId], references: [id], onDelete: Cascade)
}
model NotificationSettings {
id Int @id @default(autoincrement())
type String // "EMAIL" | "SMS"
preferencesId Int
emailSettings EmailSettings?
preferences Preferences @relation(fields: [preferencesId], references: [id], onDelete: Cascade)
}
model EmailSettings {
id Int @id @default(autoincrement())
email String
notificationSettingsId Int @unique
notificationSettings NotificationSettings @relation(fields: [notificationSettingsId], references: [id], onDelete: Cascade)
}
model ThemeSettings {
id Int @id @default(autoincrement())
theme String
preferencesId Int @unique
preferences Preferences @relation(fields: [preferencesId], references: [id], onDelete: Cascade)
}
model AccountSettings {
id Int @id @default(autoincrement())
userConfigId Int @unique
privacySettings PrivacySettings[]
userConfig UserConfig @relation(fields: [userConfigId], references: [id], onDelete: Cascade)
}
model PrivacySettings {
id Int @id @default(autoincrement())
setting String
accountSettingsId Int
accountSettings AccountSettings @relation(fields: [accountSettingsId], references: [id], onDelete: Cascade)
}
model UserConfig {
id Int @id @default(autoincrement())
userId Int @unique
preferences Preferences?
accountSettings AccountSettings?
}
model Preferences {
id Int @id @default(autoincrement())
userConfigId Int @unique
notificationSettings NotificationSettings[]
themeSettings ThemeSettings?
userConfig UserConfig @relation(fields: [userConfigId], references: [id], onDelete: Cascade)
}
model NotificationSettings {
id Int @id @default(autoincrement())
type String // "EMAIL" | "SMS"
preferencesId Int
emailSettings EmailSettings?
preferences Preferences @relation(fields: [preferencesId], references: [id], onDelete: Cascade)
}
model EmailSettings {
id Int @id @default(autoincrement())
email String
notificationSettingsId Int @unique
notificationSettings NotificationSettings @relation(fields: [notificationSettingsId], references: [id], onDelete: Cascade)
}
model ThemeSettings {
id Int @id @default(autoincrement())
theme String
preferencesId Int @unique
preferences Preferences @relation(fields: [preferencesId], references: [id], onDelete: Cascade)
}
model AccountSettings {
id Int @id @default(autoincrement())
userConfigId Int @unique
privacySettings PrivacySettings[]
userConfig UserConfig @relation(fields: [userConfigId], references: [id], onDelete: Cascade)
}
model PrivacySettings {
id Int @id @default(autoincrement())
setting String
accountSettingsId Int
accountSettings AccountSettings @relation(fields: [accountSettingsId], references: [id], onDelete: Cascade)
}
2 Replies
Hey,
To ensure clean code, you should encapsulate the functions. Having a "mega" function for everything doesn't really make sense.
Assuming you want to update UserConfig together with a nested relationship, you could customize the code.
I think that the key here is "prisma.$transaction". Include is used for data integrity.
import { PrismaService } from './prisma.service'; // Importieren Sie Ihren Prisma-Service
import { Injectable } from '@nestjs/common';
@Injectable()
export class UserConfigService {
constructor(private readonly prisma: PrismaService) {}
async updateUserConfigWithRelations(userId: number, updatedUserConfigData: any) {
const { preferences, accountSettings, ...otherFields } = updatedUserConfigData;
try {
// Starte eine Transaktion
const result = await this.prisma.$transaction(async (prisma) => {
// UserConfig aktualisieren
const updatedUserConfig = await prisma.userConfig.update({
where: { userId },
data: otherFields,
include: {
preferences: {
include: {
notificationSettings: {
include: {
emailSettings: true,
},
},
themeSettings: true,
},
},
accountSettings: {
include: {
privacySettings: true,
},
},
},
});
// Preferences aktualisieren oder erstellen
if (preferences) {
await this.updatePreferences(prisma, updatedUserConfig.preferences.id, preferences);
}
// AccountSettings aktualisieren oder erstellen
// (ähnlicher Ansatz wie bei Preferences)
return updatedUserConfig;
});
return result;
} catch (error) {
// Fehler behandeln
throw new Error(`Fehler beim Aktualisieren der Benutzerkonfiguration: ${error.message}`);
}
}
import { PrismaService } from './prisma.service'; // Importieren Sie Ihren Prisma-Service
import { Injectable } from '@nestjs/common';
@Injectable()
export class UserConfigService {
constructor(private readonly prisma: PrismaService) {}
async updateUserConfigWithRelations(userId: number, updatedUserConfigData: any) {
const { preferences, accountSettings, ...otherFields } = updatedUserConfigData;
try {
// Starte eine Transaktion
const result = await this.prisma.$transaction(async (prisma) => {
// UserConfig aktualisieren
const updatedUserConfig = await prisma.userConfig.update({
where: { userId },
data: otherFields,
include: {
preferences: {
include: {
notificationSettings: {
include: {
emailSettings: true,
},
},
themeSettings: true,
},
},
accountSettings: {
include: {
privacySettings: true,
},
},
},
});
// Preferences aktualisieren oder erstellen
if (preferences) {
await this.updatePreferences(prisma, updatedUserConfig.preferences.id, preferences);
}
// AccountSettings aktualisieren oder erstellen
// (ähnlicher Ansatz wie bei Preferences)
return updatedUserConfig;
});
return result;
} catch (error) {
// Fehler behandeln
throw new Error(`Fehler beim Aktualisieren der Benutzerkonfiguration: ${error.message}`);
}
}
private async updatePreferences(prisma: PrismaService, preferencesId: number, preferencesData: any) {
// Implementieren Sie die Logik zum Aktualisieren oder Erstellen von Preferences und verwandten Modellen
// Beispiel:
// await prisma.preferences.update({ where: { id: preferencesId }, data: preferencesData });
}
}
private async updatePreferences(prisma: PrismaService, preferencesId: number, preferencesData: any) {
// Implementieren Sie die Logik zum Aktualisieren oder Erstellen von Preferences und verwandten Modellen
// Beispiel:
// await prisma.preferences.update({ where: { id: preferencesId }, data: preferencesData });
}
}
For the update, would you prefer PUT or PATCH? It looks to me like this code would be handling a PUT payload.