241210 노드 (RESTful DB 연동)

241210 에이콘 아카데미 수업에 기반하여 작성되었음을 알립니다.

---

노드 실습 (RESTful DB 연동 O, 모듈화)

package.json

{
  "name": "project08_restdb",
  "version": "1.0.0",
  "type": "module",
  "main": "app.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start": "nodemon app.js"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "description": "",
  "dependencies": {
    "cors": "^2.8.5",
    "express": "^4.21.2",
    "mariadb": "^3.4.0",
    "mysql2": "^3.11.5",
    "nodemon": "^3.1.7"
  }
}

 

db.js > dbporcess.js > sangdataRoute.js > app.js
pool          처리                   라우팅                메인

db.js

import mariadb from 'mariadb';

export default mariadb.createPool({
    host:"localhost",
    user:"root",
    password:"1111",
    database:"test",
    port:3306,
    connectionLimit:5
});

dbprocess.js

import pool from './db.js';

// 전체 자료 읽기
export const getAllSangdata = async () => {
    const conn = await pool.getConnection();
    const rows = await conn.query("select * from sangdata");
    conn.release();
    return rows;
};

// 1개 자료 읽기
export const getSangdataByCode = async (code) => {
    const conn = await pool.getConnection();
    const rows = await conn.query("select * from sangdata where code=?", [code]);
    conn.release();
    return rows;
};

// 자료 추가
export const createSangdata = async ({code, sang, su, dan}) => {
    const conn = await pool.getConnection();
    const rows = await conn.query("insert into sangdata values(?,?,?,?)" , [code, sang, su, dan]); // 순서 주의, 순서대로 매핑됨
    conn.release();
    return rows;
};

// 자료 수정 PUT : /sangdata/3
export const updateSangdata = async (code, {sang, su, dan}) => {
    const conn = await pool.getConnection();
    const rows = await conn.query("update sangdata set sang=?, su=?, dan=? where code=?" , [sang, su, dan, code]);
    conn.release();
    return rows;
};

// 자료 삭제
export const deleteSangdata = async (code) => {
    const conn = await pool.getConnection();
    const rows = await conn.query("delete from sangdata where code=?" , [code]);
    conn.release();
    return rows;
};

sangDataRoute.js

import express from 'express';

import {getAllSangdata, getSangdataByCode, createSangdata, updateSangdata, deleteSangdata} from './dbprocess.js';

const router = express.Router();

// read all
router.get('/', async(req, res) => {
    try {
        const rows = await getAllSangdata();
        res.json(rows);
    } catch (error) {
        res.status(500).json({error:error.message});
    }
});

// read one
router.get('/:code', async(req, res) => {
    const {code} = req.params;
    try {
        const rows = await getSangdataByCode(code);
        if(rows.length === 0) {
            return res.status(404).json({error:'data not found'});
        }
        res.json(rows[0]);
    } catch (error) {
        res.status(500).json({error:error.message});
    }
});

// insert
router.post('/', async(req, res) => {
    const {code,sang,su,dan} = req.body;
    try {
        const result = await createSangdata({code,sang,su,dan});
        res.status(201).json({id:result.insertId.toString(), code,sang,su,dan});
    } catch (error) {
        res.status(500).json({error:error.message});
    }
});

// update
router.put('/:code', async(req, res) => {
    const {code} = req.params;
    const {sang,su,dan} = req.body;
    try {
        const result = await updateSangdata(code,{sang,su,dan});
        if(result.affectedRows === 0) {
            return res.status(404).json({error:'update data not found'});
        }
        res.json({code,sang,su,dan});
    } catch (error) {
        res.status(500).json({error:error.message});
    }
});

// delete
router.delete('/:code', async(req, res) => {
    const {code} = req.params;

    try {
        const result = await deleteSangdata(code);
        if(result.affectedRows === 0) {
            return res.status(404).json({error:'delete data not found'});
        }
        res.json({message:'data deleted'});
    } catch (error) {
        res.status(500).json({error:error.message});
    }
});

export default router;

app.js

// 메인
import path from 'path';
import express from 'express';
import { fileURLToPath } from 'url';
import sangRoute from './sangDataRoute.js';

const app = express();

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

app.use(express.json());
app.use(express.static(path.join(__dirname, 'public')));

app.get('/', (req, res) => {
    res.send('출발! /sangdata, /sangdata:1 ... ')
});

app.use('/sangdata',sangRoute);

const PORT = process.env.PORT || 3000;

const startServer = (app, port) => {
    app.listen(port, () => {
        console.log(`서버 서비스 중 http://localhost:${port}`);
    }).on('error', (err) => {
        console.error(`server failed to start : ${err.message}`);
        process.exit(1);
    });
};

startServer(app, PORT);

전체 조회

1개 조회

수정

추가

삭제

에러

🚨"error" : "Do not know how to serialize a BigInt"

res.status(201).json({id:result.insertId, code,sang,su,dan});
                                          🔽
res.status(201).json({id:result.insertId.toString(), code,sang,su,dan});

💡 insertId의 타입이 BigInt였다. toString()을 붙여줘서 해결!

 

클라이언트단

select.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<body>
    <h2>상품 정보</h2>
    <div>
        <button onclick="location.href='/insert.html'">상품 추가</button>
    </div>

    <table id="data-table">
        <thead>
            <tr>
                <th>코드</th><th>품명</th><th>수량</th><th>단가</th><th>처리</th>
            </tr>
        </thead>
        <tbody>
            <!-- 자료 출력 -->
        </tbody>
    </table>

    <script>
        async function getAllData(){
            try {
                const response = await fetch("/sangdata");
                const datas = await response.json();
                const tableBody = document.querySelector("#data-table tbody");
                tableBody.innerHTML = "";

                datas.forEach(row => {
                    const tr = document.createElement('tr');
                    tr.innerHTML = `
                        <td>${row.code}</td>
                        <td>${row.sang}</td>
                        <td>${row.su}</td>
                        <td>${row.dan}</td>
                        <td>
                            <button onclick="editData(${row.code})">수정</button>    
                            <button onclick="deleteData(${row.code})">삭제</button>    
                        </td>
                    `;

                    tableBody.appendChild(tr);
                })
            } catch (error) {
                console.log('읽기 오류 : ', error);
                alert('읽기 오류 : ', error);
            }
        }

        document.addEventListener("DOMContentLoaded", getAllData);
    </script>
</body>
</html>

insert.html

추가

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<body>
    <h2>상품 추가</h2>
    <form onsubmit="insertData(event)">
        코드 : <input type="text" id="code" name="code" required /></br>
        품명 : <input type="text" id="sang" name="sang" required /></br>
        수량 : <input type="number" id="su" name="su" required /></br>
        단가 : <input type="number" id="dan" name="dan" required /></br>
        <button type="submit">상품 추가</button>
    </form>
    <pre id="output"></pre>

    <script>
        async function insertData(event) {
            event.preventDefault();

            const idata = {
                code:document.getElementById('code').value,
                sang:document.getElementById('sang').value,
                su:document.getElementById('su').value,
                dan:document.getElementById('dan').value,
            };

            try {
                const response = await fetch('/sangdata', {
                    method: "POST",
                    headers:{
                        "Content-Type":"application/json"
                    },
                    body:JSON.stringify(idata),
                });

                if(response.ok){
                    alert("상품 추가 완료");
                    window.location.href = '/select.html';
                } else {
                    const result = await response.json();
                    document.getElementById("output").textContent = `오류 : ${JSON.stringify(result, null, 2)}`;
                }
            } catch (error) {
                console.error('추가 오류 : ', error);
                document.getElementById('output').textContent = `오류 : ${error.message}`;
            }
        }
    </script>
</body>
</html>

select.html

삭제

        // delete
        async function deleteData(code) {
            if(confirm("진짜로??")) {
                const response = await fetch(`/sangdata/${code}`, {method:'DELETE'});
                const result = await response.json();

                if(result.message) {
                    getAllData(); // 삭제 후 전체 자료 읽기
                } else {
                    alert(result.error || '삭제 에러 났다!');
                }
            }
        }

 

수정

select.html

1개 읽기

        // edit
        async function editData(code) {
            try {
                const response = await fetch(`/sangdata/${code}`);
                const data = await response.json();
                
                if(data.error) {
                    alert(data.error);
                    return;
                }

                location.href = `/update.html?code=${data.code}&sang=${data.sang}&su=${data.su}&dan=${data.dan}`;
            } catch (error) {
                console.log('수정 자료 읽기 오류 : ', error);
            }
        }

update.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<body>
    <h2>상품 수정</h2>
    <form id="update-form" onsubmit="updateData(event)">
        코드 : <input type="text" id="code" name="code" readonly/></br>
        품명 : <input type="text" id="sang" name="sang" required/></br>
        수량 : <input type="number" id="su" name="su" required/></br>
        단가 : <input type="number" id="dan" name="dan" required/></br>
        <button type="submit">수정</button>
    </form>

    <script>
        function updateFormQueryParams(){

        }
        async function updateData(event) {
            alert('a');
        }

        document.addEventListener('DOMContentLoaded', updateFormQueryParams);
    </script>
</body>
</html>

 

내일 이어서!