MySQL module for NestJS 8.x/9.x/10.x framework 😻
Tags: typescript, nestjs, mysql, database
Published: 2022-06-28 20:10:16


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](https://nestjs.com/), 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](https://github.com/sidorares/node-mysql2) is a package to integrate MySQL with NodeJS (see [here](https://dev.mysql.com/doc/) 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.

```sh
$ npm i -g @nestjs/cli
```
### Then create a NestJS project

```sh
$ 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

```yml
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](https://www.docker.com/get-started).
Well now let's proceed with the package installation.

### Install MysqlModule and Mysql2 dependencies

```sh
$ npm install --save nest-mysql mysql2
```

Set `MysqlModule` in `AppModule`:

```typescript
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:

```sh
$ nest g mo users  # module
$ nest g s users   # service
$ nest g co users  # controller
```

UsersModule:

```typescript
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

```typescript
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.

``` sh
$ npm i @nestjs/mapped-types
```

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

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

export class UpdateUserDto extends PartialType(CreateUserDto){}
```

We then implement ours UserService:

```typescript
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:

```typescript
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:

```typescript
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 the` UserModule` in providers, like so:

```typescript
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`:

```typescript
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](https://github.com/sidorares/node-mysql2).

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 😉