Dasturlash kitobi/6-QISM — Database19 daqiqa

6.6-bob: PostgreSQL (chuqur), pg kutubxonasi, foreign keys

6-QISM — Ma'lumotlar bazasi (Database) · 6-mavzu

1. Kirish va motivatsiya

MySQL'ni 6.5-bob ko'rdik. Endi eng kuchli, zamonaviy, ochiq kodli relatsion DB — PostgreSQL ni chuqur o'rganamiz. PostgreSQL (qisqacha "Postgres") — bugungi kunda zamonaviy backend uchun eng ko'p tavsiya etiladigan SQL DB. Sababi: u nafaqat klassik relatsion imkoniyatlarni (jadval, JOIN, ACID), balki ilg'or xususiyatlarni ham beradi — JSONB (SQL ichida NoSQL!), window functions 6.7-bob, CTE, kuchli indeks turlari, kengaytmalar (PostGIS — geografiya, pgvector — AI). Sizning stack'ingda u bor; Prisma/TypeORM 6.14-bob bilan ham ajoyib ishlaydi.

PostgreSQL — "standartga eng yaqin", "eng to'g'ri" SQL DB hisoblanadi. U yaxlitlikni jiddiy oladi (ACID — 6.1: 2.11), murakkab so'rovlarni a'lo bajaradi, va kengaytiriladigan. Ko'p yetuk loyiha (Instagram, ko'p SaaS) PostgreSQL'da. Zamonaviy o'zbek startaplari ham tobora ko'proq PostgreSQL tanlaydi.

Node.js'da PostgreSQL bilan ishlash uchun pg (node-postgres) kutubxonasi — standart. MySQL 6.5-bob bilan o'xshash (pool, parametrli so'rov), lekin ba'zi farqlar: parametr $1 (? emas), RETURNING (insertId o'rniga). Bu bobda: pg, pool, parametrli so'rov, foreign keys (bog'lanishlar — chuqur), JSONB, va PostgreSQL'ning kuchli tomonlarini o'rganamiz.

O'xshatish: MySQL — ishonchli oddiy avtomobil (keng tarqalgan, oson, ko'pchilik haydaydi). PostgreSQL — kuchli, ko'p funksiyali avtomobil (klassik haydash + ilg'or tizimlar: JSONB, window functions, kengaytmalar). Ikkalasi ham yaxshi yuradi; lekin murakkab yo'l (katta loyiha, murakkab so'rov) uchun PostgreSQL ko'proq imkoniyat beradi.

Nega muhim?

Zamonaviy tanlov — yangi loyihalar uchun eng ko'p tavsiya (kuchli, standart).
JSONB — SQL'ning ishonchliligi + NoSQL'ning moslashuvchanligi bir DB'da.
Kuchli imkoniyatlar — window functions, CTE, kengaytmalar 6.7-bob.
Stack/ORM — Prisma/TypeORM PostgreSQL bilan a'lo 6.14-bob.

2. Nazariya — chuqur tushuntirish

2.1. PostgreSQL nima va nega kuchli

PostgreSQL — ilg'or, ochiq kodli relatsion DBMS 6.1-bob. Klassik SQL + ilg'or imkoniyatlar:

text

  Klassik (har SQL DB'da):  jadval, JOIN, ACID, indeks, constraint
  PostgreSQL qo'shimcha:
  - JSONB (SQL ichida hujjat — 2.10)
  - Window functions, CTE 6.7-bob
  - Boy turlar (array, range, UUID, ENUM, geometric)
  - Kengaytmalar (PostGIS, pgvector, ...)
  - Kuchli indeks turlari (GIN, GiST, BRIN)
  - To'liq ACID, MVCC (parallel — 6.11)

Nega "eng to'g'ri": PostgreSQL SQL standartiga eng yaqin, yaxlitlikni jiddiy oladi, va doimiy rivojlanadi. "Bir DB hammasini" yondashuvi — relatsion + JSONB (NoSQL) bir joyda (polyglot kamroq kerak — 6.1: 2.18).

2.2. pg (node-postgres) kutubxonasi

Node.js'da PostgreSQL standarti — pg (node-postgres.com):

bash

npm install pg              # (5.2)

import pg from "pg";
const { Pool } = pg;
const pool = new Pool({ connectionString: process.env.DATABASE_URL });   // (5.8)

pg — MySQL'ning mysql2 6.5-bob ekvivalenti. Pool (connection pool — 6.5: 2.5) — production standarti. Default port 5432 (MySQL 3306).

2.3. Ulanish (Pool va connectionString)

import pg from "pg";

// Variant 1: connectionString (URL — qulay)
const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,   // postgresql://user:parol@host:5432/db
});

// Variant 2: alohida sozlamalar
const pool2 = new pg.Pool({
  host: "localhost", port: 5432,
  user: process.env.DB_USER, password: process.env.DB_PASSWORD, database: process.env.DB_NAME,
  max: 10,                                        // pool hajmi (6.5: connectionLimit)
  idleTimeoutMillis: 30000,                       // bo'sh ulanishni yopish
});

connectionString — postgresql://user:parol@host:5432/dbnomi (URL formati — Atlas/cloud'da qulay). .envda (14). max — pool hajmi (6.5: 2.6).

2.4. So'rov va natija (rows)

pg natija — obyekt qaytaradi (rows ichida — MySQL'dan farq):

const natija = await pool.query("SELECT * FROM users");
console.log(natija.rows);          // qatorlar (massiv)
console.log(natija.rowCount);      // nechta qator

// Odatda to'g'ridan rows:
const { rows } = await pool.query("SELECT * FROM users");

{ rows } destructuring — pg natijasi { rows, rowCount, fields }. Faqat rows kerak const { rows } = .... MySQL'dan farq (MySQL: [rows] massiv — 6.5: 2.4; pg: { rows } obyekt).

2.5. Parametrli so'rov ($1, $2 — xavfsizlik — 14)

SQL injection (6.4: 2.15) himoyasi — $N parametr (MySQL'da ? — 6.5: 2.7):

//  XAVFLI — string birlashtirish (14)
await pool.query(`SELECT * FROM users WHERE email = '${email}'`);

//  XAVFSIZ — $1, $2 parametr (node-postgres)
const { rows } = await pool.query(
  "SELECT * FROM users WHERE email = $1 AND faol = $2",   // $1, $2 — joylar
  [email, true]                                            // qiymatlar (tartibda)
);

$1, $2, ... (raqamli) — MySQL ? (tartibsiz) dan farq. pg parametrlarni xavfsiz qiladi (manba — "what goes into the query is safe"). Eng muhim xavfsizlik (14). Tartib muhim: $1 massivning [0], $2 [1].

2.6. RETURNING — natijani qaytarish

PostgreSQL'da INSERT/UPDATE/DELETE qo'shilgan/o'zgargan qatorni qaytaradi (MySQL insertId'dan kuchliroq):

// INSERT + RETURNING (yangi qator — 6.4: 2.7)
const { rows } = await pool.query(
  "INSERT INTO users (ism, email) VALUES ($1, $2) RETURNING id, ism, created_at",
  [ism, email]
);
const yangiUser = rows[0];          // { id, ism, created_at }

// UPDATE + RETURNING
const { rows: yangilangan } = await pool.query(
  "UPDATE users SET ism = $1 WHERE id = $2 RETURNING *", [ism, id]
);

RETURNING — PostgreSQL afzalligi: qo'shish/yangilash bilan bir so'rovda natijani olasiz (MySQL'da insertId, keyin alohida SELECT kerak edi). RETURNING * — butun qator; RETURNING id — faqat kerakli.

2.7. Foreign Keys (bog'lanishlar — chuqur)

Foreign Key (6.1: 2.5, 6.4: 2.6) — relatsion DB'ning yuragi; PostgreSQL uni jiddiy boshqaradi:

sql

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,   -- FK
  summa DECIMAL(10,2)
);

text

  FK kafolati: orders.user_id  faqat MAVJUD users.id (yaxlitlik)

  ON DELETE xatti-harakatlari (user o'chsa, buyurtmalarga nima bo'ladi):
  CASCADE   — buyurtmalar ham o'chadi
  SET NULL  — user_id NULL bo'ladi (buyurtma qoladi)
  RESTRICT  — agar buyurtma bo'lsa, user'ni o'chirishga RUXSAT BERMAYDI
  NO ACTION — default (RESTRICT kabi)

ON DELETE tanlovi muhim: CASCADE (bog'liq ma'lumot ham o'chadi — buyurtmalar), SET NULL (mustaqil qoladi — masalan post o'chsa, komment "o'chirilgan post" bo'ladi), RESTRICT (himoya — bog'liq ma'lumot bo'lsa o'chirmaydi). Noto'g'ri tanlov — ma'lumot yo'qolishi yoki "yetim" yozuvlar.

2.8. ON UPDATE va deferred constraints

sql

user_id INTEGER REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE

ON UPDATE CASCADE — agar users.id o'zgarsa (kamdan-kam), orders.user_id ham yangilanadi. Deferred constraints — tranzaksiya oxirigacha tekshiruvni kechiktirish (murakkab holatlar — 6.11). Odatda default yetadi.

2.9. SERIAL va identity (avto-id)

sql

id SERIAL PRIMARY KEY              -- klassik (1, 2, 3...)
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY   -- zamonaviy (SQL standart)
id UUID DEFAULT gen_random_uuid() PRIMARY KEY          -- UUID (taqsimlangan — 6.1: 2.14)

SERIAL — avto-o'suvchi son (MySQL AUTO_INCREMENT). IDENTITY — zamonaviy, standart muqobil. UUID — global noyob (mikroservis, taqsimlangan — id'lar to'qnashmaydi; MongoDB ObjectId'ga o'xshash — 6.2: 2.2).

2.10. JSONB — SQL ichida NoSQL (PostgreSQL kuchi)

PostgreSQL'ning ajoyib xususiyati — JSONB (binary JSON): SQL jadvalida moslashuvchan JSON ma'lumot (dbschema):

sql

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  nom VARCHAR(100),
  narx DECIMAL(10,2),
  xususiyatlar JSONB                -- moslashuvchan (har mahsulot har xil — 6.1: 2.9)
);

INSERT INTO products (nom, xususiyatlar)
VALUES ('Telefon', '{"rang": "qora", "xotira": "128GB", "ekran": 6.5}');

-- JSONB so'rovlar
SELECT * FROM products WHERE xususiyatlar->>'rang' = 'qora';   -- matn sifatida (->>)
SELECT * FROM products WHERE xususiyatlar @> '{"xotira": "128GB"}';  -- ichida bormi (@>)
SELECT xususiyatlar->'ekran' FROM products;                    -- JSON sifatida (->)

JSONB — ikki dunyo birga: relatsion ustunlar (nom, narx — qat'iy) + JSONB (xususiyatlar — moslashuvchan). NoSQL'ning moslashuvchanligi + SQL'ning ishonchliligi bitta DB'da (6.1: 2.18 polyglot kamroq kerak). -> (JSON), ->> (matn), @> (ichida), GIN indeks (tez qidiruv — 2.11).

2.11. PostgreSQL indeks turlari

text

  B-tree (default) — oddiy qidiruv, taqqoslash, tartiblash (6.3: 2.8)
  GIN              — JSONB, massiv, to'liq matn qidiruv 2.10-bob
  GiST             — geometrik, range
  BRIN             — juda katta, tartiblangan ma'lumot (vaqt seriyasi)
  Hash             — faqat tenglik (=)

sql

CREATE INDEX idx_email ON users (email);                    -- B-tree
CREATE INDEX idx_xususiyat ON products USING GIN (xususiyatlar);   -- JSONB (2.10)
CREATE INDEX idx_aktiv ON users (email) WHERE faol = true;  -- partial (faqat faol)

GIN indeks — JSONB/massiv/matn qidiruvni tezlashtiradi 2.10-bob. Partial index — faqat ma'lum qatorlarga (faol foydalanuvchilar — kichik, tez). Bu — PostgreSQL'ning indeks boyligi (MySQL'da kamroq).

2.12. Tranzaksiya (pg bilan — 6.11 kirish)

const client = await pool.connect();              // pool'dan ulanish (6.5: getConnection)
try {
  await client.query("BEGIN");                    // tranzaksiya boshi
  await client.query("UPDATE accounts SET balans = balans - $1 WHERE id = $2", [100, 1]);
  await client.query("UPDATE accounts SET balans = balans + $1 WHERE id = $2", [100, 2]);
  await client.query("COMMIT");                   // saqla (atomik)
} catch (err) {
  await client.query("ROLLBACK");                 // bekor (6.1: 2.11)
  throw err;
} finally {
  client.release();                               // QAYTAR (majburiy — 6.5: 2.10)
}

MySQL (6.5: 2.9) bilan deyarli bir xil: pool.connect (getConnection), BEGIN/COMMIT/ROLLBACK, release (finally). PostgreSQL'da client.query("BEGIN") (mysql2'da beginTransaction()). 6.11-bobda chuqur (izolyatsiya, MVCC).

2.13. Boy ma'lumot turlari (PostgreSQL)

sql

teglar TEXT[]                      -- massiv (PostgreSQL native!)
manzil JSONB                        -- JSON (2.10)
holat order_status                  -- ENUM (maxsus tur)
narx_oraliq INT4RANGE              -- range (oraliq)
id UUID                             -- UUID (2.9)
joylashuv POINT                     -- geometrik (PostGIS bilan kuchli)

sql

-- Massiv so'rovlari
SELECT * FROM products WHERE 'vip' = ANY(teglar);   -- massivda bormi
INSERT INTO products (teglar) VALUES (ARRAY['yangi', 'chegirma']);

PostgreSQL — boy turlar: massiv (TEXT[]), ENUM, range, UUID, geometric. MySQL'da bularning ko'pi yo'q. Bu — murakkab ma'lumotni tabiiy modellashtirish imkonini beradi.

2.14. MySQL vs PostgreSQL (farqlar — amaliy)

text

  ┌──────────────┬──────────────┬─────────────────┐
  │              │ MySQL 6.5-bob  │ PostgreSQL      │
  ├──────────────┼──────────────┼─────────────────┤
  │ Driver       │ mysql2       │ pg              │
  │ Port         │ 3306         │ 5432            │
  │ Parametr     │ ?            │ $1, $2          │
  │ Natija       │ [rows]       │ { rows }        │
  │ Avto-id      │ AUTO_INCREMENT│ SERIAL/IDENTITY │
  │ Qaytarish    │ insertId     │ RETURNING       │
  │ JSON         │ JSON         │ JSONB (kuchli)  │
  │ Massiv       │ yo'q         │ TEXT[] (native) │
  └──────────────┴──────────────┴─────────────────┘

SQL 6.4-bob ikkalasida bir xil; farqlar — driver va ilg'or imkoniyatlar. PostgreSQL — kuchliroq (JSONB, massiv, window functions — 6.7). Yangi loyiha — ko'pincha PostgreSQL.

2.15. Xavfsizlik va best practices (14)

text

   Parametrli so'rov ($1 — injection — 2.5, 14) — ENG MUHIM
   Pool (6.5: 2.5); client.connect  release (finally — 2.12)
   Kalitlar .env'da (14, 5.8); SSL production'da (cloud)
   FK + ON DELETE to'g'ri (yaxlitlik — 2.7)
   JSONB'ni o'ylab ishlating (tez-filtrlanadigan — alohida ustun — 2.10)
   Indeks (GIN JSONB uchun — 2.11); RETURNING (qo'shimcha so'rovsiz — 2.6)
   DB foydalanuvchisiga eng kam imtiyoz (14)

3. Sintaksis — tez ma'lumotnoma

import pg from "pg";
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });   // (2.3)

// So'rov (parametrli $1 — 2.5)
const { rows } = await pool.query("SELECT * FROM users WHERE id = $1", [id]);
const { rows } = await pool.query(
  "INSERT INTO users (ism) VALUES ($1) RETURNING *", [ism]   // RETURNING (2.6)
);

// Tranzaksiya 2.12-bob: pool.connect  BEGIN  COMMIT/ROLLBACK  release (finally)

// JSONB 2.10-bob: xususiyatlar->>'rang', @> '{...}'
// FK 2.7-bob: REFERENCES users(id) ON DELETE CASCADE

4. Batafsil kod namunalari

Misol 1 — Pool sozlash (2.3)

// config/db.js
import pg from "pg";
import { config } from "./index.js";                       // (5.8)
import { logger } from "../utils/logger.js";

export const pool = new pg.Pool({
  connectionString: config.databaseUrl,                     // (2.3)
  max: 10,                                                   // pool hajmi (2.3)
  idleTimeoutMillis: 30000,
  ssl: config.isProd ? { rejectUnauthorized: false } : false,   // production SSL (14)
});

export async function dbTekshir() {
  try {
    const { rows } = await pool.query("SELECT NOW()");      // oddiy test so'rov
    logger.info(` PostgreSQL ulandi: ${rows[0].now}`);
  } catch (err) {
    logger.error(" PostgreSQL ulanmadi:", err.message);
    process.exit(1);                                        // (5.8: fail fast)
  }
}
pool.on("error", (err) => logger.error("Pool xato:", err)); // (5.10)

Misol 2 — Jadvallar (FK bilan — 2.7)

sql

-- users
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  ism VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  parol VARCHAR(255) NOT NULL,
  rol VARCHAR(20) DEFAULT 'user' CHECK (rol IN ('user', 'admin')),
  created_at TIMESTAMPTZ DEFAULT NOW()                       -- vaqt zonasi bilan
);

-- orders (FK — 2.7)
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,   -- FK (2.7)
  summa DECIMAL(10,2) NOT NULL CHECK (summa > 0),
  metadata JSONB,                                            -- moslashuvchan (2.10)
  holat VARCHAR(20) DEFAULT 'yangi',
  created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_orders_user ON orders (user_id);            -- FK indeks (2.11)

Misol 3 — CRUD service (parametrli + RETURNING — 2.5, 2.6)

import { pool } from "../config/db.js";

export const UserService = {
  async yarat({ ism, email, parol }) {
    const { rows } = await pool.query(
      "INSERT INTO users (ism, email, parol) VALUES ($1, $2, $3) RETURNING id, ism, email, created_at",
      [ism, email, parol]                                    // parametrli (2.5)
    );
    return rows[0];                                          // RETURNING (2.6)
  },

  async royxat({ limit = 20, offset = 0 }) {
    const { rows } = await pool.query(
      "SELECT id, ism, email FROM users ORDER BY id DESC LIMIT $1 OFFSET $2",   // sahifalash
      [limit, offset]
    );
    return rows;
  },

  async topId(id) {
    const { rows } = await pool.query("SELECT id, ism, email FROM users WHERE id = $1", [id]);
    return rows[0] || null;
  },

  async yangila(id, { ism }) {
    const { rows } = await pool.query(
      "UPDATE users SET ism = $1 WHERE id = $2 RETURNING *", [ism, id]   // RETURNING (2.6)
    );
    return rows[0] || null;                                  // yo'q bo'lsa null
  },

  async ochir(id) {
    const { rowCount } = await pool.query("DELETE FROM users WHERE id = $1", [id]);
    return rowCount > 0;                                     // o'chirildimi (2.4)
  },
};

Misol 4 — Express controller (5.6, 5.10)

import { UserService } from "../services/userService.js";

export const userYarat = async (req, res, next) => {
  try {
    const user = await UserService.yarat(req.body);          // (validatsiya — 5.9)
    res.status(201).json({ success: true, data: user });     // (5.7)
  } catch (err) {
    if (err.code === "23505") {                              // unique_violation (2.7)
      return res.status(409).json({ error: "Email band" });  // (5.10)
    }
    if (err.code === "23503") {                              // foreign_key_violation
      return res.status(400).json({ error: "Bog'liq ma'lumot topilmadi" });
    }
    next(err);
  }
};
// PostgreSQL xato kodlari: 23505 (unique), 23503 (FK), 23502 (not null), 23514 (check)

Misol 5 — Tranzaksiya (2.12)

// Buyurtma yaratish — atomik (mahsulot zaxirasini kamaytirib — 2.12)
export async function buyurtmaYarat(userId, mahsulotId, miqdor) {
  const client = await pool.connect();                       // ulanish (2.12)
  try {
    await client.query("BEGIN");

    // 1. Zaxira yetarli ekanini tekshiring va kamaytiring (atomik)
    const { rows } = await client.query(
      "UPDATE products SET zaxira = zaxira - $1 WHERE id = $2 AND zaxira >= $1 RETURNING narx",
      [miqdor, mahsulotId]
    );
    if (rows.length === 0) throw new Error("Zaxira yetarli emas");   // yetmadi  rollback

    // 2. Buyurtma yarat
    const summa = rows[0].narx * miqdor;
    const { rows: ord } = await client.query(
      "INSERT INTO orders (user_id, summa) VALUES ($1, $2) RETURNING id",
      [userId, summa]
    );

    await client.query("COMMIT");                            // saqla
    return ord[0].id;
  } catch (err) {
    await client.query("ROLLBACK");                          // bekor (atomik — 2.12)
    throw err;
  } finally {
    client.release();                                        // QAYTAR (6.5: 2.10)
  }
}

Misol 6 — JSONB bilan ishlash (2.10)

// JSONB qo'shish
await pool.query(
  "INSERT INTO products (nom, xususiyatlar) VALUES ($1, $2)",
  ["Telefon", { rang: "qora", xotira: "128GB", ekran: 6.5 }]   // obyekt — JSONB (2.10)
);

// JSONB bo'yicha qidiruv
const { rows } = await pool.query(
  "SELECT * FROM products WHERE xususiyatlar->>'rang' = $1",   // matn ('rang' — 2.10)
  ["qora"]
);

// JSONB ichida bormi (@> operator)
const { rows: r2 } = await pool.query(
  "SELECT * FROM products WHERE xususiyatlar @> $1",
  [{ xotira: "128GB" }]                                        // shu xususiyat borlari
);

// JSONB yangilash (maydon)
await pool.query(
  "UPDATE products SET xususiyatlar = jsonb_set(xususiyatlar, '{rang}', $1) WHERE id = $2",
  ['"oq"', id]
);

Misol 7 — Massiv va ENUM (boy turlar — 2.13)

sql

-- ENUM tur
CREATE TYPE order_status AS ENUM ('yangi', 'tasdiqlandi', 'yetkazildi', 'bekor');

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  nom VARCHAR(100),
  teglar TEXT[],                                             -- massiv (2.13)
  holat order_status DEFAULT 'yangi'                         -- ENUM
);

// Massiv bilan (2.13)
await pool.query("INSERT INTO products (nom, teglar) VALUES ($1, $2)",
  ["Telefon", ["yangi", "chegirma", "vip"]]);                // JS massiv  TEXT[]

// Massivda qidiruv (ANY)
const { rows } = await pool.query("SELECT * FROM products WHERE $1 = ANY(teglar)", ["vip"]);

Misol 8 — FK xatti-harakatlari namunasi (2.7)

sql

-- CASCADE: user o'chsa, buyurtmalari ham (2.7)
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE

-- SET NULL: post o'chsa, komment qoladi ("o'chirilgan post")
post_id INTEGER REFERENCES posts(id) ON DELETE SET NULL

-- RESTRICT: kategoriyada mahsulot bo'lsa, kategoriyani o'chirmaydi (himoya)
kategoriya_id INTEGER REFERENCES categories(id) ON DELETE RESTRICT

Har bog'lanish uchun to'g'ri xatti-harakat tanlang 2.7-bob: bog'liq ma'lumot ham o'chsinmi (CASCADE), mustaqil qolsinmi (SET NULL), yoki himoyalanssinmi (RESTRICT).

5. To'g'ri va noto'g'ri holatlar

1) String birlashtirish (injection)

//  injection (14, 2.5)
await pool.query(`SELECT * FROM users WHERE email = '${email}'`);

//  $1 parametr
await pool.query("SELECT * FROM users WHERE email = $1", [email]);

2) client.connect'dan keyin release unutish

//  pool tugaydi (6.5: 2.10)
const client = await pool.connect(); await client.query(...);

//  finally release
try { ... } finally { client.release(); }

3) RETURNING'siz qo'shib, alohida SELECT

//  ikki so'rov (sekin — 2.6)
await pool.query("INSERT ..."); const r = await pool.query("SELECT ... ORDER BY id DESC LIMIT 1");

//  RETURNING (bir so'rov)
const { rows } = await pool.query("INSERT ... RETURNING *", [...]);

4) JSONB'ni hamma narsaga ishlatish

text

 tez-filtrlanadigan maydonni ham JSONB ichiga (sekin — 2.10)
 tez-filtrlanadigan — alohida ustun; moslashuvchan — JSONB

5) FK ON DELETE'siz

sql

--  user o'chsa, "yetim" buyurtmalar qoladi (2.7)
user_id INTEGER REFERENCES users(id)

--  to'g'ri xatti-harakat
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE

6. Keng tarqalgan xatolar va yechimlari

Xato 1 — ECONNREFUSED 5432

Sababi: PostgreSQL ishlamayapti yoki URL noto'g'ri 2.3-bob. Yechimi: Postgres ishga tushiring (Docker/lokal); DATABASE_URL tekshiring.

Xato 2 — error: duplicate key value (23505)

Sababi: UNIQUE buzildi 2.7-bob. Yechimi: 409 qaytaring (Misol 4); ON CONFLICT (upsert).

Xato 3 — violates foreign key constraint (23503)

Sababi: yo'q FK qiymati, yoki bog'liq qator o'chirish 2.7-bob. Yechimi: FK mavjudligini tekshiring; ON DELETE; 400 qaytaring.

Xato 4 — column "x" does not exist

Sababi: noto'g'ri ustun nomi, yoki tirnoq muammosi (PostgreSQL katta-kichik harfga sezgir — "User" ≠ user). Yechimi: ustun nomini tekshiring; kichik harf ishlating.

Xato 5 — bind message supplies N parameters (parametr mos emas)

Sababi: $1, $2 soni massiv elementlariga mos emas 2.5-bob. Yechimi: parametrlar va massiv mosligini tekshiring.

Xato 6 — JSONB so'rov ishlamaydi

Sababi: -> (JSON) va ->> (matn) aralashtirildi 2.10-bob. Yechimi: matn taqqoslashda ->>; JSON sifatida ->.

7. Integratsiya — bu mavzu stack'ning qayerida uchraydi

SQL asoslari 6.4-bob: SELECT/INSERT/... — bu yerda PostgreSQL bilan.
MySQL 6.5-bob: taqqoslash (driver, parametr, RETURNING).
Express 5.6-bob: pool controller'da.
Error handling 5.10-bob: PostgreSQL xato kodlari (23505 409).
JOIN/window/CTE 6.7-bob: PostgreSQL kuchli.
Tranzaksiya 6.11-bob: ACID, MVCC.
JSONB: NoSQL 6.2-bob o'rni — moslashuvchanlik.
ORM 6.14-bob: Prisma/TypeORM PostgreSQL bilan a'lo.
Xavfsizlik (14): injection, FK yaxlitlik.
DevOps 10.6-bob: AWS RDS, cloud Postgres.

8. Eng yaxshi amaliyotlar (best practices)

Parametrli so'rov ($1 — injection — 2.5, 14) — ENG MUHIM.
Pool (6.5: 2.5); client.connect release (finally) 2.12-bob.
RETURNING (qo'shimcha so'rovsiz natija — 2.6).
FK + to'g'ri ON DELETE (CASCADE/SET NULL/RESTRICT — yaxlitlik — 2.7).
JSONB o'ylab (tez-filtrlanadigan — alohida ustun; moslashuvchan — JSONB — 2.10).
GIN indeks JSONB/massiv uchun 2.11-bob; FK'ga indeks.
Boy turlardan foydalaning (massiv, ENUM, UUID — kerakli joyda — 2.13).
Xato kodlari bo'yicha boshqar (23505 409 — Misol 4, 5.10).
Kalitlar .env'da, SSL production'da (14, Misol 1).
TIMESTAMPTZ (vaqt zonasi bilan — Misol 2).

9. Amaliy loyiha: "PostgreSQL Backend (pg + JSONB)"

PostgreSQL'ni professional darajada mustahkamlash.

Maqsad

pg, connection pool, parametrli so'rov, RETURNING, foreign keys va JSONB bilan to'liq, kuchli backend qurish.

Talablar (requirements)

Pool: db.js — connectionString, SSL, tekshiruv (Misol 1, 2.3).
Jadvallar: users + orders + products — FK (turli ON DELETE), JSONB, indeks (Misol 2, 7, 2.7, 2.10).
CRUD service: parametrli ($1), RETURNING, rowCount (Misol 3, 2.5, 2.6).
Express controller: xato kodlari (23505409, 23503400 — Misol 4).
Tranzaksiya: buyurtma + zaxira (atomik — Misol 5, 2.12).
JSONB: moslashuvchan xususiyatlar; ->>, @> qidiruv; GIN indeks (Misol 6, 2.10, 2.11).
Boy turlar: massiv (teglar), ENUM (holat — Misol 7, 2.13).
FK xatti-harakat: CASCADE/SET NULL/RESTRICT to'g'ri tanlangan (Misol 8, 2.7).
Xavfsizlik: parametrli; .env; release 2.15-bob.

Maslahatlar (hint)

pg parametr: $1, $2 (MySQL ?dan farq — 2.5).
Natija: { rows } (MySQL [rows]dan farq — 2.4).
RETURNING: INSERT ... RETURNING * (2.6, 3-xato).
Tranzaksiya: pool.connect BEGIN finally release (2.12, 2-xato).
JSONB: ->> matn, -> JSON, @> ichida (2.10, 6-xato).
Xato kodlari: 23505 (unique), 23503 (FK) (Misol 4).

"Tayyor" mezonlari (acceptance criteria)

Pool ulanadi (tekshiruv, SSL).
Jadvallar (FK, JSONB, indeks).
CRUD ($1 parametr, RETURNING).
Express xato kodlari (409/400).
Tranzaksiya (commit/rollback/release).
JSONB qidiruv (->>, @>) + GIN indeks.
Massiv/ENUM ishlatilgan.
FK ON DELETE to'g'ri tanlangan.
Injection himoyasi.

Yechim kodi ataylab berilmagan — bu loyihani o'zingiz yozib ko'ring.

10. Xulosa va keyingi bobga ko'prik

Bu bobda eng kuchli SQL DB — PostgreSQL ni chuqur o'rgandik:

PostgreSQL (ilg'or, standart, kengaytiriladigan — 2.1); pg driver, Pool (2.2, 2.3); natija { rows } 2.4-bob.
Parametrli so'rov $1 (injection — 2.5, 14); RETURNING (natija bir so'rovda — 2.6).
Foreign Keys (chuqur — ON DELETE CASCADE/SET NULL/RESTRICT — yaxlitlik — 2.7, 2.8); SERIAL/UUID 2.9-bob.
JSONB (SQL ichida NoSQL — moslashuvchan — 2.10); indeks turlari (GIN — 2.11); boy turlar (massiv/ENUM — 2.13); tranzaksiya 2.12-bob.

Keyingi bob — 6.7-bob: SQL chuqur — JOIN turlari, subquery, CTE, window functions. Asosiy SQL va ikki DB'ni (MySQL, PostgreSQL) bildik; endi SQL'ning eng kuchli, professional qismiga o'tamiz: JOIN (jadvallarni birlashtirish — INNER/LEFT/RIGHT/FULL), subquery (so'rov ichida so'rov), CTE (WITH), va window functions (reyting, yugurib boruvchi yig'indi). Bu — SQL'ni "o'rtacha"dan "ekspert"ga ko'taradi.

Foydalanilgan rasmiy/ishonchli manbalar

node-postgres.com — pg (Pool, parameterized queries, RETURNING)
DbSchema — PostgreSQL JSONB (operatorlar, GIN indeks); Frontend Masters — foreign keys
Elysiate — PostgreSQL JSONB performance; PostgreSQL docs — data types, constraints

Izohlar (0)

Izoh yozish uchun kiring.

Hozircha izoh yo'q. Birinchi bo'ling!