> ## Documentation Index
> Fetch the complete documentation index at: https://nestjs-query.mintlify.site/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](/persistence/typeorm/getting-started)
* [Sequelize](/persistence/sequelize/getting-started)
* [Mongoose](/persistence/mongoose/getting-started)
* [Typegoose](/persistence/typegoose/getting-started)

`@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](/concepts/services#service-helpers)

All examples assume the following entity.

<Tabs>
  <Tab title="TypeOrm">
    todo-item.entity.ts
  </Tab>

  <Tab title="Sequelize">
    ```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>

  <Tab title="Mongoose">todo-item.entity.ts</Tab>

  <Tab title="Typegoose">
    ```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>

  <Tab title="Example">todo-item.entity.ts</Tab>

  <Tab title="Example">
    ```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>

  <Tab title="Example">todo-item.entity.ts</Tab>

  <Tab title="Example">
    ```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;
    }
    ```
  </Tab>
</Tabs>

## 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.

<Tabs>
  <Tab title="TypeOrm">
    todo-item.module.ts
  </Tab>

  <Tab title="Sequelize">
    ```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>

  <Tab title="Mongoose">todo-item.module.ts</Tab>

  <Tab title="Typegoose">
    ```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>

  <Tab title="Example">todo-item.module.ts</Tab>

  <Tab title="Example">
    ```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>

  <Tab title="Example">todo-item.module.ts</Tab>

  <Tab title="Example">
    ```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 {}
    ```
  </Tab>
</Tabs>

### 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.

todo-item.resolver.ts

```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>

<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](/concepts/advanced/assemblers) to understand how to convert to and from the DTO/Entity.
</Note>

## 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](/concepts/queries).

#### 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</Note>

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

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

<Tabs>
  <Tab title="TypeOrm">
    sub-task.entity.ts
  </Tab>

  <Tab title="Sequelize">
    ```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>

  <Tab title="Example">sub-task.entity.ts</Tab>

  <Tab title="Example">
    ```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;
    }
    ```
  </Tab>
</Tabs>

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

sub-task.dto.ts

```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</Note>

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.

<Tabs>
  <Tab title="TypeOrm">
    sub-task.ts
  </Tab>

  <Tab title="Sequelize">
    ```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>

  <Tab title="Example">sub-task.ts</Tab>

  <Tab title="Example">
    ```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;
    }
    ```
  </Tab>
</Tabs>

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

[Edit this page](https://github.com/tripss/nestjs-query/edit/master/docs/persistence/services)