Dasturlash kitobi/6-QISM — Database18 daqiqa

6.5-bob: MySQL (Express bilan)

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

1. Kirish va motivatsiya

6.4-bobda SQL tilini o'rgandik (CREATE, SELECT, INSERT...). Endi uni amalda — eng mashhur relatsion DB'lardan biri MySQL ni Node.js/Express 5.6-bob bilan ulashni o'rganamiz. MySQL — dunyodagi eng keng tarqalgan ochiq kodli SQL DB (WordPress, ko'p veb-sayt, korxona tizimlari). Sizning stack'ingda u bor; o'zbek bozorida ham keng ishlatiladi (ayniqsa PHP/Laravel merosidan kelgan loyihalarda, lekin Node.js bilan ham).

Backend (5-QISM) ma'lumotni DB'da saqlaydi. MongoDB 6.2-bob bilan ko'rdik; endi SQL tomoni — MySQL. Asosiy g'oya bir xil: Node.js ilova MySQL serveriga ulanadi, so'rov (SQL — 6.4) yuboradi, natija oladi. Lekin SQL DB'da ikki muhim yangi tushuncha bor: connection pool (ulanishlarni qayta ishlatish — tezlik) va prepared statement (parametrli, xavfsiz so'rov — SQL injection himoyasi — 6.4: 2.15).

Node.js'da MySQL bilan ishlashning zamonaviy standarti — mysql2 kutubxonasi (eski mysqlning yaxshilangani — Promise, prepared statement, tez). Bu bobda: o'rnatish, ulanish, connection pool, CRUD so'rovlar, parametrli so'rov, tranzaksiya, va Express API bilan to'liq integratsiya — chuqur o'rganamiz.

O'xshatish (connection pool): DB'ga ulanish — telefon qo'ng'irog'i. Har so'rovda yangi ulanish — har gap uchun raqam terib, ulanib, gaplashib, uzish (sekin, isrof). Connection pool — bir nechta ochiq liniya (band qilingan telefonlar) saqlash: so'rov kelganda bo'sh liniyani olasiz, ishlatasiz, qaytarasiz (qayta ulanmasdan). Tez, samarali. Bu — production'da hayotiy.

Nega muhim?

Keng tarqalgan — MySQL dunyodagi eng mashhur SQL DB; ko'p ish o'rni.
SQL amaliyoti — 6.4'dagi nazariyani Node.js bilan amalda.
Connection pool — production'da tezlik va barqarorlik kaliti.
Prepared statement — SQL injection himoyasi (14).

2. Nazariya — chuqur tushuntirish

2.1. MySQL nima va arxitekturasi

MySQL — relatsion DBMS (6.1: 2.1). U server sifatida ishlaydi; ilovalar unga ulanib so'rov yuboradi:

text

  Node.js ilova ──(TCP, port 3306)──▶ MySQL server ──▶ disk (ma'lumot)
       │                                    │
       └── SQL so'rov yuboradi              └── jadval/qator 6.4-bob

  MySQL server alohida jarayon (yoki alohida mashina/Docker — 10.3)

MySQL — alohida server (default port 3306). Lokal (o'rnatasiz), Docker 10.3-bob, yoki cloud (AWS RDS — 10.6, PlanetScale). Node.js — klient (driver orqali ulanadi).

2.2. mysql2 kutubxonasi (driver)

Node.js'da MySQL bilan ishlash uchun mysql2 (zamonaviy, tez, Promise — sidorares/mysql2):

bash

npm install mysql2          # (5.2)

text

  mysql2 vs mysql (eski):
  - mysql2 — tezroq, prepared statement, Promise API 2.4-bob
  - mysql — eski, callback (ishlatma)

  Import:
  import mysql from "mysql2/promise";   // Promise versiyasi (async/await — 2.11-JS)

mysql2/promise import qil (mysql2 emas): default mysql2 — callback (eski uslub). /promise — async/await bilan ishlaydi (zamonaviy — 2.11-JS, 5.1). Hamma misollar shu bilan.

2.3. Ulanish (connection)

Eng oddiy — bitta ulanish (lekin production'da pool — 2.5):

import mysql from "mysql2/promise";

const connection = await mysql.createConnection({
  host: "localhost",
  port: 3306,
  user: process.env.DB_USER,        // .env (5.8, 14)
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,    // qaysi baza
});

const [rows] = await connection.query("SELECT * FROM users");   // so'rov
await connection.end();             // ulanishni yopish

createConnection — bitta ulanish (oddiy skript uchun). Lekin har so'rovda ulanib-uzish — sekin 2.5-bob. Production'da pool 2.5-bob. Kalitlar .env'da (14).

2.4. So'rov natijasi (destructuring)

mysql2 natija — massiv qaytaradi [rows, fields]:

const [rows, fields] = await connection.query("SELECT * FROM users");
// rows — qatorlar (ma'lumot); fields — ustun metama'lumoti (kamdan-kam kerak)

const [rows] = await connection.query("SELECT * FROM users");   // odatda faqat rows

Destructuring [rows] — mysql2 har doim [natija, fields] qaytaradi. Faqat rows kerak const [rows] = .... Buni unutsangiz, rows — butun massiv (xato). MongoDB'dan (6.2 — to'g'ridan natija) farqi.

2.5. Connection Pool (ulanishlar havzasi) — muhim

Connection pool — oldindan ochilgan ulanishlar to'plami (qayta ishlatiladi — oneuptime):

import mysql from "mysql2/promise";

const pool = mysql.createPool({
  host: "localhost",
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  connectionLimit: 10,              // bir vaqtda max 10 ulanish (2.6)
  waitForConnections: true,         // bo'sh ulanish kutilsin (xato bermasin)
  enableKeepAlive: true,            // ulanish "tirik" tursin (eskirmasin)
});

const [rows] = await pool.query("SELECT * FROM users");   // pool o'zi ulanish oladi/qaytaradi

Nega pool (2.1: o'xshatish): har so'rovda yangi ulanish — sekin (ulanish o'rnatish — qimmat). Pool — ulanishlarni qayta ishlatadi (bo'sh ulanishni beradi, ishlatilgach qaytaradi). Production'da majburiy (tezlik, barqarorlik). pool.query — ulanishni avtomatik oladi/qaytaradi.

2.6. Pool sozlamalari (connectionLimit va boshqalar)

text

  connectionLimit  — max bir vaqtdagi ulanish (MySQL max_connections'ning 10-20% — manba)
  queueLimit       — navbatdagi kutuvchi so'rovlar (0 — cheksiz)
  waitForConnections — bo'sh ulanish kutilsinmi (true) yoki xato (false)
  enableKeepAlive  — eskirgan ulanishni oldini olish (true)
  idleTimeout      — bo'sh ulanishni yopish vaqti

connectionLimit tanlovi: juda kam — so'rovlar navbatda kutadi (sekin); juda ko'p — MySQL serverni bosadi. MySQL max_connectionsning 10-20% — yaxshi boshlang'ich (manba). Bir nechta Node instansiya bo'lsa — hisobga oling.

2.7. Prepared statement (parametrli — xavfsizlik — 14)

SQL injection (6.4: 2.15) himoyasi — prepared statement (? parametr):

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

//  XAVFSIZ — execute (prepared statement, ? parametr)
const [rows] = await pool.execute(
  "SELECT * FROM users WHERE email = ?",   // ? — joy
  [email]                                   // qiymat alohida (SQL emas — 6.4: 2.15)
);

execute (? bilan) ishlating (manba): pool.execute — server-side prepared statement (parametrlar SQL'dan ajratiladi — injection imkonsiz, va tezroq). query ham parametr qabul qiladi, lekin execute — afzal (prepared). Bu — eng muhim xavfsizlik amaliyoti (14).

2.8. CRUD so'rovlar (mysql2 bilan)

// CREATE — INSERT (6.4: 2.7)
const [natija] = await pool.execute(
  "INSERT INTO users (ism, email) VALUES (?, ?)", [ism, email]
);
const yangiId = natija.insertId;        // qo'shilgan qatorning id'si

// READ — SELECT (6.4: 2.8)
const [users] = await pool.execute("SELECT * FROM users WHERE faol = ?", [true]);

// UPDATE (6.4: 2.11)
const [r] = await pool.execute("UPDATE users SET ism = ? WHERE id = ?", [ism, id]);
console.log(r.affectedRows);            // nechta qator o'zgardi

// DELETE (6.4: 2.12)
await pool.execute("DELETE FROM users WHERE id = ?", [id]);

Natija metama'lumoti: INSERT insertId (yangi id); UPDATE/DELETE affectedRows (nechta qator). Bularni tekshirib, javob berasiz (masalan affectedRows === 0 topilmadi — 404).

2.9. Tranzaksiya (ACID — 6.1: 2.11)

Bir necha so'rovni atomik bajarish (hammasi yoki hech narsa — pul o'tkazish):

const conn = await pool.getConnection();   // pool'dan alohida ulanish
try {
  await conn.beginTransaction();           // tranzaksiya boshi
  await conn.execute("UPDATE accounts SET balans = balans - ? WHERE id = ?", [100, 1]);
  await conn.execute("UPDATE accounts SET balans = balans + ? WHERE id = ?", [100, 2]);
  await conn.commit();                     // ikkalasi muvaffaqiyatli  saqla
} catch (err) {
  await conn.rollback();                   // xato  ikkalasi BEKOR (atomik)
  throw err;
} finally {
  conn.release();                          // ulanishni pool'ga QAYTAR (muhim! — 2.10)
}

Tranzaksiyada getConnection (pool.execute emas): tranzaksiya bitta ulanishda bo'lishi kerak (begin/commit bir ulanishda). getConnection — alohida ulanish; oxirida release() (qaytarish) majburiy (finally'da — 2.10). 6.11-bobda chuqur (ACID, izolyatsiya).

2.10. Connection release (ulanishni qaytarish)

getConnection bilan olingan ulanish qaytarilishi shart (release):

text

  pool.query / pool.execute  ulanishni AVTOMATIK oladi/qaytaradi (qulay)
  pool.getConnection  SEN release() qilishing kerak (finally'da)

   release() unutilsa  pool ulanishlari tugaydi (ilova qotadi! — 2.6)
   finally { conn.release() } — har doim qaytariladi

Eng ko'p uchraydigan pool xatosi: getConnection qilib, release qilmaslik pool bo'shaydi yangi so'rovlar kutadi ilova qotadi. Doim finallyda release 2.9-bob.

2.11. Express bilan integratsiya

Pool'ni Express ilovasida ishlatish 5.6-bob:

// db.js — pool bir marta (5.8 config kabi)
export const pool = mysql.createPool({...});

// controller 5.6-bob — pool'ni ishlating
app.get("/api/users", async (req, res, next) => {
  try {
    const [users] = await pool.execute("SELECT id, ism, email FROM users");
    res.json({ success: true, data: users });   // (5.7)
  } catch (err) { next(err); }                   // (5.10)
});

Pool — bir marta yaratiladi (modul darajasida — 5.8), butun ilova ishlatadi. Har so'rovda yangi pool — xato. Express controller'da pool.execute (5.6, 5.10 — error handling bilan).

2.12. Pagination va dinamik so'rov (xavfsiz)

// Sahifalash (6.4: 2.10) — LIMIT/OFFSET parametr bilan
const [rows] = await pool.execute(
  "SELECT * FROM products WHERE narx > ? ORDER BY narx DESC LIMIT ? OFFSET ?",
  [minNarx, limit, (page - 1) * limit]
);

LIMIT/OFFSET parametr nuansi: ba'zi mysql2 versiyalarida execute'da LIMIT/OFFSET'ni ? bilan berishda muammo bo'ladi (string sifatida). Yechim: Number()ga aylantiring va pool.query (execute emas) ishlating, yoki validatsiya qilingan sonni to'g'ridan qo'ying (faqat validatsiyalangan son — 5.9, injection'siz).

2.13. Xato turlari (MySQL)

text

  ER_DUP_ENTRY        — UNIQUE buzildi (email takror — 6.4: 2.5)  409 5.10-bob
  ER_NO_REFERENCED_ROW — FK xatosi (yo'q user_id — 6.4: 2.6)  400
  ER_BAD_FIELD_ERROR  — noto'g'ri ustun nomi  kod xatosi
  ER_ACCESS_DENIED    — login/parol noto'g'ri  ulanish
  ECONNREFUSED        — MySQL ishlamayapti  server

2.14. MySQL vs PostgreSQL (qisqacha — 6.6 ko'prik)

text

  MySQL       — keng tarqalgan, oddiy, tez (o'qish); WordPress merosi
  PostgreSQL  — kuchliroq (JSONB, window functions, kengaytmalar — 6.6); standartga yaqin

  Ikkalasi ham a'lo; SQL deyarli bir xil 6.4-bob. PostgreSQL — zamonaviy
  loyihalarda ko'proq tavsiya (kuchli imkoniyatlar). MySQL — mavjud/oddiy.

Sintaksis kichik farqlar: MySQL ? parametr (PostgreSQL $1); MySQL AUTO_INCREMENT (PostgreSQL SERIAL); MySQL insertId (PostgreSQL RETURNING). SQL'ni bilsangiz, ikkalasiga oson o'tasiz.

2.15. Xavfsizlik va best practices (14)

text

   Prepared statement (execute, ? — injection — 2.7, 14) — ENG MUHIM
   Connection pool 2.5-bob; getConnection  release (finally — 2.10)
   Kalitlar .env'da (14, 5.8)
   DB foydalanuvchisiga eng kam imtiyoz (14)
   Kerakli ustunlarni tanlang (SELECT * emas — 6.4)
   Tranzaksiya muhim amalda (pul — 2.9)
   Xatolarni boshqar 5.10-bob; pool error event
   connectionLimit'ni server sig'imiga moslash (2.6)

3. Sintaksis — tez ma'lumotnoma

import mysql from "mysql2/promise";                        // Promise (2.2)

// Pool (bir marta — 2.5)
const pool = mysql.createPool({ host, user, password, database, connectionLimit: 10 });

// So'rov (parametrli — 2.7, 2.8)
const [rows] = await pool.execute("SELECT * FROM users WHERE id = ?", [id]);   // R
const [r] = await pool.execute("INSERT INTO users (ism) VALUES (?)", [ism]);   // C  r.insertId
await pool.execute("UPDATE users SET ism = ? WHERE id = ?", [ism, id]);        // U  affectedRows
await pool.execute("DELETE FROM users WHERE id = ?", [id]);                     // D

// Tranzaksiya 2.9-bob: getConnection  beginTransaction  commit/rollback  release (finally)

4. Batafsil kod namunalari

Misol 1 — Pool sozlash (db.js — 2.5)

// config/db.js — pool bir marta (2.5, 2.11)
import mysql from "mysql2/promise";
import { config } from "./index.js";                       // (5.8)
import { logger } from "../utils/logger.js";                // (5.12)

export const pool = mysql.createPool({
  host: config.db.host,
  port: config.db.port || 3306,
  user: config.db.user,
  password: config.db.password,
  database: config.db.name,
  connectionLimit: 10,                                       // (2.6)
  waitForConnections: true,
  enableKeepAlive: true,
});

// Ulanishni tekshirish (ilova boshida — 5.8: fail fast)
export async function dbTekshir() {
  try {
    const conn = await pool.getConnection();
    await conn.ping();                                       // ulanish ishlayaptimi
    conn.release();                                          // qaytaring (2.10)
    logger.info(" MySQL ulandi");
  } catch (err) {
    logger.error(" MySQL ulanmadi:", err.message);
    process.exit(1);
  }
}

// Pool xatolari (5.10)
pool.on("error", (err) => logger.error("Pool xato:", err));

// Toza to'xtatish (graceful shutdown — 5.12): pool'ni yopish
export async function dbYop() {
  await pool.end();                                          // hamma ulanishni yopadi
  logger.info("MySQL pool yopildi");
}
// process.on("SIGTERM", dbYop) — ilova to'xtaganda pool'ni toza yop (5.12)

Misol 2 — CRUD service (parametrli — 2.7, 2.8)

// services/userService.js — DB mantig'i (9: toza arxitektura)
import { pool } from "../config/db.js";

export const UserService = {
  async yarat({ ism, email, parol }) {
    const [r] = await pool.execute(
      "INSERT INTO users (ism, email, parol) VALUES (?, ?, ?)",   // parametrli (2.7)
      [ism, email, parol]
    );
    return { id: r.insertId, ism, email };                  // insertId (2.8)
  },

  async royxat({ limit = 20, offset = 0 }) {
    const [rows] = await pool.query(                         // query (LIMIT — 2.12)
      "SELECT id, ism, email FROM users WHERE deleted_at IS NULL ORDER BY id DESC LIMIT ? OFFSET ?",
      [Number(limit), Number(offset)]                        // son'ga aylantiring (2.12)
    );
    return rows;
  },

  async topId(id) {
    const [rows] = await pool.execute("SELECT id, ism, email FROM users WHERE id = ?", [id]);
    return rows[0] || null;                                  // bittasi yoki null
  },

  async yangila(id, { ism }) {
    const [r] = await pool.execute("UPDATE users SET ism = ? WHERE id = ?", [ism, id]);
    return r.affectedRows > 0;                               // o'zgardimi (2.8)
  },

  async ochir(id) {
    const [r] = await pool.execute("UPDATE users SET deleted_at = NOW() WHERE id = ?", [id]);  // soft (6.4)
    return r.affectedRows > 0;
  },
};

Misol 3 — Express controller (5.6, 5.10)

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

export const userlarOl = async (req, res, next) => {
  try {
    const { page = 1, limit = 20 } = req.query;             // (validatsiya — 5.9)
    const users = await UserService.royxat({
      limit: Number(limit), offset: (Number(page) - 1) * Number(limit),
    });
    res.json({ success: true, data: users });               // (5.7)
  } catch (err) { next(err); }                               // (5.10)
};

export const userYarat = async (req, res, next) => {
  try {
    const user = await UserService.yarat(req.body);
    res.status(201).json({ success: true, data: user });    // (5.7)
  } catch (err) {
    if (err.code === "ER_DUP_ENTRY") {                       // duplicate (2.13)
      return res.status(409).json({ error: "Email band" });
    }
    next(err);
  }
};

export const userTop = async (req, res, next) => {
  try {
    const user = await UserService.topId(req.params.id);
    if (!user) return res.status(404).json({ error: "Topilmadi" });   // (5.7)
    res.json({ success: true, data: user });
  } catch (err) { next(err); }
};

Misol 4 — Tranzaksiya (pul o'tkazish — 2.9)

// Pul o'tkazish — atomik (2.9, 6.1: 2.11)
export async function pulOtkaz(fromId, toId, summa) {
  const conn = await pool.getConnection();                  // alohida ulanish (2.9)
  try {
    await conn.beginTransaction();                          // boshlash

    // 1. Yechish (yetarli balans tekshiruvi bilan)
    const [r1] = await conn.execute(
      "UPDATE accounts SET balans = balans - ? WHERE id = ? AND balans >= ?",
      [summa, fromId, summa]
    );
    if (r1.affectedRows === 0) {                            // balans yetmadi
      throw new Error("Balans yetarli emas");
    }

    // 2. Qo'shish
    await conn.execute("UPDATE accounts SET balans = balans + ? WHERE id = ?", [summa, toId]);

    // 3. Log
    await conn.execute(
      "INSERT INTO transfers (from_id, to_id, summa) VALUES (?, ?, ?)",
      [fromId, toId, summa]
    );

    await conn.commit();                                    // hammasi  saqla (2.9)
    return true;
  } catch (err) {
    await conn.rollback();                                  // xato  BEKOR (atomik)
    throw err;                                              // (5.10)
  } finally {
    conn.release();                                         // QAYTAR (majburiy — 2.10)
  }
}

Misol 5 — Dinamik filtr (xavfsiz qurish — 2.7)

// Dinamik WHERE (foydalanuvchi filtri — parametrli — 2.7, 14)
export async function mahsulotQidir({ kategoriya, minNarx, qidiruv }) {
  const shartlar = [];
  const qiymatlar = [];

  if (kategoriya) { shartlar.push("kategoriya = ?"); qiymatlar.push(kategoriya); }
  if (minNarx) { shartlar.push("narx >= ?"); qiymatlar.push(Number(minNarx)); }
  if (qidiruv) { shartlar.push("nom LIKE ?"); qiymatlar.push(`%${qidiruv}%`); }   // LIKE (6.4)

  const where = shartlar.length ? `WHERE ${shartlar.join(" AND ")}` : "";
  const [rows] = await pool.execute(
    `SELECT * FROM products ${where} ORDER BY narx DESC`,
    qiymatlar                                               // hammasi parametrli (injection'siz — 14)
  );
  return rows;
}
//  Ustun NOMLARI kodda (qattiq); QIYMATLAR parametr (xavfsiz — 2.7)

Misol 6 — JOIN so'rovi (6.7 ga kirish — 2.8)

// Buyurtmalar + foydalanuvchi (JOIN — 6.7'da chuqur)
const [rows] = await pool.execute(`
  SELECT o.id, o.summa, o.holat, u.ism, u.email
  FROM orders o
  JOIN users u ON o.user_id = u.id
  WHERE o.holat = ?
  ORDER BY o.created_at DESC
`, ["tugallandi"]);
// Har qatorda buyurtma + foydalanuvchi ma'lumoti birga (relatsion kuch — 6.1: 2.5)

Misol 7 — To'liq jadval yaratish va seed (6.4)

// Jadval yaratish (migration — 6.16 ruhida)
export async function jadvallarYarat() {
  await pool.execute(`
    CREATE TABLE IF NOT EXISTS users (
      id INT AUTO_INCREMENT PRIMARY KEY,             -- MySQL: AUTO_INCREMENT (6.4: SERIAL)
      ism VARCHAR(50) NOT NULL,
      email VARCHAR(100) UNIQUE NOT NULL,
      parol VARCHAR(255) NOT NULL,
      deleted_at TIMESTAMP NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
  `);
  await pool.execute(`
    CREATE TABLE IF NOT EXISTS orders (
      id INT AUTO_INCREMENT PRIMARY KEY,
      user_id INT NOT NULL,
      summa DECIMAL(10,2) NOT NULL,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE   -- FK (6.4: 2.6)
    )
  `);
}

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

1) String birlashtirish (injection)

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

//  prepared statement
await pool.execute("SELECT * FROM users WHERE email = ?", [email]);

2) Har so'rovda yangi ulanish

//  sekin (ulanib-uzish — 2.5)
const conn = await mysql.createConnection(...); await conn.query(...); await conn.end();

//  pool (qayta ishlatish)
await pool.execute(...);

3) getConnection'dan keyin release unutish

//  pool tugaydi, ilova qotadi (2.10)
const conn = await pool.getConnection(); await conn.query(...);

//  finally'da release
try { ... } finally { conn.release(); }

4) [rows] destructuring unutish

//  rows — butun [natija, fields] massivi (2.4)
const rows = await pool.execute("SELECT ...");

//  destructuring
const [rows] = await pool.execute("SELECT ...");

5) Tranzaksiyani pool.execute bilan

//  begin/commit har xil ulanishda (ishlamaydi — 2.9)
await pool.execute("BEGIN"); await pool.execute(...);

//  getConnection (bitta ulanish)
const conn = await pool.getConnection(); await conn.beginTransaction(); ...

6. Keng tarqalgan xatolar va yechimlari

Xato 1 — ECONNREFUSED ::1:3306

Sababi: MySQL ishlamayapti yoki host/port noto'g'ri 2.1-bob. Yechimi: MySQL ishga tushiring (Docker/lokal); host/port/.env tekshiring.

Xato 2 — ER_ACCESS_DENIED_ERROR

Sababi: login/parol noto'g'ri 2.13-bob. Yechimi: DB_USER/DB_PASSWORD to'g'rimi; foydalanuvchi ruxsati.

Xato 3 — ER_DUP_ENTRY

Sababi: UNIQUE buzildi (email — 2.13). Yechimi: 409 qaytaring (Misol 3); oldindan tekshiring.

Xato 4 — Pool ulanishlari tugadi (ilova qotadi)

Sababi: release unutildi 2.10-bob. Yechimi: finally { conn.release() }; pool.query/execute ishlatib, getConnection'ni kamaytiring.

Xato 5 — Incorrect arguments to mysqld_stmt_execute (LIMIT)

Sababi: LIMIT/OFFSET execute'da string 2.12-bob. Yechimi: Number()ga aylantiring; pool.query ishlating (execute emas).

Xato 6 — Natija kutilganidek emas

Sababi: [rows] destructuring unutildi 2.4-bob. Yechimi: const [rows] = await....

7. Integratsiya — bu mavzu stack'ning qayerida uchraydi

SQL asoslari 6.4-bob: CREATE/SELECT/... — bu yerda amalda.
Express 5.6-bob: pool controller'da; CRUD endpoint.
Error handling 5.10-bob: MySQL xatolari (ER_DUP_ENTRY 409).
Env 5.8-bob: DB kalitlari.
Validatsiya 5.9-bob: kirish + parametrli so'rov.
Tranzaksiya 6.11-bob: ACID — chuqur.
JOIN 6.7-bob: ko'p jadval.
PostgreSQL 6.6-bob: taqqoslash.
ORM 6.14-bob: Sequelize MySQL bilan ham.
Xavfsizlik (14): SQL injection, secrets.

8. Eng yaxshi amaliyotlar (best practices)

mysql2/promise (async/await — 2.2); pool (bir marta — 2.5, 2.11).
execute + ? (prepared statement — injection — 2.7, 14) — ENG MUHIM.
getConnection release (finally) (pool tugamasin — 2.10).
[rows] destructuring 2.4-bob.
Tranzaksiya muhim amalda (pul — getConnection — 2.9).
Dinamik filtr — ustun kodda, qiymat parametr (xavfsiz — Misol 5).
Xatolarni kod bo'yicha boshqar (ER_DUP_ENTRY 409 — 2.13, 5.10).
Kalitlar .env'da (14); connectionLimit serverga moslash 2.6-bob.
insertId/affectedRows tekshiring (natija — 2.8).
SELECT kerakli ustunlar (maxfiy yashiring — 6.4).

9. Amaliy loyiha: "MySQL + Express CRUD API"

MySQL'ni Node.js bilan professional darajada mustahkamlash.

Maqsad

mysql2 va connection pool bilan to'liq, xavfsiz CRUD API qurish: parametrli so'rovlar, tranzaksiya va Express integratsiyasi.

Talablar (requirements)

Pool: db.js — bir marta; ulanish tekshiruvi; xato boshqaruvi (Misol 1, 2.5).
Jadvallar: users + orders (FK bilan — Misol 7, 6.4).
CRUD service: parametrli (execute, ?); insertId/affectedRows (Misol 2, 2.7, 2.8).
Express controller: CRUD endpoint; error handling (ER_DUP_ENTRY 409 — Misol 3).
Sahifalash: LIMIT/OFFSET (Number, query — Misol 2, 2.12).
Tranzaksiya: pul o'tkazish yoki buyurtma yaratish (getConnection commit/rollback release — Misol 4, 2.9).
Dinamik filtr: xavfsiz qurish (ustun kodda, qiymat parametr — Misol 5).
JOIN: buyurtma + user (Misol 6).
Xavfsizlik: prepared statement; .env; release 2.15-bob.

Maslahatlar (hint)

mysql2/promise import (2.2, 4-xato).
pool.execute("... ?", [val]) (2.7, 1-xato).
[rows] destructuring (2.4, 6-xato).
Tranzaksiya: getConnection + finally release (2.9, 3-xato).
LIMIT: Number + pool.query (2.12, 5-xato).
ER_DUP_ENTRY 409 2.13-bob.

"Tayyor" mezonlari (acceptance criteria)

Pool ulanadi (tekshiruv bilan).
Jadvallar (FK bilan) yaratiladi.
CRUD parametrli so'rovlar bilan ishlaydi.
Express endpoint'lar (error handling, 409).
Sahifalash ishlaydi.
Tranzaksiya (commit/rollback/release).
Dinamik filtr xavfsiz.
JOIN so'rovi.
Injection himoyasi (prepared statement).

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

10. Xulosa va keyingi bobga ko'prik

Bu bobda MySQL'ni Node.js/Express bilan amalda o'rgandik:

MySQL (relatsion server, port 3306 — 2.1); mysql2/promise driver 2.2-bob.
Ulanish 2.3-bob; natija [rows] 2.4-bob; connection pool (qayta ishlatish — production — 2.5, 2.6).
Prepared statement (execute + ? — injection himoyasi — 2.7, 14); CRUD (insertId/affectedRows — 2.8).
Tranzaksiya (getConnection commit/rollback release — 2.9, 2.10); Express integratsiya 2.11-bob; dinamik filtr (xavfsiz — Misol 5).

Keyingi bob — 6.6-bob: PostgreSQL (chuqur), pg kutubxonasi, foreign keys. MySQL'ni ko'rdik; endi eng kuchli, zamonaviy ochiq kodli SQL DB — PostgreSQL ni chuqur o'rganamiz: pg kutubxonasi, parametrli so'rov ($1), RETURNING, foreign keys, JSONB (SQL ichida NoSQL!), va PostgreSQL'ning kuchli imkoniyatlari.

Foydalanilgan rasmiy/ishonchli manbalar

sidorares.github.io/node-mysql2 — mysql2 (createPool, execute, prepared statements)
npmjs.com/package/mysql2 — mysql2/promise; OneUptime — MySQL connection pooling Node.js 2026
MySQL docs — connection pool, prepared statements, transactions

Izohlar (0)

Izoh yozish uchun kiring.

Hozircha izoh yo'q. Birinchi bo'ling!