TypeORM + PostgresQL

TypeORM with NestJS & PostgreSQL – Guide

1. Installation & Setup

npm install --save @nestjs/typeorm typeorm pg

import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { User } from './users/user.entity';
import { UsersModule } from './users/users.module';

@Module({
  imports: [
    TypeOrmModule.forRoot({
      type: 'postgres',
      host: 'localhost',
      port: 5432,
      username: 'postgres',
      password: 'yourpassword',
      database: 'testdb',
      entities: [User],
      synchronize: true,
    }),
    UsersModule,
  ],
})
export class AppModule {}

2. Defining Entities

@Entity()
export class User {
  @PrimaryGeneratedColumn() id: number;
  @Column({ unique: true }) email: string;
  @Column() name: string;
  @Column({ nullable: true }) age: number;
  @CreateDateColumn() createdAt: Date;
  @UpdateDateColumn() updatedAt: Date;
  @OneToMany(() => Post, post => post.user)
  posts: Post[];
}

@Entity()
export class Post {
  @PrimaryGeneratedColumn() id: number;
  @Column() title: string;
  @Column({ type: 'text' }) content: string;
  @ManyToOne(() => User, user => user.posts)
  user: User;
}

3. Repositories & Services

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

  findAll() { return this.usersRepo.find({ relations: ['posts'] }); }
  findOne(id: number) { return this.usersRepo.findOne({ where: { id }, relations: ['posts'] }); }
  create(userData: Partial<User>) { const user = this.usersRepo.create(userData); return this.usersRepo.save(user); }
  update(id: number, userData: Partial<User>) { return this.usersRepo.update(id, userData); }
  remove(id: number) { return this.usersRepo.delete(id); }
}

4. Controller Example

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

  @Get() getAll() { return this.usersService.findAll(); }
  @Get(':id') getOne(@Param('id') id: number) { return this.usersService.findOne(id); }
  @Post() create(@Body() userData: Partial<User>) { return this.usersService.create(userData); }
  @Put(':id') update(@Param('id') id: number, @Body() userData: Partial<User>) { return this.usersService.update(id, userData); }
  @Delete(':id') remove(@Param('id') id: number) { return this.usersService.remove(id); }
}

5. Query Examples

// QueryBuilder
const users = await this.usersRepo.createQueryBuilder('user')
  .leftJoinAndSelect('user.posts', 'post')
  .where('user.age > :age', { age: 25 })
  .orderBy('user.name', 'ASC')
  .getMany();

// Raw SQL
const result = await this.usersRepo.query('SELECT * FROM "user" WHERE age > $1', [25]);

6. Relationships in TypeORM

• OneToOne: User → Profile
• OneToMany / ManyToOne: User → Posts
• ManyToMany: Posts ↔ Tags

@ManyToMany(() => Tag, tag => tag.posts)
@JoinTable()
tags: Tag[];

7. Transactions

await this.dataSource.transaction(async (manager) => {
  await manager.update(User, { id: 1 }, { age: 30 });
  await manager.update(Post, { id: 5 }, { title: 'Updated' });
});

8. Migrations

npx typeorm migration:generate -n CreateUsers
npx typeorm migration:run
npx typeorm migration:revert

9. Indexes & Constraints

@Column({ unique: true }) email: string;
@Column({ nullable: true }) username: string;
@Index('idx_user_email')
@Column() email: string;

10. Advanced Tips

1. Use QueryBuilder for complex queries with joins, pagination, filtering.
2. Avoid N+1 problems with leftJoinAndSelect.
3. Use transactions for multi-table operations.
4. Use migrations in production, not synchronize: true.
5. Combine with Redis caching for heavy reads.
6. Use DTOs + ValidationPipe for input validation.