Lesson 7: TypeORM and Prisma - Database connection

NestJS: From Basics to Advanced

Part 2: Providers, Dependency Injection & Data Layer

xdev.asia

1. TypeORM — Setup with NestJS

# Cài package
npm install @nestjs/typeorm typeorm pg
# pg = PostgreSQL driver, thay bằng mysql2 cho MySQL

// app.module.ts
import { TypeOrmModule } from '@nestjs/typeorm';

@Module({
  imports: [
    TypeOrmModule.forRoot({
      type: 'postgres',
      host: 'localhost',
      port: 5432,
      username: 'postgres',
      password: 'password',
      database: 'nestjs_demo',
      entities: [__dirname + '/**/*.entity{.ts,.js}'],
      synchronize: true,  // ⚠️ CHỈ dùng dev, KHÔNG dùng production!
      logging: true,
    }),
    UsersModule,
  ],
})
export class AppModule {}

2. Definition of Entities

// users/entities/user.entity.ts
import {
  Entity, PrimaryGeneratedColumn, Column,
  CreateDateColumn, UpdateDateColumn, OneToMany,
} from 'typeorm';
import { Post } from '../../posts/entities/post.entity';

@Entity('users')
export class User {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column({ length: 100 })
  name: string;

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

  @Column({ select: false })  // Không include trong SELECT mặc định
  password: string;

  @Column({ type: 'enum', enum: ['admin', 'user'], default: 'user' })
  role: string;

  @Column({ default: true })
  isActive: boolean;

  @OneToMany(() => Post, (post) => post.author)
  posts: Post[];

  @CreateDateColumn()
  createdAt: Date;

  @UpdateDateColumn()
  updatedAt: Date;
}

// posts/entities/post.entity.ts
import { Entity, PrimaryGeneratedColumn, Column, ManyToOne, ManyToMany, JoinTable } from 'typeorm';
import { User } from '../../users/entities/user.entity';
import { Tag } from '../../tags/entities/tag.entity';

@Entity('posts')
export class Post {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column()
  title: string;

  @Column('text')
  content: string;

  @Column({ default: false })
  published: boolean;

  @ManyToOne(() => User, (user) => user.posts, { onDelete: 'CASCADE' })
  author: User;

  @Column()
  authorId: string;

  @ManyToMany(() => Tag, (tag) => tag.posts)
  @JoinTable()  // Tạo join table posts_tags
  tags: Tag[];
}

3. Repository Pattern

// users.module.ts
@Module({
  imports: [TypeOrmModule.forFeature([User])],  // Đăng ký repository
  controllers: [UsersController],
  providers: [UsersService],
  exports: [UsersService],
})
export class UsersModule {}

// users.service.ts
import { Injectable, NotFoundException } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { User } from './entities/user.entity';

@Injectable()
export class UsersService {
  constructor(
    @InjectRepository(User)
    private readonly userRepo: Repository<User>,
  ) {}

  async create(dto: CreateUserDto): Promise<User> {
    const user = this.userRepo.create(dto);
    return this.userRepo.save(user);
  }

  async findAll(page = 1, limit = 10): Promise<{ data: User[]; total: number }> {
    const [data, total] = await this.userRepo.findAndCount({
      skip: (page - 1) * limit,
      take: limit,
      order: { createdAt: 'DESC' },
    });
    return { data, total };
  }

  async findOne(id: string): Promise<User> {
    const user = await this.userRepo.findOne({
      where: { id },
      relations: ['posts'],
    });
    if (!user) throw new NotFoundException(`User #${id} not found`);
    return user;
  }

  async update(id: string, dto: UpdateUserDto): Promise<User> {
    await this.userRepo.update(id, dto);
    return this.findOne(id);
  }

  async remove(id: string): Promise<void> {
    const result = await this.userRepo.delete(id);
    if (result.affected === 0) {
      throw new NotFoundException(`User #${id} not found`);
    }
  }

  // QueryBuilder cho queries phức tạp
  async search(keyword: string): Promise<User[]> {
    return this.userRepo
      .createQueryBuilder('user')
      .leftJoinAndSelect('user.posts', 'post')
      .where('user.name ILIKE :keyword', { keyword: `%${keyword}%` })
      .orWhere('user.email ILIKE :keyword', { keyword: `%${keyword}%` })
      .orderBy('user.createdAt', 'DESC')
      .getMany();
  }
}

4. Migrations

# Tạo migration
npx typeorm migration:generate src/migrations/CreateUsers -d src/data-source.ts

# Chạy migration
npx typeorm migration:run -d src/data-source.ts

# Revert migration
npx typeorm migration:revert -d src/data-source.ts

// src/data-source.ts — cho CLI
import { DataSource } from 'typeorm';

export default new DataSource({
  type: 'postgres',
  host: 'localhost',
  port: 5432,
  username: 'postgres',
  password: 'password',
  database: 'nestjs_demo',
  entities: ['src/**/*.entity.ts'],
  migrations: ['src/migrations/*.ts'],
});

5. Prisma — Setup with NestJS

# Cài Prisma
npm install prisma --save-dev
npm install @prisma/client

# Init Prisma
npx prisma init

// prisma/schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model User {
  id        String   @id @default(uuid())
  name      String   @db.VarChar(100)
  email     String   @unique
  password  String
  role      Role     @default(USER)
  isActive  Boolean  @default(true)
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@map("users")
}

model Post {
  id        String   @id @default(uuid())
  title     String
  content   String   @db.Text
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  authorId  String
  tags      Tag[]
  createdAt DateTime @default(now())

  @@map("posts")
}

model Tag {
  id    String @id @default(uuid())
  name  String @unique
  posts Post[]

  @@map("tags")
}

enum Role {
  ADMIN
  USER
}

# Generate Prisma Client
npx prisma generate

# Tạo migration
npx prisma migrate dev --name init

# Prisma Studio (GUI)
npx prisma studio

Prisma Service in NestJS

// prisma/prisma.service.ts
import { Injectable, OnModuleInit, OnModuleDestroy } from '@nestjs/common';
import { PrismaClient } from '@prisma/client';

@Injectable()
export class PrismaService extends PrismaClient 
  implements OnModuleInit, OnModuleDestroy {
  
  async onModuleInit() {
    await this.$connect();
  }

  async onModuleDestroy() {
    await this.$disconnect();
  }
}

// prisma/prisma.module.ts
@Global()
@Module({
  providers: [PrismaService],
  exports: [PrismaService],
})
export class PrismaModule {}

// users.service.ts — với Prisma
@Injectable()
export class UsersService {
  constructor(private readonly prisma: PrismaService) {}

  async create(dto: CreateUserDto) {
    return this.prisma.user.create({ data: dto });
  }

  async findAll(page = 1, limit = 10) {
    const [data, total] = await Promise.all([
      this.prisma.user.findMany({
        skip: (page - 1) * limit,
        take: limit,
        include: { posts: true },
        orderBy: { createdAt: 'desc' },
      }),
      this.prisma.user.count(),
    ]);
    return { data, total };
  }

  async findOne(id: string) {
    const user = await this.prisma.user.findUnique({
      where: { id },
      include: { posts: { include: { tags: true } } },
    });
    if (!user) throw new NotFoundException(`User #${id} not found`);
    return user;
  }
}

6. TypeORM vs Prisma

Criteria	TypeORM	Prisma
Schema definitions	TypeScript Decorators	Prisma Schema Language
Type safety	Average	Very high (auto-generated)
Query API	Repository + QueryBuilder	Prisma Client (fluent API)
Migrations	TypeORM migrations	Prisma Migrate
Raw SQL	Easy	$queryRaw / $executeRaw
Relations	Decorators-based	Schema-based
Performance	Good	Very good (Rust engine)
GUI Tools	No	Prisma Studio
NestJS integration	@nestjs/typebug (official)	Manual PrismaService
Suitable	Familiar with Active Record/Data Mapper	Modern, type-safe first

7. Summary

• TypeORM: Official integration, decorator-based entities, QueryBuilder for complex queries
• Prisma: Type-safe auto-generated client, schema-first, Prisma Studio GUI
• Both support PostgreSQL, MySQL, SQLite, SQL Server
• Production always uses it Migrations, DO NOT use synchronize: true

The next article will explore Validation, Pipes and Exception Filters.