> ## Documentation Index
> Fetch the complete documentation index at: https://www.nestjs-query.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Services

> provides a common interface to use different ORMs in order to query and mutate your data.

The following ORMs are supported out of the box.

* [TypeOrm](https://www.nestjs-query.dev/persistence/typeorm/getting-started.md)
* [Sequelize](https://www.nestjs-query.dev/persistence/sequelize/getting-started.md)
* [Mongoose](https://www.nestjs-query.dev/persistence/mongoose/getting-started.md)
* [Typegoose](https://www.nestjs-query.dev/persistence/typegoose/getting-started.md)

`@ptc-org/nestjs-query-core` also provides a number of base `QueryService`s that can be used to create custom query services. [See the Services docs](https://www.nestjs-query.dev/concepts/services.md#service-helpers)

All examples assume the following entity.

> [tab] TypeOrm
>
> ```ts todo-item.entity.ts theme={null}
> import { Column, CreateDateColumn, Entity, PrimaryGeneratedColumn, UpdateDateColumn } from 'typeorm';
>
> @Entity()
> export class TodoItemEntity {
>
>   @PrimaryGeneratedColumn()
>   id!: string;
>
>   @Column()
>   title!: string;
>
>   @Column()
>   completed!: boolean;
>
>   @CreateDateColumn()
>   created!: Date;
>
>   @UpdateDateColumn()
>   updated!: Date;
> }
> ```

> [tab] Sequelize
>
> ```ts todo-item.entity.ts theme={null}
> import {
>
>   Table,
>   Column,
>   Model,
>   AllowNull,
>   CreatedAt,
>   UpdatedAt,
>   PrimaryKey,
>   AutoIncrement,
> } from 'sequelize-typescript';
>
> @Table
> export class TodoItemEntity extends Model<TodoItemEntity, Partial<TodoItemEntity>> {
>
>   @PrimaryKey
>   @AutoIncrement
>   @Column
>   id!: number;
>
>   @Column
>   title!: string;
>
>   @AllowNull
>   @Column
>   description?: string;
>
>   @Column
>   completed!: boolean;
>
>   @CreatedAt
>   created!: Date;
>
>   @UpdatedAt
>   updated!: Date;
> }
> ```

> [tab] Mongoose
>
> ```ts todo-item.entity.ts theme={null}
> import { Prop, Schema, SchemaFactory } from '@nestjs/mongoose';
> import { Document } from 'mongoose';
>
> @Schema({ timestamps: { createdAt: 'created', updatedAt: 'updated' } })
> export class TodoItemEntity extends Document {
>
>   @Prop({ required: true })
>   title!: string;
>
>   @Prop()
>   description?: string;
>
>   @Prop({ required: true })
>   completed!: boolean;
>
>   @Prop({ default: Date.now })
>   created!: Date;
>
>   @Prop({ default: Date.now })
>   updated!: Date;
> }
> export const TodoItemEntitySchema = SchemaFactory.createForClass(TodoItemEntity);
> ```

> [tab] Typegoose
>
> ```ts todo-item.entity.ts theme={null}
> import { ObjectId } from '@ptc-org/nestjs-query-graphql'
> import { Base } from '@typegoose/typegoose/lib/defaultClasses';
> import { Prop, modelOptions, Ref } from '@typegoose/typegoose';
> import { Types } from 'mongoose';
> import { SubTaskEntity } from '../sub-task/sub-task.entity';
> import { TagEntity } from '../tag/tag.entity';
>
> @modelOptions({
>   schemaOptions: {
>     timestamps: { createdAt: 'created', updatedAt: 'updated' },
>     collection: 'todo-items',
>     toObject: { virtuals: true },
>   },
> })
> export class TodoItemEntity implements Base {
>
>   @ObjectId()
>   _id!: Types.ObjectId
>   id!: string
>
>   @Prop({ required: true })
>   title!: string;
>
>   @Prop()
>   description?: string;
>
>   @Prop({ required: true })
>   completed!: boolean;
>
>   @Prop({ default: Date.now })
>   created!: Date;
>
>   @Prop({ default: Date.now })
>   updated!: Date;
> }
> ```

## Creating a Service[​](#creating-a-service "Direct link to Creating a Service")

### Module[​](#module "Direct link to Module")

The `nestjs-query` `typeorm`, `sequelize`, `mongoose`, and `typegoose` packages provide a module that will add providers to inject auto-created `QueryServices` using the `@InjectQueryService` decorator.

In order to use the decorator you will need to use the module that comes with the `nestjs-query` orm module providing it your entities that you want the services created for.

> [tab] TypeOrm
>
> ```ts todo-item.module.ts theme={null}
> import { NestjsQueryTypeOrmModule } from '@ptc-org/nestjs-query-typeorm';
> import { Module } from '@nestjs/common';
> import { TodoItemEntity } from './todo-item.entity';
> import { TodoItemResolver } from './todo-item.resolver';
>
> @Module({
>   providers: [TodoItemResolver],
>   imports: [NestjsQueryTypeOrmModule.forFeature([TodoItemEntity])],
> })
> export class TodoItemModule {}
> ```

> [tab] Sequelize
>
> ```ts todo-item.module.ts theme={null}
> import { NestjsQuerySequelizeModule } from '@ptc-org/nestjs-query-sequelize';
> import { Module } from '@nestjs/common';
> import { TodoItemEntity } from './todo-item.entity';
> import { TodoItemResolver } from './todo-item.resolver';
>
> @Module({
>   providers: [TodoItemResolver],
>   imports: [NestjsQuerySequelizeModule.forFeature([TodoItemEntity])],
> })
> export class TodoItemModule {}
> ```

> [tab] Mongoose
>
> ```ts todo-item.module.ts theme={null}
> import { NestjsQueryMongooseModule } from '@ptc-org/nestjs-query-mongoose';
> import { Module } from '@nestjs/common';
> import { TodoItemEntity } from './todo-item.entity';
> import { TodoItemResolver } from './todo-item.resolver';
>
> @Module({
>   providers: [TodoItemResolver],
>   imports: [
>     NestjsQueryMongooseModule.forFeature([
>       { document: TodoItemEntity, name: TodoItemEntity.name, schema: TodoItemEntitySchema },
>     ]),
>   ],
> })
> export class TodoItemModule {}
> ```

> [tab] Typegoose
>
> ```ts todo-item.module.ts theme={null}
> import { NestjsQueryTypegooseModule } from '@ptc-org/nestjs-query-typegoose';
> import { Module } from '@nestjs/common';
> import { TodoItemEntity } from './todo-item.entity';
> import { TodoItemResolver } from './todo-item.resolver';
>
> @Module({
>   providers: [TodoItemResolver],
>   imports: [NestjsQueryTypegooseModule.forFeature([TodoItemEntity])],
> })
> export class TodoItemModule {}
> ```

### Decorator[​](#decorator "Direct link to Decorator")

Once you have imported the correct module, use `@InjectQueryService` decorator to inject a `QueryService` into your class or resolver.

```ts todo-item.resolver.ts theme={null}
import { QueryService, InjectQueryService } from '@ptc-org/nestjs-query-core'
import { CRUDResolver } from '@ptc-org/nestjs-query-graphql'
import { Resolver } from '@nestjs/graphql'
import { TodoItemDTO } from './todo-item.dto'
import { TodoItemEntity } from './todo-item.entity'

@Resolver(() => TodoItemDTO)
export class TodoItemResolver extends CRUDResolver(TodoItemDTO) {

  constructor(@InjectQueryService(TodoItemEntity) readonly service: QueryService<TodoItemEntity>) {
    super(service)
  }
}
```

> [!NOTE]
>
> The above resolver is an example of manually defining the resolver, if you use the `NestjsQueryGraphQLModule` you do not need to
> define a resolver.

> [!NOTE]
>
> In the above example the DTO and entity are the same shape, if you have a case where they are different or have computed fields
> check out [Assemblers](https://www.nestjs-query.dev/concepts/advanced/assemblers.md) to understand how to convert to and from the DTO/Entity.

## Querying[​](#querying "Direct link to Querying")

The `nestjs-query` QueryService uses a common `Query` interface that allows you use a common type regardless of the persistence library in use.

To query for records from your service you can use the `query` method which will return a `Promise` of an array of entities. To read more about querying take a look at the [Queries Doc](https://www.nestjs-query.dev/concepts/queries.md).

#### Example[​](#example "Direct link to Example")

Get all records

```ts theme={null}
const records = await this.service.query({})
```

### Filtering[​](#filtering "Direct link to Filtering")

The `filter` option is translated to a `WHERE` clause.

#### Example[​](#example-1 "Direct link to Example")

To find all completed `TodoItems` by use can use the `is` operator.

```ts theme={null}
const records = await this.service.query({
  filter: {
    completed: { is: true }
  }
})
```

### Sorting[​](#sorting "Direct link to Sorting")

The `sorting` option is translated to a `ORDER BY`.

#### Example[​](#example-2 "Direct link to Example")

Sorting records by `completed` and `title`.

```ts theme={null}
const records = await this.service.query({
  sorting: [
    { field: 'completed', direction: SortDirection.ASC },
    { field: 'title', direction: SortDirection.DESC }
  ]
})
```

### Paging[​](#paging "Direct link to Paging")

The `paging` option is translated to `LIMIT` and `OFFSET`.

#### Example[​](#example-3 "Direct link to Example")

Skip the first 20 records and return the next 10.

```ts theme={null}
const records = await this.service.query({
  paging: { limit: 10, offset: 20 }
})
```

### Find By Id[​](#find-by-id "Direct link to Find By Id")

To find a single record you can use the `findById` method.

#### Example[​](#example-4 "Direct link to Example")

```ts theme={null}
const records = await this.service.findById(1)
```

### Get By Id[​](#get-by-id "Direct link to Get By Id")

The `getById` method is the same as the `findById` with one key difference, it will throw an exception if the record is not found.

#### Example[​](#example-5 "Direct link to Example")

```ts theme={null}
try {
  const records = await this.service.getById(1)
} catch (e) {
  console.error('Unable to get record with id = 1')
}
```

### Aggregating[​](#aggregating "Direct link to Aggregating")

To perform an `aggregate` query you can use the `aggregate` method which accepts a `Filter` and `AggregateQuery`.

Supported aggregates are `count`, `sum`, `avg`, `min` and `max`.

In this example we'll aggregate on all records.

```ts theme={null}
const aggregateResponse = await this.service.aggregate(
  {},
  {
    count: ['id'],
    min: ['title'],
    max: ['title']
  }
)
```

The response will look like the following

```ts theme={null}
;[
  {
    count: {
      id: 10
    },
    min: {
      title: 'Aggregate Todo Items'
    },
    min: {
      title: 'Query Todo Items'
    }
  }
]
```

In this example we'll aggregate on all completed TodoItems

```ts theme={null}
const aggregateResponse = await this.service.aggregate(
  { completed: { is: true } },
  {
    count: ['id'],
    min: ['title'],
    max: ['title']
  }
)
```

## Creating[​](#creating "Direct link to Creating")

### Create One[​](#create-one "Direct link to Create One")

To create a single record use the `createOne` method.

#### Example[​](#example-6 "Direct link to Example")

```ts theme={null}
const createdRecord = await this.service.createOne({
  title: 'Foo',
  completed: false
})
```

### Create Many[​](#create-many "Direct link to Create Many")

To create multiple records use the `createMany` method.

#### Example[​](#example-7 "Direct link to Example")

```ts theme={null}
const createdRecords = await this.service.createMany([
  { title: 'Foo', completed: false },
  { title: 'Bar', completed: true }
])
```

## Updating[​](#updating "Direct link to Updating")

### Update One[​](#update-one "Direct link to Update One")

To update a single record use the `updateOne` method.

#### Example[​](#example-8 "Direct link to Example")

Updates the record with an id equal to 1 to completed.

```ts theme={null}
const updatedRecord = await this.service.updateOne(1, { completed: true })
```

### Update Many[​](#update-many "Direct link to Update Many")

To update multiple records use the `updateMany` method.

**NOTE** This method returns a `UpdateManyResponse` which contains the updated record count.

#### Example[​](#example-9 "Direct link to Example")

Updates all `TodoItemEntities` to completed if their title ends in `Bar`

```ts theme={null}
const { updatedCount } = await this.service.updateMany(
  { completed: true }, // update
  { completed: { is: false }, title: { like: '%Bar' } } // filter
)
```

## Deleting[​](#deleting "Direct link to Deleting")

### Delete One[​](#delete-one "Direct link to Delete One")

To delete a single record use the `deleteOne` method.

#### Example[​](#example-10 "Direct link to Example")

Delete the record with an id equal to 1.

```ts theme={null}
const deletedRecord = await this.service.deleteOne(1)
```

### Delete Many[​](#delete-many "Direct link to Delete Many")

To delete multiple records use the `deleteMany` method.

**NOTE** This method returns a `DeleteManyResponse` which contains the deleted record count.

#### Example[​](#example-11 "Direct link to Example")

Delete all `TodoItemEntities` older than `Jan 1, 2019`.

```ts theme={null}
const { deletedCount } = await this.service.deleteMany(
  { created: { lte: new Date('2019-1-1') } } // filter
)
```

## Foreign Keys[​](#foreign-keys "Direct link to Foreign Keys")

It is a common use case to include a foreign key from your entity in your DTO.

To do this you should add the foreign key to your entity as well as your DTO.

> [!NOTE]
>
> This section only applies when using typeorm and sequelize with relations

### Example[​](#example-12 "Direct link to Example")

Assume TodoItems can have SubTasks we would set up our SubTaskEntity using the following

> [tab] TypeOrm
>
> ```ts sub-task.entity.ts theme={null}
> import {
>
>   Entity,
>   PrimaryGeneratedColumn,
>   Column,
>   CreateDateColumn,
>   UpdateDateColumn,
>   ObjectType,
>   ManyToOne,
>   JoinColumn,
> } from 'typeorm';
> import { TodoItemEntity } from '../todo-item/todo-item.entity';
>
> @Entity({ name: 'sub_task' })
> export class SubTaskEntity {
>
>   @PrimaryGeneratedColumn()
>   id!: number;
>
>   @Column()
>   title!: string;
>
>   @Column({ nullable: true })
>   description?: string;
>
>   @Column()
>   completed!: boolean;
>   // add the todoItemId to the model
>
>   @Column({ nullable: false, name: 'todo_item_id' })
>   todoItemId!: string;
>
>   @ManyToOne((): ObjectType<TodoItemEntity> => TodoItemEntity, (td) => td.subTasks, {
>     onDelete: 'CASCADE',
>     nullable: false,
>   })
>   // specify the join column we want to use.
>
>   @JoinColumn({ name: 'todo_item_id' })
>   todoItem!: TodoItemEntity;
>
>   @CreateDateColumn()
>   created!: Date;
>
>   @UpdateDateColumn()
>   updated!: Date;
> }
> ```

> [tab] Sequelize
>
> ```ts sub-task.entity.ts theme={null}
> import {
>
>   Table,
>   AllowNull,
>   Column,
>   ForeignKey,
>   BelongsTo,
>   CreatedAt,
>   UpdatedAt,
>   Model,
>   AutoIncrement,
>   PrimaryKey,
> } from 'sequelize-typescript';
> import { TodoItemEntity } from '../todo-item/entity/todo-item.entity';
>
> @Table({})
> export class SubTaskEntity extends Model<SubTaskEntity, Partial<SubTaskEntity>> {
>
>   @PrimaryKey
>   @AutoIncrement
>   @Column
>   id!: number;
>
>   @Column
>   title!: string;
>
>   @AllowNull
>   @Column
>   description?: string;
>
>   @Column
>   completed!: boolean;
>
>   @Column
>   @ForeignKey(() => TodoItemEntity)
>   todoItemId!: number;
>
>   @BelongsTo(() => TodoItemEntity)
>   todoItem!: TodoItemEntity;
>
>   @CreatedAt
>   created!: Date;
>
>   @UpdatedAt
>   updated!: Date;
> }
> ```

Then we could add the `todoItemId` to the SubTaskDTO.

```ts sub-task.dto.ts theme={null}
import { FilterableField, IDField } from '@ptc-org/nestjs-query-graphql'
import { ObjectType, ID, GraphQLISODateTime } from '@nestjs/graphql'

@ObjectType('SubTask')
export class SubTaskDTO {

  @IDField(() => ID)
  id!: number

  @FilterableField()
  title!: string

  @FilterableField({ nullable: true })
  description?: string

  @FilterableField()
  completed!: boolean

  @FilterableField(() => GraphQLISODateTime)
  created!: Date

  @FilterableField(() => GraphQLISODateTime)
  updated!: Date
  // expose the todoItemId as a filterable field.

  @FilterableField()
  todoItemId!: string
}
```

## Relations[​](#relations "Direct link to Relations")

> [!NOTE]
>
> This section only applies when you combine your DTO and entity and are using Typeorm or Sequelize

When your DTO and entity are the same class and you have relations defined, you should not decorate your the relations in the DTO with `@Field` or `@FilterableField`.

Instead decorate the class with `@CursorConnection`, `@OffsetConnection`, `@UnPagedRelation` or `@Relation`.

### Example[​](#example-13 "Direct link to Example")

Assume you have the following subtask definition.

> [tab] TypeOrm
>
> ```ts sub-task.ts theme={null}
> import {
>
>   Entity,
>   PrimaryGeneratedColumn,
>   Column,
>   CreateDateColumn,
>   UpdateDateColumn,
>   ManyToOne,
>   JoinColumn,
> } from 'typeorm';
> import { ObjectType, ID } from '@nestjs/graphql';
> import { FilterableField, IDField, Relation } from '@ptc-org/nestjs-query-graphql';
> import { TodoItem } from '../todo-item/todo-item';
>
> @ObjectType()
> @Relation('todoItem', () => TodoItem, { update: { enabled: true } })
> @Entity({ name: 'sub_task' })
> export class SubTask {
>
>   @IDField(() => ID)
>   @PrimaryGeneratedColumn()
>   id!: number;
>
>   @FilterableField()
>   @Column()
>   title!: string;
>
>   @FilterableField()
>   @Column({ nullable: true })
>   description?: string;
>
>   @FilterableField()
>   @Column()
>   completed!: boolean;
>
>   @FilterableField()
>   @Column({ nullable: false, name: 'todo_item_id' })
>   todoItemId!: string;
>   // do not decorate with @Field
>
>   @ManyToOne(() => TodoItem, (td) => td.subTasks, {
>     onDelete: 'CASCADE',
>     nullable: false,
>   })
>
>   @JoinColumn({ name: 'todo_item_id' })
>   todoItem!: TodoItem;
>
>   @FilterableField()
>   @CreateDateColumn()
>   created!: Date;
>
>   @FilterableField()
>   @UpdateDateColumn()
>   updated!: Date;
> }
> ```

> [tab] Sequelize
>
> ```ts sub-task.ts theme={null}
> import {
>
>   Table,
>   AllowNull,
>   Column,
>   ForeignKey,
>   BelongsTo,
>   CreatedAt,
>   UpdatedAt,
>   Model,
>   AutoIncrement,
>   PrimaryKey,
> } from 'sequelize-typescript';
> import { FilterableField, IDField } from '@ptc-org/nestjs-query-graphql';
> import { ObjectType, ID, GraphQLISODateTime } from '@nestjs/graphql';
> import { TodoItem } from '../todo-item/entity/todo-item';
>
> @ObjectType()
> @Relation('todoItem', () => TodoItem, { update: { enabled: true } })
> @Table
> export class SubTaskEntity extends Model<SubTaskEntity, Partial<SubTaskEntity>> {
>
>   @IDField(() => ID)
>   @PrimaryKey
>   @AutoIncrement
>   @Column
>   id!: number;
>
>   @FilterableField()
>   @Column
>   title!: string;
>
>   @FilterableField({ nullable: true })
>   @AllowNull
>   @Column
>   description?: string;
>
>   @FilterableField()
>   @Column
>   completed!: boolean;
>
>   @FilterableField()
>   @Column
>   @ForeignKey(() => TodoItemEntity)
>   todoItemId!: number;
>   // do not decorate with @Field
>
>   @BelongsTo(() => TodoItem)
>   todoItem!: TodoItem;
>
>   @FilterableField(() => GraphQLISODateTime)
>   @CreatedAt
>   created!: Date;
>
>   @FilterableField(() => GraphQLISODateTime)
>   @UpdatedAt
>   updated!: Date;
> }
> ```

Notice how the `todoItem` is not decorated with a field decorator, instead it is exposed through the `@Relation` decorator.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.