Database Migrations (Prisma)

~20 daq · NestJS · Ma'lumotlar bazasi

Muammodan boshlaylik

Prisma bilan loyiha yozdingiz. Schema.prisma da model yozdingiz. Lekin schema o'zgarganda ma'lumotlar bazasiga qanday yetib boradi?

Ikkita yo'l bor:

  prisma db push            prisma migrate dev
  ──────────────────────────────────────────────────────
  Schema ni to'g'ridan-to'g'ri  │  Migration fayl yozib,
  bazaga qo'llaydi              │  keyin qo'llaydi

  Tez, lekin tarix saqlanmaydi │  Sekinroq, lekin tarix bor

  Development sinab ko'rish    │  Haqiqiy loyiha uchun
  prototip uchun               │  (development + production)

  Migration fayllar yaratilmaydi│  Har o'zgarish — alohida fayl

Real loyihalarda migration ishlatiladi. Nima uchun? Chunki:

• Har bir o'zgarish git da saqlanadi — kim, qachon, nima o'zgartirgan
• Production ga xavfsiz qo'llash mumkin — test qilingan SQL
• Bir nechta dasturchi ishlasa — barchaning bazasi bir xil bo'ladi
• Xato bo'lsa — orqaga qaytarish mumkin

Prisma migration qanday ishlaydi?

  schema.prisma o'zgardi
         │
         ▼
  prisma migrate dev
         │
         ├─► 1. Baza holati bilan schemani taqqoslaydi
         │
         ├─► 2. Farqni SQL sifatida yozadi
         │       prisma/migrations/20260101_add_phone/migration.sql
         │
         ├─► 3. SQL ni bazaga qo'llaydi
         │
         └─► 4. Prisma Client qayta generatsiya qiladi


  _prisma_migrations jadvali (PostgreSQL da avtomatik yaratiladi)
  ┌──────┬────────────────────────────────────┬──────────┬────────────┐
  │  id  │ migration_name                     │ finished │ applied_at │
  ├──────┼────────────────────────────────────┼──────────┼────────────┤
  │  1   │ 20260101000000_init                 │   true   │ 2026-01-01 │
  │  2   │ 20260115000000_add_phone_to_users   │   true   │ 2026-01-15 │
  │  3   │ 20260120000000_create_posts_table   │   true   │ 2026-01-20 │
  └──────┴────────────────────────────────────┴──────────┴────────────┘

  Prisma bu jadvalga qarab: "qaysi migrationlar bajarilgan?" deb biladi.
  Yangi fayllar — bajarilmagan, ularni ishga tushiradi.

Asosiy buyruqlar

Beshta asosiy buyruq bor. Har birini tushuntiramiz.

1. migrate dev — development uchun

npx prisma migrate dev --name add_phone_to_users

Nima qiladi:

1. Hozirgi schema va baza holatini taqqoslaydi
2. Farqni SQL faylga yozadi
3. SQL ni bazaga qo'llaydi
4. prisma generate — Client qayta yaratiladi

  Fayl yaratiladi:
  prisma/migrations/20260115120000_add_phone_to_users/migration.sql
  ─────────────────┬─────────────────────────────────┬────────────
                   │ timestamp (avtomatik)             │ --name dagi nom

Migration fayl ichida — Prisma yozgan SQL:

-- prisma/migrations/20260115120000_add_phone_to_users/migration.sql

-- AlterTable
ALTER TABLE "users" ADD COLUMN "phone" TEXT;

💡 --name — migration nomini ko'rsatadi. Qisqa, tushuntiruvcchi yozing: add_phone_to_users, create_posts_table, rename_name_to_fullname. Bu nom fayl nomida va git tarixida ko'rinadi.

2. migrate deploy — production uchun

npx prisma migrate deploy

Nima qiladi:

1. Bajarilmagan migrationlarni topadi
2. Ularni tartib bilan bazaga qo'llaydi
3. prisma generate bajarMaydi — alohida kerak

migrate dev dan farqi — interaktiv emas, savol bermaydi. CI/CD da ishlatish uchun mo'ljallangan.

⚠️ Production da migrate dev emas, migrate deploy ishlatiladi. migrate dev development bazasini tozalashi mumkin.

3. migrate status — holat

npx prisma migrate status

  Natija:

  3 migrations found in prisma/migrations

  ✔  20260101000000_init
  ✔  20260115000000_add_phone_to_users
  ✗  20260120000000_create_posts_table   ← bajarilmagan (kutmoqda)

  1 migration not yet applied.
  Run `prisma migrate deploy` to apply.

4. migrate reset — bazani tozalash

npx prisma migrate reset

Nima qiladi:

1. Bazadagi barcha jadvallarni o'chiradi
2. Barcha migrationlarni boshidan bajaradi
3. prisma db seed — seed ma'lumotlar (bo'lsa)

⚠️ Faqat development da. Production da hech qachon — barcha ma'lumotlar yo'qoladi.

Qachon kerak? O'rganish jarayonida chalkashib ketganda, yoki migration fayllar baza bilan mos kelmay qolganda bazani "sifr" dan boshlash uchun.

5. db pull — mavjud bazadan schema olish

npx prisma db pull

Mavjud PostgreSQL bazasidagi jadvallardan schema.prisma ni avtomatik yaratadi. Eski loyihaga Prisma qo'shayotganda yoki boshqa ORM dan o'tishda foydali.

Amaliy jarayon — bosqichma-bosqich

Keling, yangi loyihadan boshlab migration qanday ishlashini ko'ramiz.

Qadam 1: Dastlabki schema

// prisma/schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  name      String
  email     String   @unique
  createdAt DateTime @default(now())
  @@map("users")
}

npx prisma migrate dev --name init

  prisma/migrations/
  └── 20260101000000_init/
      └── migration.sql

-- migration.sql
CREATE TABLE "users" (
    "id"        SERIAL NOT NULL,
    "name"      TEXT NOT NULL,
    "email"     TEXT NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "users_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "users_email_key" ON "users"("email");

  users jadvali yaratildi:
  ┌────┬──────┬───────┬───────────┐
  │ id │ name │ email │ createdAt │
  └────┴──────┴───────┴───────────┘

Qadam 2: Yangi maydon qo'shish

model User {
  id        Int      @id @default(autoincrement())
  name      String
  email     String   @unique
  phone     String?  // ← yangi, nullable
  role      Role     @default(USER)  // ← yangi, enum
  createdAt DateTime @default(now())
  @@map("users")
}

enum Role {
  USER
  ADMIN
}

npx prisma migrate dev --name add_phone_and_role_to_users

-- migration.sql (Prisma yozdi)
CREATE TYPE "Role" AS ENUM ('USER', 'ADMIN');

ALTER TABLE "users"
  ADD COLUMN "phone" TEXT,
  ADD COLUMN "role"  "Role" NOT NULL DEFAULT 'USER';

  users jadvali yangilandi:
  ┌────┬──────┬───────┬───────┬────────┬───────────┐
  │ id │ name │ email │ phone │ role   │ createdAt │
  ├────┼──────┼───────┼───────┼────────┼───────────┤
  │ 1  │ Ali  │ali@.. │ NULL  │  USER  │ 2026-01-01│  ← eski ma'lumotlar saqlanib qoldi
  └────┴──────┴───────┴───────┴────────┴───────────┘

Qadam 3: Yangi jadval qo'shish

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

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

  @@map("posts")
}

// User modeliga ham qo'shamiz
model User {
  // ...
  posts Post[]
}

npx prisma migrate dev --name create_posts_table

-- migration.sql
CREATE TABLE "posts" (
    "id"        SERIAL NOT NULL,
    "title"     TEXT NOT NULL,
    "content"   TEXT NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "authorId"  INTEGER NOT NULL,
    CONSTRAINT "posts_pkey" PRIMARY KEY ("id")
);

ALTER TABLE "posts"
  ADD CONSTRAINT "posts_authorId_fkey"
  FOREIGN KEY ("authorId")
  REFERENCES "users"("id")
  ON DELETE CASCADE ON UPDATE CASCADE;

Migration papkasi hozir:

  prisma/migrations/
  ├── 20260101000000_init/
  │   └── migration.sql              ← users jadvali
  ├── 20260115000000_add_phone_and_role_to_users/
  │   └── migration.sql              ← phone va role ustunlari
  └── 20260120000000_create_posts_table/
      └── migration.sql              ← posts jadvali

Ma'lumot yo'qotmasdan ustun o'zgartirish

Eng ko'p uchraydigan holat — ustun nomini o'zgartirish. Prisma bu yerda ehtiyotkor.

// name → fullName ga o'zgartirmoqchisiz
model User {
  // ...
  fullName String   // ← name o'rniga
}

npx prisma migrate dev --name rename_name_to_fullname

Prisma shunday savol beradi:

  ⚠️  We need to reset the PostgreSQL database "mydb".
      The following migration(s) are not applied:
      ...

  ? Are you sure you want to reset your database? (y/N)

Yoki migration SQL ni ko'rsatadi:

-- Prisma avtomatik yozgan SQL — bu ma'lumot yo'qotadi!
ALTER TABLE "users" DROP COLUMN "name";      ← eski ma'lumot yo'qoladi
ALTER TABLE "users" ADD COLUMN "fullName" TEXT NOT NULL; ← bo'sh

To'g'ri yechim — migration faylini qo'lda tahrirlash:

# Avval migration faylini yarating (--create-only — bazaga qo'llamaydi)
npx prisma migrate dev --name rename_name_to_fullname --create-only

Yaratilgan faylni ochasiz va qo'lda to'g'irlaysiz:

-- prisma/migrations/20260125000000_rename_name_to_fullname/migration.sql

-- 1. Yangi ustun qo'shamiz
ALTER TABLE "users" ADD COLUMN "fullName" TEXT;

-- 2. Eski ma'lumotni ko'chiramiz
UPDATE "users" SET "fullName" = "name";

-- 3. fullName ni NOT NULL qilamiz (ma'lumot ko'chirilgandan keyin)
ALTER TABLE "users" ALTER COLUMN "fullName" SET NOT NULL;

-- 4. Eski ustunni o'chiramiz
ALTER TABLE "users" DROP COLUMN "name";

Keyin bazaga qo'llaymiz:

npx prisma migrate dev

  users jadvali:
  Oldin:                    Keyin:
  ┌────┬────────┬───────┐   ┌────┬──────────┬───────┐
  │ id │ name   │ email │   │ id │ fullName  │ email │
  ├────┼────────┼───────┤   ├────┼──────────┼───────┤
  │ 1  │ Ali    │ali@.. │   │ 1  │ Ali      │ali@.. │  ← ma'lumot saqlanib qoldi
  │ 2  │ Vali   │vali@.│   │ 2  │ Vali     │vali@.│
  └────┴────────┴───────┘   └────┴──────────┴───────┘

Production da migration

CI/CD pipeline da

# deploy.sh yoki GitHub Actions da
echo "Prisma Client generatsiya..."
npx prisma generate

echo "Migrationlar qo'llanmoqda..."
npx prisma migrate deploy

echo "Ilova ishga tushmoqda..."
npm run start:prod

NestJS ilovasi ichida avtomatik

// main.ts
import { PrismaClient } from '@prisma/client';

async function bootstrap() {
  // Ilova ishga tushishidan OLDIN migrationlarni bajaring
  const prisma = new PrismaClient();
  await prisma.$executeRaw`SELECT 1`; // ulanishni tekshirish

  const app = await NestFactory.create(AppModule);
  await app.listen(3000);
}
bootstrap();

Yoki PrismaService da:

// prisma/prisma.service.ts
import { Injectable, OnModuleInit } from '@nestjs/common';
import { PrismaClient } from '@prisma/client';
import { execSync } from 'child_process';

@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit {

  async onModuleInit() {
    // Production da migration avtomatik bajaring
    if (process.env.NODE_ENV === 'production') {
      execSync('npx prisma migrate deploy', { stdio: 'inherit' });
    }
    await this.$connect();
  }
}

Seed — boshlang'ich ma'lumotlar

Seed — yangi o'rnatilgan yoki reset qilingan bazaga namuna ma'lumotlar yozish. Test uchun admin user, kategoriyalar va h.k.

// prisma/seed.ts
import { PrismaClient } from '@prisma/client';
import * as bcrypt from 'bcrypt';

const prisma = new PrismaClient();

async function main() {
  // Admin user yaratamiz
  const admin = await prisma.user.upsert({
    where:  { email: 'admin@example.com' },
    update: {},
    // upsert — bor bo'lsa hech narsa qilmaydi (update bo'sh)
    // yo'q bo'lsa — create qiladi
    create: {
      name:     'Admin',
      email:    'admin@example.com',
      password: await bcrypt.hash('admin123', 10),
      role:     'ADMIN',
    },
  });

  // Kategoriyalar yaratamiz
  const categories = await Promise.all([
    prisma.category.upsert({
      where:  { name: 'NestJS' },
      update: {},
      create: { name: 'NestJS' },
    }),
    prisma.category.upsert({
      where:  { name: 'TypeScript' },
      update: {},
      create: { name: 'TypeScript' },
    }),
  ]);

  console.log('Seed muvaffaqiyatli bajarildi');
  console.log({ admin, categories });
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect());

package.json ga seed konfiguratsiyasi:

{
  "prisma": {
    "seed": "ts-node prisma/seed.ts"
  },
  "scripts": {
    "db:seed": "prisma db seed"
  }
}

# Seed ni ishga tushirish
npx prisma db seed

# migrate reset seed ni avtomatik ishga tushiradi
npx prisma migrate reset

Barcha buyruqlarni bir joyda

// package.json — foydali skriptlar
{
  "scripts": {
    "prisma:migrate":        "prisma migrate dev",
    "prisma:migrate:deploy": "prisma migrate deploy",
    "prisma:migrate:status": "prisma migrate status",
    "prisma:migrate:reset":  "prisma migrate reset",
    "prisma:generate":       "prisma generate",
    "prisma:seed":           "prisma db seed",
    "prisma:studio":         "prisma studio",
    "prisma:pull":           "prisma db pull"
  }
}

  Buyruq                   Qachon ishlatiladi
  ─────────────────────────────────────────────────────────────────
  migrate dev              Schema o'zgarganda (development)
  migrate deploy           Production ga deploy qilganda
  migrate status           Qaysi migration bajarilganini ko'rish
  migrate reset            Bazani sifr dan boshlash (faqat dev)
  generate                 schema.prisma o'zgarganda Client yangilash
  db seed                  Boshlang'ich ma'lumotlar yozish
  studio                   Vizual interfeys — brauzerda baza ko'rish
  db pull                  Mavjud bazadan schema olish

TypeORM migration vs Prisma migration

  ┌──────────────────────┬────────────────────────┬───────────────────────────┐
  │                      │     TypeORM            │        Prisma             │
  ├──────────────────────┼────────────────────────┼───────────────────────────┤
  │ Migration yaratish   │ migration:generate     │ prisma migrate dev        │
  ├──────────────────────┼────────────────────────┼───────────────────────────┤
  │ Migration bajarish   │ migration:run          │ prisma migrate deploy     │
  ├──────────────────────┼────────────────────────┼───────────────────────────┤
  │ Orqaga qaytarish     │ migration:revert       │ Yo'q (qo'lda yozish kerak)│
  ├──────────────────────┼────────────────────────┼───────────────────────────┤
  │ SQL kim yozadi?      │ TypeORM (generate) yoki│ Prisma o'zi yozadi        │
  │                      │ dasturchi (create)     │                           │
  ├──────────────────────┼────────────────────────┼───────────────────────────┤
  │ Client generatsiya   │ Shart emas             │ Har migration da kerak    │
  ├──────────────────────┼────────────────────────┼───────────────────────────┤
  │ Seed                 │ Alohida skript         │ prisma db seed            │
  ├──────────────────────┼────────────────────────┼───────────────────────────┤
  │ Baza tozalash        │ schema drop + run      │ migrate reset             │
  └──────────────────────┴────────────────────────┴───────────────────────────┘

Prisma migration ning afzalligi: SQL ni o'zi yozadi — siz faqat schemani o'zgartirасиз. TypeORM da generate to'g'ri SQL yozmasa — qo'lda tuzatish kerak.

Prisma migration ning kamchiligi: Orqaga qaytarish (revert) yo'q. Agar migration xato bo'lsa — yangi migration yozib tuzatish kerak.

Vazifalar

1-vazifa — Amaliy: schema.prisma ga Category modeli qo'shing (id, name unique, createdAt). Migration yarating va bajaring. Keyin Post ga categoryId qo'shing (One-to-Many). Yangi migration yarating.

2-vazifa — Ma'lumot saqlash: Post modelida title ustunini headline ga o'zgartirmoqchisiz. --create-only bilan migration yarating va SQL ni qo'lda to'g'irlang — ma'lumot yo'qolmasin.

3-vazifa — Seed: prisma/seed.ts yozing: 1 ta admin user va 3 ta kategoriya (NestJS, TypeScript, PostgreSQL) yaratsin. upsert ishlatsin — ikki marta ishga tushirganda dublikat bo'lmasin.

Xulosa

migrate dev — development da schema o'zgarganda. SQL yozadi, bazaga qo'llaydi, Client yangilaydi.

migrate deploy — production da. Faqat qo'llaydi, savol bermaydi.

--create-only — SQL ni qo'lda tahrirlash kerak bo'lganda. Avval fayl yaratadi, keyin alohida qo'llash mumkin.

Ma'lumot yo'qotmaslik — ustun o'zgartishda: --create-only → SQL ni tuzatish → migrate dev.

Seed — boshlang'ich ma'lumotlar. upsert ishlatiladi — ikki marta ishga tushirganda muammo bo'lmasin.

Prisma vs TypeORM: Prisma SQL ni o'zi yozadi (qulay), lekin revert yo'q. TypeORM da revert bor, lekin SQL qo'lda yozish kerak bo'lishi mumkin.

Keyingi dars

22-dars: Mongoose + MongoDB