Prisma•7mo ago

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:

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

Models in Prisma Schema:

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)
}

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!

2 Replies

JTB•6mo ago

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.

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 });
  }
}

I think that the key here is "prisma.$transaction". Include is used for data integrity.

Fright XOOP•6mo ago

For the update, would you prefer PUT or PATCH? It looks to me like this code would be handling a PUT payload.

Gaming

Programming

Best Practices for Updating Nested Relations in Prisma with NestJS and SQL Server