From Good to Great: Scaling Applications with TypeORM Optimization

TypeORM is a popular Object-Relational Mapping (ORM) library for Node.js. It provides a high-level abstraction over relational databases, making it easy to perform CRUD (Create, Read, Update, Delete) operations.

However, like any ORM, TypeORM can be used in ways that can lead to performance problems. In this blog post, we will discuss some tips on how to improve TypeORM operations.

1. Use a connection pool

A connection pool is a group of database connections that can be reused. This can improve performance by reducing the overhead of opening and closing database connections.

To use a connection pool in TypeORM, simply set the connection option in your TypeORM configuration to a DataSource instance.

import { DataSource } from "typeorm"

const connectionPool = new DataSource({
  type: 'mysql',
  host: 'localhost',
  port: 3306,
  username: 'root',
  password: '',
  database: 'test',
});

const typeormConfig = {
  connection: connectionPool,
};

2. Use lazy loading

Lazy loading is a technique that delays the loading of database entities until they are actually needed. This can improve performance by reducing the amount of data that needs to be transferred between the database and your application.

To enable lazy loading in TypeORM, simply set the lazy property to true on your entity definitions.

import {
    Entity,
    BaseEntity,
    PrimaryGeneratedColumn,
    Column,
    OneToMany,
    ManyToOne
} from "typeorm"

@Entity()
export class PostEntity extends BaseEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  title: string;

  @Column()
  comment: string;

  @Column()
  userId: number;

  // Lazy load the post's user
  @ManyToOne(() => UserEntity, user => user.posts)
  user: UserEntity[];
}

@Entity()
export class UserEntity extends BaseEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column()
  email: string;

  // Lazy load the user's posts
  @OneToMany(() => PostEntity, post => post.user)
  posts: PostEntity[];
}


// ❌ Bad Query
const users = await UserEntity.find();

Promise.all(users.map( async (user) => {
    const posts = await PostEntity.find({
        where: { userId: user.id }
    });
    return posts
}));

// ✅ Good Query
const users = await UserEntity.find({
    relations: {
        posts: true
    }
}).catch(() => []);

3. Use caching

Caching can also improve performance by storing frequently accessed data in memory. TypeORM provides built-in support for caching, so you can easily enable it in your application.

To enable caching in TypeORM, simply set the cache option in your TypeORM configuration to a Cache instance.

// Cache global
const connection = new DataSource({
  type: 'mysql',
  host: 'localhost',
  port: 3306,
  username: 'root',
  password: '',
  database: 'test',
  cache: true
});

// One query
const user = await UserEntity.findOne({
    where: { id: 1 },
    cache: 10000
}).catch(() => null)

4. Use transactions

Transactions are a way to ensure that multiple database operations are performed as a single unit. This can help to prevent data corruption and improve the reliability of your application.

To use transactions in TypeORM, simply wrap your database operations in a transaction() call.

const connection = new DataSource()

await connection.manager.transaction( async (manager) => {

    // lets now open a new transaction:
    await manager.startTransaction()

    try {
         // execute some operations on this transaction:
        await manager.save(user);
        await manager.save(userInfo);

        // commit transaction now:
        await manager.commitTransaction()
    } catch (error) {
        // since we have errors let's rollback changes we made
        await manager.rollbackTransaction()
    } finally {
        // you need to release query runner which is manually created:
        await manager.release()
    }
});

5. Optimize your queries

TypeORM provides a number of features that can help you to optimize your queries, such as the ability to use named parameters and subqueries.

You can also use a query profiler to identify performance bottlenecks in your queries.

Database Profiling and Monitoring Tools:

1. Database Profiling and Monitoring Tools:
   • pgAdmin (for PostgreSQL): pgAdmin provides a query tool that allows you to execute and analyze SQL queries. You can view query execution plans, see query statistics, and track slow queries.
   • MySQL Workbench (for MySQL): MySQL Workbench has query execution profiling features that can help you analyze query performance.
   • SQL Server Management Studio (for SQL Server): SSMS offers query execution plans and query statistics features for performance analysis.
2. ORM-Specific Query Logging:
   • TypeORM and other ORMs often allow you to enable query logging. This can be a valuable source of information about executed queries, especially when combined with other monitoring tools.
3. Visual Query Builders:
   • Tools like TablePlus, DBeaver, or HeidiSQL provide visual query building interfaces. While not performance analysis tools per se, they can help you build and understand complex queries more easily.
4. Third-party Profiling Tools:
   • Tools like "pg_stat_statements" for PostgreSQL can be used to log and analyze query performance. It's not a visual tool, but it can provide insights into query efficiency.
5. Database Monitoring Platforms:
   • Tools like Datadog, New Relic, and AppDynamics provide visual dashboards for monitoring database performance and identifying bottlenecks.
6. Query Visualizers:
   • Tools like EverSQL Query Optimizer can take an SQL query and provide visual representations of query execution plans, suggesting optimizations.
7. Custom Dashboard Solutions:
   • You can build custom dashboards using Grafana, Kibana, or other visualization tools, integrating database performance metrics and logs for a holistic view of your application's performance

6. Monitor your application

It is important to monitor the performance and health of your TypeORM application. This can help you to identify and troubleshoot problems before they cause outages.

There are a number of TypeORM monitoring tools available that can help you to do this.

By following these tips, you can improve the performance and scalability of your TypeORM application.

• AppMetrics
• Clinic.js
• Express Status Monitor
• PM2