database.md

Work with database

In NestJS Boilerplate uses TypeORM and PostgreSQL for working with database, and all examples will for PostgreSQL, but you can use any database.

---

Table of Contents

• Working with database schema
  • Generate migration
  • Run migration
  • Revert migration
  • Drop all tables in database
• Seeding
  • Creating seeds
  • Run seed
• Performance optimization
  • Indexes and Foreign Keys
  • Max connections

---

Working with database schema

Generate migration

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

   // /src/posts/entities/post.entity.ts
   
   import { Column, Entity, PrimaryGeneratedColumn } from 'typeorm';
   import { EntityHelper } from 'src/utils/entity-helper';
   
   @Entity()
   export class Post extends EntityHelper {
     @PrimaryGeneratedColumn()
     id: number;
   
     @Column()
     title: string;
   
     @Column()
     body: string;
   
     // Here any fields what 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

---

Seeding

Creating seeds

1. Go to src/database/seeds and create directory for your seed. For example post

2. Create 2 files: module and service. For example: post-seed.module.ts and post-seed.service.ts:

   // /src/database/seeds/post/post-seed.module.ts
   
   import { Module } from '@nestjs/common';
   import { TypeOrmModule } from '@nestjs/typeorm';
   import { Post } from 'src/posts/entities/post.entity';
   import { PostSeedService } from './post-seed.service';
   
   @Module({
     imports: [TypeOrmModule.forFeature([Post])],
     providers: [PostSeedService],
     exports: [PostSeedService],
   })
   export class PostSeedModule {}

   // /src/database/seeds/post/post-seed.service.ts
   
   import { Injectable } from '@nestjs/common';
   import { InjectRepository } from '@nestjs/typeorm';
   import { Post } from 'src/posts/entities/post.entity';
   import { Repository } from 'typeorm';
   
   @Injectable()
   export class PostSeedService {
     constructor(
       @InjectRepository(Post)
       private repository: Repository<Post>,
     ) {}
   
     async run() {
       const count = await this.repository.count();
   
       if (count === 0) {
         await this.repository.save(
           this.repository.create({
             title: 'Hello',
             body: 'World',
           }),
         );
       }
     }
   }

3. Go to src/database/seeds/seed.module.ts and add your module to imports. For example:

   // /src/database/seeds/seed.module.ts
   
   // Some code here...
   import { PostSeedModule } from './post/post-seed.module';
   
   @Module({
     imports: [
       // Some code here...
       PostSeedModule,
     ],
   })
   export class SeedModule {}

4. Go to src/database/seeds/run-seed.ts and invoke method run from your service in runSeed function. For example:

   // /src/database/seeds/run-seed.ts
   
   // Some code here...
   import { PostSeedService } from './post/post-seed.service';
   
   const runSeed = async () => {
     // Some code here...
     await app.get(PostSeedService).run();
     // Some code here...
   };
   // Some code here...

5. Run npm run seed:run

Run seed

npm run seed:run

---

Performance optimization

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.

---

Next: Auth

GitHub: https://github.com/atlas-cli/nestjs-boilerplate