database.md

Database

Table of Contents

• About databases
• Working with database schema (TypeORM)
  • Generate migration
  • Run migration
  • Revert migration
  • Drop all tables in database
• Working with database schema (Mongoose)
  • Create schema
• Seeding (TypeORM)
  • Creating seeds (TypeORM)
  • Run seed (TypeORM)
  • Factory and Faker (TypeORM)
• Seeding (Mongoose)
  • Creating seeds (Mongoose)
  • Run seed (Mongoose)
• Performance optimization (PostgreSQL + TypeORM)
  • Indexes and Foreign Keys
  • Max connections
• Performance optimization (MongoDB + Mongoose)
  • Design schema
• Switch PostgreSQL to MySQL

---

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

Switch PostgreSQL to MySQL

If you want to use MySQL instead of PostgreSQL, you can make the changes after following the complete guide given here.

Once you have completed all the steps, you should have a running app.

If you've made it this far, it only requires a few changes to switch from PostgreSQL to MySQL.

Change the .env file to the following:

DATABASE_TYPE=mysql
# set "localhost" if you are running app on local machine
# set "mysql" if you are running app on docker
DATABASE_HOST=localhost
DATABASE_PORT=3306
DATABASE_USERNAME=root
DATABASE_PASSWORD=secret
DATABASE_NAME=app

Change the docker-compose.yml to the following:

services:
  mysql:
    image: mysql:9.2.0
    ports:
      - ${DATABASE_PORT}:3306
    volumes:
      - mysql-boilerplate-db:/var/lib/mysql
    environment:
      MYSQL_USER: ${DATABASE_USERNAME}
      MYSQL_PASSWORD: ${DATABASE_PASSWORD}
      MYSQL_ROOT_PASSWORD: ${DATABASE_PASSWORD}
      MYSQL_DATABASE: ${DATABASE_NAME}

  # other services here...

volumes:
  # other volumes here...
  mysql-boilerplate-db:

After completing the above setup, run Docker with the following command:

docker compose up -d mysql adminer maildev

All three services should be running as shown below:

Once your services are up and running, you're almost halfway through.

Now install the MySQL client:

npm i mysql2 --save

Delete the existing migration file and generate a new one with the following script:

npm run migration:generate -- src/database/migrations/newMigration --pretty=true

Run migrations:

npm run migration:run

Run seeds:

npm run seed:run:relational

Run the app in dev mode:

npm run start:dev

Open http://localhost:3000

To set up Adminer:

Open the running port in your browser. Open http://localhost:8080

Running App:

---

Previous: Command Line Interface

Next: Auth