Real loyiha — to'liq DB schema loyihalash va yozish

~40 daq · PostgreSQL · PostgreSQL va Node.js

Bu kursda o'rgangan barcha narsani bir loyihada birlashtirish vaqti. E-commerce platformasi — foydalanuvchilar, mahsulotlar, buyurtmalar. Boshlashdan yakunigacha: loyihalash, schema, indekslar, Node.js integratsiya.

Loyiha talablari

✅ Foydalanuvchilar ro'yxatdan o'tishi va kirishi
✅ Mahsulotlar katalogi (kategoriyalar bilan)
✅ Savatcha va buyurtmalar
✅ Foydalanuvchi faqat o'z buyurtmalarini ko'radi (RLS)
✅ Qidiruv (pg_trgm)
✅ Softdelete

Schema

-- Extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Kategoriyalar (daraxtsimon)
CREATE TABLE kategoriyalar (
  id          SERIAL PRIMARY KEY,
  nomi        TEXT NOT NULL,
  ota_id      INT REFERENCES kategoriyalar(id),
  yaratilgan  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Foydalanuvchilar
CREATE TABLE foydalanuvchilar (
  id           BIGSERIAL PRIMARY KEY,
  ism          TEXT NOT NULL,
  email        TEXT NOT NULL UNIQUE,
  parol_hash   TEXT NOT NULL,
  rol          TEXT NOT NULL DEFAULT 'user' CHECK (rol IN ('user', 'admin')),
  faol         BOOLEAN NOT NULL DEFAULT TRUE,
  yaratilgan   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  yangilangan  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  o_chirilgan  TIMESTAMPTZ
);

-- Mahsulotlar
CREATE TABLE mahsulotlar (
  id            BIGSERIAL PRIMARY KEY,
  nomi          TEXT NOT NULL,
  tavsif        TEXT,
  narxi         NUMERIC(12, 2) NOT NULL CHECK (narxi >= 0),
  qoldiq        INT NOT NULL DEFAULT 0 CHECK (qoldiq >= 0),
  kategoriya_id INT REFERENCES kategoriyalar(id),
  rasm_url      TEXT,
  faol          BOOLEAN NOT NULL DEFAULT TRUE,
  yaratilgan    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  yangilangan   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Manzillar
CREATE TABLE manzillar (
  id               BIGSERIAL PRIMARY KEY,
  foydalanuvchi_id BIGINT NOT NULL REFERENCES foydalanuvchilar(id),
  shahar           TEXT NOT NULL,
  ko_cha           TEXT NOT NULL,
  uy               TEXT NOT NULL,
  asosiy           BOOLEAN NOT NULL DEFAULT FALSE,
  yaratilgan       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Buyurtmalar
CREATE TABLE buyurtmalar (
  id               BIGSERIAL PRIMARY KEY,
  foydalanuvchi_id BIGINT NOT NULL REFERENCES foydalanuvchilar(id),
  manzil_id        BIGINT REFERENCES manzillar(id),
  holat            TEXT NOT NULL DEFAULT 'yangi'
                   CHECK (holat IN ('yangi', 'tasdiqlangan', 'yetkazilmoqda', 'yetkazildi', 'bekor')),
  jami_summa       NUMERIC(12, 2) NOT NULL,
  yaratilgan       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  yangilangan      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Buyurtma elementlari
CREATE TABLE buyurtma_elementlari (
  id           BIGSERIAL PRIMARY KEY,
  buyurtma_id  BIGINT NOT NULL REFERENCES buyurtmalar(id) ON DELETE CASCADE,
  mahsulot_id  BIGINT NOT NULL REFERENCES mahsulotlar(id),
  soni         INT NOT NULL CHECK (soni > 0),
  narxi        NUMERIC(12, 2) NOT NULL, -- o'sha vaqtdagi narx
  UNIQUE (buyurtma_id, mahsulot_id)
);

Indekslar

-- Qidiruv uchun
CREATE INDEX idx_mahsulotlar_nomi_trgm ON mahsulotlar USING GIN (nomi gin_trgm_ops);
CREATE INDEX idx_mahsulotlar_kategoriya ON mahsulotlar (kategoriya_id) WHERE faol = TRUE;

-- Buyurtmalar
CREATE INDEX idx_buyurtmalar_foydalanuvchi ON buyurtmalar (foydalanuvchi_id);
CREATE INDEX idx_buyurtmalar_holat_vaqt   ON buyurtmalar (holat, yaratilgan DESC);

-- Foydalanuvchilar
CREATE INDEX idx_foydalanuvchilar_email ON foydalanuvchilar (email);

Row Level Security

ALTER TABLE buyurtmalar ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_buyurtmalari ON buyurtmalar
FOR ALL
USING (
  foydalanuvchi_id = current_setting('app.user_id')::BIGINT
  OR current_setting('app.user_role') = 'admin'
);

Node.js — asosiy so'rovlar

// Mahsulot qidirish
async function mahsulotQidirish(q, limit = 20, offset = 0) {
  const { rows } = await pool.query(`
    SELECT m.*, k.nomi AS kategoriya
    FROM mahsulotlar m
    LEFT JOIN kategoriyalar k ON m.kategoriya_id = k.id
    WHERE m.faol = TRUE
      AND (m.nomi ILIKE $1 OR m.tavsif ILIKE $1)
    ORDER BY similarity(m.nomi, $2) DESC, m.id
    LIMIT $3 OFFSET $4
  `, [`%${q}%`, q, limit, offset]);
  return rows;
}

// Buyurtma yaratish (transaction)
async function buyurtmaYaratish(userId, manzilId, elementlar) {
  return withTransaction(async (client) => {
    // Mahsulotlar va qoldiqlarni tekshirish
    for (const el of elementlar) {
      const { rows } = await client.query(
        'SELECT narxi, qoldiq FROM mahsulotlar WHERE id = $1 AND faol = TRUE FOR UPDATE',
        [el.mahsulot_id]
      );
      if (!rows[0]) throw new Error(`Mahsulot ${el.mahsulot_id} topilmadi`);
      if (rows[0].qoldiq < el.soni) throw new Error(`Mahsulot ${el.mahsulot_id} qoldig'i yetarli emas`);
      el.narxi = rows[0].narxi;
    }

    // Jami hisoblash
    const jami = elementlar.reduce((s, el) => s + el.narxi * el.soni, 0);

    // Buyurtma yaratish
    const { rows: [buyurtma] } = await client.query(`
      INSERT INTO buyurtmalar (foydalanuvchi_id, manzil_id, jami_summa)
      VALUES ($1, $2, $3) RETURNING *
    `, [userId, manzilId, jami]);

    // Elementlar qo'shish va qoldiq kamaytirish
    for (const el of elementlar) {
      await client.query(`
        INSERT INTO buyurtma_elementlari (buyurtma_id, mahsulot_id, soni, narxi)
        VALUES ($1, $2, $3, $4)
      `, [buyurtma.id, el.mahsulot_id, el.soni, el.narxi]);

      await client.query(
        'UPDATE mahsulotlar SET qoldiq = qoldiq - $1 WHERE id = $2',
        [el.soni, el.mahsulot_id]
      );
    }

    return buyurtma;
  });
}

Trigger — yangilangan vaqtni avtomatik yangilash

CREATE OR REPLACE FUNCTION yangilangan_vaqt()
RETURNS TRIGGER AS $
BEGIN
  NEW.yangilangan = NOW();
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER tr_foydalanuvchilar_yangilangan
BEFORE UPDATE ON foydalanuvchilar
FOR EACH ROW EXECUTE FUNCTION yangilangan_vaqt();

CREATE TRIGGER tr_mahsulotlar_yangilangan
BEFORE UPDATE ON mahsulotlar
FOR EACH ROW EXECUTE FUNCTION yangilangan_vaqt();

Xulosa

Bu kursda qamrab olgan asosiy tushunchalar:

✅ DDL/DML         — CREATE, INSERT, SELECT, UPDATE, DELETE
✅ JOIN             — INNER, LEFT, RIGHT, FULL, SELF
✅ Ilg'or SQL       — Subquery, CTE, Window Functions
✅ JSONB, Array     — maxsus turlar
✅ Full-text Search — tsvector, pg_trgm
✅ Index            — B-tree, GIN, GiST, Composite, Partial
✅ Transaction      — ACID, Isolation Levels, Locking
✅ MVCC             — qanday ishlaydi
✅ Normalizatsiya   — 1NF, 2NF, 3NF
✅ Xavfsizlik       — GRANT, RLS, SQL Injection himoyasi
✅ Backup           — pg_dump, pg_restore
✅ Extensions       — PostGIS, pg_trgm, TimescaleDB
✅ Node.js          — pg, Pool, Transaction

PostgreSQL — kuchli, ishonchli, va kengaytiriladigan database. Bu kurs — asoslar. Amalda ishlash — eng yaxshi o'rgatuvchi.