How to use TypeORM Find, findAndCount and Find Options

TypeORM uses find methods to get data from a database. In this tutorial, I will show you examples of how to use TypeORM find and findAndCount methods alongside any TypeORM find options you need to know.

Now, Dive in as I provide you with all you need with typeorm find and findAndCount.

Step 1: Setting up Basic TypeORM Find App with Nest.js

Before learning these methods, I will assume you have a working TypeORM Nest.js application. However, because I want you to follow along and understand every section, go ahead and create the following simple app:

# Install nestjs cli
npm i -g @nestjs/cli
# Create a nestjs app
nest new nestjs-typeorm
### Using find Method in TypeORM

Change the directory and point to nestjs-typeorm (cd nestjs-typeorm). Then, install the following packages. If you are using MySQL or other databases, ensure the drivers are installed as such.

# Install TypeORM and PostgreSQL driver
npm install @nestjs/typeorm typeorm pg

We’ll be using a product example. Create a Product resource and use REST API as follows:

nest g resource product --no-spec

Your app needs an entity that TypeORM will use to communicate with the database. Go to your product’s product.entity.ts file and create a Product Entity:

import { Entity, PrimaryGeneratedColumn, Column } from 'typeorm';

@Entity()
export class Product {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column()
  description: string;

  @Column({type: "float"})
  price: number;
}

Ensure the Product Module can access this entity using TypeOrmModule forFeature method: (product.module.ts):

// other imports

import { Product } from './entities/product.entity';
import { TypeOrmModule } from '@nestjs/typeorm';

@Module({
  imports: [TypeOrmModule.forFeature([Product])],
  controllers: [ProductController],
  providers: [ProductService],
})
export class ProductModule {}

Related How to use TypeOrmModule forFeature and forRoot Methods

And lastly, add TypeORM connection to your database in the app.module.ts file:

// required modules
import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import { ProductModule } from './product/product.module';
import { TypeOrmModule } from '@nestjs/typeorm';

@Module({
  // TypeORM configuration
  imports: [
    TypeOrmModule.forRoot({
      type: 'postgres',
      host: 'localhost',
      port: 5432,
      username: 'postgres',
      password: 'pass',
      database: 'shop',
      entities: [__dirname + '/**/*.entity{.ts,.js}'],
      synchronize: true,
    }),
    ProductModule,
  ],
  // controllers 
  controllers: [AppController],
  // services (providers) 
  providers: [AppService],
})
export class AppModule {}

At this point, ensure you have the database shop created.

Step 2: Adding TypeORM find Method

TypeORM find method retrieves records from the database that meet conditions. TypeORM uses a services (providers) Repository to allow you to handle this complex task.

Now, based on this Product app, go to your product.service.ts file and create a new method executing find() as follows:

• First, update the Provider Repository and use the @Injectable decorator as follows:

import { Injectable } from '@nestjs/common';
import { Product } from './entities/product.entity';
import { Repository } from 'typeorm';
import { InjectRepository } from '@nestjs/typeorm';

@Injectable()
export class ProductService {
  // Injecting the repository for the Product entity
  constructor(
    @InjectRepository(Product)
    private productsRepository: Repository<Product>,
  ) {}
}

• Now create fetchProducts and use find() method as follows:

  // Fetch all products from the database
  async fetchProducts(): Promise<Product[]> {
    return this.productsRepository.find();
  }

Let’s test how this works. Run your app:

npm run start:dev

This should create your table with its attributes as follows:

To use Find, ensure you have some data in your table as follows:

INSERT INTO Product (name, description, price) VALUES
  ('Smartphone X', 'High-end smartphone with advanced features', 899.99),
  ('Laptop Pro', 'Powerful laptop for professional use', 1299.99),
  ('Wireless Headphones', 'Premium wireless headphones with noise cancellation', 199.99),
  ('Smartwatch Series 5', 'Latest smartwatch with health monitoring features', 349.99),
  ('Gaming Console Deluxe', 'State-of-the-art gaming console with 4K support', 499.99),
  ('4K Ultra HD TV', 'Large screen 4K TV for immersive entertainment', 799.99),
  ('Digital Camera EOS', 'Professional-grade digital camera for photography enthusiasts', 1299.99),
  ('Fitness Tracker Pro', 'Advanced fitness tracker with heart rate monitoring', 79.99),
  ('Home Security System', 'Smart home security system with video surveillance', 399.99),
  ('Electric Scooter Eco', 'Environment-friendly electric scooter for urban commuting', 299.99);

Or use How to Seed Database with TypeORM, Nest.js, and PostgreSQL to assist you.

To execute find, you need to add the controller in your product.controller.ts file:

  @Get()
  findAll() {
    return this.productService.fetchProducts();
  }

This will expose the http://localhost:3000/product/ endpoint you will use to fetch data. Here is the TypeORM find results using Postman GET request:

Step 3: Using TypeORM find Options

While you are using Find, you need to expand your conditions. TypeORM provides additional options to filter, sort, limit the results, add relations, order results, etc.

Still using the above example, let’s add more conditions to tell TypeORM what exactly we want the FIND method to retrieve.

Assume you want to get all products with a price less than 1000 and still order the results in descending order. At the same time, you want to limit the number of results.

This means the find() method can’t handle these conditions. That’s why TypeORM extends find with options such as:

• where
• order
• take
• skip
• relations

This way, you will update your code based on the conditions you want the results to meet as follows:

  async fetchProducts(): Promise<Product[]> {
    return this.productsRepository.find({
      where: { price: LessThan(1000) },
      order: { price: 'DESC' },
      take: 4, // Limit the number of results
    });
  }

Here, TypeORM will use find with additional options:

• where to only fetch products meeting the price condition of less than 1000 using the LessThan method. (ensure you have import { LessThan, Repository } from 'typeorm'; import).
• order allows you to tell TypeORM how to arrange the results. In this case, the order will be executed in descending price order.
• take adds limit. TypeORM will only result in 4 records.

Once you send a GET request to http://localhost:3000/product/ with Postman, here are the results of using TypeORM find options:

Other options include:

• relations if you have a TypeORM relationship in your entities.
• skip as an Offset for pagination.
• select to only select specific columns.
• where and MoreThan to get results based on more than conditions.

These are the common find options TypeORM uses. However, if you want to take TypeORM conditions to the next level, always use QueryBuilder. I have created this How to Create QueryBuilder with TypeORM and NestJS guide to help you construct complex SQL queries programmatically with TypeORM.

Step 4: Using the findAndCount Method in TypeORM

What if you want TypeORM to retrieve records and return the total count of records with the matching conditions? You will use the findAndCount method. findAndCount gets the total count of records based on the requirements you want TypeORM to use.

It’s like you are combining Find but still counting the returned records. This should be the best approach if you want to add pagination to TypeORM.

You only need to replace find with findAndCount, and TypeORM will get your records and the count.

For example, assume you want to get all records with prices Less than 300 and count them.

FindAndCount will return an array of Products and count (a number). Then findAndCount will be used as follows:

• Create a promise resolving to an object with two properties: products (an array of Products) and count (a number).
• Query the productsRepository to find and count products.
• The query result will be an array of products and a count. Therefore, use array destructuring syntax to extract these values from the result.
• Return an object containing the products and the count. The returned object has the shape { products: Product[], count: number }

The code will be represented as follows:

  // a promise with two properties (an array of Products and count as a number)
  async fetchProducts(): Promise<{ products: Product[]; count: number }> {
    // destructure and extract these values from the result
    const [products, count] = await this.productsRepository.findAndCount({
      // results are based on this condition
      where: { price: LessThan(300) },
    });
    // Return the object containing products and count
    return { products, count };
  }

The returned results should have the following:

• Object { products: }
• Array Product[]
• The count: number

Send and GET request to Postman to check the results as follows:

Conclusion

This guide showed how to use TypeORM find and findAndCount methods alongside other find options. I hope you learned something helpful.