Mastering NestJS: Connecting NestJs to PostgreSQL Database

Introduction

In the ever-changing world of web development, having a dependable and efficient database connection is critical. In our continued exploration of NestJS, we've looked at its essential building components such as modules, controllers, and services. Now it's time to get into a vital part of developing powerful backend applications: using TypeORM to connect NestJS to a PostgresSQL database.

In this post, we'll walk you through the steps of connecting your NestJS application to a PostgresSQL database. We'll go through everything from database configuration to defining database entities, query execution, and database operations. By the end of this lesson, you'll have the knowledge and skills to fully utilize the capabilities of NestJS with PostgresSQL in your project.

I created a GitHub repo for this series accessible at the following address

Installing required packages

NestJS provides the `@nestjs/typeorm package for connecting with SQL databases. TypeORM is the most advanced Object Relational Mapper (ORM) for TypeScript. It works well with the Nest framework because it is written in TypeScript. The approach outlined in this chapter is applicable to any TypeORM-supported database. You only need to install the client API libraries for your chosen database.

To get started, we must first install the required dependencies.

Let's create our first CRUD resource the following cli command:

bash

    npm install --save @nestjs/typeorm typeorm pg

Once the installation process is complete, we can import the TypeOrmModule into the root AppModule.

app.module.ts

import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import { ProductsModule } from './products/products.module';
import { TypeOrmModule } from '@nestjs/typeorm';

@Module({
  imports: [
    ProductsModule,
    TypeOrmModule.forRoot({
      type: 'postgres',
      host: 'localhost',
      port: 5432,
      username: 'root',
      password: '',
      database: 'awesome-nestjs',
      autoLoadEntities: true,
      synchronize: true, // avoid doing this in production please and use migrations instead
    }),
  ],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule {
}

Here is what the official documentation offers us. But we'll come back to this a little later and make some improvements.

Entities

In the previous post we talked about entities. But this time I would like to come back to it because there are some smalls arrangements to be made. Let me show what I'm talking about

app.module.ts

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

// @Entity annotation is used to define our Product class as an entity
// products simply represent the table name (it's optional).
@Entity('products')
export class Product {
  // this anotation is used to define a property as the primary key.
  // it will also make sure the PK is auto increment! pretty cool hien ??? 
  @PrimaryGeneratedColumn()
  id: number;

  @Column() // simply define a property as a table column
  name: string;

  @Column({ nullable: true }) // simply means that the field will can be nullable in the table
  description?: string;

  @Column()
  category: string;

  @Column()
  price: number;

  @Column()
  image: string;
}

Repository Pattern

The Repository Pattern is a commonly used design pattern that helps isolate your application's business logic from the underlying database activities. In NestJS, you can apply this approach smoothly using TypeORM, providing a clean and organized way to connect with your database.

Let's update the product module file by importing the Product entity in the module

products.module.ts

import { Module } from '@nestjs/common';
import { ProductsService } from './products.service';
import { ProductsController } from './products.controller';
import { TypeOrmModule } from '@nestjs/typeorm';
import { Product } from './entities/product.entity';

@Module({
  imports: [TypeOrmModule.forFeature([Product])],
  controllers: [ProductsController],
  providers: [ProductsService],
})
export class ProductsModule {
}

This module uses the forFeature() method to specify which repositories are registered in the current scope. With that in place, we can use the @InjectRepository() decorator to inject the UsersRepository into the ProductsService:

products.service.ts

import { Injectable } from '@nestjs/common';
import { CreateProductDto } from './dto/create-product.dto';
import { UpdateProductDto } from './dto/update-product.dto';
import { Product } from './entities/product.entity';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';

@Injectable()
export class ProductsService {
  constructor(
    @InjectRepository(Product)
    private productRepository: Repository<Product>,
  ) {
  }

  async create(createProductDto: CreateProductDto) {
    // The save method is used to persist an entity, either by creating a new record in the database or updating an existing one
    return await this.productRepository.save(createProductDto);
  }

  async findAll(): Promise<Product[]> {
    // with the find method, we can laod all the products (an array of products)
    return this.productRepository.find();
  }

  async findOne(id: number): Promise<Product | null> {
    // the findoneBy method allow to retrieve the first row that matches with the provided id
    // it could be name, or product reference, but you have to make sure the field is unique
    return await this.productRepository.findOneBy({ id });
  }

  async update(id: number, updateProductDto: UpdateProductDto) {
    // the update method modify an existing record
    return await this.productRepository.update(id, updateProductDto);
  }

  async remove(id: number) {
    // to delete a row by it's id we use delete method
    return await this.productRepository.delete(id);
  }
}

Environment variables

Applications are frequently run in a variety of contexts. Different setup options should be used depending on the environment. For example, the local environment typically relies on unique database credentials that are only valid for the local DB instance. The production environment would have its own set of database credentials. Because configuration variables change, it is recommended to save them in the environment.

Enough chatters, and let's get to the point to install the required node modules.

bash

    npm i --save @nestjs/config

Now we need to create an .env file which will be automatically loaded when our project runs.

.env

POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_USER=root
POSTGRES_PASSWORD=password
POSTGRES_DB=awesome-nestjs

Do not forget to update with your actual database information

Okay, now let's update our app.module.file in order to benefit from the nest config features

app.module.ts

import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import { ProductsModule } from './products/products.module';
import { TypeOrmModule } from '@nestjs/typeorm';
import { ConfigModule, ConfigService } from '@nestjs/config';

@Module({
  imports: [
    ConfigModule.forRoot({ cache: true }),
    ProductsModule,
    TypeOrmModule.forRootAsync({
      imports: [ConfigModule],
      inject: [ConfigService],
      useFactory: (configService: ConfigService) => ({
        type: 'postgres',
        host: configService.get<string>('POSTGRES_HOST'),
        port: configService.get<number>('POSTGRES_PORT'),
        username: configService.get<string>('POSTGRES_USER'),
        password: configService.get<string>('POSTGRES_PASSWORD'),
        database: configService.get<string>('POSTGRES_DB'),
        autoLoadEntities: true,
        synchronize: true,
      }),
    }),
  ],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule {
}

That's it, we've just made our lives easier with the nest/configuration package. But I don't know if you are also a data validation freak. In short, I show you how we can validate the values in an .env file

Schema validation

If required environment variables are not provided or do not meet specific validation requirements, it is usual practice to throw an exception during program starting.

Let's manage this quickly:

bash

    npm install --save joi

Now we can define a Joi validation schema and pass it via the validationSchema property of the forRoot() method's option object, as shown below:

app.module.ts

import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import { ProductsModule } from './products/products.module';
import { TypeOrmModule } from '@nestjs/typeorm';
import { ConfigModule, ConfigService } from '@nestjs/config';
import * as Joi from 'joi';

@Module({
  imports: [
    ProductsModule,
    ConfigModule.forRoot({
      cache: true,
      validationSchema: Joi.object({
        POSTGRES_HOST: Joi.string().required(),
        POSTGRES_PORT: Joi.number().required(),
        POSTGRES_USER: Joi.string().required(),
        POSTGRES_PASSWORD: Joi.string().required(),
        POSTGRES_DB: Joi.string().required(),
      }),
    }),
    TypeOrmModule.forRootAsync({
      imports: [ConfigModule],
      inject: [ConfigService],
      useFactory: (configService: ConfigService) => ({
        type: 'postgres',
        host: configService.get<string>('POSTGRES_HOST'),
        port: configService.get<number>('POSTGRES_PORT'),
        username: configService.get<string>('POSTGRES_USER'),
        password: configService.get<string>('POSTGRES_PASSWORD'),
        database: configService.get<string>('POSTGRES_DB'),
        autoLoadEntities: true,
        synchronize: true,
      }),
    }),
  ],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule {
}

Trust me, you should get in the habit of validating the .env file to avoid running into problems in production

Summary

In this post, we set out to integrate NestJS with PostgresSQL, an open-source relational database. We learnt how to create a smooth link between our NestJS application and the database using TypeORM, providing the groundwork for data-driven apps that are efficient and maintainable.

We covered crucial tasks throughout this lesson, such as creating the database connection, defining database entities, and running queries to conduct CRUD operations. We learned more about how NestJS and TypeORM work together to streamline database interactions, making it easier than ever to design scalable and performant APIs.

In the next post, we'll learn together how to implement the OneToOne, OneToMany and ManyToMany relationships using TypeORM and PostgresSQL

Stay tuned !!