5, 6주차 실습. MySQL Express 연동

3주차 실습 포스팅에서 MySQL과 Express를 연동하는 방법에 대해 잠깐 다루었습니다. 이번 포스팅에서는 MySQL과 Express를 연동하여 실제 Query를 실행할 수 있는 웹 페이지를 만들어보도록 하겠습니다. 참고로 5, 6주차 실습을 한번에 진행하는 이유는, 6주차 실습이 5주차의 내용을 확장하는 실습이었기 때문입니다.

1. 프로젝트 생성하기

① db 폴더 > Database > week5 폴더를 생성한다.

② cmd 창을 열어 해당경로로 이동한 후, 아래의 명령을 입력한다.

npm init
npm install express mysql2 body-parser nodemon morgan dotenv
npm install @babel/node @babel/core @babel/preset-env
npm link hbs

③ week5를 VSCode에서 열고 구성이 아래와 같게 되도록 파일을 생성한다.

④ sql.js에 아래의 내용을 입력한다.

• 비밀번호를 알맞게 변경한다.
• 4주차에 생성했던 week4 데이터베이스를 사용한다.

import mysql from 'mysql2';

require("dotenv").config();

const pool = mysql.createPool({
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: '{비밀번호}',
    database: 'week4',
});

const promisePool = pool.promise();

// select query
export const selectSql = {
    getBuilding: async () => {
        const sql = `select * from building`;
        const [result] = await promisePool.query(sql);
        return result;
    },
    getDepartment: async () => {
        const sql = `select * from department`;
        const [result] = await promisePool.query(sql);
        return result;
    },
    getRoom: async () => {
        const sql = `select * from room`;
        const [result] = await promisePool.query(sql);
        return result;
    },
    getStudent: async () => {
        const sql = `select * from student`;
        const [result] = await promisePool.query(sql);
        return result;
    },
    getClass: async () => {
        const sql = `select * from class`;
        const [result] = await promisePool.query(sql);
        return result;
    },
}

// insert query
export const insertSql = {
    setStudent: async (data) => {
        const sql = `insert into student values (
            "${data.id}", "${data.name}", "${data.email}", 
            "${data.phone_number}", "${data.department_id}"
        )`
        console.log(data);
        await promisePool.query(sql);
    },
};

// update query
export const updateSql = {
    updateStudent: async (data) => {
        console.log(data);
        const sql = `
            UPDATE Student 
            SET id = ${data.id}, name = "${data.name}", 
                email = "${data.email}", phone_number = "${data.phone_number}",
                department_id = "${data.department_id}"
            WHERE id = ${data.id}`;
        console.log(sql);
        await promisePool.query(sql);
    },
    updateDepartment: async (data) => {
        console.log(data);
        const sql = `
            UPDATE Department 
            SET id = ${data.id}, name = "${data.name}", 
                email = "${data.email}", phone_number = "${data.phone_number}"
            WHERE id = ${data.id}`;
        console.log(sql);
        await promisePool.query(sql);
    }
};

• require("dotenv").config();
  • dotenv 라이브러리를 사용하여 환경 변수를 로드하는 코드로, .env 파일에 저장된 환경 변수를 현재 환경으로 가져와서 사용할 수 있게 해준다.
  • 주로 비밀 정보나 구성 설정을 안전하게 저장하는 데 사용된다.
• pool = mysql.createPool({ ... });: MySQL 데이터베이스에 연결할 연결 pool을 생성한다.
• promisePool = pool.promise();
  • Promise 기반의 연결 풀을 생성한다.
  • 비동기적으로 쿼리를 실행하고 결과를 처리하기 위한 목적으로 사용된다.
• export const
  • selectSql 객체를 통해 그 안에 정의된 함수를 다른 모듈에서 사용할 수 있게 해준다.
  • 다른 모듈에서 해당 변수를 사용하려면 import 문을 사용해야 한다.
• const [result]
  • sql 쿼리의 결과 배열의 첫번째 원소를 result에 넣는다.
  • 3주차 실습에서도 확인했다시피, 두번째 원소부터는 별로 관심 없는 정보들이 담겨있기 때문에 버리는 것이다.

⑤ home.js에 아래의 내용을 입력한다.

import express from 'express';
import { insertSql } from '../database/sql';

const router = express.Router();

router.get('/', (req, res) => {
    res.render('home', { data: " " });
})

router.post('/', (req, res) => {
    const vars = req.body;

    const data = {
        id: vars.id,
        name: vars.name,
        email: vars.email,
        phone_number: vars.phone_number,
        department_id: vars.department_id,
    };
    insertSql.setStudent(data);
})

module.exports = router;

• const router = express.Router();:
  • Express의 Router 객체를 생성한다.
  • 라우팅 경로와 관련된 핸들러를 라우터 객체와 연결하기 위해 사용된다.
• router.get('/', (req, res))
  • / 경로로 들어온 GET 요청을 처리하는 핸들러를 정의한다.
  • 이 핸들러는 을 처리합니다. (req, res) => { ... } 부분은 요청(request) 및 응답(response) 객체에 대한 콜백 함수를 나타냅니다. 이 함수는 클라이언트로부터의 요청을 처리하고 응답을 생성합니다.
  • home.hbs를 렌더링하고, { data: " " } 객체를 템플릿에 전달하는 것은 뷰 템플릿에 표시할 내용이 없음을 의미한다.
• insertSql.setStudent(data);: 방금 생성한 sql.js에서 setStudent 함수를 가져다 쓰고 있다.
• module.exports = router;
  • 다른 파일에서 router 변수에 접근할 수 있도록 해준다.
  • index.js에서 이 router를 사용할 것이다.

⑥ select.js에 아래의 내용을 입력한다.

import express from 'express';
import { selectSql } from '../database/sql';

const router = express.Router();

router.get('/', async (req, res) => {
    const Building = await selectSql.getBuilding();
    const Department = await selectSql.getDepartment();
    const Room = await selectSql.getRoom();
    const Student = await selectSql.getStudent();
    const Class = await selectSql.getClass();

    res.render('select', {
        main_title: "Tables in InhaDB",
        title1: "Building",
        title2: "Department",
        title3: "Room",
        title4: "Student",
        title5: "Class",
        Building,
        Department,
        Room,
        Student,
        Class
    })
})

module.exports = router;

• select.hbs를 렌더링하고, select.hbs에 넣어줄 데이터를 설정하고 있다.
• 참고로 select.js의 라우터의 엔드포인트도 '/'로 home.js랑 같다고 오해할 수도 있는데, 나중에 index.js에서 select라는 prefix를 정의해줄 것이기 때문에 다른 엔드포인트를 갖게 된다.

⑦ update.js에 아래의 내용을 입력한다.

import express from 'express';
import { selectSql, updateSql } from '../database/sql';

const router = express.Router();

router.get('/student', async (_req, res) => {
    const student_res = await selectSql.getStudent();
    console.log(student_res)
    res.render('updateStudent', {
        main_title: "UPDATE 'Student' table",
        student_res,
    });
});
router.post('/student', async (req, res) => {
    const vars = req.body;
    const data = {
        id: vars.id,
        name: vars.name,
        email: vars.email,
        phone_number: vars.phone_number,
        department_id: vars.department_id,
    }
    await updateSql.updateStudent(data);

    res.redirect('/update/student');
})

router.get('/department', async (_req, res) => {
    const department_res = await selectSql.getDepartment();
    console.log(department_res)
    res.render('updateDepartment', {
        main_title: "UPDATE 'Department' table",
        department_res,
    });
});

router.post('/department', async (req, res) => {
    const vars = req.body;
    const data = {
        id: vars.id,
        name: vars.name,
        email: vars.email,
        phone_number: vars.phone_number,
    }
    await updateSql.updateDepartment(data);

    res.redirect('/update/department');
})

module.exports = router;

• res.redirect: update 이후에 update가 반영된 기존 웹 페이지를 반환하기 위해 사용한다.
• 마찬가지로, index.js에서 update라는 prefix를 지정해줄 것이기 때문에 엔드포인트는 /update/student, /update/department가 된다.

⑧ index.js에 아래의 내용을 입력한다.

import express from 'express';
import logger from 'morgan';
import path from 'path';

import homeRouter from '../routes/home';
import updateRouter from '../routes/update';
import selectRouter from '../routes/select';

const PORT = 3000;

const app = express(); 

app.use(express.static(path.join(__dirname, '/src')));
app.use(express.urlencoded({ extended: false })) 
app.use(express.json()); 

app.set('views', path.join(__dirname, '../views')); 
app.set('view engine', 'hbs');

app.use(logger('dev'));

app.use('/', homeRouter);
app.use('/update', updateRouter);
app.use('/select', selectRouter);

app.listen(PORT, () => {
    console.log(`Server is running at http://localhost:${PORT}`)
});

• const app = express();: Express 애플리케이션을 생성한다.
• app.use(express.static(path.join(__dirname, '/src')));: /src 디렉토리에 있는 정적 파일을 제공하기 위한 목적으로 사용된다.
• app.use(express.urlencoded({ extended: false })): 클라이언트가 제출한 폼 데이터를 파싱하여 요청 객체 req에 저장하고, 확장된 URL 인코딩을 허용하지 않는다.
• app.use(express.json());: 클라이언트가 JSON 형식으로 데이터를 보낼 때 요청 객체 req에 파싱하여 저장한다.
• app.set('views', path.join(__dirname, '../views'));: 뷰 템플릿 파일의 디렉토리를 설정한다.
• app.set('view engine', 'hbs');: hbs를 뷰 엔진으로 사용하도록 설정한다.
• app.use(logger('dev'));: dev 형식의 로그 메시지를 콘솔에 기록한다.
• app.use('/', ...Router)
  • 각각 /, /update, /select 경로에 대한 라우터를 등록하고 있다. - 각 URL 경로에 맞는 요청을 처리하고 응답을 생성한다.
• app.listen(PORT, () =>
  • Express 애플리케이션을 포트 3000에서 실행한다.
  • 서버가 시작되면 콘솔에 메시지가 출력된다.

⑨ home.hbs에 아래의 내용을 입력한다.

<h1>Insert Student</h1>
<form name="student" method="post" action="/">
    <div>
        <label for="id">Id</label>
        <input id="id" name="id" type="text" required placeholder="Id" />
    </div>
    <div>
        <label for="name">Name</label>
        <input id="name" name="name" type="text" required placeholder="Name" />
    </div>
    <div>
        <label for="email">E-mail</label>
        <input id="email" name="email" type="text" placeholder="email" />
    </div>
    <div>
        <label for="phone_number">Phone Number</label>
        <input id="phone_number" name="phone_number" type="text" 
        required placeholder="000-0000-0000" />
    </div>
    <div>
        <label for="department_id">Department Id</label>
        <input id="department_id" name="department_id" type="text" required placeholder="department id" />
    </div>
    <div>
        <input type="submit" value="insert" />
    </div>
</form>

⑩ layout.hbs에 아래의 내용을 입력한다.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Inha Database</title>
    <style type="text/css">
        table {
                border-collapse: collapse;
            }

        th, td {
            border: 1px solid black;
            width: 100px;
            height: 20px;
        }
    </style>
</head>
<body>
    {{{body}}}
</body>
</html>

• 모든 hbs에 공통적으로 적용할 레이아웃 파일이다.

⑪ select.hbs에 아래의 내용을 입력한다.

<h1>{{main_title}}</h1>

<!--Building-->
<h2>{{title1}}</h2>

<table>
    <tr>
        <td>Id</td>
        <td>Name</td>
    </tr>
    {{#each Building}}
    <tr>
        <td>{{id}}</td>
        <td>{{name}}</td>
    </tr>
    {{/each}}
</table>

<!--Department-->
<h2>{{title2}}</h2>

<table>
    <tr>
        <td>Id</td>
        <td>Name</td>
        <td>Email</td>
        <td>Phone number</td>
    </tr>
    {{#each Department}}
    <tr>
        <td>{{id}}</td>
        <td>{{name}}</td>
        <td>{{email}}</td>
        <td>{{phone_number}}</td>
    </tr>
    {{/each}}
</table>

<!--Room-->
<h2>{{title3}}</h2>

<table>
    <tr>
        <td>Id</td>
        <td>Name</td>
        <td>Capacity</td>
        <td>Building Id</td>
        <td>Department Id</td>
        <td>Club Id</td>
    </tr>
    {{#each Room}}
    <tr>
        <td>{{id}}</td>
        <td>{{name}}</td>
        <td>{{capacity}}</td>
        <td>{{Building_id}}</td>
        <td>{{department_id}}</td>
        <td>{{club_id}}</td>
    </tr>
    {{/each}}
</table>

<!--Student-->
<h2>{{title4}}</h2>

<table>
    <tr>
        <td>Id</td>
        <td>Name</td>
        <td>Email</td>
        <td>Phone number</td>
        <td>Department Id</td>
    </tr>
    {{#each Student}}
    <tr>
        <td>{{id}}</td>
        <td>{{name}}</td>
        <td>{{email}}</td>
        <td>{{phone_number}}</td>
        <td>{{department_id}}</td>
    </tr>
    {{/each}}
</table>

<!--CLass-->
<h2>{{title5}}</h2>

<table>
    <tr>
        <td>Id</td>
        <td>Name</td>
        <td>Professor</td>
        <td>Numbers Of Participants</td>
        <td>Department Id</td>
        <td>Room Id</td>
    </tr>
    {{#each Class}}
    <tr>
        <td>{{id}}</td>
        <td>{{name}}</td>
        <td>{{Professor}}</td>
        <td>{{numbers_of_participants}}</td>
        <td>{{department_id}}</td>
        <td>{{Room_id}}</td>
    </tr>
    {{/each}}
</table>

• select.js에서 만든 데이터가 {{ ... }} 안에 들어간다.
• {{#each Building}}
  • Building 배열을 순회하고, 각 Building 요소에 대한 HTML을 생성한다.
  • 각 Building은 id와 name 속성을 갖는다.
  • {{/each}}로 #each 반복 블록을 종료한다.

⑫ updateDepartment.hbs에 아래의 내용을 입력한다.

<h1>{{main_title}}</h1>
<table>
    <tr>
        <td>Id</td>
        <td>Name</td>
        <td>Email</td>
        <td>Phone_number</td>
    </tr>
    {{#each department_res}}
    <form method="post">
        <tr>
            <td>{{id}}</td>
            <td style="display: none"><input type="text" name="id" value="{{id}}"></td> 
            <td><input type="text" name="name" value={{name}}></td> 
            <td><input type="text" name="email" value={{email}}></td> 
            <td><input type="text" name="phonenumber" value={{phone_number}}></td> 
            <td><input type="submit" value="Modify" formaction="/update/department"></td>
        </tr>
    </form>
    {{/each}}
</table>

⑬ updateStudent.hbs에 아래의 내용을 입력한다.

<h1>{{main_title}}</h1>
<table>
    <tr>
        <td>Id</td>
        <td>Name</td>
        <td>Email</td>
        <td>Phone_number</td>
        <td>Department_Id</td>
    </tr>
    {{#each student_res}}
    <form method="post">
        <tr>
            <td>{{id}}</td>
            <td style="display: none"><input type="text" name="id" value="{{id}}"></td> 
            <td><input type="text" name="name" value={{name}}></td> 
            <td><input type="text" name="email" value={{email}}></td> 
            <td><input type="text" name="phone_number" value={{phone_number}}></td> 
            <td><input type="text" name="department_id" value={{department_id}}></td>
            <td><input type="submit" value="Modify" formaction="/update/student"></td>
        </tr>
    </form>
    {{/each}}
</table>

• formaction="/update/student": 제출 버튼을 클릭하면 폼 데이터가 /update/student URL로 전송된다.

⑭ Babel.confing.json에 아래의 내용을 입력한다.

{
    "presets" : ["@babel/preset-env"]
}

⑮ package.json의 scripts 부분을 아래와 같이 수정한다.

"scripts": {
  "test" : "echo \"Error: no test specified\" && exit 1",
  "start" : "nodemon --exec babel-node ./src/index.js" 
},

2. 결과 확인하기

1) home.hbs

① localhost:3000으로 접속하면, 아래와 같은 화면이 로드될 것이다.

② 아래와 같이 입력하고 insert 버튼을 클릭해보면, cmd에서 새롭게 insert된 아이템을 확인할 수 있다.

2) select.hbs

localhost:3000/select로 접속하면, 아래와 같은 화면이 로드될 것이다.

3) updateStudent.hbs

① localhost:3000/update/student로 접속하면, 아래와 같은 화면이 로드될 것이다.

② hyunseop987@naver.com을 hyunseop123@naver.com으로 수정해보자.

③ cmd에서 update된 결과를 확인할 수 있다.

4) updateDepartment.hbs

① localhost:3000/update/department로 접속하면, 아래와 같은 화면이 로드될 것이다.

② incom123@inha.ac.kr을 infocom123@inha.ac.kr로 변경해보자.

③ cmd에서 update된 결과를 확인할 수 있다.