MySQL — mysql2 moduli, CRUD

~20 daq · Express · Ma'lumotlar Bazasi

MySQL va PostgreSQL — farqi nima?

Ikkalasi ham relyatsion ma'lumotlar bazasi — jadvallar, SQL, CRUD. Asosiy farqlar:

PostgreSQL — kuchliroq, murakkab so'rovlar, JSON qo'llab-quvvatlash, kengaytmalar ko'p. Yangi loyihalarda ko'proq tavsiya etiladi.

MySQL — tezroq oddiy so'rovlarda, WordPress va PHP ekotizimida keng tarqalgan, hosting larda ko'proq mavjud. Ko'p eski loyihalar MySQL da.

Sintaksis deyarli bir xil — o'rganganingiz ikkalasida ham ishlaydi.

mysql2 nima uchun mysql emas?

npm da mysql va mysql2 — ikki paket bor. mysql2 — yangi versiya. async/await ni qo'llab-quvvatlaydi, tezroq, xavfsizroq. Har doim mysql2 ishlatiladi.

npm install mysql2

Ulanish — pool

PostgreSQL da bo'lgani kabi, MySQL da ham pool ishlatiladi:

// db.js
const mysql = require('mysql2/promise'); // promise versiyasi — async/await uchun

const pool = mysql.createPool({
  host:     process.env.DB_HOST     || 'localhost',
  port:     process.env.DB_PORT     || 3306,
  database: process.env.DB_NAME     || 'mydb',
  user:     process.env.DB_USER     || 'root',
  password: process.env.DB_PASSWORD || 'secret',
  waitForConnections: true,
  connectionLimit:    10,
  queueLimit:         0,
});

module.exports = pool;

mysql2/promise — async/await bilan ishlash uchun. mysql2 (promise siz) — callback bilan ishlaydi, eski uslub.

So'rov yuborish

PostgreSQL da $1, $2 ishlatilgan. MySQL da esa ? ishlatiladi:

// PostgreSQL:
pool.query('SELECT * FROM users WHERE id = $1', [id]);

// MySQL:
pool.query('SELECT * FROM users WHERE id = ?', [id]);

Boshqa hamma narsa bir xil — ? placeholder SQL Injection dan himoya qiladi.

const [rows] = await pool.query('SELECT * FROM users');
// MySQL pool.query massiv qaytaradi: [rows, fields]
// Birinchi element — qatorlar, ikkinchisi — ustun ma'lumotlari
// Destructuring bilan faqat rows olinadi

PostgreSQL da result.rows edi. MySQL da esa pool.query to'g'ridan-to'g'ri [rows, fields] qaytaradi. Destructuring bilan const [rows] = await pool.query(...) — faqat qatorlar olinadi.

CRUD

const express = require('express');
const router  = express.Router();
const pool    = require('../db');

// READ — hammasi
router.get('/', async (req, res, next) => {
  try {
    const [rows] = await pool.query('SELECT id, name, email FROM users');
    res.json(rows);
  } catch (err) {
    next(err);
  }
});

// READ — bittasi
router.get('/:id', async (req, res, next) => {
  try {
    const [rows] = await pool.query(
      'SELECT id, name, email FROM users WHERE id = ?',
      [req.params.id]
    );

    if (rows.length === 0) {
      return res.status(404).json({ message: 'Topilmadi' });
    }

    res.json(rows[0]);
  } catch (err) {
    next(err);
  }
});

// CREATE
router.post('/', async (req, res, next) => {
  try {
    const { name, email } = req.body;

    const [result] = await pool.query(
      'INSERT INTO users (name, email) VALUES (?, ?)',
      [name, email]
    );

    // MySQL da RETURNING yo'q — insertId bilan yangi ID olinadi
    const [rows] = await pool.query(
      'SELECT * FROM users WHERE id = ?',
      [result.insertId]
    );

    res.status(201).json(rows[0]);
  } catch (err) {
    next(err);
  }
});

// UPDATE
router.patch('/:id', async (req, res, next) => {
  try {
    const { name, email } = req.body;

    const [result] = await pool.query(
      'UPDATE users SET name = ?, email = ? WHERE id = ?',
      [name, email, req.params.id]
    );

    if (result.affectedRows === 0) {
      return res.status(404).json({ message: 'Topilmadi' });
    }

    const [rows] = await pool.query('SELECT * FROM users WHERE id = ?', [req.params.id]);
    res.json(rows[0]);
  } catch (err) {
    next(err);
  }
});

// DELETE
router.delete('/:id', async (req, res, next) => {
  try {
    const [result] = await pool.query(
      'DELETE FROM users WHERE id = ?',
      [req.params.id]
    );

    if (result.affectedRows === 0) {
      return res.status(404).json({ message: 'Topilmadi' });
    }

    res.status(204).send();
  } catch (err) {
    next(err);
  }
});

module.exports = router;

PostgreSQL va MySQL farqlari — amalda

Mantiq bir xil — faqat sintaksis farqi.

Tez-tez uchraydigan xatolar

mysql2 o'rniga mysql2/promise import qilmaslik:

// Xato — callback uslubi, async/await ishlamaydi:
const mysql = require('mysql2');

// To'g'ri:
const mysql = require('mysql2/promise');

Destructuring unutish:

// Xato:
const rows = await pool.query('SELECT * FROM users');
// rows = [[{...}, {...}], [{...}]] — massiv ichida massiv

// To'g'ri:
const [rows] = await pool.query('SELECT * FROM users');
// rows = [{...}, {...}]

Xulosa

MySQL va PostgreSQL — ikkalasi relyatsion ma'lumotlar bazasi, SQL bir xil. Farq — parametr sintaksisi (? vs $1) va natija formati.

mysql2/promise — async/await bilan ishlash uchun. Pool — ulanishlarni qayta ishlatish uchun.

Keyingi dars

16-dars: MongoDB — mongoose, schema, model, CRUD