서버/Express

ExpressJS MySQL CRUD

realtrynna 2022. 5. 14. 15:47

ExpressJS MySQL CRUD

폴더 구조

 

라이브러리

 

회원가입

import express from "express";
import {
  isAuthenticated,
  isNotAuthenticated,
} from "../middleware/middleware.js";
import {
  authJoin,
  authLogin,
} from "../controllers/authController.js";

const router = express.Router();

router.post("/join", isNotAuthenticated, authJoin);
router.post("/login", isNotAuthenticated, authLogin);

export default router;

routes > auth.js

 

import passport from "passport";
import bcrypt from "bcrypt";
import { 
  db,
  userQuery,
  authQuery,              
} from "../database/index.js";

const authJoin = async (req, res, next) => {
  const { name, password, email, gender } = req.body;
  try {
    await db.query("START TRANSACTION");
    const searchName = await userQuery.searchName(name);
    if (searchName.length > 0) return res.redirect("/?error=이미 가입된 회원입니다.");
    const hash = await bcrypt.hash(password, 12);
    const joinUser = await authQuery.joinUser(name, hash);
    const userId = joinUser.insertId;
    const user = await authQuery.infoUser(userId, email, gender);
    await db.query("COMMIT");
    res.json(user);
  } catch (err) {
    await db.query("ROLLBACK");
    console.error(err);
    next(err);
  }
}

export {
  authJoin,
  authLogin,
}

controllers > authController.js

 

import mysql from "mysql2";
import dotenv from "dotenv";

dotenv.config();
const pool = mysql.createPool({
  host: "localhost",
  user: process.env.DB_ID,
  database: process.env.DB,
  password: process.env.DB_PW,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
});

pool.getConnection(async (err, conn) => {
  try {
    conn.release();
  }
  catch (err) {}
    err ? console.error(err) : console.log("db connect");
});

const db = pool.promise();

const authQuery = {
  joinUser: async (name, password) => {
    const [ sql ] = await db.query(`
      INSERT INTO users
        (name, password)
        VALUES
        ("${name}", "${password}")
    `);
    return sql;
  },
  infoUser: async (fk, email, gender) => {
    const [ sql ] = await db.query(`
      INSERT INTO infos
        (fk_user_id, email, gender)
        VALUES
        (${fk}, "${email}", "${gender}")
    `);
  },
}

export {
  db,
  userQuery,
  authQuery,
  postQuery,
}

database > index.js

 

  • post /auth/join 요청이 들어오면 authJoin 함수가 실행된다.
  • 먼저 기존 유저인지 검사 후 아니면 회원가입을 진행한다.
  • 유저와 세부 정보 테이블이 따로 분리돼있으므로 먼저 유저 테이블에 이름과 패스워드를 넣는다.
  • 그 결과를 바탕으로 세부 정보 테이블에 외래키와 나머지 정보들을 넣는다.
  • 유저 테이블에 데이터가 들어가야 세부 정보 테이블에도 넣을 수 있으므로 트랜잭션을 사용했다.

 

로그인

import passport from "passport";
import bcrypt from "bcrypt";
import { 
  db,
  userQuery,
  authQuery,              
} from "../database/index.js";

const authLogin = async (req, res, next) => {
  passport.authenticate("local", (authError, user, info) => {
    if (authError) {
      console.error(authError);
      next(authError);
    }
    if (!user) return res.redirect(`/?error=${ info.message }`);
    return req.login(user, (loginError) => {
      if (loginError) {
        console.error(loginError);
        next(loginError);
      }
      return res.redirect("/main");
    });
  })(req, res, next);
}

export {
  authJoin,
  authLogin,
}

controllers > authController.js

 

import mysql from "mysql2";
import dotenv from "dotenv";

dotenv.config();
const pool = mysql.createPool({
  host: "localhost",
  user: process.env.DB_ID,
  database: process.env.DB,
  password: process.env.DB_PW,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
});

pool.getConnection(async (err, conn) => {
  try {
    conn.release();
  }
  catch (err) {}
    err ? console.error(err) : console.log("db connect");
});

const db = pool.promise();

const userQuery = {
  usersPosts: async () => {
    const [ sql ] = await db.query(`
      SELECT * FROM users U
      JOIN infos I
        ON U.user_id = I.fk_user_id
      JOIN posts P
        ON U.user_id = P.fk_user_id
    `);
    return sql;
  },
  searchId: async (id) => {
    const [ sql ] = await db.query(`
      SELECT * FROM users U
      JOIN infos I
        ON U.user_id = I.fk_user_id
      WHERE U.user_id = ${id}
    `);
    return sql;
  },
  searchName: async (name) => {
    const [ sql ] = await db.query(`
      SELECT * FROM users
      WHERE name = "${name}"
    `);
    return sql;
  },
}

export {
  db,
  userQuery,
  authQuery,
  postQuery,
}

database > index.js

 

import passport from "passport";
import passportLocal from "passport-local";
import bcrypt from "bcrypt";
import { db } from "../database/index.js";

const LocalStrategy = passportLocal.Strategy;

export default () => {
  passport.use(new LocalStrategy({
    usernameField: "name",
    passwordField: "password",
  }, async (name, password, done) => {
    try {
      const sql = `SELECT * FROM users WHERE name = "${name}"`;
      const [ exist ] = await db.query(sql);
      const user = exist[0];
      if (user) {
        const compare = await bcrypt.compare(password, user.password);
        if (compare) {
          done(null, user);
        } else {
          done(null, false, { message: "비밀번호 불일치" });
        }
      } else {
        done(null, false, { message: "가입되지 않은 회원" });
      }
    } catch (err) {
      console.error(err);
      done(err);
    }
  }));
};

passport > localStrategy.js

 

import passport from "passport";
import local from "./localStrategy.js";

import { db } from "../database/index.js";

export default () => {
  passport.serializeUser((user, done) => {
    done(null, user.user_id);
  });
  passport.deserializeUser((id, done) => {
    const user = `SELECT * FROM users WHERE user_id = ${id}`;
    db.query(user)
      .then(res => done(null, res[0]))
      .catch(err => done(err));
  });
  local();
}

passport > index.js

 

import express from "express";
import morgan from "morgan";
import nunjucks from "nunjucks";
import path from "path";
import dotenv from "dotenv";
import cookieParser from "cookie-parser";
import session from "express-session";
import passport from "passport";

import indexRouter from "./routes/index.js";
import authRouter from "./routes/auth.js";
import postRouter from "./routes/post.js";
import passportConfig from "./passport/index.js";

dotenv.config();
const app = express();
const __dirname = path.resolve();
passportConfig();
app.set("port", process.env.NODE_ENV || 1000);
app.set("view engine", "html");
nunjucks.configure("views", {
  express: app,
  watch: true,
});

app.use(morgan("dev"));
app.use("/image", express.static(path.join(__dirname, "images")));
app.use(express.static(path.join(__dirname, "public")));
app.use(express.json());
app.use(express.urlencoded({ extended: true }));
app.use(cookieParser(process.env.COOKIE_SECRET));
app.use(session({
  resave: false,
  saveUninitialized: false,
  secret: process.env.COOKIE_SECRET,
  cookie: {
    httpOnly: true,
    secure: false,
  },
}));

app.use(passport.initialize());
app.use(passport.session());

app.use("/", indexRouter);
app.use("/auth", authRouter);
app.use("/post", postRouter);

app.use((req, res, next) => {
  const error = new Error(`$${req.method} ${req.url} 존재하지 않습니다.`);
  error.status = 404;
  next(error);
});

app.use((err, req, res, next) => {
  res.locals.message = err.message;
  res.locals.error = process.env.NODE_ENV !== "production" ? err : {};
  res.status(err.status || 500);
  res.render("error");
});

const server = app.listen(app.get("port"), () => {
  console.log("1000");
});

app.js

 

  • port /auth/login 요청이 들어오면 authLogin 함수가 실행된다.
  • 그 후 패스포트 전략을 사용하여 검증이 끝나면 /main 페이지로 리다이렉트시킨다.
  • 패스포트는 세션에 의존하므로 반드시 세션 미들웨어 아래 라우터 미들웨어 위에 initialize/session을 호출한다.

 

게시글 작성

import express from "express";
import {
  isAuthenticated,
  isNotAuthenticated,
} from "../middleware/middleware.js";
import {
  postWrite,
  postUpdate,
  postDelete,
  multerNone,
  multerImage,
} from "../controllers/postController.js";

const router = express.Router();

router.post("/write", isAuthenticated, multerImage.fields([
  { name: "image1" },
  { name: "image2" },
  { name: "image3" },
]), postWrite);


export default router;

routes > post.js

 

import multer from "multer";
import path from "path";
import {
  userQuery,
  postQuery,
} from "../database/index.js";

const multerNone = multer();
const multerImage = multer({
  storage: multer.diskStorage({
    destination(req, file, done) {
      done(null, "images/")
    },
    filename(req, file, done) {
      const ext = path.extname(file.originalname);
      done(null, path.basename(file.originalname, ext) + Date.now() + ext);
    },
  }),
  limits: { fileSize: 5 * 1024 * 1024 },
});

const postWrite = async (req, res, next) => {
  try {
    await postQuery.writePost(
      req.user[0].user_id,
      req.body.title,
      req.body.content,
      req.files["image1"]?.[0].filename ?? null,
      req.files["image2"]?.[0].filename ?? null,
      req.files["image3"]?.[0].filename ?? null
    );
    res.redirect("/notice");
  } catch (err) {
    console.error(err);
    next(err);
  }
}

export {
  postWrite,
  postUpdate,
  postDelete,
  multerNone,
  multerImage,
}

controllers > postController.js

 

import mysql from "mysql2";
import dotenv from "dotenv";

dotenv.config();
const pool = mysql.createPool({
  host: "localhost",
  user: process.env.DB_ID,
  database: process.env.DB,
  password: process.env.DB_PW,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
});

pool.getConnection(async (err, conn) => {
  try {
    conn.release();
  }
  catch (err) {}
    err ? console.error(err) : console.log("db connect");
});

const db = pool.promise();

const postQuery = {
  searchId: async (id) => {
    const [ sql ] = await db.query(`
      SELECT * FROM users U
      JOIN posts P
        ON U.user_id = P.fk_user_id
      WHERE P.post_id = ${id}
    `);
    return sql;
  },
  writePost: async (fk, title,  content, image1, image2, image3) => {
    const[ sql ] = await db.query(`
      INSERT INTO posts 
        (fk_user_id, title, content, image1, image2, image3)   
        VALUES (${fk}, "${title}", "${content}", "${image1}", "${image2}", "${image3}")
    `);
    return sql;
  },
  deletePost: async (id) => {
    const [ sql ] = await db.query(`
      DELETE FROM posts
      WHERE post_id = ${id}
    `);
    return sql;
  },
  updatePost: async (id, title, content) => {
    const [ sql ] = await db.query(`
      UPDATE posts
      SET
        title = "${title}",
        content = "${content}"
      WHERE
        post_id = ${id}
    `);
    return sql;
  },
}

export {
  db,
  userQuery,
  authQuery,
  postQuery,
}

database > index.js

 

  • post /post/write 요청이 들어오면 postWrite 함수가 실행된다.
  • 게시글 정보(req.body)와 유저 정보(req.user)를 넣는다.
  • multer로 여러 개의 이미지를 fields 메서드(하나면 single)로 받는다.
  • 게시글의 이미지는 NULL 값이 될 수 있으므로 존재하면 이미지의 파일 이름을 넣고 없으면 NULL 값으로 넣는다.

 

게시글 업데이트

import express from "express";
import {
  isAuthenticated,
  isNotAuthenticated,
} from "../middleware/middleware.js";
import {
  postWrite,
  postUpdate,
  postDelete,
  multerNone,
  multerImage,
} from "../controllers/postController.js";

const router = express.Router();

router.put("/update/:id", isAuthenticated, postUpdate);

export default router;

routes > post.js

 

import multer from "multer";
import path from "path";
import {
  userQuery,
  postQuery,
} from "../database/index.js";

const postUpdate = async (req, res, next) => {
  try {
    await postQuery.updatePost(
      req.params.id,
      req.body.title,
      req.body.content
    );
    res.redirect("/notice");
  } catch (err) {
    console.error(err);
    next(err);
  }
}

export {
  postWrite,
  postUpdate,
  postDelete,
  multerNone,
  multerImage,
}

controllers > postController.js

 

import mysql from "mysql2";
import dotenv from "dotenv";

dotenv.config();
const pool = mysql.createPool({
  host: "localhost",
  user: process.env.DB_ID,
  database: process.env.DB,
  password: process.env.DB_PW,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
});

pool.getConnection(async (err, conn) => {
  try {
    conn.release();
  }
  catch (err) {}
    err ? console.error(err) : console.log("db connect");
});

const db = pool.promise();

const postQuery = {
  updatePost: async (id, title, content) => {
    const [ sql ] = await db.query(`
      UPDATE posts
      SET
        title = "${title}",
        content = "${content}"
      WHERE
        post_id = ${id}
    `);
    return sql;
  },
}

export {
  db,
  userQuery,
  authQuery,
  postQuery,
}

database > index.js

 

  • put /post/update/:id 요청이 들어오면 postUpdate 함수가 실행된다.
  • 게시글 번호(req.params.id)와 업데이트 정보(req.body)를 받아 업데이트한다.

 

게시글 삭제

import express from "express";
import {
  isAuthenticated,
  isNotAuthenticated,
} from "../middleware/middleware.js";
import {
  postWrite,
  postUpdate,
  postDelete,
  multerNone,
  multerImage,
} from "../controllers/postController.js";

const router = express.Router();

router.delete("/delete/:id", isAuthenticated, postDelete);


export default router;

routes / post.js

 

import multer from "multer";
import path from "path";
import {
  userQuery,
  postQuery,
} from "../database/index.js";

const postDelete = async (req, res, next) => {
  try {
    await postQuery.deletePost(req.params.id);
    res.redirect("/notice");
  } catch (err) {
    console.error(err);
    next(err);
  }
}

export {
  postWrite,
  postUpdate,
  postDelete,
  multerNone,
  multerImage,
}

controllers > postController.js

 

import mysql from "mysql2";
import dotenv from "dotenv";

dotenv.config();
const pool = mysql.createPool({
  host: "localhost",
  user: process.env.DB_ID,
  database: process.env.DB,
  password: process.env.DB_PW,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
});

pool.getConnection(async (err, conn) => {
  try {
    conn.release();
  }
  catch (err) {}
    err ? console.error(err) : console.log("db connect");
});

const db = pool.promise();

const postQuery = {
  deletePost: async (id) => {
    const [ sql ] = await db.query(`
      DELETE FROM posts
      WHERE post_id = ${id}
    `);
    return sql;
  },
}

export {
  db,
  userQuery,
  authQuery,
  postQuery,
}

database > index.js

 

  • delete /post/delete/:id 요청이 들어오면 postDelete 함수가 실행된다.
  • 게시글 번호(req.params.id)를 받아 해당하는 게시글을 삭제한다.

'서버 > Express' 카테고리의 다른 글

ExpressJS CSRF  (0) 2022.06.19
ExpressJS Sequelize transaction  (0) 2022.06.17
ExpressJS CORS(Cross-Origin-Resource-Sharing)  (0) 2022.03.28
ExpressJS JWT  (0) 2022.03.26
ExpressJS method-override  (0) 2022.03.22

'서버/Express'의 다른글

  • 현재글ExpressJS MySQL CRUD

관련글

댓글

티스토리툴바