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 {
@IsNotEmpty()
@IsString()
firstName: string;
@IsNotEmpty()
@IsString()
lastName: string;
@IsNotEmpty()
@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 (!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;
}
}
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": "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
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)