Creating relationships in Prisma

Question

I am creating a database with the help of Prisma and I have encountered a problem. I have 2 models: User and Lesson. In Lesson I store the IDs of the users who participated in it and the ID of the trainer, which is also a User model. I would like, when downloading Lesson, to be able to immediately get the users who took part in it and the trainer, but not their IDs, but the data of the whole user.

I know I need a relationship, however I can't create one. My schema.prisma:

datasource db {
    provider = "postgresql"
    url      = env("DATABASE_URL")
}

generator client {
    provider = "prisma-client-js"
}

enum LessonState {
    ACTIVE
    CLOSED
}

model Lesson {
    id          String      @id @default(uuid()) @map("_id") @db.Uuid
    date        DateTime    @default(now())
    subject     String
    description String
    state       LessonState @default(ACTIVE)
    usersIds    String[]    @db.Uuid
    coachId     String      @unique @db.Uuid
}

enum UserRoles {
    ADMIN
    USER
}

model User {
    id        String    @id @default(uuid()) @map("_id") @db.Uuid
    name      String
    surname   String
    password  String
    login     String    @unique
    birthDate DateTime
    role      UserRoles @default(USER)
    createdAt DateTime  @default(now())
}

I care about one-way relationships, but from what I've found out, Prisma does not allow such, so if necessary they can be two-way. As for the choice of postgresql it is, you could say, random - if some other system allows to do what I need I can switch to it. The same goes for Prisma - I can use another ORM if it supports what I need.

Answer 1

you need to add relations to solve this issue as following one to many for coach relation and many to many for lesson users relation

// schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgres"
  url      = env("DATABASE_URL")
}

enum LessonState {
    ACTIVE
    CLOSED
}

model Lesson {
    id          String      @id @default(uuid()) @map("_id") @db.Uuid
    date        DateTime    @default(now())
    subject     String
    description String
    state       LessonState @default(ACTIVE)
    users       User[]   @relation(name: "lesson_users")
    coachId     String   @db.Uuid
    coach User  @relation(name: "coach", fields: [coachId], references: [id])
}

enum UserRoles {
    ADMIN
    USER
}

model User {
    id        String    @id @default(uuid()) @map("_id") @db.Uuid
    name      String
    surname   String
    password  String
    login     String    @unique
    birthDate DateTime
    role      UserRoles @default(USER)
    createdAt DateTime  @default(now())
    myLessons   Lesson[] @relation(name: "lesson_users")
    lessonsITeach    Lesson[] @relation(name: "coach")
}

The following query returns all lessons with a list of the users who participated in it and the the trainer

await prisma.lesson.findMany({
  include: {
    users: true,
    coach: true,
  },
});

to get ids of users that Lesson participated in lesson

await prisma.lesson.findFirst({
      include: {
        users: { select: { id: true } },
        coach: true,
      },
    });

to get all lessons the user participated in and what lessons the user teach

await prisma.user.findFirst({
  include: {
    myLessons: true,
    lessonsITeach: true,
  },
});

to get only ids

await prisma.user.findFirst({
  include: {
    myLessons: { select: { id: true } },
    lessonsITeach: { select: { id: true } },
  },
});