const express = require("express"); const app = express(); const sqlite3 = require("sqlite3"); const path = require("path"); const bodyParser = require("body-parser"); const { resolve } = require("path"); const { rejects } = require("assert"); const dbPath = "app/db/database.sqlite3"; app.use(bodyParser.urlencoded({extended: true})); app.use(bodyParser.json()); app.use(express.static(path.join(__dirname, "public"))); //all app.get("/api/v1/users", (req, res) => { const db = new sqlite3.Database(dbPath); db.all("SELECT * FROM users", (err, rows) => { res.json(rows); }); db.close(); }); //user app.get("/api/v1/users/:id", (req, res) => { const db = new sqlite3.Database(dbPath); const id = req.params.id; db.get(`SELECT * FROM users WHERE id = ${id}`, (err, row) => { res.json(row); }); db.close(); }); //search app.get("/api/v1/search", (req, res) => { const db = new sqlite3.Database(dbPath); const keyword = req.query.q; db.all(`SELECT * FROM users WHERE name LIKE "%${keyword}%"`, (err, rows) => { res.json(rows); }); db.close(); }); const run = async (sql, db, res, message) => { return new Promise((resolve, reject) => { db.run(sql, (err) => { if(err) { res.status(500).send(err); return reject(); } else { res.json({message: message}); return resolve(); } }); }) } app.post("/api/v1/users", async (req, res) => { const db = new sqlite3.Database(dbPath); const name = req.body.name; const profile = req.body.profile ? req.body.profile : ""; const dateOfBirth = req.body.date_of_birth ? req.body.date_of_birth : ""; await run(`INSERT INTO users (name, profile, date_of_birth) VALUES ("${name}", "${profile}", "${dateOfBirth}")`, db, res, "新規ユーザーを作成"); db.close(); }); app.put("/api/v1/users/:id", async (req, res) => { const db = new sqlite3.Database(dbPath); const id = req.params.id; db.get(`SELECT * FROM users WHERE id = ${id}`, async (err, row) => { const name = req.body.name ? req.body.name : row.name; const profile = req.body.profile ? req.body.profile : row.profile; const dateOfBirth = req.body.date_of_birth ? req.body.date_of_birth : row.date_of_birth; await run(`UPDATE users SET name="${name}", profile="${profile}", date_of_birth="${dateOfBirth}" WHERE id=${id}`, db, res, "ユーザー情報更新"); }); db.close(); }); app.delete("/api/v1/users/:id", async (req, res) => { const db = new sqlite3.Database(dbPath); const id = req.params.id; await run(`DELETE FROM users WHERE id=${id}`, db, res, "ユーザー情報削除"); db.close(); }); const port = process.env.PORT || 3000; app.listen(port); console.log("port" + port);
!DOCTYPE html> html lang="en"> head> meta charset="UTF-8"> meta name="viewport" content="width=device-width, initial-scale=1.0"> title>API/title> script src="js/search.js" defer>/script> script src="js/users.js" defer>/script> script src="js/index.js" defer>/script> /head> body> main> h1>API/h1> label for="search">ユーザー名検索/label> input type="text" id="search"> button id="search-btn">検索/button> a href="create.html">新規作成/a> table> thead> tr> th>ID/th> th>ユーザー名/th> th>プロフィール/th> th>誕生日/th> th>登録日時/th> th>更新日時/th> th>menu/th> /tr> /thead> tbody id="users-list"> /tbody> /table> /main> /body> /html>
!DOCTYPE html> html lang="en"> head> meta charset="UTF-8"> meta name="viewport" content="width=device-width, initial-scale=1.0"> title>API/title> script src="js/search.js" defer>/script> script src="js/users.js" defer>/script> script src="js/index.js" defer>/script> /head> body> main> h1>ユーザー編集/h1> label for="uid">ID/label> input type="text" id="uid" placeholder="自動採番" disabled /> label for="name">ユーザー名/label> input type="text" id="name" /> label for="profile">プロフィール/label> input type="text" id="profile" /> label for="date-of-birth">誕生日/label> input type="text" id="date-of-birth" /> button id="save-btn">保存/button> button id="cancel-btn">キャンセル/button> button id="delete-btn">削除/button> /main> /body> /html>
const indexModule = (() => { const path = window.location.pathname; switch(path) { case "/": document.getElementById("search-btn").addEventListener("click", () => { return searchModule.searchUsers(); }); return usersModule.fetchAllUsers(); case "/create.html": document.getElementById("save-btn").addEventListener("click", () => { return usersModule.createUser(); }); document.getElementById("cancel-btn").addEventListener("click", () => { return window.location.href = "/"; }); break; case "/edit.html": const uid = window.location.search.split("?uid=")[1]; document.getElementById("save-btn").addEventListener("click", () => { return usersModule.saveUser(uid); }); document.getElementById("cancel-btn").addEventListener("click", () => { return window.location.href = "/"; }); document.getElementById("delete-btn").addEventListener("click", () => { return usersModule.deleteUser(uid); }); return usersModule.setExistingValue(uid); default: break; } })()
const usersModule = (() => { const BASE_URL = "http://localhost:3000/api/v1/users"; const headers = new Headers(); headers.set("Content-Type", "application/json"); return { fetchAllUsers: async () => { const res = await fetch(BASE_URL); const users = await res.json(); for(let i=0; i td>${user.id}/td> td>${user.name}/td> td>${user.profile}/td> td>${user.date_of_birth}/td> td>${user.created_at}/td> td>${user.updated_at}/td> td>a href="edit.html?uid=${user.id}">編集/a>/td> /tr>` document.getElementById("users-list").insertAdjacentHTML("beforeend", body); } }, createUser: async () => { const name = document.getElementById("name").value; const profile = document.getElementById("profile").value; const dateOfBirth = document.getElementById("date-of-birth").value; const body = { name: name, profile: profile, date_of_birth: dateOfBirth } const res = await fetch(BASE_URL, { method: "POST", headers: headers, body: JSON.stringify(body) }) const resJson = await res.json(); alert(resJson.message); window.location.href = "/"; }, setExistingValue: async (uid) => { const res = await fetch(BASE_URL + "/" + uid); const resJson = await res.json(); document.getElementById("name").value = resJson.name; document.getElementById("profile").value = resJson.profile; document.getElementById("date-of-birth").value = resJson.date_of_birth; }, saveUser: async (uid) => { const name = document.getElementById("name").value; const profile = document.getElementById("profile").value; const dateOfBirth = document.getElementById("date-of-birth").value; const body = { name: name, profile: profile, date_of_birth: dateOfBirth } const res = await fetch(BASE_URL + "/" + uid, { method: "PUT", headers: headers, body: JSON.stringify(body) }) const resJson = await res.json(); alert(resJson.message); window.location.href = "/"; }, deleteUser: async (uid) => { const ret = window.confirm("削除する?"); if(!ret){ return false } else { const res = await fetch(BASE_URL + "/" + uid, { method: "DELETE", headers: headers }) const resJson = await res.json(); alert(resJson.message); window.location.href = "/"; } } } })()