Currently working on a project to learn a bit more about prisma with nestjs. But cant get the schema to work. The seed wont go through because the flat depends on the owner field, that can only be filled with the created user.

I wanted to create a simple database setup where a user can be an owner of a flat, but must have a flat.

A flat needs to have an owner, and can have multiple tennants.

Would be very thankful for a helpful hand here, otherwise its just another project that will never get over concepts.

Schema:

model User {
  userId        String    @id @default(uuid())
  firstName     String?
  lastName      String?
  nickname      String
  email         String    @unique
  password      String
  phoneNumber   String?
  ownerOf       Flat?     @relation("owner")
  ownerOfId     String?   @unique
  flatId        String
  flat          Flat      @relation(fields: [flatId], references: [flatId])
  paidFor    Expense[] @relation("paidBy")
  otherExpenses Expense[]
  updatedAt     DateTime? @updatedAt
  createdAt     DateTime? @default(now())

  @@map("users")
}

model Flat {
  flatId    String    @id @default(uuid())
  name      String
  owner     User?      @relation("owner", fields: [ownerId], references: [userId])
  ownerId   String?    @unique
  flatmates User[]
  expenses  Expense[]
  updatedAt DateTime? @updatedAt
  createdAt DateTime? @default(now())

  @@map("flats")
}

model Expense {
  expenseId   String    @id @default(uuid())
  flatId      String
  flat        Flat      @relation(fields: [flatId], references: [flatId])
  paidBy   User      @relation("paidBy", fields: [paidById], references: [userId])
  paidById String
  expenseFor  User[]
  amount      Float
  updatedAt   DateTime? @updatedAt
  createdAt   DateTime? @default(now())

  @@map("expenses")
}

const users = await prisma.user.create({
    data: {
      firstName: 'Flo',
      lastName: 'Test',
      nickname: 'flo',
      email: '[email protected]',
      password: hash,
      flat: {
        create: {
          name: 'Test Flat',
          owner: {
            connect: {
              users,
            },
          },
        },
      },
    },
  });

CodePudding user response：

You will need to create the user first and then connect the flat in a subsequent operation. I would wrap this in an interactive transaction.

CodePudding user response：

I would start thinking in terms of relations.

• What is the relation between a flat and its tenants? It's 1:n. One flat can have multiple tenants. A user can only be tenant in one flat.
• What is the relation between a flat and its owner? From your schema it is 1:1. (You might want to double check that. Can't a user own multiple flats?)

Additional requirements are:

• A user has to be tenant in a flat.
• A flat has to have a owner (from your question text). But your schema does not require that! And it's a critical point.

owner of flat remains optional

You can model these relations like this in your schema.prisma (simplified):

model User {
  userId    String @id @default(uuid())
  nickname  String
  flatId    String
  flat      Flat   @relation(fields: [flatId], references: [flatId], name: "tenantRelation")
  ownedFlat Flat?  @relation(name: "ownerRelation")

  @@map("users")
}

model Flat {
  flatId  String  @id @default(uuid())
  name    String
  ownerId String? @unique
  owner   User?   @relation(fields: [ownerId], references: [userId], name: "ownerRelation")
  tenants User[]  @relation(name: "tenantRelation")

  @@map("flats")
}

There is no need to introduce a redundant User.ownerOfId.

That schema does not guarantee that each flat has an owner as ownerId is optional.

If you accept this flaw, you can create user and flat like this:

await prisma.user.create({
  data: { nickname: "flo", flat: { create: { name: "Test flat" } } },
});

and in a second step set the owner of the flat to the user...

owner of flat should be mandatory

If you do not accept this flaw, and make ownerId non-optional, seeding becomes indeed more difficult as you are having a circular dependency.

In the schema, you just remove the two question marks:

model Flat {
  ...
  ownerId String @unique
  owner   User   @relation(fields: [ownerId], references: [userId], name: "ownerRelation")
  ...

You would then have to:

• Set the foreign keys DEFERRABLE INITIALLY DEFERRED or DEFERRABLE INITIALLY IMMEDIATE on your database. Currently, prisma cannot do that (see: https://github.com/prisma/prisma/issues/8807).
• Execute raw queries in the same transaction like this:

import { v4 as uuidv4 } from "uuid";

// ...

  const userId = uuidv4();
  const flatId = uuidv4();

  await prisma.$transaction(async ($tx) => {
    await $tx.$executeRaw`set constraints all deferred;`;
    await $tx.$executeRaw`INSERT INTO users ("userId", "nickname", "flatId") VALUES (${userId}, 'flo', ${flatId});`;
    await $tx.$executeRaw`INSERT INTO flats ("flatId", "name", "ownerId") VALUES (${flatId}, 'Test flat', ${userId});`;
  });

which currently requires previewFeatures = ["interactiveTransactions"] in your schema.prisma.

But before going down that route, I'd suggest to double check how your app would really use the database. Would user and flat always be created in the same step?