Prisma ORM — schema, migration, CRUD

~25 daq · MySQL · MySQL va Dasturlash Tillari

Muammodan boshlaylik

Sequelize bilan ishlaganda (57-dars) bir muammo doim seziladi:

const b = await Buyurtma.findByPk(3, { include: [{ model: Mijoz, as: 'mijoz' }] });
console.log(b.mjoz.ism);   // xato yozildi — faqat ishga tushganda bilinadi

Muharrir b ichida nima borligini bilmaydi. Ustun nomida xato, noto'g'ri tur, kiritilmagan include — hammasi faqat ishga tushganda (yoki production da) ko'rinadi.

Prisma — boshqacha yondashuvdagi zamonaviy ORM: sxema bitta faylda yoziladi, va undan to'liq tiplangan mijoz avtomatik generatsiya qilinadi. TypeScript bilan har bir xato kod yozilayotganda ko'rinadi.

O'rnatish

npm install prisma --save-dev
npm install @prisma/client
npx prisma init --datasource-provider mysql

Yaratiladi:

prisma/schema.prisma    ← butun sxema shu yerda
.env                    ← DATABASE_URL

.env:

DATABASE_URL="mysql://dokon_app:Parol@localhost:3306/dokon_dev"

schema.prisma — yagona haqiqat manbai

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

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

model Toifa {
  id          Int        @id @default(autoincrement()) @db.UnsignedInt
  nom         String     @unique @db.VarChar(100)
  mahsulotlar Mahsulot[]

  @@map("toifalar")
}

model Mahsulot {
  id        Int       @id @default(autoincrement()) @db.UnsignedInt
  toifaId   Int?      @map("toifa_id") @db.UnsignedInt
  nom       String    @db.VarChar(200)
  narx      Int       @db.UnsignedInt
  soni      Int       @default(0) @db.UnsignedInt
  createdAt DateTime  @default(now()) @map("created_at")

  toifa     Toifa?             @relation(fields: [toifaId], references: [id], onDelete: SetNull)
  qatorlar  BuyurtmaQatori[]

  @@index([toifaId, narx])
  @@map("mahsulotlar")
}

model Mijoz {
  id          Int        @id @default(autoincrement()) @db.UnsignedInt
  ism         String     @db.VarChar(100)
  email       String     @unique @db.VarChar(255)
  telefon     String?    @db.VarChar(20)
  shahar      String?    @db.VarChar(50)
  buyurtmalar Buyurtma[]

  @@map("mijozlar")
}

enum BuyurtmaHolati {
  yangi
  tolangan
  yolda
  yetkazildi
  bekor
}

model Buyurtma {
  id         Int              @id @default(autoincrement()) @db.UnsignedInt
  mijozId    Int              @map("mijoz_id") @db.UnsignedInt
  holat      BuyurtmaHolati   @default(yangi)
  createdAt  DateTime         @default(now()) @map("created_at")

  mijoz      Mijoz            @relation(fields: [mijozId], references: [id], onDelete: Restrict)
  qatorlar   BuyurtmaQatori[]

  @@index([mijozId, createdAt])
  @@map("buyurtmalar")
}

model BuyurtmaQatori {
  id          Int      @id @default(autoincrement()) @db.UnsignedInt
  buyurtmaId  Int      @map("buyurtma_id") @db.UnsignedInt
  mahsulotId  Int      @map("mahsulot_id") @db.UnsignedInt
  soni        Int      @db.UnsignedSmallInt
  narx        Int      @db.UnsignedInt

  buyurtma    Buyurtma @relation(fields: [buyurtmaId], references: [id], onDelete: Cascade)
  mahsulot    Mahsulot @relation(fields: [mahsulotId], references: [id], onDelete: Restrict)

  @@map("buyurtma_qatorlari")
}

O'qilishi:

• Int? — NULL bo'lishi mumkin; Int — NOT NULL
• @id, @unique, @default(...) — cheklovlar (6-dars)
• @db.VarChar(100), @db.UnsignedInt — aniq MySQL turi (4-dars)
• @map("toifa_id"), @@map("toifalar") — kodda toifaId (camelCase), bazada toifa_id (snake_case)
• @relation(fields, references, onDelete) — foreign key va CASCADE/RESTRICT/SET NULL (7-dars)
• Mahsulot[] — teskari tomon: "bu toifaning mahsulotlari" (bazada ustun emas)
• @@index([...]) — composite indeks (31-dars)

Butun sxema bitta faylda — xuddi ER diagramma (48-dars) kabi o'qiladi.

Migratsiyalar

npx prisma migrate dev --name init

Prisma:

1. schema.prisma ni hozirgi baza bilan solishtiradi
2. Farqdan SQL migratsiya faylini yaratadi: prisma/migrations/20261104_init/migration.sql
3. Uni bazaga qo'llaydi
4. Tiplangan mijozni qayta generatsiya qiladi

SQL fayl — oddiy, o'qiladigan:

CREATE TABLE `mijozlar` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `ism` VARCHAR(100) NOT NULL,
    ...
    UNIQUE INDEX `mijozlar_email_key`(`email`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Sxemani o'zgartirasiz (masalan, tugilganKun DateTime? @db.Date qo'shasiz) → yana migrate dev → yangi migratsiya fayli.

[!TIP] Mavjud bazangiz bormi? npx prisma db pull — Prisma jadvallarni o'qib, schema.prisma ni o'zi yozadi. Keyin faqat nomlarni chiroyli qilasiz.

CRUD

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

// CREATE
const mijoz = await prisma.mijoz.create({
  data: { ism: 'Dilshod', email: 'dilshod@mail.uz', shahar: 'Toshkent' },
});

// READ
const bitta = await prisma.mijoz.findUnique({ where: { email: 'aziz@mail.uz' } });

const mahsulotlar = await prisma.mahsulot.findMany({
  where: {
    narx: { gte: 100000, lte: 5000000 },
    soni: { gt: 0 },
    nom:  { contains: 'Pro' },
    OR:   [{ toifaId: 1 }, { toifaId: 3 }],
  },
  select: { id: true, nom: true, narx: true },   // SELECT * emas
  orderBy: { narx: 'asc' },
  take: 20,
  skip: 0,
});

// UPDATE
await prisma.mahsulot.update({
  where: { id: 5 },
  data: { soni: { decrement: 1 } },             // SET soni = soni - 1 — atomar (12-dars)
});

// UPSERT (9-dars)
await prisma.toifa.upsert({
  where: { nom: 'Planshetlar' },
  update: {},
  create: { nom: 'Planshetlar' },
});

// DELETE
await prisma.mijoz.delete({ where: { id: 4 } });

// Agregatlar
const stat = await prisma.mahsulot.aggregate({
  _count: true, _sum: { soni: true }, _avg: { narx: true },
});

const toifalar = await prisma.mahsulot.groupBy({
  by: ['toifaId'],
  _count: { _all: true },
  _max: { narx: true },
});

Har bir qator to'liq tiplangan: mahsulotlar[0].nom — string; mahsulotlar[0].tavsif — kompilyatsiya xatosi, chunki select da yo'q.

Munosabatlar

include va ichma-ich select

const buyurtma = await prisma.buyurtma.findUnique({
  where: { id: 3 },
  include: {
    mijoz: { select: { ism: true, telefon: true } },
    qatorlar: {
      include: { mahsulot: { select: { nom: true } } },
    },
  },
});

buyurtma?.mijoz.ism;                    // string — tiplangan
buyurtma?.qatorlar[0].mahsulot.nom;     // string

include qilinmagan munosabatga murojaat — kompilyatsiya xatosi. N+1 ni "tasodifan" yozib bo'lmaydi.

Munosabat bo'yicha filtr

// Kamida bitta yetkazilgan buyurtmasi bor mijozlar (EXISTS, 22-dars)
await prisma.mijoz.findMany({
  where: { buyurtmalar: { some: { holat: 'yetkazildi' } } },
});

// Hech qanday buyurtmasi yo'qlar (NOT EXISTS)
await prisma.mijoz.findMany({
  where: { buyurtmalar: { none: {} } },
});

// Har bir mijoz — buyurtmalar soni bilan
await prisma.mijoz.findMany({
  include: { _count: { select: { buyurtmalar: true } } },
});

Ichma-ich yaratish

const yangi = await prisma.buyurtma.create({
  data: {
    mijoz: { connect: { id: 2 } },
    qatorlar: {
      create: [
        { mahsulotId: 5, soni: 1, narx: 3100000 },
        { mahsulotId: 6, soni: 2, narx: 120000 },
      ],
    },
  },
  include: { qatorlar: true },
});

Buyurtma va qatorlar — bitta chaqiruvda, avtomatik tranzaksiya ichida.

Tranzaksiyalar

const buyurtmaId = await prisma.$transaction(async (tx) => {
  const mahsulot = await tx.mahsulot.findUniqueOrThrow({ where: { id: 5 } });
  if (mahsulot.soni < 1) throw new Error("Omborda yo'q");

  const ozgardi = await tx.mahsulot.updateMany({
    where: { id: 5, soni: { gte: 1 } },              // atomar tekshiruv
    data: { soni: { decrement: 1 } },
  });
  if (ozgardi.count === 0) throw new Error("Omborda yo'q");

  const b = await tx.buyurtma.create({
    data: { mijozId: 2, qatorlar: { create: [{ mahsulotId: 5, soni: 1, narx: mahsulot.narx }] } },
  });
  return b.id;
}, { isolationLevel: 'ReadCommitted', timeout: 5000 });

tx — tranzaksiya ichidagi mijoz. Xato tashlansa — avtomatik ROLLBACK.

Prisma da to'g'ridan-to'g'ri SELECT ... FOR UPDATE yo'q — yuqoridagidek shartli updateMany (WHERE soni >= 1) bilan atomar tekshiruv qilinadi, yoki xom SQL.

Xom SQL

const shahar = 'Toshkent';
const natija = await prisma.$queryRaw<{ ism: string; jami: bigint }[]>`
  SELECT m.ism, SUM(q.soni * q.narx) AS jami
  FROM mijozlar m
  JOIN buyurtmalar b ON b.mijoz_id = m.id
  JOIN buyurtma_qatorlari q ON q.buyurtma_id = b.id
  WHERE m.shahar = ${shahar}
  GROUP BY m.id
  ORDER BY jami DESC
`;

$queryRaw tagged template — ${shahar} avtomatik placeholder ga aylanadi, xavfsiz.

[!WARNING] $queryRawUnsafe(sqlString) — nomida "Unsafe" bejiz emas. U oddiy string qabul qiladi va unga o'zgaruvchi qo'shsangiz — SQL injection (51-dars).

Sequelize va Prisma

Yangi TypeScript loyihasi uchun Prisma ko'pincha qulayroq tanlov.

Nima uchun shunday?

Nima uchun Prisma sxemani alohida fayl va alohida tilda yozadi, JavaScript classlari bilan emas?

Chunki sxema — ilova kodi emas, ma'lumotlar tuzilmasining tavsifi. Uni maxsus til bilan yozish uchta imkoniyat beradi: undan bir vaqtda migratsiya SQL ini, TypeScript tiplarini va tiplangan so'rov mijozini generatsiya qilish; sxemani hozirgi baza bilan aniq solishtirish (migrate diff); va uni ER diagramma kabi bir qarashda o'qish. JavaScript classlari bilan bular ancha qiyin — classlar ish vaqtida (runtime) mavjud, sxema esa kod yozilishidan oldin kerak.

Xulosa

Eslab qoling:

• schema.prisma — yagona haqiqat manbai: modellar, munosabatlar, indekslar, enum lar
• @map / @@map — kodda camelCase, bazada snake_case
• prisma migrate dev — migratsiya yaratish; migrate deploy — production; db pull — mavjud bazadan
• findMany({ where, select, orderBy, take, skip }), create, update, upsert, delete
• { decrement: 1 } — atomar o'zgartirish
• include / select — munosabatlar, to'liq tiplangan
• some, none, _count — munosabat bo'yicha filtr va hisob
• $transaction(async tx => ...) — avtomatik rollback
• $queryRaw — xavfsiz; $queryRawUnsafe — ehtiyot

Keyingi dars

59-dars: TypeORM — entity, repository, relation