Sequelize ORM — model, migration, association

~30 daq · MySQL · MySQL va Dasturlash Tillari

Muammodan boshlaylik

mysql2 bilan ishladik (55-56 darslar). Hammasi aniq va nazorat ostida. Lekin loyiha o'sgach:

• Har bir jadval uchun bir xil CRUD funksiyalarini qayta-qayta yozish
• Buyurtmani mijoz va mahsulotlari bilan olish uchun qo'lda JOIN va natijani ichma-ich ob'ektga yig'ish
• Jadval tuzilmasidagi o'zgarishlar — qo'lda ALTER TABLE va "serverda bajarishni unutdim"

ORM (Object-Relational Mapping) — jadvallarni JavaScript classlari (modellar) sifatida ifodalaydi. SQL ni ko'p hollarda ORM o'zi yozadi. Sequelize — Node.js dagi eng eski va keng tarqalgan ORM lardan biri.

O'rnatish

npm install sequelize mysql2
npm install --save-dev sequelize-cli
npx sequelize-cli init

init papkalar yaratadi:

config/config.json     ← ulanish sozlamalari
models/                ← modellar
migrations/            ← jadval o'zgarishlari
seeders/               ← boshlang'ich ma'lumotlar

config/config.json (parollarni muhit o'zgaruvchilaridan olish uchun uni config.js ga aylantirish tavsiya etiladi):

{
  "development": {
    "username": "dokon_app",
    "password": null,
    "database": "dokon_dev",
    "host": "127.0.0.1",
    "dialect": "mysql"
  }
}

Migratsiyalar — jadval tuzilmasi kod sifatida

npx sequelize-cli migration:generate --name create-mijozlar

migrations/20261104120000-create-mijozlar.js:

'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('mijozlar', {
      id:         { type: Sequelize.INTEGER.UNSIGNED, autoIncrement: true, primaryKey: true },
      ism:        { type: Sequelize.STRING(100), allowNull: false },
      email:      { type: Sequelize.STRING(255), allowNull: false, unique: true },
      telefon:    { type: Sequelize.STRING(20) },
      shahar:     { type: Sequelize.STRING(50) },
      created_at: { type: Sequelize.DATE, allowNull: false, defaultValue: Sequelize.literal('CURRENT_TIMESTAMP') },
      updated_at: { type: Sequelize.DATE, allowNull: false, defaultValue: Sequelize.literal('CURRENT_TIMESTAMP') },
    });
  },

  async down(queryInterface) {
    await queryInterface.dropTable('mijozlar');
  },
};

npx sequelize-cli db:migrate            # barcha yangi migratsiyalarni bajarish
npx sequelize-cli db:migrate:undo       # oxirgisini bekor qilish (down)
npx sequelize-cli db:migrate:status     # qaysilari bajarilgan

• up — o'zgarishni qo'llash, down — qaytarish
• Sequelize bajarilganlarni SequelizeMeta jadvalida kuzatadi
• Migratsiya fayllari Git da — har bir dasturchi va server bir xil tuzilmaga ega (8-dars)

Ustun qo'shish:

async up(queryInterface, Sequelize) {
  await queryInterface.addColumn('mijozlar', 'tugilgan_kun', { type: Sequelize.DATEONLY });
  await queryInterface.addIndex('mijozlar', ['shahar']);
},
async down(queryInterface) {
  await queryInterface.removeIndex('mijozlar', ['shahar']);
  await queryInterface.removeColumn('mijozlar', 'tugilgan_kun');
},

[!WARNING] sequelize.sync() — modellardan jadvallarni avtomatik yaratadi. O'rganish va testlar uchun qulay, lekin production da ishlatmang: sync({ alter: true }) ma'lumotni yo'qotishi mumkin, sync({ force: true }) esa jadvallarni o'chirib qayta yaratadi. Production — faqat migratsiyalar.

Modellar

models/mijoz.js:

const { Model, DataTypes } = require('sequelize');

module.exports = (sequelize) => {
  class Mijoz extends Model {
    static associate(models) {
      Mijoz.hasMany(models.Buyurtma, { foreignKey: 'mijoz_id', as: 'buyurtmalar' });
    }
  }

  Mijoz.init({
    id:      { type: DataTypes.INTEGER.UNSIGNED, autoIncrement: true, primaryKey: true },
    ism:     { type: DataTypes.STRING(100), allowNull: false, validate: { len: [2, 100] } },
    email:   { type: DataTypes.STRING(255), allowNull: false, unique: true, validate: { isEmail: true } },
    telefon: DataTypes.STRING(20),
    shahar:  DataTypes.STRING(50),
  }, {
    sequelize,
    modelName: 'Mijoz',
    tableName: 'mijozlar',
    underscored: true,      // createdAt → created_at
  });

  return Mijoz;
};

• validate — ilova darajasidagi tekshiruv (bazadagi cheklovlarga qo'shimcha, 6-dars)
• tableName — o'zbekcha nomlar uchun aniq ko'rsatish kerak (Sequelize standart holatda inglizcha ko'plik yasaydi: Mijoz → Mijozs)
• underscored: true — snake_case ustunlar (49-dars)

CRUD

const { Mijoz, Mahsulot } = require('./models');
const { Op } = require('sequelize');

// CREATE
const m = await Mijoz.create({ ism: 'Dilshod', email: 'dilshod@mail.uz', shahar: 'Toshkent' });
console.log(m.id);

// READ
const bitta = await Mijoz.findByPk(1);
const email = await Mijoz.findOne({ where: { email: 'aziz@mail.uz' } });

const toshkentliklar = await Mijoz.findAll({
  where: { shahar: 'Toshkent' },
  attributes: ['id', 'ism', 'email'],        // SELECT * emas
  order: [['ism', 'ASC']],
  limit: 20,
  offset: 0,
});

// Murakkab shartlar — Op operatorlari
const arzonlar = await Mahsulot.findAll({
  where: {
    narx: { [Op.between]: [100000, 5000000] },
    soni: { [Op.gt]: 0 },
    nom:  { [Op.like]: '%Pro%' },
    [Op.or]: [{ toifa_id: 1 }, { toifa_id: 3 }],
  },
});

// UPDATE
await Mijoz.update({ shahar: 'Buxoro' }, { where: { id: 4 } });
// yoki
bitta.shahar = 'Buxoro';
await bitta.save();

// DELETE
await Mijoz.destroy({ where: { id: 4 } });

// Hisoblash
const soni = await Mijoz.count({ where: { shahar: 'Toshkent' } });
const jami = await Mahsulot.sum('soni');

Barcha qiymatlar Sequelize ichida prepared statements orqali yuboriladi — SQL injection xavfi yo'q (agar sequelize.literal() yoki sequelize.query() ga o'zgaruvchi qo'shmasangiz).

Association — munosabatlar

// models/buyurtma.js — associate ichida
Buyurtma.belongsTo(models.Mijoz, { foreignKey: 'mijoz_id', as: 'mijoz' });
Buyurtma.hasMany(models.BuyurtmaQatori, { foreignKey: 'buyurtma_id', as: 'qatorlar' });

// models/mahsulot.js
Mahsulot.belongsTo(models.Toifa, { foreignKey: 'toifa_id', as: 'toifa' });
Mahsulot.belongsToMany(models.Teg, {
  through: 'mahsulot_teglar',
  foreignKey: 'mahsulot_id',
  otherKey: 'teg_id',
  as: 'teglar',
});

// models/foydalanuvchi.js
Foydalanuvchi.hasOne(models.Profil, { foreignKey: 'foydalanuvchi_id', as: 'profil' });

Eager loading — include

const buyurtma = await Buyurtma.findByPk(3, {
  include: [
    { model: Mijoz, as: 'mijoz', attributes: ['ism', 'telefon'] },
    {
      model: BuyurtmaQatori, as: 'qatorlar',
      include: [{ model: Mahsulot, as: 'mahsulot', attributes: ['nom'] }],
    },
  ],
});

console.log(buyurtma.mijoz.ism);                    // Malika Rahimova
console.log(buyurtma.qatorlar[0].mahsulot.nom);     // MacBook Air M3

Sequelize JOIN yozadi va natijani ichma-ich ob'ektlarga yig'adi. Qo'lda bunday "yig'ish" — o'nlab qator kod.

N+1 tuzog'i

// ❌ N+1 — har bir buyurtma uchun alohida so'rov (18-dars)
const buyurtmalar = await Buyurtma.findAll({ limit: 50 });
for (const b of buyurtmalar) {
  const mijoz = await b.getMijoz();   // 50 ta qo'shimcha so'rov!
}

// ✅ include — 1 ta so'rov
const buyurtmalar = await Buyurtma.findAll({ limit: 50, include: [{ model: Mijoz, as: 'mijoz' }] });

Ishlab chiqishda logging: console.log yoqing — Sequelize yuborayotgan har bir SQL ni ko'rasiz.

Tranzaksiyalar

const t = await sequelize.transaction();
try {
  const b = await Buyurtma.create({ mijoz_id: 2 }, { transaction: t });
  await BuyurtmaQatori.create({ buyurtma_id: b.id, mahsulot_id: 5, soni: 1, narx: 3100000 }, { transaction: t });
  await Mahsulot.decrement('soni', { by: 1, where: { id: 5 }, transaction: t });
  await t.commit();
} catch (e) {
  await t.rollback();
  throw e;
}

Har bir so'rovga { transaction: t } uzatishni unutsangiz — u tranzaksiyadan tashqarida bajariladi. Qulayroq — "managed" tranzaksiya, avtomatik commit/rollback bilan:

await sequelize.transaction(async (t) => {
  const mahsulot = await Mahsulot.findByPk(5, { lock: t.LOCK.UPDATE, transaction: t });   // FOR UPDATE
  if (mahsulot.soni < 1) throw new Error('Omborda yo\'q');
  // ...
});

Xom SQL

ORM hamma narsani qila olmaydi. Murakkab hisobotlar, window funksiyalar (29-dars) — xom SQL bilan:

const [natija] = await sequelize.query(
  `SELECT m.ism, SUM(q.soni * q.narx) AS jami
   FROM mijozlar m JOIN buyurtmalar b ON b.mijoz_id = m.id
   JOIN buyurtma_qatorlari q ON q.buyurtma_id = b.id
   WHERE b.holat <> 'bekor' AND m.shahar = :shahar
   GROUP BY m.id ORDER BY jami DESC`,
  { replacements: { shahar: 'Toshkent' }, type: QueryTypes.SELECT }
);

replacements — placeholder. Hech qachon ${shahar} qo'shmang.

Nima uchun shunday?

Nima uchun ORM lar ko'p tanqid qilinadi, agar ular bunchalik qulay bo'lsa?

Chunki ular SQL ni yashiradi — va yashirilgan narsa nazoratdan chiqadi. Dasturchi buyurtma.getMijoz() ni oddiy xususiyat kabi ishlatadi va siklda 500 ta so'rov yuborayotganini bilmaydi. Murakkab so'rovlar uchun ORM sintaksisi SQL dan ham chalkashroq. Lekin oddiy CRUD, munosabatlar va migratsiyalar uchun ORM vaqtni sezilarli tejaydi. Muvozanat: ORM — kundalik ish uchun, xom SQL — murakkab hisobotlar uchun, va doim generatsiya qilingan SQL ni ko'rib turing.

Xulosa

Eslab qoling:

• ORM — jadvallar classlar sifatida; SQL ni ko'p hollarda o'zi yozadi
• Migratsiyalar: up / down, db:migrate; production da sync() emas
• Model: init({...}, { tableName, underscored }), validate
• CRUD: create, findByPk, findAll({ where, attributes, order, limit }), update, destroy
• Op.gt, Op.like, Op.or — murakkab shartlar
• belongsTo, hasMany, hasOne, belongsToMany; include — eager loading, N+1 dan himoya
• Tranzaksiya: sequelize.transaction(async t => ...) + har so'rovga { transaction: t }
• Xom SQL — sequelize.query + replacements
• logging yoqib, generatsiya qilingan SQL ni kuzating

Keyingi dars

58-dars: Prisma ORM — schema, migration, CRUD