[Express] MySQL 연동 및 쿼리 수정

YTT.erica·2024년 6월 10일

Express와 mysql 연결

mysql 연결하기

[ myql2 설치 ]

npm install mysql2
npm install express-mysql-session

mysql2: 데이터베이스 연결
express-mysql-session: mysql에 session 연결 및 테이블 생성

[ express와 연결 ]

✏️ app.cjs

const session = require('express-session')

require('dotenv').config()
const { db_info } = require('./config/config.cjs')

const MySQLStore = require('express-mysql-session')(session)
const sessionStore = new MySQLStore(db_info)

app.use(
	session({
		//secret: 'pizzamandu',
		**secret: process.env.SESSION_SECRET,**
		saveUninitialized: true,
		resave: false,
		cookie: {
			secure: false,
			maxAge: 1000 * 60 * 60 * 24
		},
		store: sessionStore
	})
)

session 사용을 위해서 MySQLStore 생성 및 session에 할당

→ session 정보는 store에 기재한 sessionStore에 저장

✏️ mysql.cjs → config.cjs

require('dotenv').config()

**const db_info = {
	host: process.env.DB_HOST,
	port: process.env.DB_PORT,
	user: process.env.DB_USER,
	password: process.env.DB_PASSWORD,
	database: process.env.DB_DATABASE
}**

module.exports = { db_info }

쿼리 사용

각 모델에서 사용하는 쿼리는 하단 참고

[ queryPromise.cjs ]

const conn = mysql.createConnection(db_info)
const { db_info } = require('../config/mysql.cjs')

const queryPromise = query => {
	return new Promise((resolve, reject) => {
		conn.query(query, (err, result) => {
			if (err) {
				console.error(err)
				reject(err)
			} else {
				resolve(result)
			}
		})
	})
}

module.exports = { queryPromise }

각 쿼리마다 공통된 로직을 분리하여 코드 중복 최소화

[ user 모델 ]

✏️ import

const fs = require('fs')
const path = require('path')
const bcrypt = require('bcryptjs')

const queryPromise = require('../tools/queryUtils.cjs')

const {
	getUserQuery,
	nicknameQuery,
	emailQuery,
	addUserQuery,
	updateUserPasswordQuery,
	updateUserProfileQuery,
	deleteUserQuery,
	getPostCountQuery,
	getCommentCountQuery
} = require('../queries/users.cjs')

✏️ 쿼리문으로 로직 구현

// userId 유효성 조회 로직
const checkUserIdModel = async userId => {
	try {
		const query = getUserQuery(userId)
		const result = await queryPromise(query.sql, query.values)
		return result.length !== 0
	} catch (error) {
		console.log(error)
		return -1
	}
}

// userId로 유저 정보 조회
const checkUserModel = async userId => {
	try {
		const query = getUserQuery(userId)
		const result = await queryPromise(query.sql, query.values)
		return result.length !== 0 ? result[0] : null
	} catch (error) {
		console.log(error)
		return -1
	}
}

// 닉네임 조회 (해당 닉네임 존재 유무)
const checkUserNicknameModel = async nickname => {
	try {
		const query = nicknameQuery(nickname)
		const result = await queryPromise(query.sql, query.values)
		return result.length !== 0
	} catch (error) {
		console.log(error)
		return -1
	}
}

// 이메일 조회 (해당 이메일 존재 유무)
const checkUserEmailModel = async email => {
	try {
		const query = emailQuery(email)
		const result = await queryPromise(query.sql, query.values)
		return result.length !== 0
	} catch (error) {
		console.log(error)
		return -1
	}
}

// 유저 등록 (등록한 후 유저 아이디 반환)
const addUserModel = async data => {
	const salt = bcrypt.genSaltSync(10)
	const hash = bcrypt.hashSync(data.password, salt)
	const addData = { email: data.email, nickname: data.nickname, password: hash, profile_image: data.profile_image }
	try {
		const query = addUserQuery(addData)
		const result = await queryPromise(query.sql, query.values)
		return result.insertId
	} catch (error) {
		return -1
	}
}

// 입력한 email과 같은 유저 정보 조회
const checkLogInModel = async email => {
	const query = emailQuery(email)
	try {
		const result = await queryPromise(query.sql, query.values)
		return result.length !== 0 ? result[0] : null
	} catch (error) {
		console.log(error)
		return -1
	}
}

// 로그인 로직
const logInUserModel = async (email, password) => {
	try {
		const user = await checkLogInModel(email)
		if (!user) return false
		if (user === -1) return -1
		const passwordCorrect = await bcrypt.compare(password, user.password)
		return passwordCorrect ? user : false
	} catch (error) {
		console.log(error)
		return -1
	}
}

// 유저 프로필 정보 수정
const updateUserProfileModel = async data => {
	if (!data.nickname && !data.profile_image) return null
	try {
		const query = updateUserProfileQuery(data)
		await queryPromise(query.sql, query.values)
		return true
	} catch (error) {
		console.log(error)
		return -1
	}
}

// 유저 비밀번호 수정
const updateUserPasswordModel = async data => {
	const { userId, password } = data
	if (!userId || !password) return false

	const salt = bcrypt.genSaltSync(10)
	const hash = bcrypt.hashSync(password, salt)
	const updateData = { password: hash, userId }
	const query = updateUserPasswordQuery(updateData)

	try {
		await queryPromise(query.sql, query.values)
		return true
	} catch (error) {
		console.log(error)
		return -1
	}
}

// 유저 회원 탈퇴
const deleteUserModel = async id => {
	if (!id) return false

	const userExists = await checkUserIdModel(id)
	if (!userExists) return false
	if (userExists === -1) return -1

	const query = deleteUserQuery(id)

	try {
		await queryPromise(query.sql, query.values)
		return true
	} catch (error) {
		console.log(error)
		return -1
	}
}

// 본인이 작성한 게시물, 댓글 수 조회
const getUserWriteCount = async userId => {
	try {
		const postQuery = getPostCountQuery(userId)
		const commentQuery = getCommentCountQuery(userId)
		const postCountResult = await queryPromise(postQuery.sql, postQuery.values)
		const commentCountResult = await queryPromise(commentQuery.sql, commentQuery.values)
		return {
			postCount: postCountResult[0]?.count || 0,
			commentCount: commentCountResult[0]?.count || 0
		}
	} catch (error) {
		console.log(error)
		return -1
	}
}

[ posts 모델 ]

✏️ import

const path = require('path')
const fs = require('fs')
const queryPromise = require('../tools/queryUtils.cjs')

const {
	postsQuery,
	postQuery,
	myPostsQuery,
	otherPostsQuery,
	codingPostsQuery,
	addPostQuery,
	updatePostQuery,
	updatePostViewQuery,
	deletePostQuery
} = require('../queries/posts.cjs')

✏️ 쿼리문으로 로직 구현

// 전체 게시물 조회
const getPostsModel = async () => {
	try {
		const query = postsQuery()
		return await queryPromise(query.sql, query.values)
	} catch (error) {
		console.log(error)
		return -1
	}
}

// 특정 게시물 조회
const getPostModel = async id => {
	try {
		const query = postQuery(id)
		return await queryPromise(query.sql, query.values)
	} catch (error) {
		console.log(error)
		return -1
	}
}

// 게시물 조회수 수정
const updatePostViewModel = async id => {
	try {
		const query = updatePostViewQuery(id)
		await queryPromise(query.sql, query.values)
		return true
	} catch (error) {
		console.log(error)
		return -1
	}
}

// 본인이 작성한 게시물 조회
const getMyPostsModel = async userId => {
	try {
		const query = myPostsQuery(userId)
		return await queryPromise(query.sql, query.values)
	} catch (error) {
		console.log(error)
		return -1
	}
}

// 고민 주제 게시물 조회
const getOtherPostsModel = async () => {
	try {
		const query = otherPostsQuery()
		return await queryPromise(query.sql, query.values)
	} catch (error) {
		console.log(error)
		return -1
	}
}

// 개발 주제 게시물 조회
const getCodingPostsModel = async () => {
	try {
		const query = codingPostsQuery()
		return await queryPromise(query.sql, query.values)
	} catch (error) {
		console.log(error)
		return -1
	}
}

// 게시물 작성자 조회
const checkPostOwnerModel = async data => {
	try {
		const query = postQuery(data.postId)
		const post = await queryPromise(query.sql, query.values)
		return post[0].user_id === data.userId
	} catch (error) {
		console.log(error)
		return -1
	}
}

// 게시물 작성
const addPostModel = async data => {
	try {
		const query = addPostQuery(data)
		const result = await queryPromise(query.sql, query.values)
		return result.insertId
	} catch (error) {
		console.log(error)
		return -1
	}
}

// 게시물 수정
const updatePostModel = async data => {
	try {
		const query = updatePostQuery(data)
		await queryPromise(query.sql, query.values)
		return data.id
	} catch (error) {
		console.log(error)
		return -1
	}
}

// 게시물 삭제
const deletePostModel = async id => {
	try {
		const query = deletePostQuery(id)
		await queryPromise(query.sql, query.values)
		return true
	} catch (error) {
		console.log(error)
		return -1
	}
}

//top 게시물 조회
const getTopPostsModel = async () => {
	try {
		const query = getTopPostQuery()
		return await queryPromise(query.sql, query.values)
	} catch (error) {
		console.log(error)
		return -1
	}
}

[ comments 모델 ]

✏️ import

const {
	getCommentQuery,
	getCommentsQuery,
	addCommentQuery,
	updateCommentQuery,
	deleteCommentQuery
} = require('../queries/comments.cjs')

const queryPromise = require('../tools/queryUtils.cjs')

✏️ 쿼리문으로 로직 구현

댓글 추가, 댓글 삭제 트랜잭션 적용 댓글 추가할 때 게시글 comment_count + 1 하는 쿼리실행 → transaction을 통해 두 개의 쿼리 묶기 댓글 삭제할 때 게시글 comment_count - 1 하는 쿼리 실행 → transaction을 통해 두 개의 쿼리 묶기

// 특정 댓글 조회
const getCommentModel = async commentId => {
	const query = getCommentQuery(commentId)
	try {
		return await queryPromise(query.sql, query.values)
	} catch (error) {
		return -1
	}
}

// 댓글 조회
const getCommentsModel = async postId => {
	try {
		const query = getCommentsQuery(postId)
		return await queryPromise(query.sql, query.values)
	} catch (error) {
		console.log(error)
		return -1
	}
}

// 댓글 작성자 확인
const checkCommentOwnerModel = async data => {
	try {
		const comment = await getCommentModel(data.commentId)
		if (comment === -1) return -1
		return comment[0].user_id === data.userId
	} catch (error) {
		console.log(error)
		return -1
	}
}

// 댓글 추가, 트랜잭션 추가
const addCommentModel = async data => {
	const connection = await mysql.createConnection(db_info)

	try {
		await connection.beginTransaction()
		try {
			// 트랜잭션 격리 수준 설정
				await queryPromise('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ', [], connection)
			// 트랜잭션 잠금
			await queryPromise('SELECT * FROM posts WHERE postId = ? FOR UPDATE', [data.postId], connection)
			
			// 댓글 추가
			const query = addCommentQuery(data)
			await queryPromise(query.sql, query.values, connection)
			// 게시물의 댓글 수 업데이트
			await queryPromise(upCommentCountQuery, [data.postId], connection)

			// 트랜잭션 커밋
			await connection.commit()
			return true
		} catch (error) {
			await connection.rollback()
			return -1
		}
	} finally {
		await connection.end()
	}
}

//댓글 수정
const updateCommentModel = async data => {
	try {
		const query = updateCommentQuery(data)
		await queryPromise(query.sql, query.values)
		return true
	} catch (error) {
		console.log(error)
		return -1
	}
}

// 댓글 삭제, 트랜잭션 추가
const deleteCommentModel = async (postId, commentId) => {
	const connection = await mysql.createConnection(db_info)

	try {
		await connection.beginTransaction()
		try {
			// 트랜잭션 격리 수준 설정
			await queryPromise('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ', [], connection)
			// 트랜잭션 잠금
			await queryPromise('SELECT * FROM posts WHERE postId = ? FOR UPDATE', [postId], connection)
			// 댓글 삭제
			const query = deleteCommentQuery(commentId)
			await queryPromise(query.sql, query.values, connection)
			// 게시물의 댓글 수 업데이트
			await queryPromise(downCommentCountQuery, [postId], connection)

			// 트랜잭션 커밋
			await connection.commit()
			return true
		} catch (error) {
			await connection.rollback()
			return false
		}
	} finally {
		await connection.end()
	}
}

Query 모음

[ user 쿼리 ]

SELECT 유저 id 조회

const getUserQuery = id => {
	return {
		sql: `SELECT * 
      FROM users 
      WHERE 
        user_id = ?;`,
		values: [id]
	}
}

SELECT 닉네임 조회

const nicknameQuery = nickname => {
	return {
		sql: `SELECT * 
      FROM users 
      WHERE 
        nickname = ?;`,
		values: [nickname]
	}
}

SELECT 이메일 조회

const emailQuery = email => {
	return {
		sql: `SELECT * 
      FROM users 
      WHERE 
        email = ?;`,
		values: [email]
	}
}

SELECT 유저가 작성한 게시물 수 조회

const getPostCountQuery = id => {
	return {
		sql: `SELECT COUNT(*) AS count 
      FROM posts 
      WHERE 
        user_id = ?;`,
		values: [id]
	}
}

SELECT 유저가 작성한 댓글 수 조회

const getCommentCountQuery = id => {
	return {
		sql: `SELECT COUNT(*) AS count 
      FROM comments 
      WHERE 
        user_id = ?;`,
		values: [id]
	}
}

INSERT 유저 추가

const addUserQuery = data => {
	return {
		sql: `INSERT INTO users (
        email,
        nickname,
        password,
        profile_image,
        created_at
      ) VALUES (
        ?, ?, ?, ?, NOW()
      );`,
		values: [data.email, data.nickname, data.password, data.profile_image]
	}
}

UPDATE 유저 프로필 수정

const updateUserProfileQuery = data => {
	return {
		sql: `UPDATE users
      SET 
        nickname = ?,
        profile_image = ?
      WHERE 
        user_id = ?;`,
		values: [data.nickname, data.profile_image, data.userId]
	}
}

UPDATE 유저 비밀번호 수정

const updateUserPasswordQuery = data => {
	return {
		sql: `UPDATE users
      SET 
        password = ?,
        updated_at = NOW()
      WHERE 
        user_id = ?;`,
		values: [data.password, data.userId]
	}
}

DELETE 유저 삭제

const deleteUserQuery = id => {
	return {
		sql: `DELETE FROM users 
      WHERE 
        user_id = ?;`,
		values: [id]
	}
}

[ post 쿼리 ]

SELECT 게시물 조회

const postsQuery = () => {
	return {
		sql: `SELECT 
        posts.*, 
        users.nickname, 
        users.profile_image
      FROM 
        posts 
      INNER JOIN 
        users 
      ON 
        posts.user_id = users.user_id 
      ORDER BY
        posts.created_at DESC;`,
		values: []
	}
}

SELECT 특정 게시물 조회

const postQuery = id => {
	return {
		sql: `SELECT 
        posts.*, 
        users.nickname, 
        users.profile_image
      FROM 
        posts 
      INNER JOIN 
        users 
      ON 
        posts.user_id = users.user_id 
      WHERE 
        posts.post_id = ?;`,
		values: [id]
	}
}

SELECT 본인 게시물 조회

const myPostsQuery = userId => {
	return {
		sql: `SELECT 
        posts.*, 
        users.nickname, 
        users.profile_image
      FROM 
        posts 
      INNER JOIN 
        users 
      ON 
        posts.user_id = users.user_id 
      WHERE 
        users.user_id = ?
      ORDER BY 
        posts.created_at DESC;`,
		values: [userId]
	}
}

SELECT 고민 주제 게시물 조회

const otherPostsQuery = () => {
	return {
		sql: `SELECT 
        posts.*, 
        users.nickname, 
        users.profile_image
      FROM 
        posts 
      INNER JOIN 
        users 
      ON 
        posts.user_id = users.user_id 
      WHERE 
        posts.type = 'other' 
      ORDER BY 
        posts.created_at DESC;`,
		values: []
	}
}

SELECT 개발 주제 게시물 조회

const codingPostsQuery = () => {
	return {
		sql: `SELECT 
        posts.*, 
        users.nickname, 
        users.profile_image
      FROM 
        posts 
      INNER JOIN 
        users 
      ON 
        posts.user_id = users.user_id 
      WHERE 
        posts.type = 'coding' 
      ORDER BY 
        posts.created_at DESC;`,
		values: []
	}
}

SELECT 탑 10 게시물 조회

const getTopPostQuery = () => {
	return {
		sql: `SELECT post_id, title
      FROM posts
      ORDER BY view DESC
      LIMIT 10;`,
		values: []
	}
}

INSERT 게시물 작성

const addPostQuery = data => {
	return {
		sql: `INSERT INTO posts (
        user_id, 
        post_image, 
        title, 
        content, 
        created_at, 
        type
      ) VALUES (
        ?, ?, ?, ?, NOW(), ?
      );`,
		values: [data.userId, data.postImage, data.title, data.content, data.type]
	}
}

UPDATE 게시물 조회수 수정

const updatePostViewQuery = id => {
	return {
		sql: `UPDATE 
        posts 
      SET 
        view = view + 1 
      WHERE 
        posts.post_id = ?;`,
		values: [id]
	}
}

UPDATE 게시물 수정

const updatePostQuery = data => {
	return {
		sql: `UPDATE posts 
      SET 
        title = ?, 
        content = ?, 
        post_image = ?, 
        type = ?,
        updated_at = NOW()
      WHERE 
        posts.post_id = ?;`,
		values: [data.title, data.content, data.postImage, data.type, data.id]
	}
}

DELETE 게시물 삭제

const deletePostQuery = id => {
	return {
		sql: `DELETE FROM posts 
      WHERE post_id = ?;`,
		values: [id]
	}
}

UPDATE 게시물 댓글수 수정

const upCommentCountQuery = postId => {
	return {
		sql: `UPDATE posts
      SET comment_count = comment_count + 1
      WHERE post_id = ?
      FOR UPDATE;`,
		values: [postId]
	}
}

const downCommentCountQuery = postId => {
	return {
		sql: `UPDATE posts
      SET comment_count = comment_count - 1
      WHERE post_id = ?
      FOR UPDATE;`,
		values: [postId]
	}
}

[ comment 쿼리 ]

SELECT 댓글들 조회

const getCommentsQuery = id => {
	return {
		sql: `SELECT 
        comments.*, 
        users.nickname, 
        users.profile_image 
      FROM 
        comments 
      INNER JOIN 
        users 
      ON 
        comments.user_id = users.user_id 
      INNER JOIN 
        posts 
      ON 
        comments.post_id = posts.post_id 
      WHERE 
        posts.post_id = ?
      ORDER BY 
        comments.created_at DESC;`,
		values: [id]
	}
}

SELECT 특정 댓글 조회

const getCommentQuery = id => {
	return {
		sql: `SELECT * 
      FROM comments 
      WHERE 
        comment_id = ?;`,
		values: [id]
	}
}

INSERT 댓글 작성

const addCommentQuery = data => {
	return {
		sql: `INSERT INTO comments (
        comment,
        post_id,
        user_id,
        created_at
      ) VALUES (
        ?, ?, ?, NOW()
      );`,
		values: [data.comment, data.postId, data.userId]
	}
}

UPDATE 댓글 수정

const updateCommentQuery = data => {
	return {
		sql: `UPDATE comments 
      SET 
        comment = ?,
        updated_at = NOW() 
      WHERE 
        comment_id = ?;`,
		values: [data.commentContent, data.commentId]
	}
}

DELETE 댓글 삭제

const deleteCommentQuery = id => {
	return {
		sql: `DELETE FROM comments 
      WHERE 
        comment_id = ?;`,
		values: [id]
	}
}

YTT.erica

'◡'✿ 꿈을 찾아가보자고~ '◡'✿

이전 포스트

[MySQL] 트랜잭션 격리수준 적용

다음 포스트

[Express] MySQL 연동 및 쿼리 수정

Express와 mysql 연결

mysql 연결하기

✏️ app.cjs

✏️ mysql.cjs → config.cjs

쿼리 사용

✏️ import

✏️ 쿼리문으로 로직 구현

✏️ import

✏️ 쿼리문으로 로직 구현

✏️ import

✏️ 쿼리문으로 로직 구현

Query 모음

[MySQL] 트랜잭션 격리수준 적용

[스프링부트] Jdbc Template 적용하기

0개의 댓글