tony133 / nestjs-mysql

Mysql module for Nest framework (node.js) 😻

A progressive Node.js framework for building efficient and scalable server-side applications.

Description

MySQL module for Nest framework (node.js) 😻

Installation

First install the module via yarn or npm or pnpm and do not forget to install the driver package as well:

    $ npm i --save nest-mysql mysql2

or

    $ yarn add nest-mysql mysql2

or

    $ pnpm add nest-mysql mysql2

Table of Contents

• Description
• Table of Contents
• Usage
  • MySqlModule
  • MultiConnectionsDatabase
  • ExampleOfUse

Usage

MySqlModule

MySqlModule is the primary entry point for this package and can be used synchronously

@Module({
  imports: [
    MysqlModule.forRoot({
        host: 'localhost',
        database: 'test2',
        password: 'root',
        user: 'root',
        port: 3306,      
    }),
  ],
})

or asynchronously

@Module({
  imports: [
    MysqlModule.forRootAsync({
      useFactory: () => ({
        host: 'localhost',
        database: 'test',
        password: 'root',
        user: 'root',
        port: 3306,      
      }),
    }),
  ],
})

Example of use

UsersService:

import { Injectable } from '@nestjs/common';
import { InjectClient } from 'nest-mysql';
import { Connection } from 'mysql2';
import { User } from '../interfaces/user.interface';

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

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

    return results;
  }
}

UsersController:

import { Controller, Get } from '@nestjs/common';
import { UsersService } from './users.service';
import { User } from '../interfaces/user.interface';

@Controller()
export class UsersController {
  constructor(private readonly usersService: UsersService) {}

  @Get()
  async getAllUsers(): Promise<User[]> {
    return await this.usersService.findAll();
  }
}

Multi Connections Database

@Module({
  imports: [
    MysqlModule.forRootAsync(
      {
        useFactory: () => ({
          host: 'localhost',
          database: 'test1',
          password: 'root',
          user: 'root',
          port: 3306,      
        }),
      },
      'db1Connection',
    ),
    MysqlModule.forRootAsync(
      {
        useFactory: () => ({
          host: 'localhost',
          database: 'test2',
          password: 'root',
          user: 'root',
          port: 3307,      
        }),
      },
      'db2Connection',
    ),
  ],
  controllers: [],
  providers: [],
})
export class AppModule {}

Usage example with Multi Connection

PostService:

import { Pool } from 'mysql2';
import { InjectConnection } from 'nest-mysql';
import { CreatePostDto } from './dto/create-post.dto';
import { Post } from '../interfaces/post.interface';

@Injectable()
export class PostService {
  constructor(
    @InjectConnection('db2Connection')
    private dbConnection: Pool,
  ) {}

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

    return results;
  }

  public async create(createPostDto: CreatePostDto): Promise<Post[]> {
    try {
      const post = await this.dbConnection.query(
        'INSERT INTO posts (title, description) VALUES (?, ?)',
        [createPostDto.title, createPostDto.description],
      );
      return post;
    } catch (err) {
      throw new HttpException(err, HttpStatus.BAD_REQUEST);
    }
  }
}

UsersService:

import { Pool } from 'mysql2';
import { InjectConnection } from 'nest-mysql';
import { CreateUserDto } from './dto/create-user.dto';
import { User } from '../interfaces/user.interface';

@Injectable()
export class UsersService {
  constructor(
    @InjectConnection('db1Connection')
    private dbConnection: Pool,
  ) {}

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

    return results;
  }

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

For more information on node-mysql see here

Contribute

Feel free to help this library, I'm quite busy with also another Nestjs packages, but the community will appreciate the effort of improving this library. Make sure you follow the guidelines

Stay in touch

• Author - Tony133
• Framework - https://nestjs.com

License

MIT licensed

nestjs-mysql's Issues

How to config `decimalNumbers: true`?

It seems that there is no such config in MysqlModule.forRoot.

[Nest] 30 - 04/14/2024, 4:54:00 AM ERROR [ExceptionsHandler] Can't add new command when connection is in closed state Error: Can't add new command when connection is in closed state

[Nest] 30 - 04/14/2024, 4:54:00 AM ERROR [ExceptionsHandler] Can't add new command when connection is in closed state
Error: Can't add new command when connection is in closed state
Errors are reported automatically at regular intervals

Dependency Dashboard

This issue lists Renovate updates and detected dependencies. Read the Dependency Dashboard docs to learn more.

Open

These updates have all been created already. Click a checkbox below to force a retry/rebase of any.

• chore(deps): update dependency @types/node to v20.14.15
• chore(deps): update dependency @commitlint/cli to v19.4.0
• chore(deps): update dependency eslint to v9
• chore(deps): update typescript-eslint monorepo to v8 (major) (@typescript-eslint/eslint-plugin, @typescript-eslint/parser)
• Click on this checkbox to rebase all open PRs at once

Detected dependencies

github-actions

.github/workflows/codeql-analysis.yml

• actions/checkout v4
• github/codeql-action v3
• github/codeql-action v3
• github/codeql-action v3

.github/workflows/nodejs-environment.yml

• actions/checkout v4
• actions/setup-node v4
• actions/checkout v4
• actions/setup-node v4
• actions/setup-node v4

npm

package.json

• @commitlint/cli 19.3.0
• @commitlint/config-angular 19.3.0
• @nestjs/common 10.3.10
• @nestjs/config 3.2.3
• @nestjs/core 10.3.10
• @nestjs/platform-fastify ^10.3.7
• @nestjs/testing 10.3.10
• @types/jest 29.5.12
• @types/node 20.14.13
• @types/supertest 6.0.2
• @typescript-eslint/eslint-plugin 7.18.0
• @typescript-eslint/parser 7.18.0
• eslint 8.57.0
• eslint-config-prettier 9.1.0
• eslint-plugin-import 2.29.1
• husky 9.1.4
• jest 29.7.0
• mysql2 3.11.0
• prettier 3.3.3
• reflect-metadata 0.2.2
• release-it 17.6.0
• rxjs 7.8.1
• supertest ^7.0.0
• ts-jest 29.2.4
• ts-node 10.9.2
• tsconfig-paths 4.2.0
• typescript 5.5.4
• @nestjs/common ^7.0.0 || ^8.0.0 || ^9.0.0 || ^10.0.0
• @nestjs/core ^7.0.0 || ^8.0.0 || ^9.0.0 || ^10.0.0
• mysql2 ^2.3.3 || ^3.0.0
• reflect-metadata ^0.1.13 || ^0.2.0
• rxjs ^6.6.3 || ^7.2.0

---

• Check this box to trigger a request for Renovate to run again on this repository

Calling stored procedures with variables and then selecting causes parsing errors from MySQL side?

I used a simple calcUserRep procedure with user_id In parameter and reputation Out parameter.

The code in service.ts is also very straightforward, it takes string from dto and runs it as query:

async postSql(dto: SqlPost): Promise<string> {
    const data = await this.connection.query(dto.query);
    return JSON.stringify(Object.assign([{}], data));
  }

However, a single query with CALL and SELECT operators throws error in MySQL:
The query: CALL calcUserRep(1, @rep); SELECT @rep;
The error: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT @rep' at line 1
Inside MySQL Workbench under the same user the query works perfectly.

I also tried running two separate this.connection.query() methods, but seems like MySQL doesn't remember variables between the two.

Is this made by design or this is a bug? And how I can workaround this?

My software: nestjs-mysql 0.0.14, nest 10.3.8