アプリケーションの作成

アプリケーションは、Express Generatorを使い作成します。

データベースへの接続は、mysql2を使います。

example
- bin
  - www
- node_modules
  - ...
- public
  - images
  - javascripts
  - stylesheets
    - style.css
- routes
  - members
- views
  - members
    - add.ejs
    - delete.ejs
    - edit.ejs
    - index.ejs
    - show.ejs
  - error.ejs
- app.js
- package-lock.json
- package.json

データベースの設定

db.js

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  database: 'users',
  password: 'password',
});

module.exports = pool;

メインプログラム

app.js

var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');

var membersRouter = require('./routes/members');

var app = express();

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

app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

app.use('/', membersRouter);

// catch 404 and forward to error handler
app.use(function(req, res, next) {
  next(createError(404));
});

// error handler
app.use(function(err, req, res, next) {
  // set locals, only providing error in development
  res.locals.message = err.message;
  res.locals.error = req.app.get('env') === 'development' ? err : {};

  // render the error page
  res.status(err.status || 500);
  res.render('error');
});

module.exports = app;

ルーティング

/routes/members.js

var express = require('express');
var router = express.Router();
const pool = require('../db');
const mysql = require('mysql2/promise');

router.get('/', function(req, res, next){
  (async () => {
    try {
      const [results, fields] = await pool.query('SELECT * FROM users');
      let data = { users: results };
      res.render('members/index', data);
    } catch (err) {
      console.log(err);
    }
  })();
});

// レコードの追加
router.get('/add', (req, res, next) => {
  res.render('members/add');
});

router.post('/add', (req, res, next) => {
  let nm = req.body.name;
  let ag = req.body.age;
  let data = { 'name' : nm, 'age' : ag };

  (async () => {
    try {
      const [results, fields] = await pool.query('insert into users set ?', data);
      res.redirect('/');
    } catch (err) {
      console.log(err);
    }
  })();
});

// レコードの表示
router.get('/show/:id', (req, res, next) => {
  let id = req.params.id;

  (async () => {
    try {
      const [results, fields] = await pool.query('SELECT * FROM users where id=?', id);
      let data = { user: results[0] }
      res.render('members/show', data);
    } catch (err) {
      console.log(err);
    }
  })();
});

// 編集画面の遷移
router.get('/edit/:id', (req, res, next) => {
  let id = req.params.id;

  (async () => {
    try {
      const [results, fields] = await pool.query('SELECT * FROM users where id=?', id);
      let data = { user: results[0] }
      res.render('members/edit', data);
    } catch (err) {
      console.log(err);
    }
  })();
});

// 編集フォーム送信の処理
router.post('/edit', (req, res, next) => {
  let id = req.body.id;
  let nm = req.body.name;
  let ag = req.body.age;
  let data = { 'name' : nm, 'age' : ag };

  (async () => {
    try {
      const [results, fields] = await pool.query('UPDATE users SET ? WHERE id = ?', [data, id]);
      res.redirect('/');
    } catch (err) {
      console.log(err);
    }
  })();
});

// 削除画面への遷移
router.get('/delete/:id', (req, res, next) => {
  let id = req.params.id;

  (async () => {
    try {
      const [results, fields] = await pool.query('SELECT * FROM users WHERE id = ?', id);
      let data = { user: results[0] }
      res.render('members/delete', data);
    } catch (err) {
      console.log(err);
    }
  })();
});

// 削除フォームの送信処理
router.post('/delete', (req, res, next) => {
  let id = req.body.id;

  (async () => {
    try {
      const [results, fields] = await pool.query('DELETE FROM users WHERE id = ?', id);
      res.redirect('/');
    } catch (err) {
      console.log(err);
    }
  })();
});

module.exports = router;

ビュー

ビューのそれぞれの HTML

/views/members/index.ejs

<!DOCTYPE html>
<html lang="ja">
<head>
    <meta charset="UTF-8">
    <title>MemberApp - index</title>
</head>
<body>
    <div id="wrapper">
        <div id="header">
            <h1>Member Index</h1>
        </div>
        <div id="content">
            <ul>
                <li><a href="/add">作成</a></li>
            </ul>
            <table>
                <tr>
                    <th>ID</th>
                    <th>Name</th>
                    <th>Age</th>
                </tr>
                <% for(let i in users){ %>
                    <% let obj = users[i]; %>
                    <tr>
                        <td><%= obj.id %></td>
                        <td><%= obj.name %></td>
                        <td><%= obj.age %></td>
                        <td>
                            <a href="/show/<%= obj.id %>">show</a>
                            <a href="/edit/<%= obj.id %>">edit</a>
                            <a href="/delete/<%= obj.id %>">delete</a>
                        </td>
                    </tr>
                <% } %>
            </table>
        </div>
    </div>
</body>
</html>

/views/members/show.ejs

<!DOCTYPE html>
<html lang="ja">
<head>
    <meta charset="UTF-8">
    <title>MemberApp - Show</title>
</head>
<body>
    <div id="main">
        <div id="header">
            <h1>Member Show</h1>
        </div>
        <table>
            <tr>
                <th>Id</th>
                <th>Name</th>
                <th>Age</th>
            </tr>
            
            <tr>
                <td><%= user.id %></td>
                <td><%= user.name %></td>
                <td><%= user.age %></td>
            </tr>
        </table>
    </div>
</body>
</html>

/views/members/add.ejs

<!DOCTYPE html>
<html lang="ja">
<head>
    <meta charset="UTF-8">
    <title>MemberApp - Add</title>
</head>
<body>
    <div id="main">
        <div id="header">
            <h1>Member Add</h1>
        </div>
        <form method="post" action="/add">
            <table>
                <tr>
                    <th>Name</th>
                    <th>Age</th>
                </tr>
                <tr>
                    <td><input type="text" name="name" id="name"></td>
                    <td><input type="text" name="age" id="age"></td>
                </tr>
            </table>
            <p><input type="submit" value="作成"></p>
        </form>
    </div>
</body>
</html>

/views/members/edit.ejs

<!DOCTYPE html>
<html lang="ja">
<head>
    <meta charset="UTF-8">
    <title>>MemberApp - Edit</title>
</head>
<body>
    <div id="main">
        <div id="header">
            <h1>Member Edit</h1>
        </div>
        <form action="/edit" method="post">
            <input type="hidden" name="id" value="<%= user.id %>">
            <table>
                <tr>
                    <td><input type="text" name="name" id="" value="<%= user.name %>"></td>
                </tr>
                <tr>
                    <td><input type="text" name="age" id="" value="<%= user.age %>"></td>
                </tr>
                <tr>
                    <td><input type="submit" value="更新"></td>
                </tr>
            </table>
        </form>
    </div>
</body>
</html>

/views/members/delete.ejs

<!DOCTYPE html>
<html lang="ja">
<head>
    <meta charset="UTF-8">
    <title>MemberApp - Delete</title>
</head>
<body>
    <div id="main">
        <div id="header">
            <h1>Member Delete</h1>
        </div>
        <table>
            <tr>
                <th>ID</th>
                <th>NAME</th>
                <th>AGE</th>
            </tr>
            <tr>
                <td><%= user.id %></td>
                <td><%= user.name %></td>
                <td><%= user.age %></td>
            </tr>
        </table>
        <form action="/delete" method="post">
            <input type="hidden" name="id" value="<%= user.id %>">
            <p><input type="submit" value="削除"></p>
        </form>
    </div>
</body>
</html>