Configuring a Database (PostgreSQL or MySQL) for NestJS

NestJS, a progressive Node.js framework, provides robust tools for integrating with databases like PostgreSQL and MySQL. This guide walks you through the process of setting up and configuring either PostgreSQL or MySQL within your NestJS application.

Choosing Your Database: PostgreSQL vs MySQL

Both PostgreSQL and MySQL are powerful relational database management systems (RDBMS). The choice depends on your project's specific requirements. Here's a brief comparison:

• PostgreSQL: Known for its adherence to standards, extensibility, and advanced features like JSON support and geospatial data types. Often preferred for complex applications with demanding data integrity requirements.
• MySQL: Widely used, mature, and performant. Suitable for many web applications, particularly those that prioritize speed and simplicity.

Installing Necessary Drivers and Establishing the Connection

The primary method for connecting to databases in NestJS is through the @nestjs/typeorm module. This module leverages TypeORM, an Object-Relational Mapper (ORM), to simplify database interactions.

Step 1: Install Dependencies

First, install @nestjs/typeorm, TypeORM itself, and the specific database driver for either PostgreSQL or MySQL:

PostgreSQL:

npm install --save @nestjs/typeorm typeorm pg

MySQL:

npm install --save @nestjs/typeorm typeorm mysql2

Explanation:

• @nestjs/typeorm: Provides the NestJS module that integrates TypeORM.
• typeorm: The TypeORM library itself.
• pg (PostgreSQL): The Node.js driver for PostgreSQL.
• mysql2 (MySQL): A modern and faster MySQL driver for Node.js.

Step 2: Configure the TypeORM Module

Import the TypeOrmModule in your AppModule (or another relevant module) and configure it with your database connection details. Replace the placeholders with your actual database credentials.

 import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';

@Module({
  imports: [
    TypeOrmModule.forRoot({
      type: 'postgres', // Or 'mysql'
      host: 'your_host',
      port: 5432,      // Default PostgreSQL port
      username: 'your_username',
      password: 'your_password',
      database: 'your_database_name',
      entities: [__dirname + '/**/*.entity{.ts,.js}'], // Path to your entity files
      synchronize: true, // Auto-creates database schema based on entities (ONLY FOR DEV - see notes below)
    }),
  ],
  controllers: [],
  providers: [],
})
export class AppModule {} 

Explanation:

• type: Specifies the database type ('postgres' or 'mysql').
• host: The database server's hostname or IP address.
• port: The database server's port. The default ports are 5432 for PostgreSQL and 3306 for MySQL.
• username: The database user.
• password: The password for the database user.
• database: The name of the database.
• entities: An array of paths to your entity files. TypeORM uses these files to define your database schema. The provided path __dirname + '/**/*.entity{.ts,.js}' will look for `.entity.ts` or `.entity.js` files in the current directory and all subdirectories. Adjust this path as needed.
• synchronize: IMPORTANT: When set to true, TypeORM will automatically create or update your database schema based on your entity definitions. This is extremely useful for development but should never be used in production. In production, use database migrations to manage schema changes. Setting this to `true` in production can lead to data loss.

Step 3: Create Entities

Entities represent your database tables as classes. Define your entities with TypeORM decorators.

 // src/user/user.entity.ts

import { Entity, PrimaryGeneratedColumn, Column } from 'typeorm';

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ length: 50 })
  firstName: string;

  @Column({ length: 50 })
  lastName: string;

  @Column({ default: true })
  isActive: boolean;
} 

Explanation:

• @Entity(): Marks the class as a database entity.
• @PrimaryGeneratedColumn(): Defines the primary key column, which will be auto-generated.
• @Column(): Defines a regular column in the database table. You can specify column options like length, type, nullable, etc.

Step 4: Create a Service and Repository

Use a service to encapsulate database logic and a repository to interact with TypeORM.

 // src/user/user.service.ts

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { User } from './user.entity';

@Injectable()
export class UserService {
  constructor(
    @InjectRepository(User)
    private usersRepository: Repository<User>,
  ) {}

  async findAll(): Promise<User[]> {
    return this.usersRepository.find();
  }

  async findOne(id: number): Promise<User | undefined> {
    return this.usersRepository.findOne({ where: { id } });
  }

  async create(user: Partial<User>): Promise<User> {
    const newUser = this.usersRepository.create(user);
    return this.usersRepository.save(newUser);
  }
} 

Explanation:

• @InjectRepository(User): Injects the repository for the User entity. This allows you to use TypeORM methods like find(), findOne(), create(), and save().
• The UserService provides methods to interact with the database to retrieve, create, and update user data.

Step 5: Use the Service in a Controller

Inject the service into your controller and use it to handle requests.

 // src/user/user.controller.ts

import { Controller, Get, Post, Body, Param } from '@nestjs/common';
import { UserService } from './user.service';
import { User } from './user.entity';

@Controller('users')
export class UserController {
  constructor(private readonly userService: UserService) {}

  @Get()
  async findAll(): Promise<User[]> {
    return this.userService.findAll();
  }

  @Get(':id')
  async findOne(@Param('id') id: string): Promise<User | undefined> {
    return this.userService.findOne(Number(id));
  }

  @Post()
  async create(@Body() user: Partial<User>): Promise<User> {
    return this.userService.create(user);
  }
} 

Explanation:

• The UserController defines routes (/users) for retrieving and creating user data.
• It injects the UserService and uses its methods to handle the requests.

Important Considerations:

• Database Migrations: As mentioned, using synchronize: true is not recommended for production environments. Use database migrations to manage schema changes safely and consistently. TypeORM provides migration tools to help you with this.
• Connection Pooling: TypeORM handles connection pooling internally. However, you can configure connection pooling options in the TypeOrmModule.forRoot() configuration for fine-grained control.
• Error Handling: Implement proper error handling in your services and controllers to gracefully handle database errors.
• Environment Variables: Store your database credentials in environment variables to avoid hardcoding them in your application code.