Database

Database nima?

Database (ma'lumotlar bazasi) - bu ma'lumotlarni tartibli ravishda saqlash, boshqarish va olish uchun mo'ljallangan tizim. Ilovaning "xotirasi" deb tushunish mumkin.

Har qanday ilova ma'lumotlar bilan ishlaydi:

• Foydalanuvchi ma'lumotlari - ism, email, parol
• Kontent - postlar, mahsulotlar, sharhlar
• Tranzaksiyalar - buyurtmalar, to'lovlar
• Loglar - harakatlar tarixi, analytics

Database Management System (DBMS) - bu databaseni boshqaruvchi dastur. MySQL, PostgreSQL, MongoDB - bular DBMS misolari.

Nega kerak?

Ma'lumotlarni oddiy fayllarda saqlash mumkin emas mi? Kichik loyihalarda ha, lekin real ilovalar uchun database zarur:

Asosiy tushunchalar

SQL vs NoSQL

Schema Design

Schema - bu database strukturasi. Yaxshi schema dizayni:

• Normalization - takrorlanishni kamaytirish (1NF, 2NF, 3NF)
• Primary Key - har bir row uchun unique identifikator
• Foreign Key - tablelar orasidagi bog'lanish
• Indexes - tez qidiruv uchun strukturalar
• Constraints - NOT NULL, UNIQUE, CHECK

Indexing

Index - bu kitob oxiridagi "index" kabi. Butun kitobni o'qimasdan, kerakli sahifani tez topish:

• B-Tree index - eng keng tarqalgan, range query uchun
• Hash index - exact match uchun, juda tez
• Composite index - bir nechta column'ga index
• Full-text index - matn qidiruvi uchun

Transactions va ACID

Transaction - bu bir nechta operatsiyani bitta "atomik" birlik sifatida bajarish:

• Atomicity - hammasi bajariladi yoki hech narsa
• Consistency - database har doim valid holatda
• Isolation - transactionlar bir-biriga ta'sir qilmaydi
• Durability - commit bo'lgandan keyin ma'lumot saqlanadi

Migrations

Migration - bu schema o'zgarishlarini versiyalash va boshqarish:

• Yangi table, column qo'shish
• Ma'lumotlarni o'zgartirish
• Rollback imkoniyati
• Jamoa bo'ylab sync

Amaliy jarayon (step-by-step)

Eng ko'p uchraydigan xatolar

Best practices

• Har bir table'da surrogate primary key (id) ishlating - UUID yoki auto-increment
• created_at va updated_at column'larini qo'shing - debugging uchun zarur
• Foreign key constraintlarini e'tiborsiz qoldirmang - data integrity
• Connection pooling ishlating - har so'rovda yangi connection ochish xato
• Soft delete (is_deleted flag) o'rniga hard delete - audit uchun
• Sensitive ma'lumotlarni encrypt qiling - PII, financial data
• Read replica'lar bilan read traffic'ni taqsimlang
• Database-specific features o'rganing - PostgreSQL JSONB, MySQL full-text
• Query'larni parametrize qiling - SQL injection'dan himoya
• Index faqat kerakli joyga - ortiqcha index write'ni sekinlashtiradi

Asboblar va texnologiyalar

Mini misol

PostgreSQL schema va Prisma ORM misoli:

// Prisma schema - database modellarini aniqlash

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

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

model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String?
  password  String
  role      Role     @default(USER)
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([email])
}

model Post {
  id        String   @id @default(uuid())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  String
  tags      Tag[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([authorId])
  @@index([published])
}

model Tag {
  id    String @id @default(uuid())
  name  String @unique
  posts Post[]
}

enum Role {
  USER
  ADMIN
}

Prisma Client bilan CRUD operatsiyalari:

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

// Yangi user yaratish
async function createUser(email: string, name: string, password: string) {
  return prisma.user.create({
    data: {
      email,
      name,
      password: await hashPassword(password),
    },
  });
}

// User va uning postlarini olish (eager loading)
async function getUserWithPosts(userId: string) {
  return prisma.user.findUnique({
    where: { id: userId },
    include: {
      posts: {
        where: { published: true },
        orderBy: { createdAt: 'desc' },
        take: 10,
      },
    },
  });
}

// Transaction bilan pul o'tkazish
async function transferCredits(fromId: string, toId: string, amount: number) {
  return prisma.$transaction(async (tx) => {
    // Yuboruvchidan ayirish
    const sender = await tx.user.update({
      where: { id: fromId },
      data: { credits: { decrement: amount } },
    });
    
    if (sender.credits < 0) {
      throw new Error('Insufficient credits');
    }
    
    // Qabul qiluvchiga qo'shish
    await tx.user.update({
      where: { id: toId },
      data: { credits: { increment: amount } },
    });
    
    return sender;
  });
}

Raw SQL query (murakkab holatlar uchun):

-- Index yaratish
CREATE INDEX idx_posts_author_published 
ON posts (author_id, published) 
WHERE published = true;

-- Aggregation query
SELECT 
  u.id,
  u.name,
  COUNT(p.id) AS post_count,
  MAX(p.created_at) AS last_post_date
FROM users u
LEFT JOIN posts p ON u.id = p.author_id
WHERE u.role = 'ADMIN'
GROUP BY u.id, u.name
HAVING COUNT(p.id) > 5
ORDER BY post_count DESC
LIMIT 10;

Ko'p so'raladigan savollar (FAQ)

Glossary (Atamalar lug'ati)