DEV Community

Antonio Tripodi
Antonio Tripodi

Posted on • Edited on

Postgres module for NestJS 8.x/9.x/10.x framework 😻

#postgres #nestjs #typescript #database

In this post I will explain how to use my Node PostgreSQL module for NestJS 😻.
For those unfamiliar or unfamiliar with NestJS, it is a TypeScript Node.js framework that helps you build efficient and scalable enterprise-grade Node.js applications.

For those who have never used node-postgres is a package to integrate PostgreSQL with NodeJS (see here for more information about PostgreSQL and its features).

So let's get started by creating the NestJS app 😻.
Open terminal and install CLI for NestJS, if you already have it installed, skip this step.

$ npm i -g @nestjs/cli
Enter fullscreen mode Exit fullscreen mode

Then create a NestJS project 

$ nest new app
$ cd app
// start the application
$ npm run start:dev
Enter fullscreen mode Exit fullscreen mode

Open the browser on localhost:3000 to verify that hello world is displayed.

then we create a docker-compose.yml file to create the service PostgreSQL

version: "3"

services:
  db:
    image: postgres
    restart: always
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: pass123
      POSTGRES_DB: nest
Enter fullscreen mode Exit fullscreen mode

for those who do not know what docker is I leave the link here for more information Docker.
Well now let's proceed with the package installation.

Install PostgresModule and Node-Postgres dependencies 

$ npm install --save nest-postgres pg
Enter fullscreen mode Exit fullscreen mode

Set PostgresModule in AppModule

import { Module } from '@nestjs/common';
import { PostgresModule } from 'nest-postgres';

@Module ({
   imports: [
    PostgresModule.forRoot({
        connectionString: 'postgresql://postgres:pass123@localhost:5432/nest',
        // or
        // host: 'localhost',
        // database: 'nest',
        // password: 'pass123',
        // user: 'postgres',
        // port: 5432,
    }),
   ],
})
export class AppModule {}
Enter fullscreen mode Exit fullscreen mode

Now let's create a REST API and call it users. We open the terminal and run the commands to create the module, the service and the controller for the users:

$ nest g mo users  # module
$ nest g s users   # service
$ nest g co users  # controller
Enter fullscreen mode Exit fullscreen mode

UsersModule:

import { Module } from '@nestjs/common';
import { UsersService } from './users.service';
import { UsersController } from './users.controller';

@Module({
  controllers: [UsersController],
  providers: [UsersService]
})
export class UsersModule {}
Enter fullscreen mode Exit fullscreen mode

Before we start building our API, create the Data Transfer Objects (Dto) class to create the users

import { IsEmail, IsNotEmpty, IsString } from "class-validator";

export class CreateUserDto {
    @Notempty()
    @IsString()
    firstName: string;

    @Notempty()
    @IsString()
    lastName: string;

    @Notempty()
    @IsString()
    @IsEmail()
    email: string;
}
Enter fullscreen mode Exit fullscreen mode

Remember to install this package before creating the dto class for the upgrade.

$ npm i @nestjs/mapped-types
Enter fullscreen mode Exit fullscreen mode

Well, now to update the users data we extend the CreateUserDto class:

import { PartialType } from '@nestjs/mapped-types';
import { CreateUserDto } from './create-user.dto';

export class UpdateUserDto extends PartialType(CreateUserDto){}
Enter fullscreen mode Exit fullscreen mode

We then implement ours UserService:

import {
  BadRequestException,
  HttpException,
  HttpStatus,
  Injectable,
  NotFoundException,
} from '@nestjs/common';
import { Client } from 'pg';
import { InjectClient } from 'nest-postgres';
import { CreateUserDto } from './dto/create-user.dto';
import { UpdateUserDto } from './dto/update-user.dto';
import { User } from './interfaces/user.interface';

@Injectable()
export class UsersService {
  constructor(@InjectClient() private readonly pg: Client) {}

  public async findAll(): Promise<User[]> {
    const users = await this.pg.query('SELECT * FROM users');
    return users.rows;
  }

  public async findOne(id: string): Promise<User[]> {
    if (!id) {
      throw new BadRequestException();
    }

    const result = await this.pg.query('SELECT * FROM users WHERE id=$1', [id]);

    if (!result) {
      throw new NotFoundException();
    }

    return result.rows;
  }

  public async create(createUserDto: CreateUserDto): Promise<User[]> {
    try {
      const user = await this.pg.query(
        'INSERT INTO users (firstName, lastName, email)  VALUES ($1, $2, $3) RETURNING *',
        [createUserDto.firstName, createUserDto.lastName, createUserDto.email],
      );
      return user.rows;
    } catch (err) {
      throw new HttpException(err, HttpStatus.BAD_REQUEST);
    }
  }

  public async update(id: number, updateUserDto: UpdateUserDto): Promise<User[]> {
    try {
      const users = await this.pg.query(
        'UPDATE users SET firstName=$1, lastName=$2, email=$3 WHERE id=$3 RETURNING *',
        [updateUserDto.firstName, updateUserDto.lastName, updateUserDto.email, id],
      );
      return users.rows;
    } catch (err) {
      throw new HttpException(err, HttpStatus.BAD_REQUEST);
    }
  }

  public async remove(id: string): Promise<void[]> {
    if (!id) {
      throw new BadRequestException();
    }

    const users = await this.pg.query(
      'DELETE FROM users WHERE id=$1 RETURNING *',
      [id],
    );
    return users.rows;
  }
}
Enter fullscreen mode Exit fullscreen mode

To further improve the UsersService we can create a repository where we move all the queries there, like this:

import {
  BadRequestException,
  HttpException,
  HttpStatus,
  Injectable,
  NotFoundException,
} from '@nestjs/common';
import { Client } from 'pg';
import { InjectClient } from 'nest-postgres';
import { CreateUserDto } from '../dto/create-user.dto';
import { UpdateUserDto } from '../dto/update-user.dto';
import { User } from '../interfaces/user.interface';

@Injectable()
export class UsersRepository {
  constructor(@InjectClient() private readonly pg: Client) {}

  public async selectAll(): Promise<User[]> {
    const users = await this.pg.query('SELECT * FROM users');
    return users.rows;
  }

  public async selectOne(id: string): Promise<User[]> {
    if (!id) {
      throw new BadRequestException();
    }

    const result = await this.pg.query('SELECT * FROM users WHERE id=$1', [id]);

    if (!result) {
      throw new NotFoundException();
    }

    return result.rows;
  }

  public async create(createUserDto: CreateUserDto): Promise<User[]> {
    try {
      const { firstName, lastName, email } = createUserDto;
      const user = await this.pg.query(
        'INSERT INTO users (firstName, lastName, email)  VALUES ($1, $2, $3) RETURNING *',
        [firstName, lastName, email],
      );
      return user.rows;
    } catch (err) {
      throw new HttpException(err, HttpStatus.BAD_REQUEST);
    }
  }

  public async update(
    id: number,
    updateUserDto: UpdateUserDto,
  ): Promise<User[]> {
    try {
      const { firstName, lastName } = updateUserDto;

      const users = await this.pg.query(
        'UPDATE users SET firstName=$1, lastName=$2, email=$3 WHERE id=$4 RETURNING *',
        [firstName, lastName, email, id],
      );
      return users.rows;
    } catch (err) {
      throw new HttpException(err, HttpStatus.BAD_REQUEST);
    }
  }

  public async delete(id: string): Promise<void[]> {
    if (!id) {
      throw new BadRequestException();
    }

    const users = await this.pg.query(
      'DELETE FROM users WHERE id=$1 RETURNING *',
      [id],
    );
    return users.rows;
  }
}
Enter fullscreen mode Exit fullscreen mode

Now let's edit the UsersService again as follows:

import { HttpException, HttpStatus, Injectable } from '@nestjs/common';
import { CreateUserDto } from './dto/create-user.dto';
import { UpdateUserDto } from './dto/update-user.dto';
import { User } from './interfaces/user.interface';
import { UsersRepository } from './repositories/users.repository';

@Injectable()
export class UsersService {
  constructor(private usersRepository: UsersRepository) {}

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

  public async findOne(id: string): Promise<User[]> {
    return this.usersRepository.selectOne(id);
  }

  public async create(createUserDto: CreateUserDto): Promise<User[]> {
    try {
      return this.usersRepository.create(createUserDto);
    } catch (err) {
      throw new HttpException(err, HttpStatus.BAD_REQUEST);
    }
  }

  public async update(
    id: number,
    updateUserDto: UpdateUserDto,
  ): Promise<User[]> {
    try {
      return this.usersRepository.update(id, updateUserDto);
    } catch (err) {
      throw new HttpException(err, HttpStatus.BAD_REQUEST);
    }
  }

  public async remove(id: string): Promise<void[]> {
    return this.usersRepository.delete(id);
  }
}
Enter fullscreen mode Exit fullscreen mode

now we add on providers UsersRepository into UserModule like so:

import { Module } from '@nestjs/common';
import { UsersService } from './users.service';
import { UsersController } from './users.controller';
import { UsersRepository } from './repositories/users.repository';

@Module({
  controllers: [UsersController],
  providers: [UsersService, UsersRepository],
})
export class UsersModule {}
Enter fullscreen mode Exit fullscreen mode

Well now let's implement ours UsersController:

import {
  Controller,
  Get,
  Post,
  Body,
  Put,
  Param,
  Delete,
} from '@nestjs/common';
import { UsersService } from './users.service';
import { CreateUserDto } from './dto/create-user.dto';
import { UpdateUserDto } from './dto/update-user.dto';
import { User } from './interfaces/user.interface';

@Controller('user')
export class UsersController {
  constructor(private readonly usersService: UsersService) {}

  @Post()
  create(@Body() createUserDto: CreateUserDto): Promise<User[]> {
    return this.usersService.create(createUserDto);
  }

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

  @Get(':id')
  findOne(@Param('id') id: string): Promise<User[]> {
    return this.usersService.findOne(id);
  }

  @Put(':id')
  update(
    @Param('id') id: string,
    @Body() updateUserDto: UpdateUserDto,
  ): Promise<User[]> {
    return this.usersService.update(+id, updateUserDto);
  }

  @Delete(':id')
  remove(@Param('id') id: string): Promise<void[]> {
    return this.usersService.remove(id);
  }
}
Enter fullscreen mode Exit fullscreen mode

well now we should have our API tested if everything works perfectly this commands from curl or whatever you prefer to use.

    $ curl -H 'content-type: application/json' -v -X GET http://127.0.0.1:3000/api/users  
    $ curl -H 'content-type: application/json' -v -X GET http://127.0.0.1:3000/api/users/:id 
    $ curl -H 'content-type: application/json' -v -X POST -d '{"firstName": "firstName #1", "lastName": "lastName #1", "email": "example@nest.it"}' http://127.0.0.1:3000/api/users 
    $ curl -H 'content-type: application/json' -v -X PUT -d '{"firstName": "firstName update #1", "lastName": "lastName update #1", "email": "example@nest.it}' http://127.0.0.1:3000/api/users/:id 
    $ curl -H 'content-type: application/json' -v -X DELETE http://127.0.0.1:3000/api/users/:id
Enter fullscreen mode Exit fullscreen mode

For more information on node-postgres see here.

I point out that the code written above is only an example to show the functioning of the package by those who perhaps come from orm as typeorm, etc.
Choose the way you feel best for your needs and functionality.

This module is compatible with version 7.x of NestJS 😻.

That's it 😀
Hope it can be useful in your projects.
For anything write me in the comments 😉

Top comments (2)

Some comments may only be visible to logged-in visitors. Sign in to view all comments.

Read next

franckpachot profile image

Aurora Limitless - Collocation (and porting PgBench)

Franck Pachot -

franckpachot profile image

Referential integrity In The Absence Of Foreign Key

Franck Pachot -

franckpachot profile image

Aurora Limitless - Resharding

Franck Pachot -

skoyig profile image

Building an AI Tattoo Generator with Next.js

Matt Phelps -