Database Relations (Prisma)

~25 daq · NestJS · Ma'lumotlar bazasi

Muammodan boshlaylik

Oldingi darsda Prisma bilan oddiy CRUD yozdingiz. Hamma narsa bir jadvalda edi. Lekin real loyihalarda ma'lumotlar bir-biriga bog'liq bo'ladi:

• Foydalanuvchi → Profil (bitta)
• Foydalanuvchi → Postlar (ko'p)
• Post → Teglar (ko'p, va teg ham ko'p postda bo'lishi mumkin)

Bu darsda Prisma da bog'liqliklarni qanday yozish va ishlatishni ko'ramiz. TypeORM dagi @OneToOne, @OneToMany, @ManyToMany dekoratorlari o'rniga Prisma da bular schema.prisma faylda yoziladi.

Bog'liqlik turlari — eslatma

Oldingi TypeORM darsida ko'rgan uch turni Prisma da ham ishlataмиз:

  One-to-One        One-to-Many          Many-to-Many
  ──────────────────────────────────────────────────────
  User ──── Profile   User ──< Posts       Posts >──< Tags

  1 user             1 user               1 post ko'p
  1 profile          ko'p post            tegga ega

  Misol:             Misol:               Misol:
  Foydalanuvchi      Foydalanuvchi        Post va teglar
  va uning profili   va uning postlari

1. One-to-One — birga bir

Misol: Har bir foydalanuvchining bitta profili bor.

  users jadvali            profiles jadvali
  ┌────┬───────┬────────┐  ┌────┬──────────┬────────┬─────────┐
  │ id │ name  │ email  │  │ id │ bio      │ phone  │ userId  │
  ├────┼───────┼────────┤  ├────┼──────────┼────────┼─────────┤
  │ 1  │ Ali   │ ali@.. │◄─│ 1  │Dasturchi │998901..│    1    │
  │ 2  │ Vali  │ vali@..│◄─│ 2  │Dizayner  │998902..│    2    │
  └────┴───────┴────────┘  └────┴──────────┴────────┴─────────┘

  Foreign key (userId) profiles jadvalida yotadi

Schema

model User {
  id        Int      @id @default(autoincrement())
  name      String
  email     String   @unique
  password  String
  createdAt DateTime @default(now())

  profile   Profile?
  // Profile? — ixtiyoriy, user profilsiz ham bo'lishi mumkin
  // Bu maydon bazada ustun emas — faqat Prisma uchun bog'liqlik belgisi

  @@map("users")
}

model Profile {
  id     Int     @id @default(autoincrement())
  bio    String?
  phone  String?
  avatar String?

  userId Int     @unique
  // @unique — bitta userId bir martadan bo'lishi mumkin (One-to-One kafolati)
  user   User    @relation(fields: [userId], references: [id], onDelete: Cascade)
  // fields: [userId]    — profiles jadvalidagi ustun
  // references: [id]    — users jadvalidagi ustun
  // onDelete: Cascade   — user o'chsa, profil ham o'chsin

  @@map("profiles")
}

@relation ni tushunish:

  @relation(fields: [userId], references: [id])
  ──────────────────────────────────────────────
  fields:     [userId]   — "shu jadvalda (profiles) userId ustuni bor"
  references: [id]       — "u users jadvalidagi id ga ishora qiladi"

  profiles.userId → users.id

Service — One-to-One

// users/users.service.ts
@Injectable()
export class UsersService {
  constructor(private readonly prisma: PrismaService) {}

  // User va profil birga yaratish
  async createWithProfile(dto: CreateUserWithProfileDto) {
    return await this.prisma.user.create({
      data: {
        name:     dto.name,
        email:    dto.email,
        password: await bcrypt.hash(dto.password, 10),

        profile: {
          create: {
            bio:   dto.bio,
            phone: dto.phone,
          },
          // create — profil ham shu yerda yaratiladi
          // Alohida prisma.profile.create() shart emas
        },
      },
      include: { profile: true },
      // Javobda profil ham bo'lsin
    });
  }

  // User ni profili bilan olish
  async findOneWithProfile(id: number) {
    const user = await this.prisma.user.findUnique({
      where: { id },
      include: { profile: true },
    });

    if (!user) throw new NotFoundException('Foydalanuvchi topilmadi');
    return user;
  }

  // Profilni alohida yangilash
  async updateProfile(userId: number, dto: UpdateProfileDto) {
    return await this.prisma.profile.upsert({
      where:  { userId },
      // upsert — bor bo'lsa update, yo'q bo'lsa create
      update: { bio: dto.bio, phone: dto.phone, avatar: dto.avatar },
      create: { userId, bio: dto.bio, phone: dto.phone, avatar: dto.avatar },
    });
  }
}

upsert nima? — "update + insert" qisqartmasi. Yozuv bor bo'lsa — yangilaydi. Yo'q bo'lsa — yaratadi. User profili bor-yo'qligini alohida tekshirmasdan ishlaydi.

2. One-to-Many — birga ko'p

Misol: Bir foydalanuvchi ko'p post yozishi mumkin.

  users jadvali          posts jadvali
  ┌────┬───────┐         ┌────┬─────────────────┬──────────┐
  │ id │ name  │         │ id │ title           │ authorId │
  ├────┼───────┤         ├────┼─────────────────┼──────────┤
  │    │       │◄────────│ 1  │ NestJS haqida   │    1     │
  │ 1  │ Ali   │◄────────│ 2  │ Prisma CRUD     │    1     │
  │    │       │◄────────│ 3  │ PostgreSQL tips  │    1     │
  ├────┼───────┤         ├────┼─────────────────┼──────────┤
  │ 2  │ Vali  │◄────────│ 4  │ CSS qo'llanma   │    2     │
  └────┴───────┘         └────┴─────────────────┴──────────┘

  Foreign key (authorId) doim "ko'p" tomonda — posts jadvalida

Schema

model User {
  id    Int    @id @default(autoincrement())
  name  String
  email String @unique

  posts Post[]
  // Post[] — bu user ning postlari ro'yxati
  // Bazada ustun emas — Prisma uchun bog'liqlik belgisi

  @@map("users")
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String
  published Boolean  @default(false)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  authorId  Int
  // authorId — haqiqiy ustun, bazada yotadi
  author    User   @relation(fields: [authorId], references: [id], onDelete: Cascade)
  // onDelete: Cascade — user o'chsa, uning postlari ham o'chsin

  @@map("posts")
}

Qaysi tomonda @relation yoziladi?

  Foreign key qaysi jadvalda bo'lsa — @relation shu modelda yoziladi.
  One-to-Many da foreign key doim "Ko'p" tomonda.

  posts.authorId → users.id
  Shuning uchun @relation Post modelida yozildi.

Service — One-to-Many

// posts/posts.service.ts
@Injectable()
export class PostsService {
  constructor(private readonly prisma: PrismaService) {}

  // Post yaratish
  async create(authorId: number, dto: CreatePostDto) {
    return await this.prisma.post.create({
      data: {
        title:    dto.title,
        content:  dto.content,
        author: {
          connect: { id: authorId },
          // connect — mavjud userni post ga ulaydi
          // create bo'lsa — yangi user ham yaratilardi (bizda kerak emas)
        },
      },
      include: { author: { select: { id: true, name: true } } },
    });
  }

  // Foydalanuvchining barcha postlari
  async findByAuthor(authorId: number, page = 1, limit = 10) {
    const [posts, total] = await this.prisma.$transaction([
      this.prisma.post.findMany({
        where:   { authorId, published: true },
        skip:    (page - 1) * limit,
        take:    limit,
        orderBy: { createdAt: 'desc' },
        include: { author: { select: { id: true, name: true } } },
      }),
      this.prisma.post.count({ where: { authorId, published: true } }),
    ]);

    return { data: posts, total, page, lastPage: Math.ceil(total / limit) };
  }

  // Bitta post — muallifi bilan
  async findOne(id: number) {
    const post = await this.prisma.post.findUnique({
      where:   { id },
      include: {
        author: { select: { id: true, name: true, email: true } },
      },
    });

    if (!post) throw new NotFoundException('Post topilmadi');
    return post;
  }

  // Post yangilash — faqat muallif o'zgartira oladi
  async update(id: number, authorId: number, dto: UpdatePostDto) {
    const post = await this.prisma.post.findUnique({ where: { id } });

    if (!post) throw new NotFoundException('Post topilmadi');

    if (post.authorId !== authorId) {
      throw new ForbiddenException('Siz bu postni o\'zgartira olmaysiz');
    }

    return await this.prisma.post.update({
      where: { id },
      data:  dto,
    });
  }
}

connect va create farqi:

  author: { connect: { id: authorId } }
  — Mavjud userni ulaydi. User allaqachon bazada bor.
  — Agar user topilmasa — Prisma xato beradi.

  author: { create: { name: '...', email: '...' } }
  — Yangi user yaratib, postga ulaydi.
  — Bir vaqtda user va post yaratiladi.

3. Many-to-Many — ko'pga ko'p

Misol: Postda bir nechta teg bo'lishi mumkin. Bir teg ko'p postda ishlatilishi mumkin.

  posts              oraliq jadval           tags
  ┌────┬──────────┐  ┌─────────┬────────┐   ┌────┬──────────┐
  │ id │ title    │  │ post_id │ tag_id │   │ id │ name     │
  ├────┼──────────┤  ├─────────┼────────┤   ├────┼──────────┤
  │ 1  │ NestJS   │─►│    1    │   1    │◄──│ 1  │ nestjs   │
  │    │          │─►│    1    │   2    │◄──│ 2  │ typescript│
  │    │          │─►│    1    │   3    │◄──│ 3  │ backend  │
  │ 2  │ CSS tips │─►│    2    │   4    │◄──│ 4  │ css      │
  │    │          │─►│    2    │   1    │   └────┴──────────┘
  └────┴──────────┘  └─────────┴────────┘

  Prisma bu oraliq jadvalni (_PostToTag) avtomatik yaratadi

Schema

model Post {
  id      Int    @id @default(autoincrement())
  title   String
  content String

  authorId Int
  author   User  @relation(fields: [authorId], references: [id])

  tags     Tag[]
  // Tag[] — Many-to-Many, oraliq jadval avtomatik yaratiladi

  @@map("posts")
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique

  posts Post[]
  // Post[] — Many-to-Many ning ikkinchi tomoni

  @@map("tags")
}

Many-to-Many da @relation yozish shart emas — Prisma ikkala tomonda Model[] ko'rsa, oraliq jadvalni o'zi yaratadi.

💡 Oraliq jadval nomi avtomatik: _PostToTag. Agar nomi yoqmasa yoki oraliq jadvalga qo'shimcha maydon kerak bo'lsa — qo'lda yoziladi (pastda ko'ramiz).

Service — Many-to-Many

// posts/posts.service.ts

// Post teg bilan yaratish
async createWithTags(authorId: number, dto: CreatePostWithTagsDto) {
  const { tagIds, tagNames, ...postData } = dto;

  return await this.prisma.post.create({
    data: {
      ...postData,
      author: { connect: { id: authorId } },
      tags: {
        // connect — mavjud teglarni ulash (id bo'yicha)
        connect: tagIds?.map(id => ({ id })),

        // connectOrCreate — bor bo'lsa ul, yo'q bo'lsa yarat
        connectOrCreate: tagNames?.map(name => ({
          where:  { name },
          create: { name },
        })),
      },
    },
    include: { tags: true, author: { select: { id: true, name: true } } },
  });
}

// Postga teg qo'shish
async addTags(postId: number, tagIds: number[]) {
  return await this.prisma.post.update({
    where: { id: postId },
    data: {
      tags: {
        connect: tagIds.map(id => ({ id })),
        // Mavjud teglarga qo'shimcha ulaydi — eski teglar saqlanadi
      },
    },
    include: { tags: true },
  });
}

// Postdan teg olib tashlash
async removeTags(postId: number, tagIds: number[]) {
  return await this.prisma.post.update({
    where: { id: postId },
    data: {
      tags: {
        disconnect: tagIds.map(id => ({ id })),
        // disconnect — oraliq jadvaldan o'chiradi, tag o'zi saqlanadi
      },
    },
    include: { tags: true },
  });
}

// Postning teglarini to'liq almashtirish
async setTags(postId: number, tagIds: number[]) {
  return await this.prisma.post.update({
    where: { id: postId },
    data: {
      tags: {
        set: tagIds.map(id => ({ id })),
        // set — eski teglarni o'chirib, yangilarini ulaydi
      },
    },
    include: { tags: true },
  });
}

// Teg bo'yicha postlarni qidirish
async findByTag(tagName: string) {
  return await this.prisma.post.findMany({
    where: {
      tags: {
        some: {
          name: { equals: tagName, mode: 'insensitive' },
        },
        // some — kamida bitta teg shu nomga mos kelsin
      },
    },
    include: { tags: true },
  });
}

connect, connectOrCreate, disconnect, set — farqi:

  connect          — mavjud yozuvni ulaydi (id kerak)
  connectOrCreate  — bor bo'lsa ulaydi, yo'q bo'lsa yaratib ulaydi
  disconnect       — bog'liqlikni uzadi, yozuv o'zi saqlanadi
  set              — barcha bog'liqliklarni o'chirib, yangilarini qo'yadi
  create           — yangi yozuv yaratib ulaydi

Qo'shimcha maydonli Many-to-Many

Ba'zan oraliq jadvalga qo'shimcha ma'lumot saqlanishi kerak. Masalan, foydalanuvchi kursga yozilgan sana, to'lov holati.

  users             enrollments (oraliq + qo'shimcha)        courses
  ┌────┬──────┐     ┌──────────┬──────────┬──────────────┐   ┌────┬──────────┐
  │ id │ name │     │ userId   │ courseId │ enrolledAt   │   │ id │ title    │
  ├────┼──────┤     ├──────────┼──────────┼──────────────┤   ├────┼──────────┤
  │ 1  │ Ali  │────►│    1     │    1     │ 2026-01-15   │◄──│ 1  │ NestJS   │
  │    │      │────►│    1     │    2     │ 2026-01-20   │◄──│ 2  │ React    │
  │ 2  │ Vali │────►│    2     │    1     │ 2026-02-01   │   └────┴──────────┘
  └────┴──────┘     └──────────┴──────────┴──────────────┘

Schema — explicit Many-to-Many

model User {
  id          Int          @id @default(autoincrement())
  name        String
  enrollments Enrollment[]  // oraliq model orqali
  @@map("users")
}

model Course {
  id          Int          @id @default(autoincrement())
  title       String
  enrollments Enrollment[]  // oraliq model orqali
  @@map("courses")
}

// Oraliq model — qo'shimcha maydonlar uchun
model Enrollment {
  userId     Int
  courseId   Int
  enrolledAt DateTime @default(now())
  isPaid     Boolean  @default(false)
  grade      Float?

  user   User   @relation(fields: [userId],   references: [id], onDelete: Cascade)
  course Course @relation(fields: [courseId], references: [id], onDelete: Cascade)

  @@id([userId, courseId])
  // Kompozit primary key — bir user bir kursga bir marta yozilishi mumkin
  @@map("enrollments")
}

Service — explicit Many-to-Many

// enrollments/enrollments.service.ts
@Injectable()
export class EnrollmentsService {
  constructor(private readonly prisma: PrismaService) {}

  // Kursga yozilish
  async enroll(userId: number, courseId: number) {
    // Allaqachon yozilganmi?
    const existing = await this.prisma.enrollment.findUnique({
      where: { userId_courseId: { userId, courseId } },
      // kompozit key bo'yicha qidirish
    });

    if (existing) {
      throw new ConflictException('Siz bu kursga allaqachon yozilgansiz');
    }

    return await this.prisma.enrollment.create({
      data: { userId, courseId },
      include: {
        course: { select: { id: true, title: true } },
      },
    });
  }

  // Foydalanuvchining kurslari
  async findUserCourses(userId: number) {
    const enrollments = await this.prisma.enrollment.findMany({
      where:   { userId },
      include: {
        course: true,
      },
      orderBy: { enrolledAt: 'desc' },
    });

    return enrollments.map(e => ({
      ...e.course,
      enrolledAt: e.enrolledAt,
      isPaid:     e.isPaid,
      grade:      e.grade,
    }));
  }

  // To'lovni tasdiqlash
  async confirmPayment(userId: number, courseId: number) {
    return await this.prisma.enrollment.update({
      where: { userId_courseId: { userId, courseId } },
      data:  { isPaid: true },
    });
  }
}

Ichma-ich (Nested) queries

Prisma ning kuchli tomoni — bog'liq ma'lumotlarni bir so'rovda olish va filtrlash:

// Murakkab so'rov — user, uning publish qilingan postlari, har post uchun teglar
async findUserWithPublishedPosts(userId: number) {
  return await this.prisma.user.findUnique({
    where: { id: userId },
    include: {
      profile: true,
      posts: {
        where:   { published: true },
        orderBy: { createdAt: 'desc' },
        take:    10,
        include: {
          tags: {
            select: { id: true, name: true },
          },
        },
      },
    },
  });
}

Natija:

{
  "id": 1,
  "name": "Ali",
  "profile": { "bio": "Dasturchi", "phone": "998901234567" },
  "posts": [
    {
      "id": 3,
      "title": "PostgreSQL tips",
      "published": true,
      "tags": [
        { "id": 1, "name": "postgresql" },
        { "id": 3, "name": "backend" }
      ]
    }
  ]
}

Relation filtrlash — where ichida

// Kamida bitta tegi bor postlar
this.prisma.post.findMany({
  where: { tags: { some: { name: 'nestjs' } } },
})

// Barcha teglari mos keladi
this.prisma.post.findMany({
  where: { tags: { every: { name: { in: ['nestjs', 'typescript'] } } } },
})

// Hech qanday tegi yo'q postlar
this.prisma.post.findMany({
  where: { tags: { none: {} } },
})

// Kamida 3 ta tegi bor postlar
// (Prisma da bevosita count filter yo'q — _count bilan)
this.prisma.post.findMany({
  where: {
    tags: { some: {} },     // kamida bitta teg bor
  },
  include: {
    _count: { select: { tags: true } },
    // _count — relation da nechta yozuv borligini sanaydi
  },
})

Relation filter operatorlari:

  some  — kamida bitta mos kelsin  (SQL: EXISTS)
  every — barchasi mos kelsin
  none  — hech biri mos kelmasin   (SQL: NOT EXISTS)

onDelete — bog'liq yozuv o'chganda nima bo'ladi

model Post {
  authorId Int
  author   User @relation(fields: [authorId], references: [id], onDelete: Cascade)
  //                                                             ─────────────────
}

  Cascade   — bog'liq yozuv o'chsa, bu yozuv ham o'chsin
              User o'chsa → uning postlari ham o'chsin

  SetNull   — bog'liq yozuv o'chsa, foreign key NULL bo'lsin
              User o'chsa → post.authorId = NULL
              (authorId nullable bo'lishi kerak: Int?)

  Restrict  — bog'liq yozuv o'chirib bo'lmaydi
              Postlari bor Userni o'chirmoqchi bo'lsa — xato beradi
              Avval postlarni o'chirish kerak

  NoAction  — Restrict ga o'xshash, lekin tekshirish keyinga qoldiriladi

Qachon qaysi?

  Cascade  → Profillar, manzillar, yordamchi ma'lumotlar
             (user o'chsa ulari ham kerak emas)

  SetNull  → Loglar, sharhlar, tarix
             (user o'chsa ham log saqlansin, faqat kim qilgani yo'qolsin)

  Restrict → Moliyaviy tranzaksiyalar, muhim yozuvlar
             (user o'chirilishidan avval boshqa narsalar hal qilinishi kerak)

Barcha bog'liqliklarni bir joyda ko'rish

// To'liq schema — barcha bog'liqliklar bilan

model User {
  id        Int      @id @default(autoincrement())
  name      String
  email     String   @unique
  password  String
  createdAt DateTime @default(now())

  profile     Profile?       // One-to-One
  posts       Post[]         // One-to-Many
  enrollments Enrollment[]   // Explicit Many-to-Many (oraliq model)

  @@map("users")
}

model Profile {
  id     Int     @id @default(autoincrement())
  bio    String?
  phone  String?

  userId Int  @unique
  user   User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("profiles")
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String
  published Boolean  @default(false)
  createdAt DateTime @default(now())

  authorId Int
  author   User  @relation(fields: [authorId], references: [id], onDelete: Cascade)
  tags     Tag[] // Implicit Many-to-Many (oraliq model avtomatik)

  @@map("posts")
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]
  @@map("tags")
}

model Course {
  id          Int          @id @default(autoincrement())
  title       String
  enrollments Enrollment[]
  @@map("courses")
}

model Enrollment {
  userId     Int
  courseId   Int
  enrolledAt DateTime @default(now())
  isPaid     Boolean  @default(false)

  user   User   @relation(fields: [userId],   references: [id], onDelete: Cascade)
  course Course @relation(fields: [courseId], references: [id], onDelete: Cascade)

  @@id([userId, courseId])
  @@map("enrollments")
}

TypeORM vs Prisma — relations taqqoslash

  ┌──────────────────┬─────────────────────────────┬──────────────────────────────┐
  │                  │         TypeORM              │          Prisma              │
  ├──────────────────┼─────────────────────────────┼──────────────────────────────┤
  │ One-to-One       │ @OneToOne + @JoinColumn      │ @unique + @relation          │
  ├──────────────────┼─────────────────────────────┼──────────────────────────────┤
  │ One-to-Many      │ @OneToMany + @ManyToOne      │ Model[] + @relation          │
  ├──────────────────┼─────────────────────────────┼──────────────────────────────┤
  │ Many-to-Many     │ @ManyToMany + @JoinTable     │ Model[] + Model[]            │
  │ (implicit)       │                             │ (oraliq jadval avtomatik)    │
  ├──────────────────┼─────────────────────────────┼──────────────────────────────┤
  │ Many-to-Many     │ Alohida entity + @ManyToOne  │ Alohida model + @@id         │
  │ (explicit)       │ ikki tomonda                 │ (kompozit primary key)       │
  ├──────────────────┼─────────────────────────────┼──────────────────────────────┤
  │ Bog'liqni yuklash│ relations: { posts: true }   │ include: { posts: true }     │
  ├──────────────────┼─────────────────────────────┼──────────────────────────────┤
  │ Bog'liqni ulash  │ entity.user = user; save()  │ connect: { id: userId }      │
  ├──────────────────┼─────────────────────────────┼──────────────────────────────┤
  │ onDelete         │ @ManyToOne({ onDelete: ... })│ @relation(onDelete: ...)     │
  └──────────────────┴─────────────────────────────┴──────────────────────────────┘

Vazifalar

1-vazifa — Schema: Comment modeli qo'shing. Har bir comment bitta postga tegishli (One-to-Many), va bitta foydalanuvchi yozgan (One-to-Many). Schema yozing, migration bajaring.

2-vazifa — Service: CommentsService da quyidagilarni yozing:

• create(postId, authorId, content) — yangi comment yaratish
• findByPost(postId) — postning barcha commentlari (muallif nomi bilan)
• remove(commentId, authorId) — faqat o'z commentini o'chirish mumkin

3-vazifa — O'ylash: Post o'chirilganda uning commentlari ham o'chirilishi kerak, lekin foydalanuvchi o'chirilganda uning commentlari authorId = NULL bo'lishi kerak (comment saqlansin). Comment modelida onDelete qanday yoziladi?

Xulosa

One-to-One — @unique + @relation. upsert — bor bo'lsa yangilash, yo'q bo'lsa yaratish.

One-to-Many — Model[] va @relation. Foreign key "ko'p" tomonda. connect — mavjudni ulash, create — yangi yaratib ulash.

Many-to-Many (implicit) — ikkala modelda Model[]. Oraliq jadval avtomatik. connect, disconnect, set, connectOrCreate.

Many-to-Many (explicit) — qo'shimcha maydon kerak bo'lganda. Alohida model, @@id([field1, field2]) kompozit kalit.

Relation filtrlash — some, every, none. _count — relation da nechta yozuv borligini sanash.

onDelete — Cascade (birga o'chsin), SetNull (NULL bo'lsin), Restrict (o'chirib bo'lmaydi).

Keyingi dars

21-dars: Prisma — Migration