nestjs-boilerplate

Work with database

Table of Contents

About databases

Boilerplate supports two types of databases: PostgreSQL with TypeORM and MongoDB with Mongoose. You can choose one of them or use both in your project. The choice of database depends on the requirements of your project.

For support of both databases used Hexagonal Architecture.

Working with database schema (TypeORM)

Generate migration

  1. Create entity file with extension .entity.ts. For example post.entity.ts:

    // /src/posts/infrastructure/persistence/relational/entities/post.entity.ts

import { Column, Entity, PrimaryGeneratedColumn } from 'typeorm';
import { EntityRelationalHelper } from '../../../../../utils/relational-entity-helper';

@Entity()
export class Post extends EntityRelationalHelper {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  title: string;

  @Column()
  body: string;

  // Here any fields that you need
}

  2. Next, generate migration file:

    npm run migration:generate -- src/database/migrations/CreatePostTable

  3. Apply this migration to database via npm run migration:run.

Run migration

npm run migration:run

Revert migration

npm run migration:revert

Drop all tables in database

npm run schema:drop

Working with database schema (Mongoose)

Create schema

  1. Create entity file with extension .schema.ts. For example post.schema.ts:

    // /src/posts/infrastructure/persistence/document/entities/post.schema.ts

import { Prop, Schema, SchemaFactory } from '@nestjs/mongoose';
import { HydratedDocument } from 'mongoose';

export type PostSchemaDocument = HydratedDocument<PostSchemaClass>;

@Schema({
  timestamps: true,
  toJSON: {
    virtuals: true,
    getters: true,
  },
})
export class PostSchemaClass extends EntityDocumentHelper {
  @Prop()
  title: string;

  @Prop()
  body: string;

  // Here any fields that you need
}

export const PostSchema = SchemaFactory.createForClass(PostSchemaClass);

Seeding (TypeORM)

Creating seeds (TypeORM)

  1. Create seed file with npm run seed:create:relational -- --name=Post. Where Post is name of entity.
  2. Go to src/database/seeds/relational/post/post-seed.service.ts.
  3. In run method extend your logic.
  4. Run npm run seed:run:relational

Run seed (TypeORM)

npm run seed:run:relational

Factory and Faker (TypeORM)

  1. Install faker:

     npm i --save-dev @faker-js/faker

  2. Create src/database/seeds/relational/user/user.factory.ts:

     import { faker } from '@faker-js/faker';
 import { RoleEnum } from '../../../../roles/roles.enum';
 import { StatusEnum } from '../../../../statuses/statuses.enum';
 import { Injectable } from '@nestjs/common';
 import { InjectRepository } from '@nestjs/typeorm';
 import { Repository } from 'typeorm';
 import { RoleEntity } from '../../../../roles/infrastructure/persistence/relational/entities/role.entity';
 import { UserEntity } from '../../../../users/infrastructure/persistence/relational/entities/user.entity';
 import { StatusEntity } from '../../../../statuses/infrastructure/persistence/relational/entities/status.entity';

 @Injectable()
 export class UserFactory {
   constructor(
     @InjectRepository(UserEntity)
     private repositoryUser: Repository<UserEntity>,
     @InjectRepository(RoleEntity)
     private repositoryRole: Repository<RoleEntity>,
     @InjectRepository(StatusEntity)
     private repositoryStatus: Repository<StatusEntity>,
   ) {}

   createRandomUser() {
     // Need for saving "this" context
     return () => {
       return this.repositoryUser.create({
         firstName: faker.person.firstName(),
         lastName: faker.person.lastName(),
         email: faker.internet.email(),
         password: faker.internet.password(),
         role: this.repositoryRole.create({
           id: RoleEnum.user,
           name: 'User',
         }),
         status: this.repositoryStatus.create({
           id: StatusEnum.active,
           name: 'Active',
         }),
       });
     };
   }
 }

  3. Make changes in src/database/seeds/relational/user/user-seed.service.ts:

     // Some code here...
 import { UserFactory } from './user.factory';
 import { faker } from '@faker-js/faker';

 @Injectable()
 export class UserSeedService {
   constructor(
     // Some code here...
     private userFactory: UserFactory,
   ) {}

   async run() {
     // Some code here...

     await this.repository.save(
       faker.helpers.multiple(this.userFactory.createRandomUser(), {
         count: 5,
       }),
     );
   }
 }

  4. Make changes in src/database/seeds/relational/user/user-seed.module.ts:

     import { Module } from '@nestjs/common';
 import { TypeOrmModule } from '@nestjs/typeorm';
    
 import { UserSeedService } from './user-seed.service';
 import { UserFactory } from './user.factory';

 import { UserEntity } from '../../../../users/infrastructure/persistence/relational/entities/user.entity';
 import { RoleEntity } from '../../../../roles/infrastructure/persistence/relational/entities/role.entity';
 import { StatusEntity } from '../../../../statuses/infrastructure/persistence/relational/entities/status.entity';

 @Module({
   imports: [TypeOrmModule.forFeature([UserEntity, Role, Status])],
   providers: [UserSeedService, UserFactory],
   exports: [UserSeedService, UserFactory],
 })
 export class UserSeedModule {}

  5. Run seed:

     npm run seed:run

Seeding (Mongoose)

Creating seeds (Mongoose)

  1. Create seed file with npm run seed:create:document -- --name=Post. Where Post is name of entity.
  2. Go to src/database/seeds/document/post/post-seed.service.ts.
  3. In run method extend your logic.
  4. Run npm run seed:run:document

Run seed (Mongoose)

npm run seed:run:document

Performance optimization (PostgreSQL + TypeORM)

Indexes and Foreign Keys

Don’t forget to create indexes on the Foreign Keys (FK) columns (if needed), because by default PostgreSQL does not automatically add indexes to FK.

Max connections

Set the optimal number of max connections to database for your application in /.env:

DATABASE_MAX_CONNECTIONS=100

You can think of this parameter as how many concurrent database connections your application can handle.

Performance optimization (MongoDB + Mongoose)

Design schema

Designing schema for MongoDB is completely different from designing schema for relational databases. For best performance, you should design your schema according to:

  1. MongoDB Schema Design Anti-Patterns
  2. MongoDB Schema Design Best Practices

Previous: Command Line Interface

Next: Auth

This site is open source. Improve this page.