패키지 설치
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
Installation
sidorares.github.io
'JavaScript > TypeScript' 카테고리의 다른 글
| [TS] Express 속성 타입 추가하기 (0) | 2024.06.17 |
|---|
