DEV Community

Antonio Tripodi
Antonio Tripodi

Posted on • Updated on

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

In this post I will explain how to use my mysql module for NestJS 😻, suitable for using raw SQL, in "style nest".

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-mysql2 is a package to integrate MySQL with NodeJS (see here for more information about MySQL 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 MySQL

version: "3"

services:
  mysql:
    image: mysql:8.0
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: nest
    ports:
      - "3306:3306"
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 MysqlModule and Mysql2 dependencies

$ npm install --save nest-mysql mysql2
Enter fullscreen mode Exit fullscreen mode

Set MysqlModule in AppModule:

import { Module } from '@nestjs/common';
import { MysqlModule } from 'nest-mysql';

@Module ({
   imports: [
    MysqlModule.forRoot({
      host: 'localhost',
      database: 'nest',
      password: 'root',
      user: 'root',
      port: 3306,
    }),
   ],
})
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 {
    @IsNotEmpty()
    @IsString()
    firstName: string;

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

    @IsNotEmpty()
    @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 { InjectClient } from 'nest-mysql';
import { CreateUserDto } from './dto/create-user.dto';
import { UpdateUserDto } from './dto/update-user.dto';
import { Connection } from 'mysql2';

@Injectable()
export class UsersService {
  constructor(@InjectClient() private readonly connection: Connection) {}

  public async findAll(): Promise<User[]> {
    const users = await this.connection.query('SELECT * FROM users');
    const results = Object.assign([{}], users[0]);

    return results;
  }

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

    const user = await this.connection.query('SELECT * FROM users WHERE id=?', [
      id,
    ]);

    if (!user) {
      throw new NotFoundException();
    }
    const result = Object.assign([{}], user[0]);

    return result;
  }

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

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

      const users = await this.connection.query(
        'UPDATE users SET firstName=?, lastName=?, email=? WHERE id=?',
        [firstName, lastName, email, id],
      );
      return users;
    } 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.connection.query('DELETE FROM users WHERE id=?', [
      id,
    ]);
    return users;
  }
}
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 { InjectClient } from 'nest-mysql';
import { Connection } from 'mysql2';
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 connection: Connection) {}

  public async selectAll(): Promise<User> {
    const users = await this.connection.query('SELECT * FROM users');
    const results = Object.assign([{}], users[0]);

    return results;
  }

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

    const user = await this.connection.query('SELECT * FROM users WHERE id=?', [
      id,
    ]);

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

    const result = Object.assign([{}], user[0]);

    return result;
  }

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

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

      const users = await this.connection.query(
        'UPDATE users SET firstName=?, lastName=?, email=? WHERE id=?',
        [firstName, lastName, email, id],
      );
      return users;
    } catch (err) {
      throw new HttpException(err, HttpStatus.BAD_REQUEST);
    }
  }

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

    const user = await this.connection.query('DELETE FROM users WHERE id=?', [
      id,
    ]);
    return user;
  }
}
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 let's add UsersRepository in theUserModule in providers, 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('/api/users')
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-mysql2 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 (0)