1. Sequelize
- node.js에서 mysql 등 RDBMS를 쉽게 다룰 수 있도록 도와주는 라이브러리
- branch 추가
- 설치
npm i sequelize
- db > database.js : import, 객체 바꿔주기
< db > database.js >
// import mysql from 'mysql2'; // 삭제
import { config } from '../config.js';
import SQ from 'sequelize'; //추가
// 삭제
// const pool = mysql.createPool({
// host: config.db.host,
// port: config.db.port,
// user: config.db.user,
// database: config.db.database,
// password: config.db.password
// });
// export const db = pool.promise();
const { host, user, database, password, port } = config.db;
export const sequelize = new SQ.Sequelize(database, user, password, {
host,
dialect: 'mysql',
logging: false
})
 |
- app.js : import / DB연결 바꿔주기
< app.js >
import express from "express";
import morgan from "morgan";
import tweetsRouter from './router/tweets.js';
import authRouter from './router/auth.js';
import { config } from './config.js';
// import { db } from './db/database.js'; //추가
import { sequelize } from "./db/database.js" //추가
const app = express();
app.use(express.json());
app.use(morgan("dev"));
app.use('/tweets', tweetsRouter);
app.use('/auth', authRouter);
app.use((req, res, next) => {
res.sendStatus(404);
});
//DB 연결 테스트
//1.
//db.getConnection().then(connection => console.log(connection)); //추가
//2.
sequelize.sync().then(()=>{
app.listen(config.host.port);
})
  |
- data > tweets/js : import 바꿔주기
< data>tweets/js >
//import { db } from '../db/database.js';
import SQ from 'sequelize'; // 추가
import { sequelize } from '../db/database.js'; // 추가
import { user } from './auth.js'; // 추가
  |
- data > auth.js 객체, 함수 바꿔주기
< data > auth.js >
// import { db } from '../db/database.js'; //삭제
import SQ from 'sequelize';//추가
import { sequelize } from '../db/database.js'; //추가
const DataTypes = SQ.DataTypes; //추가
export const User = sequelize.define(
'user',
{
id: {
type: DataType.INTEGER,
autoIncrement: true,
allowNull: false,
primaryKey: true
},
username: {
type: DataType.STRING(50),
allowNull: false
},
password: {
type: DataType.STRING(150),
allowNull: false
},
name: {
type: DataType.STRING(50),
allowNull: false
},
url: DataType.STRING(1000)
},
{ timestamp: false }
)
//아이디 (username) 중복검사
export async function findByUsername(username){
// return db.execute('select * from users where username = ?', [username]).then((result) => {
// console.log(result);
// return result[0][0];
// });
return User.findOne({where: {username}});
}
//id 중복검사
export async function findById(id){
// return db.execute('select * from users where id = ?', [id]).then((result) => {
// console.log(result);
// return result[0][0];
// });
return User.findByPk(id);
}
//회원가입
export async function createUser(user){
// console.log(user);
// const {username, hashed, name, email, url} = user;
// return db.execute('insert into users (username, password, name, email, url) values (?, ?, ?, ?, ?)', [username, hashed, name, email, url]).then((result) => {
// console.log(result); // result[0].insertId
// return result[0].insertId;
// });
return User.create(user).then((data) => data.dataValues.id)
}
// //로그인
// export async function login(username){
// return users.find((users) => users.username === username);
// }
      |
- controllar > tweet.js 변경
< controllar > tweet.js >
// 회원가입 함수
export async function signup(req, res, next){
let {username, password, name, email, url} = req.body;
const found = await authRepository.findByUsername(username);
if(found){
return res.status(409).json({message:`${username}이 이미 있습니다`});
}
password = await bcrypt.hash(password, config.bcrypt.saltRounds);
const userId = await authRepository.createUser({username, password, name, email, url});
const token = createJwtToken(userId);
res.status(201).json({token, username});
}
| * 변경 const => let hashed => password |
 |
- postman 확인 (회원가입, 로그인 가능)
- Workbench 확인 (회원가입하면 DB에 잘 들어감)
- data > tweet.js : 객체, 함수 바꿔주기
< data > tweet.js >
//import { db } from '../db/database.js';
import SQ from 'sequelize';
import { sequelize } from '../db/database.js';
import { User } from './auth.js';
const DataTypes = SQ.DataTypes;
const Sequelize = sequelize;
const INCLUDE_USER = {
attributes: [
'id',
'text',
'createdAt',
'userId',
[Sequelize.col('user.name'), 'name'],
[Sequelize.col('user.username'), 'username'],
[Sequelize.col('user.url'), 'url']
],
include: {
model: User,
attributes: [],
}
}
const ORDER_DESC = {
order: [['createdAt', 'DESC']]
}
const Tweet = sequelize.define('tweet', {
id: {
type: DataTypes.INTEGER,
autoIncrement: true,
allowNull: false,
primaryKey: true
},
text: {
type: DataTypes.TEXT,
allowNull: false
}
}, { timestamps: false });
Tweet.belongsTo(User);
// const SELECT_JOIN = 'select tw.id, tw.text, tw.createdAt, tw.userId, us.username, us.name, us.email, us.url from tweets as tw join users as us on tw.userId = us.id';
// const ORDER_DESC = 'order by tw.createdAt desc';
// 모든 트윗을 리턴
// export async function getAll() {
// return db.execute(`${SELECT_JOIN} ${ORDER_DESC}`).then((result) => {
// console.log(result);
// return result;
// });
// }
export async function getAll() {
return Tweet.findAll({...INCLUDE_USER, ...ORDER_DESC});
}
// 해당 아이디에 대한 트윗을 리턴
// export async function getAllByUsername(username){
// return db.execute(`${SELECT_JOIN} where username = ? ${ORDER_DESC}`, [username]).then((result) => {
// console.log(result);
// return result;
// });
// }
export async function getAllByUsername(username){
return Tweet.findAll({...INCLUDE_USER, ...ORDER_DESC, include:{
...INCLUDE_USER.include, where: {username}
}});
}
// 글번호에 대한 트윗을 리턴
// export async function getById(id){
// return db.execute(`${SELECT_JOIN} where tw.id = ? ${ORDER_DESC}`, [id]).then((result) => {
// console.log(result);
// return result;
// });
// }
export async function getById(id){
return Tweet.findOne({ where : {id}, ...INCLUDE_USER});
}
// 트윗을 작성
// export async function create(text, userId){
// return db.execute('insert into tweets (text, userId) values (?, ?)', [text, userId]).then((result) => {
// console.log(result);
// return getById(result[0].insertId);
// });
// }
export async function create(text, userId){
return Tweet.create({text, userId}).then((data) => this.getById(data.dataValues.id));
}
// 트윗을 변경
// export async function update(id, text){
// return db.execute('update tweets set text = ? where id = ?', [text, id]).then((result) => {
// console.log(result);
// return getById(id);
// });
// }
export async function update(id, text){
return Tweet.findByPk(id, INCLUDE_USER).then((tweet)=>{
tweet.text = text;
return tweet.save();
})
}
// 트윗을 삭제
// export async function remove(id){
// return db.execute('delete from tweets where id = ?', [id]);
// }
export async function remove(id){
return Tweet.findByPk(id).then((tweet)=>{
tweet.destroy();
});
}
           |
- 오류수정
더보기
수정하면 업데이트 가능
- postman 확인
- git-hub에 올리기 : Sequelize 적용
 |
'Web > Node.js' 카테고리의 다른 글
| 19. Mongoose (0) | 2024.05.10 |
|---|---|
| 18. MongoDB (0) | 2024.05.09 |
| 16. ORM (0) | 2024.05.03 |
| 15. 환경변수, 웹소켓 (0) | 2024.05.03 |
| 14. Postman 정리, 아이디 중복체크 (0) | 2024.05.02 |
