LoginSignup
2
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@kiririri

express+ejs+sqlite3 掲示板

Posted at

はじめに

自分はnodeを勉強する際に「Node.js超入門 | 掌田津耶乃」を参考にして掲示板をつくりました。しかし仕組みや内容を理解できない部分がありました。
もっと理解を深めたいと思い、参考本と同じような開発環境で作られたシンプルな掲示板のサンプルを探していました。
そんなときにこちらのサイトを見つけました。めちゃめちゃわかりやすかったです。こちらのサイトはデータベースにmysqlを使っていますが自分はsqlite3( + DB Browser for SQLite)で作りました。
今回は自分用にそのできあがった掲示版をまとめます.

参考、出典

ディレクトリ構成

Board
　├ bin
　├ node_modules
　├ public
　│　└stylesheets
　│ 　　　└style.css
　│
　├ routes
　│　├ boards.js
　│　├ index.js
　│　├ login.js
　│　├ logout.js
　│　└ register.js
　│
　├ views
　│　├ board.ejs
　│　├ error.ejs
　│　├ index.ejs
　│　├ login.ejs
　│　└ register.ejs
　│　　
　├ app.js
　├ bulletin_board.sqlite3
　├ package-lock.json
　├ package.json
　└ setUser.js

コード

細かい説明、style.css、DBのテーブル構成はこちらのサイトを見てください。

package.json

package.json
{
  "name": "Board",
  "version": "0.0.0",
  "private": true,
  "scripts": {
    "start": "node ./bin/www"
  },
  "dependencies": {
    "cookie-parser": "~1.4.3",
    "debug": "~2.6.9",
    "ejs": "~2.5.7",
    "ejs-lint": "^0.3.0",
    "express": "~4.16.0",
    "express-session": "^1.15.6",
    "http-errors": "~1.6.2",
    "moment": "^2.22.2",
    "morgan": "~1.9.0",
    "sqlite3": "^4.0.4"
  }
}

app.js

app.js
const createError = require('http-errors');
const express = require('express');
const path = require('path');
const cookieParser = require('cookie-parser');
const logger = require('morgan');
const session = require('express-session');

const indexRouter = require('./routes/index');
const boards = require('./routes/boards');
const register = require('./routes/register');
const login = require('./routes/login');
const setUser = require('./setUser');
const logout = require('./routes/logout');

const 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(session({
  secret: 'keyboard cat',
  resave: false,
  saveUninitialized: true
}));

app.use('/', setUser, indexRouter);
app.use('/boards', setUser, boards);
app.use('/register', register);
app.use('/login', login);
app.use('/logout', logout);

// 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;

setUser.js

setUser.js
const sqlite3 = require('sqlite3');

const db = new sqlite3.Database('bulletin_board.sqlite3');

module.exports = (req, res, next) => {
  const userId = req.session.user_id;
  if (userId) {
    let query = 'select user_id, user_name from users where user_id = ?';
    db.serialize(() => {
      db.get(query, [userId], (err, userInf) => {
        if (userInf) {
          res.locals.user = userInf;
          console.log(userInf)
        } else {
          res.locals.user = false;
        }
      });
    });
  };
  next();
}

routes

boards.js
const express = require('express');
const router = express.Router();
const sqlite3 = require('sqlite3');
const moment = require('moment');

const db = new sqlite3.Database('bulletin_board.sqlite3');

router.get('/:id', (req, res, next) => {
  const id = req.params.id;
  db.serialize(() => {
    let q = "select * from boards where board_id = ?";
    let getmessage = "select messages.message, ifnull(users.user_name,'名無し') as user_name, messages.created_at from messages left outer join users on messages.user_id = users.user_id where messages.board_id = ?";
    db.get(q, [id], (err, row) => {
      if (!err) {
        db.all(getmessage, [id], (err, msg) => {
          console.log(msg);
          res.render('board', {
            each: row,
            messageList: msg
          });
        });
      }
    });
  });
});


router.post('/:id', (req, res, next) => {
  const message = req.body.message;
  const id = req.params.id;
  const userId = req.session.user_id ? req.session.user_id : 0;
  const createAt = moment().format('YYYY-MM-DD HH:mm:ss');
  db.run('insert into messages (board_id,message,user_id,created_at) values (?,?,?,?)', id, message, userId, createAt);
  res.redirect('/boards/' + id);
});
module.exports = router;
index.js
const express = require('express');
const router = express.Router();
const moment = require('moment');
const sqlite3 = require('sqlite3');

const db = new sqlite3.Database('bulletin_board.sqlite3');

router.get('/', (req, res, next) => {
  db.serialize(() => {
    let query = "select boards.board_id, boards.user_id, boards.title, ifnull(users.user_name,'名無し') as user_name, boards.created_at from boards left outer join users on boards.user_id = users.user_id";
    db.all(query, (err, rows) => {
      if (!err) {
        // console.log(rows);
        res.render('index', {
          title: 'Enjoy node!!',
          boardList: rows
        });
      }
    });
  });
});

router.post('/', (req, res, next) => {
  const title = req.body.title;
  const userId = req.session.user_id ? req.session.user_id : 0;
  const createdAt = moment().format('YYYY-MM-DD HH:mm:ss');
  db.run('insert into boards (title,created_at,user_id) values (?,?,?)', title, createdAt, userId);
  res.redirect('/');
});

module.exports = router;
login.js
const express = require('express');
const router = express.Router();
const sqlite3 = require('sqlite3');

const db = new sqlite3.Database('bulletin_board.sqlite3');

router.get('/', (req, res) => {
  if (req.session.user_id) {
    res.redirect('/');
  } else {
    res.render('login', {
      title: 'ログイン'
    });
  }
});

router.post('/', (req, res) => {
  const email = req.body.email;
  const password = req.body.password;
  const query = 'select user_id from users where email = ? and password = ? limit 1';
  db.serialize(() => {
    db.all(query, [email, password], (err, rows) => {
      const userId = rows.length ? rows[0].user_id : false;
      if (userId) {
        req.session.user_id = userId;
        res.redirect('/');
      } else {
        res.render('login', {
          title: 'ログイン',
          noUser: 'メールドレスとパスワードが一致するユーザーはいません'
        });
      }
    });
  });
});

module.exports = router;
logout.js
const express = require('express');
const router = express.Router();

router.get('/', (req, res) => {
  req.session.destroy();
  res.redirect('/login');
});

module.exports = router;
register.js
const express = require('express');
const router = express.Router();
const moment = require('moment');
const sqlite3 = require('sqlite3');

const db = new sqlite3.Database('bulletin_board.sqlite3');

router.get('/', (req,res) => {
  res.render('register', {
    title: '新規会員登録'
  });
});

router.post('/', (req, res) => {
  const userName = req.body.user_name;
  const email = req.body.email;
  const password = req.body.password;
  const createdAt = moment().format('YYYY-MM-DD HH:mm:ss');
  db.serialize(() => {
    let emailExistsQuery = "select * from users where email = ? limit 1";
    db.all(emailExistsQuery, [email], (err, mail) => {
      if (mail.length) {
        res.render('register', {
          title: '新規会員登録',
          emailExists: 'すでに登録されているメールアドレスです'
        });
      } else {
        db.run('insert into users (user_name,email,password,created_at) values (?,?,?,?)', userName, email, password, createdAt);
        res.redirect('/login');
      }
    });
  });
});

module.exports = router;

views

board.ejs
<!DOCTYPE html>
<html lang="ja">

<head>
    <title>
        <%= each.title %>
    </title>
    <link rel='stylesheet' href='/stylesheets/style.css'>
</head>

<body>
    <div class="wrapper">
        <p class="main-title">
            <%= each.title %>
        </p>
        <% if(messageList) { %>
        <div class="white-bg">
            <ul class="main-list">
                <% messageList.forEach(function(messageItem) { %>
                <li class="main-list__item">
                    <div class="message">
                        <p class="message__title">
                            <%= messageItem.message %>
                        </p>
                        <p class="message__date">
                            <%= messageItem.user_name %>
                            <%= messageItem.created_at %>
                        </p>
                    </div>
                </li>
                <% }); %>
            </ul>
        </div>
        <% } %>
        <form action="/boards/<%= each.board_id %>" method="post" class="board-form">
            <input type="text" name="message" class="input" required>
            <button type="submit" class="submit">投稿</button>
        </form>
        <a href="/" class="btn">トップへもどる</a>
        <% if(typeof user !== 'undefined') { %>
        <span class='login-user'>
            <%= user.user_name %>さんとしてログインしています</span>
        <% } %>
    </div>
</body>

</html>
index.ejs
<!DOCTYPE html>
<html>

<head>
  <title>
    <%= title %>
  </title>
  <link rel='stylesheet' href='/stylesheets/style.css' />
</head>

<body>
  <div class="wrapper">
    <form action="/" method="post" class="board-form">
      タイトル：<input type="text" name="title" class="input" required><br>
      <br>
      <button type="submit" class="submit">ボード作成</button>
    </form>
    <ul class="main_list">
      <% for(let i in boardList) { %>
      <% let obj = boardList[i] %>
      <li class="main_list__item">
        <a href="/boards/<%= obj.board_id %>" class="board">
          <p class="board__title">
            <%= obj.title %>
          </p>
          <p class="board__date">
            <%= obj.user_name %>
            <%= obj.created_at %>
          </p>
        </a>
      </li>
      <% } %>
    </ul>
    <% if(typeof user !== 'undefined') { %>
    <span class='login-user'>
      <%= user.user_name %>さんとしてログインしています</span>
    <% } %>
  </div>
</body>

</html>
login.ejs
<!DOCTYPE html>
<html>

<head>
  <title>
    <%= title %>
  </title>
  <link rel='stylesheet' href='/stylesheets/style.css'>
</head>

<body>
  <div class='wrapper'>
    <p class='main-title'>
      <%= title %>
    </p>
    <form action='/login' method='post' class='board-form'>
      <span class='label'>Ｅメール</span><input type='email' name='email' class='input' required><br>
      <br>
      <span class='label'>パスワード</span><input type='password' name='password' class='input' required><br>
      <br>
      <button type='submit' class='submit'>ログイン</button>
    </form>
    <% if(typeof noUser !== 'undefined') { %>
    <p class='error'>
      <%= noUser %>
    </p>
    <% } %>
    <a href='/' class='btn'>トップへ戻る</a>
  </div>
</body>

</html>
register.ejs
<!DOCTYPE html>
<html>

<head>
    <title>
        <%= title %>
    </title>
    <link rel='stylesheet' href='/stylesheets/style.css'>
</head>

<body>
    <div class="wrapper">
        <p class="main-title">
            <%= title %>
        </p>
        <form action="/register" method="post" class="board-form">
            <span class="label">ユーザー名</span><input type="text" name="user_name" class="input" required><br>
            <br>
            <span class="label">Eメール</span><input type="text" name="email" class="input" required><br>
            <br>
            <span class="label">パスワード</span><input type="password" name="password" class="input" required><br>
            <br>
            <button type="submit" class="submit">新規会員登録</button>
        </form>
        <% if (typeof emailExists !== 'undefined') { %>
        <p class="error">
            <%= emailExists %>
        </p>
        <% } %>
        <a href="/" class="btn">トップへもどる</a>
    </div>
</body>

</html>

メモ

  • ルーティングの際にコールバック関数で引数に(req,res)を受け取る事が多いが、reqしか使わない場合もresを受け取らないとうまく動かない。今回の場合は、index.jsのgetやregister.jsのgetのルーティング

  • 例えば、'res.locals.user = anyValue'でuserという名前でejsページに値を渡すことができる。
    今回はsetUser.jsで使っており、更にnext()で値をセットするだけのjsファイルが用意されている。このファイルの使うタイミングはapp.jsのここ↓をこうこうこう。。。して調整する。

app.js
app.use('/', setUser, indexRouter);
app.use('/boards', setUser, boards);
  • left outer join onやつ

onの後の条件に一致したusersのカラムをmessagesの指定したカラムと結合し、一つのカラムとして返す。
条件が同じ物をまとめて、一つのカラムとして持ってくる

sql
select messages.message, ifnull(users.user_name,'名無し') as user_name, messages.created_at from messages left outer join users on messages.user_id = users.user_id where messages.board_id = ?
  • module.exports = router;

jsファイルの一番最後に必ずある"module.exports = router;"は、他のファイルでこのファイルを使えるようにするためのもの。
これがないとターミナルでこんな↓エラーが出て実行できない。新しくjsファイルを作ったときにやりがち。

TypeError: Router.use() requires a middleware function but got a Object

　

2
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?