VitNode
Database

Pagination

How to use cursor based pagination with database.

Offset pagination

VitNode doesn't support the offset pagination. You can create your own offset pagination.

Backend

Query (DTO)

Create a new file for your DTO. You need to extend the PaginationQuery class to show first, last and cursor fields.

apps/shared/plugins/{your_plugin_code}/example.dto.ts
import { IsString, IsOptional } from 'class-validator';
import { ApiProperty, ApiPropertyOptional } from '@nestjs/swagger';
import { PaginationQuery } from 'vitnode-shared/utils/pagination.dto'; 
 
export class ShowExampleWelcomeQuery extends PaginationQuery {}

Object (DTO)

Our second DTO will be for the return object. You need to extend the PaginationObj class and add edges field. The PaginationObj will add total_count, end_cursor, count, has_next_page, has_previous_page and start_cursor fields.

apps/shared/plugins/{your_plugin_code}/example.dto.ts
import { IsString, IsOptional } from 'class-validator';
import { ApiProperty, ApiPropertyOptional } from '@nestjs/swagger';
import {
  PaginationObj,
  PaginationQuery,
} from 'vitnode-shared/utils/pagination.dto'; 
 
export class ShowExampleWelcomeQuery extends PaginationQuery {}
 

export class ExampleWelcomeObj {
  @ApiProperty() 
  name: string; 

}
 

export class ShowExampleWelcomeObj extends PaginationObj {
  @ApiProperty({ type: [ExampleWelcomeObj] }) 
  edges: ExampleWelcomeObj[]; 

}

Import DTO to Service

Now you need to import the DTO to your service.

apps/backend/src/plugins/{your_plugin}/admin/services/example.service.ts
import { Injectable } from '@nestjs/common';
import {
  ShowExampleWelcomeQuery,
  ShowExampleWelcomeObj,
} from 'shared/plugins/{your_plugin_code}/example.dto';
 
@Injectable()
export class ExampleWelcomeService {
  async show({
    first,
    last,
    cursor,
  }: ShowExampleWelcomeQuery): Promise<ShowExampleWelcomeObj> {}
}

Query to Database

In your service, you need to initialize the database using constructor() from the DatabaseService and use the paginationCursor method to get data with cursor pagination. As an example, we will use the core_languages table.

apps/backend/src/plugins/{your_plugin}/admin/services/example.service.ts
import { Injectable } from '@nestjs/common';
import { DatabaseService } from '@/database/database.service'; 
import { SortDirectionEnum } from 'vitnode-shared/utils/pagination.enum'; 
import {
  ShowExampleWelcomeQuery,
  ShowExampleWelcomeObj,
} from 'shared/plugins/{your_plugin_code}/example.dto';
import { core_languages } from '@/database/schema/langs'; 
 
@Injectable()
export class ExampleWelcomeService {
  constructor(private readonly databaseService: DatabaseService) {} 
 
  async show({
    first,
    last,
    cursor,
  }: ShowExampleWelcomeQuery): Promise<ShowExampleWelcomeObj> {

    const pagination = await this.databaseService.paginationCursor({
      cursor, 
      database: core_languages, 
      first, 
      last, 

      defaultSortBy: {
        direction: SortDirectionEnum.desc, 
        column: 'updated_at', 
      }, 

      query: async args =>
        await this.databaseService.db.query.core_languages.findMany(args),
    });
 
    return pagination; 
  }
}

Advanced Usage

Where Arguments

If you want to use where argument you can pass it to the findMany() method using and() method from drizzle-orm.

apps/backend/src/plugins/{your_plugin}/admin/services/example.service.ts
import { ilike } from 'drizzle-orm'; 
import { Injectable } from '@nestjs/common';
import { DatabaseService } from '@/database/database.service';
import { SortDirectionEnum } from 'vitnode-shared/utils/pagination.enum';
import {
  ShowExampleWelcomeQuery,
  ShowExampleWelcomeObj,
} from 'shared/plugins/{your_plugin_code}/example.dto';
import { core_languages } from '@/database/schema/langs';
 
@Injectable()
export class ExampleWelcomeService {
  constructor(private readonly databaseService: DatabaseService) {}
 
  async show({
    first,
    last,
    cursor,
  }: ShowExampleWelcomeQuery): Promise<ShowExampleWelcomeObj> {
    const search = 'english'; 
    const where = ilike(core_languages.name, `%${search}%`); 
 
    const pagination = await this.databaseService.paginationCursor({
      cursor,
      database: core_languages,
      first,
      last,
      where, 
      defaultSortBy: {
        direction: SortDirectionEnum.desc,
        column: 'updated_at',
      },
      query: async args =>
        await this.databaseService.db.query.core_languages.findMany(args),
    });
 
    return pagination;
  }
}

Custom Query

If you want to use a custom query, you can pass it to the query argument.

apps/backend/src/plugins/{your_plugin}/admin/services/example.service.ts
const pagination = await this.databaseService.paginationCursor({
  cursor,
  database: core_languages,
  first,
  last,
  defaultSortBy: {
    direction: SortDirectionEnum.desc,
    column: "updated_at"
  },
  query: async (args) => await this.databaseService.db.query.core_languages.findMany(args) 

  query: async (args) =>

    await this.databaseService.db.query.core_languages.findMany({
      ...args, 
      columns: { 
        id: true, 
        name: true, 
        updated_at: true

      }

    })
});

Where argument in query

Do not change where argument in the query method. It will couse an issue with total_count field.

Primary cursor

As default primaryCursor is set to id column. If you want to change it, you can pass the primaryCursor argument.

apps/backend/src/plugins/{your_plugin}/admin/services/example.service.ts
const pagination = await this.databaseService.paginationCursor({
  cursor,
  database: core_languages,
  first,
  last,
  primaryCursor: 'id_something', 
  defaultSortBy: {
    direction: SortDirectionEnum.desc,
    column: 'updated_at',
  },
  query: async args =>
    await this.databaseService.db.query.core_languages.findMany(args),
});

Frontent integration

Frontend API

You can read how to implement Data Table with pagination API here.

Edit on GitHub

Previous

Migrations

Next

Sorting

On this page