MySQL module for NestJS 8.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
Then create a NestJS project
$ nest new app$ cd app// start the application$ npm run start:dev
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"
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
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 {}
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
UsersModule:
import { Module } from '@nestjs/common';import { UsersService } from './users.service';import { UsersController } from './users.controller';@Module({ controllers: [UsersController], providers: [UsersService]})export class UsersModule {}
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;}
Remember to install this package before creating the dto class for the upgrade.
$ npm i @nestjs/mapped-types
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){}
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; }}
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 (!result) { 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; }}
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); }}
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 {}
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); }}
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": "[email protected]"}' 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": "[email protected]}' 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
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
Original Link: https://dev.to/tony133/mysql-module-for-nestjs-8x-framework-5gh9
Dev To
An online community for sharing and discovering great ideas, having debates, and making friendsMore About this Source Visit Dev To