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)