Node.js — mysql2 moduli, CRUD, parametrli so'rovlar

~25 daq · MySQL · MySQL va Dasturlash Tillari

Muammodan boshlaylik

SQL ni terminalda yozishni o'rgandingiz. Endi uni ilova ichidan ishlatish kerak: Express API mijozlar ro'yxatini qaytarsin, yangi buyurtma qabul qilsin. Node.js dan MySQL ga qanday ulaniladi, so'rov qanday yuboriladi, natija qanday olinadi?

Node.js uchun eng ko'p ishlatiladigan MySQL drayveri — mysql2: tez, Promise / async-await ni qo'llab-quvvatlaydi, haqiqiy prepared statements bor.

O'rnatish

mkdir dokon-api && cd dokon-api
npm init -y
npm install mysql2 express dotenv

package.json ga "type": "module" qo'shing — import sintaksisi uchun.

.env (Git ga qo'shilmaydi):

DB_HOST=localhost
DB_PORT=3306
DB_USER=dokon_app
DB_PASSWORD=Kuchli_Parol_2026!
DB_NAME=dokon

Ulanish — pool bilan

db.js:

import mysql from 'mysql2/promise';
import 'dotenv/config';

export const pool = mysql.createPool({
  host: process.env.DB_HOST,
  port: Number(process.env.DB_PORT),
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  connectionLimit: 10,
  charset: 'utf8mb4',
  timezone: 'Z',             // sanalarni UTC sifatida
  decimalNumbers: true,      // DECIMAL → JS number (string emas)
});

Har so'rovda yangi ulanish emas — pool (54-dars). mysql2/promise — async/await bilan ishlash uchun.

Ulanishni tekshirish:

const [rows] = await pool.query('SELECT VERSION() AS v');
console.log('MySQL', rows[0].v);

Natija tuzilishi

const [rows, fields] = await pool.query('SELECT id, nom, narx FROM mahsulotlar LIMIT 2');

console.log(rows);
// [
//   { id: 1, nom: 'iPhone 15', narx: 12500000 },
//   { id: 2, nom: 'Samsung Galaxy A55', narx: 4800000 }
// ]

So'rov massiv qaytaradi: [natija, ustunlar_haqida_ma'lumot]. Odatda faqat birinchisi kerak — shuning uchun const [rows] = ... destructuring.

SELECT uchun rows — ob'ektlar massivi. INSERT/UPDATE/DELETE uchun esa — natija ob'ekti:

const [result] = await pool.query("UPDATE mahsulotlar SET soni = soni + 1 WHERE id = 1");
console.log(result.affectedRows);   // 1
console.log(result.changedRows);    // 1

Parametrli so'rovlar — majburiy

// ❌ SQL INJECTION (51-dars)
const [rows] = await pool.query(`SELECT * FROM mijozlar WHERE email = '${email}'`);

// ✅ Placeholder
const [rows] = await pool.execute('SELECT * FROM mijozlar WHERE email = ?', [email]);

execute va query farqi

Odatda — execute. IN ro'yxati yoki dinamik qismlar kerak bo'lsa — query.

const ids = [1, 3, 5];
const [rows] = await pool.query('SELECT id, nom FROM mahsulotlar WHERE id IN (?)', [ids]);
// SELECT ... WHERE id IN (1, 3, 5)

CRUD — to'liq misol

mahsulotlar.js:

import { pool } from './db.js';

// READ — ro'yxat, filtr va sahifalash bilan
export async function royxat({ toifaId, sahifa = 1, hajm = 20 }) {
  const shartlar = [];
  const params = [];

  if (toifaId) {
    shartlar.push('toifa_id = ?');
    params.push(toifaId);
  }

  const where = shartlar.length ? `WHERE ${shartlar.join(' AND ')}` : '';
  const offset = (sahifa - 1) * hajm;

  const [rows] = await pool.query(
    `SELECT id, nom, narx, soni FROM mahsulotlar ${where} ORDER BY id LIMIT ? OFFSET ?`,
    [...params, hajm, offset]
  );
  return rows;
}

// READ — bitta
export async function top(id) {
  const [rows] = await pool.execute(
    'SELECT id, toifa_id, nom, narx, soni FROM mahsulotlar WHERE id = ?',
    [id]
  );
  return rows[0] ?? null;
}

// CREATE
export async function yarat({ toifaId, nom, narx, soni = 0 }) {
  const [result] = await pool.execute(
    'INSERT INTO mahsulotlar (toifa_id, nom, narx, soni) VALUES (?, ?, ?, ?)',
    [toifaId ?? null, nom, narx, soni]
  );
  return result.insertId;
}

// UPDATE
export async function yangila(id, { nom, narx }) {
  const [result] = await pool.execute(
    'UPDATE mahsulotlar SET nom = ?, narx = ? WHERE id = ?',
    [nom, narx, id]
  );
  return result.affectedRows > 0;
}

// DELETE
export async function ochir(id) {
  const [result] = await pool.execute('DELETE FROM mahsulotlar WHERE id = ?', [id]);
  return result.affectedRows > 0;
}

E'tibor bering: WHERE dinamik yasalyapti, lekin SQL ga faqat bizning matnimiz ('toifa_id = ?') qo'shiladi. Foydalanuvchi qiymatlari — faqat params orqali.

[!WARNING] execute bilan undefined qiymat berib bo'lmaydi: Bind parameters must not contain undefined. Ixtiyoriy maydonlarni ?? null bilan null ga aylantiring.

Express API

server.js:

import express from 'express';
import * as Mahsulot from './mahsulotlar.js';

const app = express();
app.use(express.json());

app.get('/api/mahsulotlar', async (req, res, next) => {
  try {
    const rows = await Mahsulot.royxat({
      toifaId: req.query.toifa ? Number(req.query.toifa) : undefined,
      sahifa: Number(req.query.sahifa) || 1,
    });
    res.json({ data: rows });
  } catch (e) { next(e); }
});

app.get('/api/mahsulotlar/:id', async (req, res, next) => {
  try {
    const m = await Mahsulot.top(Number(req.params.id));
    if (!m) return res.status(404).json({ xato: 'Topilmadi' });
    res.json({ data: m });
  } catch (e) { next(e); }
});

app.post('/api/mahsulotlar', async (req, res, next) => {
  try {
    const { nom, narx } = req.body;
    if (!nom || !Number.isInteger(narx) || narx <= 0) {
      return res.status(422).json({ xato: "nom va musbat butun narx kerak" });
    }
    const id = await Mahsulot.yarat(req.body);
    res.status(201).json({ data: await Mahsulot.top(id) });
  } catch (e) { next(e); }
});

app.delete('/api/mahsulotlar/:id', async (req, res, next) => {
  try {
    const ok = await Mahsulot.ochir(Number(req.params.id));
    res.status(ok ? 204 : 404).end();
  } catch (e) { next(e); }
});

// Xatolar
app.use((err, req, res, next) => {
  console.error(err);
  if (err.code === 'ER_DUP_ENTRY')        return res.status(409).json({ xato: 'Takroriy qiymat' });
  if (err.code === 'ER_NO_REFERENCED_ROW_2') return res.status(422).json({ xato: "Bog'liq yozuv topilmadi" });
  if (err.code === 'ER_ROW_IS_REFERENCED_2') return res.status(409).json({ xato: "Bu yozuv boshqa joyda ishlatilgan" });
  res.status(500).json({ xato: 'Ichki xato' });
});

app.listen(3000, () => console.log('http://localhost:3000'));

MySQL xato kodlari

err.code va err.errno orqali xatoni aniq ushlash:

Xom MySQL xato matnini foydalanuvchiga ko'rsatmang — logga yozing.

Tur moslashuvi

Pul uchun DECIMAL — string sifatida olish va maxsus kutubxona (decimal.js) bilan hisoblash xavfsizroq.

Nima uchun shunday?

Nima uchun mysql2 natijani [rows, fields] massivi sifatida qaytaradi — faqat rows qaytarsa qulayroq emasmi?

Chunki bitta interfeys turli so'rovlarga xizmat qiladi: SELECT — qatorlar, INSERT — natija ob'ekti (insertId, affectedRows), ko'p so'rovli chaqiruv yoki procedure (CALL) — bir nechta natija to'plami. fields esa ustun turlari va nomlari haqida metama'lumot — ORM lar va universal vositalar uchun. Massiv destructuring bilan kerakli qismni olish oson: const [rows] = ....

Xulosa

Eslab qoling:

• mysql2/promise + createPool — ulanish
• const [rows] = await pool.execute(sql, params) — natija
• Har doim placeholder ?; template string bilan SQL yasamang
• execute — prepared statement; query — IN (?) va dinamik qismlar uchun
• result.insertId, result.affectedRows — yozish natijalari
• undefined → null
• err.code bo'yicha xatolarni HTTP status ga aylantiring
• BIGINT, DECIMAL, TINYINT(1) turlariga e'tibor

Keyingi dars

56-dars: Node.js — Transaction, xatolarni boshqarish