TypeORM is a powerful ORM that provides extensive support for complex database relationships. In this guide, we'll explore implementing many-to-many relationships with custom junction tables, optimizing queries using the Query Builder, and best practices for maintaining high performance.
Custom Many-to-Many Relationships
Let's start with a real-world example: a course enrollment system where students can enroll in courses with additional metadata like enrollment date and grade.
typescript
// entities/Student.ts
@Entity()
export class Student {
@PrimaryGeneratedColumn()
id: number;
@Column()
name: string;
@OneToMany(() => Enrollment, enrollment => enrollment.student)
enrollments: Enrollment[];
}
// entities/Course.ts
@Entity()
export class Course {
@PrimaryGeneratedColumn()
id: number;
@Column()
title: string;
@OneToMany(() => Enrollment, enrollment => enrollment.course)
enrollments: Enrollment[];
}
// entities/Enrollment.ts
@Entity()
export class Enrollment {
@PrimaryColumn()
studentId: number;
@PrimaryColumn()
courseId: number;
@Column({ type: 'timestamp', default: () => 'CURRENT_TIMESTAMP' })
enrollmentDate: Date;
@Column({ type: 'decimal', precision: 3, scale: 2, nullable: true })
grade: number;
@ManyToOne(() => Student, student => student.enrollments)
@JoinColumn({ name: 'studentId' })
student: Student;
@ManyToOne(() => Course, course => course.enrollments)
@JoinColumn({ name: 'courseId' })
course: Course;
}Efficient Query Building
The Query Builder provides fine-grained control over SQL queries. Here's how to build efficient queries for our enrollment system:
typescript
// services/EnrollmentService.ts
export class EnrollmentService {
constructor(
@InjectRepository(Enrollment)
private enrollmentRepo: Repository<Enrollment>
) {}
async getStudentEnrollments(studentId: number) {
return this.enrollmentRepo
.createQueryBuilder('enrollment')
.leftJoinAndSelect('enrollment.course', 'course')
.where('enrollment.studentId = :studentId', { studentId })
.orderBy('enrollment.enrollmentDate', 'DESC')
.getMany();
}
async getTopPerformingStudents(courseId: number, limit: number = 10) {
return this.enrollmentRepo
.createQueryBuilder('enrollment')
.leftJoinAndSelect('enrollment.student', 'student')
.where('enrollment.courseId = :courseId', { courseId })
.andWhere('enrollment.grade IS NOT NULL')
.orderBy('enrollment.grade', 'DESC')
.limit(limit)
.getMany();
}
async getCourseStatistics(courseId: number) {
return this.enrollmentRepo
.createQueryBuilder('enrollment')
.select([
'COUNT(*) as totalStudents',
'AVG(enrollment.grade) as averageGrade',
'MAX(enrollment.grade) as highestGrade',
'MIN(enrollment.grade) as lowestGrade'
])
.where('enrollment.courseId = :courseId', { courseId })
.andWhere('enrollment.grade IS NOT NULL')
.getRawOne();
}
}Query Optimization Techniques
To optimize query performance, we can implement several strategies:
- Use proper indexing for frequently queried columns
- Implement eager loading strategically
- Utilize query result caching
- Optimize JOIN operations
- Use pagination for large datasets
typescript
// Index definitions in entities
@Entity()
@Index(['studentId', 'enrollmentDate']) // Composite index for student queries
@Index(['courseId', 'grade']) // Composite index for grade queries
export class Enrollment {
// ... entity properties
}
// Implementing query caching
export class OptimizedEnrollmentService {
async getStudentEnrollmentsWithCache(studentId: number) {
const cacheKey = `student_enrollments_${studentId}`;
const cachedResult = await this.cache.get(cacheKey);
if (cachedResult) {
return JSON.parse(cachedResult);
}
const result = await this.enrollmentRepo
.createQueryBuilder('enrollment')
.leftJoinAndSelect('enrollment.course', 'course')
.where('enrollment.studentId = :studentId', { studentId })
.orderBy('enrollment.enrollmentDate', 'DESC')
.cache(true, { id: cacheKey, milliseconds: 60000 })
.getMany();
await this.cache.set(cacheKey, JSON.stringify(result), 'EX', 60);
return result;
}
async getPaginatedEnrollments(courseId: number, page: number = 1, limit: number = 25) {
const [enrollments, total] = await this.enrollmentRepo
.createQueryBuilder('enrollment')
.leftJoinAndSelect('enrollment.student', 'student')
.where('enrollment.courseId = :courseId', { courseId })
.skip((page - 1) * limit)
.take(limit)
.getManyAndCount();
return {
data: enrollments,
meta: {
total,
page,
limit,
totalPages: Math.ceil(total / limit)
}
};
}
}Complex Query Patterns
For more complex scenarios, we can combine multiple conditions and relationships:
typescript
async getAdvancedCourseAnalytics(courseId: number) {
return this.enrollmentRepo
.createQueryBuilder('enrollment')
.leftJoinAndSelect('enrollment.student', 'student')
.leftJoinAndSelect('student.profile', 'profile')
.select([
'student.id',
'student.name',
'profile.major',
'enrollment.grade',
'enrollment.enrollmentDate'
])
.addSelect(subQuery => {
return subQuery
.select('COUNT(other_enrollments.courseId)', 'totalCourses')
.from(Enrollment, 'other_enrollments')
.where('other_enrollments.studentId = student.id');
}, 'totalEnrollments')
.where('enrollment.courseId = :courseId', { courseId })
.andWhere(qb => {
const subQuery = qb
.subQuery()
.select('AVG(e.grade)', 'avgGrade')
.from(Enrollment, 'e')
.where('e.courseId = :courseId', { courseId })
.getQuery();
return 'enrollment.grade > ' + subQuery;
})
.orderBy('enrollment.grade', 'DESC')
.getMany();
}Performance Monitoring
To ensure optimal performance, implement proper monitoring and logging:
typescript
// config/typeorm.config.ts
export const typeOrmConfig: TypeOrmModuleOptions = {
type: 'postgres',
// ... other config
logging: true,
logger: 'advanced-console',
maxQueryExecutionTime: 1000, // Log slow queries (>1s)
cache: {
type: 'redis',
options: {
host: 'localhost',
port: 6379
},
duration: 60000 // 1 minute
}
};
// Implementing query logging middleware
@Injectable()
export class QueryLoggingInterceptor implements NestInterceptor {
intercept(context: ExecutionContext, next: CallHandler): Observable<any> {
const start = Date.now();
return next.handle().pipe(
tap(() => {
const duration = Date.now() - start;
if (duration > 100) { // Log queries taking more than 100ms
console.warn(`Slow query detected: ${duration}ms`);
}
})
);
}
}Best Practices
- Always use repository pattern for database operations
- Implement proper error handling and transactions
- Use query builders for complex queries instead of find options
- Cache frequently accessed data
- Monitor query performance regularly
- Use appropriate indexes based on query patterns
By following these patterns and implementing proper optimization strategies, you can build efficient and scalable applications with TypeORM. Remember to always monitor query performance and adjust your strategies based on actual usage patterns.