Sequelize ORM + PostgreSQL — CRUD

~30 daq · NestJS · Ma'lumotlar bazasi

Sequelize nima va nima muammoni hal qiladi?

Ma'lumotlar bazasi bilan ishlashda odatda SQL yoziladi:

SELECT * FROM users WHERE id = 1;
INSERT INTO users (name, email) VALUES ('Ali', 'ali@example.com');
UPDATE users SET name = 'Vali' WHERE id = 1;
DELETE FROM users WHERE id = 1;

Bu ishlaydi — lekin muammolar bor:

• Xavfsizlik: SQL injection — foydalanuvchi kiritgan ma'lumot to'g'ridan-to'g'ri so'rovga tushsa, hacker bazani buzishi mumkin
• Takroriylik: Har safar bir xil SELECT, INSERT kodni yozish
• Tip xavfsizligi: TypeScript bilan SQL string — hech qanday tip tekshiruvi yo'q
• Ko'chma emas: MySQL dan PostgreSQL ga o'tish — barcha so'rovlarni qayta yozish

ORM (Object-Relational Mapping) — bu muammolarni hal qiladi. SQL o'rniga JavaScript/TypeScript ob'ektlari bilan ishlaysiz. ORM SQL ni o'zi yozadi.

Sequelize — Node.js uchun eng mashhur ORM lardan biri. PostgreSQL, MySQL, SQLite, MSSQL ni qo'llab-quvvatlaydi.

// SQL o'rniga — TypeScript ob'ekti
const user = await User.findOne({ where: { id: 1 } });
await User.create({ name: 'Ali', email: 'ali@example.com' });
await user.update({ name: 'Vali' });
await user.destroy();

O'rnatish

npm install @nestjs/sequelize sequelize sequelize-typescript pg pg-hstore
npm install --save-dev @types/sequelize

• sequelize — asosiy kutubxona
• sequelize-typescript — TypeScript decorator qo'llab-quvvatlash
• pg — PostgreSQL driver
• pg-hstore — PostgreSQL hstore turi uchun

Ulanish

// app.module.ts
import { Module } from '@nestjs/common';
import { SequelizeModule } from '@nestjs/sequelize';
import { ConfigModule, ConfigService } from '@nestjs/config';

@Module({
  imports: [
    ConfigModule.forRoot({ isGlobal: true }),

    SequelizeModule.forRootAsync({
      useFactory: (config: ConfigService) => ({
        dialect: 'postgres',
        host: config.get('DB_HOST'),
        port: config.get<number>('DB_PORT'),
        username: config.get('DB_USERNAME'),
        password: config.get('DB_PASSWORD'),
        database: config.get('DB_NAME'),
        autoSync: true,   // modellar asosida jadvallarni avtomatik yaratadi
        // ⚠️ autoSync: true — faqat development da! Production da migration ishlatiladi
      }),
      inject: [ConfigService],
    }),
  ],
})
export class AppModule {}

.env:

DB_HOST=localhost
DB_PORT=5432
DB_USERNAME=postgres
DB_PASSWORD=secret
DB_NAME=myapp

Model yaratish

Model — ma'lumotlar bazasidagi jadvalning TypeScript ko'rinishi. Har bir property — jadval ustuni.

// users/user.model.ts
import {
  Column,
  DataType,
  Model,
  Table,
  CreatedAt,
  UpdatedAt,
} from 'sequelize-typescript';

@Table({ tableName: 'users' })
export class User extends Model {

  @Column({ primaryKey: true, autoIncrement: true })
  id: number;

  @Column({ allowNull: false })
  name: string;

  @Column({ unique: true, allowNull: false })
  email: string;

  @Column({ defaultValue: 'user' })
  role: string;

  @Column({ type: DataType.BOOLEAN, defaultValue: true })
  isActive: boolean;

  @CreatedAt
  createdAt: Date;

  @UpdatedAt
  updatedAt: Date;
}

@Table — bu class qaysi jadvalga mos kelishini bildiradi. @Column — jadval ustuni. allowNull, unique, defaultValue kabi cheklovlar shu yerda. @CreatedAt, @UpdatedAt — Sequelize avtomatik to'ldiradi.

Module va Service

// users/users.module.ts
import { Module } from '@nestjs/common';
import { SequelizeModule } from '@nestjs/sequelize';
import { User } from './user.model';
import { UsersService } from './users.service';
import { UsersController } from './users.controller';

@Module({
  imports: [SequelizeModule.forFeature([User])], // bu modul User modelini ishlatadi
  providers: [UsersService],
  controllers: [UsersController],
})
export class UsersModule {}

SequelizeModule.forFeature([User]) — nima uchun kerak? Bu UsersService ga User modelini inject qilish imkonini beradi. Har bir modul faqat o'zi ishlatadigan modellarni ro'yxatga oladi.

// users/users.service.ts
import { Injectable, NotFoundException } from '@nestjs/common';
import { InjectModel } from '@nestjs/sequelize';
import { User } from './user.model';

@Injectable()
export class UsersService {
  constructor(
    @InjectModel(User) private readonly userModel: typeof User,
  ) {}

  // CREATE
  async create(data: { name: string; email: string }): Promise<User> {
    return this.userModel.create(data);
  }

  // READ ALL
  async findAll(): Promise<User[]> {
    return this.userModel.findAll();
  }

  // READ ONE
  async findOne(id: number): Promise<User> {
    const user = await this.userModel.findByPk(id);
    if (!user) throw new NotFoundException(`Foydalanuvchi #${id} topilmadi`);
    return user;
  }

  // UPDATE
  async update(id: number, data: Partial<{ name: string; role: string }>): Promise<User> {
    const user = await this.findOne(id);
    return user.update(data);
  }

  // DELETE
  async remove(id: number): Promise<void> {
    const user = await this.findOne(id);
    await user.destroy();
  }
}

@InjectModel(User) — User modelini inject qiladi. typeof User — model instance emas, model klassi (static metodlar uchun).

Controller

// users/users.controller.ts
import { Controller, Get, Post, Put, Delete, Param, Body, ParseIntPipe } from '@nestjs/common';
import { UsersService } from './users.service';

@Controller('users')
export class UsersController {
  constructor(private readonly usersService: UsersService) {}

  @Post()
  create(@Body() body: { name: string; email: string }) {
    return this.usersService.create(body);
  }

  @Get()
  findAll() {
    return this.usersService.findAll();
  }

  @Get(':id')
  findOne(@Param('id', ParseIntPipe) id: number) {
    return this.usersService.findOne(id);
  }

  @Put(':id')
  update(
    @Param('id', ParseIntPipe) id: number,
    @Body() body: { name?: string; role?: string },
  ) {
    return this.usersService.update(id, body);
  }

  @Delete(':id')
  remove(@Param('id', ParseIntPipe) id: number) {
    return this.usersService.remove(id);
  }
}

Foydali so'rovlar

// Filtr bilan qidirish
const activeUsers = await this.userModel.findAll({
  where: { isActive: true, role: 'admin' },
});

// Faqat kerakli ustunlar
const users = await this.userModel.findAll({
  attributes: ['id', 'name', 'email'], // password, role kelmaydi
});

// Tartib va limit
const users = await this.userModel.findAll({
  order: [['createdAt', 'DESC']],
  limit: 10,
  offset: 0, // pagination uchun
});

// Yoki topilmasa null
const user = await this.userModel.findOne({
  where: { email: 'ali@example.com' },
});

// Mavjud bo'lsa qaytaradi, bo'lmasa yaratadi
const [user, created] = await this.userModel.findOrCreate({
  where: { email: 'ali@example.com' },
  defaults: { name: 'Ali' },
});

Tez-tez uchraydigan xatolar

1. SequelizeModule.forFeature unutilgan

Error: Nest can't resolve dependencies of the UsersService.
Please make sure that the argument InjectModel(User) is available.

Sabab: UsersModule da SequelizeModule.forFeature([User]) yo'q.

// ✅ To'g'ri
@Module({
  imports: [SequelizeModule.forFeature([User])],
  ...
})

2. autoSync production da yoqilgan

autoSync: true — har ishga tushganda jadval tuzilmasini model bilan moslashtiradi. Agar model o'zgarsa — mavjud ma'lumotlar o'chib ketishi mumkin.

// ❌ Production da xavfli
autoSync: true

// ✅ Production da migration ishlatiladi (15-dars)
autoSync: process.env.NODE_ENV !== 'production'

3. findByPk null qaytaradi — tekshirilmagan

// ❌ user null bo'lsa — keyingi qatorda crash
const user = await this.userModel.findByPk(id);
return user.name; // TypeError: Cannot read properties of null

// ✅ Har doim tekshiring
const user = await this.userModel.findByPk(id);
if (!user) throw new NotFoundException(`Foydalanuvchi #${id} topilmadi`);
return user.name;

Xulosa

Sequelize nima beradi:

• SQL yozmasdan TypeScript ob'ektlari bilan ishlash
• SQL injection dan himoya (parametrlar avtomatik escaped)
• PostgreSQL, MySQL, SQLite — bir xil kod

Asosiy metodlar:

Keyingi dars

14-dars: Database Relations — One-to-Many, Many-to-Many (Sequelize)