[TS] mysql2 패키지 모듈화

 

패키지 설치

npm install --save mysql2
npm install --save-dev @types/node

 

모듈화

import mysql, {ConnectionOptions, ResultSetHeader, RowDataPacket} from "mysql2/promise";
import "../config/env";
import {DatabaseError} from "../middleware/errors/DatabaseError";

const access: ConnectionOptions = {
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    port: Number(process.env.DB_PORT || "3306"),
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0,
    connectTimeout: 10000, // MySQL 서버에 연결할 때의 타임아웃(ms 단위)
    enableKeepAlive: true // 연결이 일정 시간 동안 비활성 상태일 때에도 끊어지지 않도록
};

export const database = mysql.createPool(access);

export const selectList = async <T extends RowDataPacket>(statement: string): Promise<T[]> => {
    let connection;

    try {
        connection = await database.pool.promise().getConnection();
        const result = await connection.query<T[]>(statement);
        return result[0];
    } catch (e: unknown) {
        if (e instanceof Error) {
            throw new DatabaseError(e.message);
        }
        throw new Error("DB Unexpected Error");
    } finally {
        if (connection) {
            connection.release();
        }
    }
};

export const selectOne = async <T extends RowDataPacket>(statement: string): Promise<T> => {
    let connection;

    try {
        connection = await database.pool.promise().getConnection();
        const result = await connection.query<T[]>(statement)

        return result[0][0];
    } catch (e: unknown) {
        if (e instanceof Error) {
            throw new DatabaseError(e.message);
        }
        throw new Error("DB Unexpected Error");
    } finally {
        if (connection) {
            connection.release();
        }
    }
};

export const mutation = async (statement: string): Promise<ResultSetHeader> => {
    let connection;

    try {
        connection = await database.pool.promise().getConnection();
        const result = await connection.query<ResultSetHeader>(statement);
        return result[0];
    } catch (e: unknown) {
        if (e instanceof Error) {
            throw new DatabaseError(e.message);
        }
        throw new Error("DB Unexpected Error");
    } finally {
        if (connection) {
            connection.release();
        }
    }
}

 

사용 방법

import {mutation, selectList, selectOne} from "../../config/db";
import {ResultSetHeader} from "mysql2/promise";
import UserType from "../../types/UserType";

class UserModel {
	static async getAllUsers(): Promise<UserType[]> {
        const statement = `
          SELECT USER_ID
          FROM USER_TABLE
        `;

        return await selectList<UserType>(statement);
      }

      static async getUserById(USER_ID: string): Promise<UserType> {
        const statement = `
          SELECT USER_ID
          FROM USER_TABLE
          WHERE USER_ID = '${USER_ID}'
        `;

        return await selectOne<UserType>(statement);
      }

      static async createUser(user: UserType): Promise<ResultSetHeader> {
        const statement = `INSERT INTO ...`
        return await mutation(statement);
      }
  
      tatic async updateUser(user: UserType): Promise<ResultSetHeader> {
        const statement = `UPDATE ...`
        return await mutation(statement);
      }
      
      static async deleteUser(user: UserType): Promise<ResultSetHeader> {
        const statement = `DELETE ...`
        return await mutation(statement);
      }
  }

export default UserModel;

 

 

---

 

Using MySQL2 with TypeScript | Quickstart