【Express】body-parserでフォームからHerokuのPostgreSQLにCRUD（後編）

Expressの続きです。body-parserでフォームのデータを連携してHerokuのPostgreSQLにCRUD操作する。前回は前編としてPostgreSQLに新規テーブルを作ったりbody-parserを設定しました。今回は後編としてCRUD操作を作ります。それによって見えてきた次の課題もあり、応急処置として過去投稿の削除設定も行いました。それではいきましょう！

【目次】

前回のおさらい
CRUD操作のコード
ローカル環境で動作確認
見えてきた課題：誰でもCRUDできちゃう問題
Readに一定期間で削除する設定を追加
GitHubにコミット
Herokuのデプロイを確認
最後に

※参考：前回記事
【Express】body-parserでフォームからHerokuのPostgreSQLにCRUD（前編） - クモのようにコツコツと

※参考：Node.js / Expressを習得するためにやったことまとめ
qiita.com

前回のおさらい

CRUD操作用にファイルを追加
f:id:idr_zz:20201105064547j:plain

以前、MySQLでCRUD操作した時と同じ構成

※参考：【Express】Fetch APIとMySQLを連携してフォームからデータベースにCRUDする - クモのようにコツコツと

PostgreSQLの新規テーブルを作成 f:id:idr_zz:20201105063112j:plain

body-parserをインポートし、MySQLの時と同様、CRUD操作を分離して書けるように修正した。

詳細は前回の記事を参照。

※参考：【Express】body-parserでフォームからHerokuのPostgreSQLにCRUD（前編） - クモのようにコツコツと

CRUD操作のコード

Create：データ追加

Expressの設定ファイルindex.jsにCRUD操作の処理を書いていく。

PostgreSQLのCRUD操作で参考になった記事

※参考：【PostgreSQL】基本的なCRUD操作 - Qiita

まずはCRUDのC、データ追加（Create）

Create はapp.post()を実行

// Create
app.post('/fetch', jsonParser, (req, res) => {
  const ikku = req.body.ikku;

  const insertSql = "INSERT INTO ikkulist(ikku) VALUES ($1)"
  const selectSql = 'SELECT * FROM ikkulist WHERE ikku = $1';

  pool.query(insertSql, [ikku], (err, result) => {
      if (err) throw err;
      pool.query(selectSql, [ikku], (err, result) => {
          if (err) throw err;
          console.log(result.rows[0]);
          res.send(result.rows[0]);
      });
  });
});

app.post()の第一引数はパスで/fetch、第二引数は前回設定したjsonParser（body-parserのデータをJSON形式で扱う）。第三引数が処理（無名関数）で、引数はリクエストreqとレスポンス res。

ここまではMySQLの時と同じ。

※参考：【Express】Fetch APIとMySQLを連携してフォームからデータベースにCRUDする - クモのようにコツコツと

中身の書き方がMySQLとPostgreSQLでは少し違いがあった。

まず前回は変数bodyでbody情報全体を取得したが、今回は不要になった。

    // const body = req.body;

変数insertSqlINSERT文

    // const insertSql = "INSERT INTO ikkulist SET ?"
    const insertSql = "INSERT INTO ikkulist(ikku) VALUES ($1)"

MySQLではikkulistテーブルのSETのプレースホルダ?に値を指定した
PostgreSQLはikkulist()でかっこの中に取得したいカラム名を書く*1、VALUESの後にカッコでプレースホルダを設定。プレースホルダは$数字と言う書き方。

InsertはSETではなくVALUES

※参考：https://www.postgresql.jp/document/8.0/html/sql-insert.html

プレースホルダは?ではなく$1

※参考：Node.jsからPostgreSQLに接続する(node-postgres) - Symfoware
※参考：https://www.postgresql.jp/document/8.0/html/xfunc-sql.html

query()（接続の変数名MySQLconnection、PostgreSQLはpool）

   // connection.query(insertSql, body, (err, result) => {
    pool.query(insertSql, [ikku], (err, result) => {

MySQLの時は第二引数はbodyを入れた
PostgreSQLの場合はカラム名を配列に入れる（そのため、bodyは不要になった）

配列に入れないと下記のようなエラーになる

  Error: Query values must be an array

エラー：クエリ値は配列である必要があります

カラム名を配列に入れる

※参考：Heroku postgres error: query must be array when selecting data - CodeProject

res.send()

            // res.send(result[0]);
            res.send(result.rows[0]);

MySQLはresult
PostgreSQLはresult.rows

これは前回と同じ

※参考：【Express】body-parserでフォームからHerokuのPostgreSQLにCRUD（前編） - クモのようにコツコツと

Read：データ取得

次はCRUDのR、データ取得（Read）

Readはapp.get()を実行

// Read
app.get('/fetch', jsonParser, (req, res) => {
  const selectSql = "SELECT * FROM ikkulist";
  
  pool.query(selectSql, (err, result) => {  
    if (err) throw err;
  
    console.log(result.rows);
    res.send(result.rows);
  });
});

MySQLとの違いはresultがresult.rowsになるだけ。

            // res.send(result);
            res.send(result.rows);

Update：データ修正

次はCRUDのU、データ修正（Update）

Updateはapp.put()を実行

// Update
app.put('/fetch', jsonParser, (req, res) => {
  const id = req.body.id;
  const ikku = req.body.ikku;

  const updateSql = 'UPDATE ikkulist SET ikku = $1 WHERE id = $2';
  const selectSql = 'SELECT * FROM ikkulist WHERE id = $1';

  pool.query(updateSql, [ikku, id], (err, result) => {
      if (err) throw err;
      console.log(result.row);
      pool.query(selectSql, [id], (err, result) => {
          if (err) throw err;
          console.log(result.rows[0]);
          res.send(result.rows[0]);
      });
  });
});

変数updateSql、selectSql

    // const updateSql = 'UPDATE ikkulist SET ikku = ? WHERE id = ?';
    const updateSql = 'UPDATE ikkulist SET ikku = $1 WHERE id = $2';
    // const selectSql = 'SELECT * FROM ikkulist WHERE id = ?';
    const selectSql = 'SELECT * FROM ikkulist WHERE id = $1';

プレースホルダ?が$1、$2などになる。

内側のquery()

        // connection.query(selectSql, id, (err, result) => {
        pool.query(selectSql, [id], (err, result) => {

カラム名idを配列に入れる[id]

なお、外側のquery()のカラム名は複数で、複数の時はMySQLも配列に入れた。

resultには.rowsがつく

// res.send(result[0]);
res.send(result.rows[0]);

Delete：データ削除

最後、CRUDのD、データ削除（Delete）

Deleteはapp.delete()を実行

// delete
app.delete('/fetch', jsonParser, (req, res) => {
  const id = req.body.id;

  const deleteSql = 'DELETE FROM ikkulist WHERE id = $1';

  pool.query(deleteSql, [id], (err, result) => {
      if (err) throw err;
      res.json({
          "id": Number(id),
          "ikku": "deleted"
      });
  });
});

変数deleteSql、プレースホルダの?を$1に

   // const deleteSql = 'DELETE FROM ikkulist WHERE id = ?';
   const deleteSql = 'DELETE FROM ikkulist WHERE id = $1';

query()の第二引数、カラム名idを配列に入れて[id]に

    // connection.query(deleteSql, id, (err, result) => {
    pool.query(deleteSql, [id], (err, result) => {

ローカル環境で動作確認

ページを表示

ローカル環境でherokuを起動

$ heroku local web

ローカルホスト5000の「fetch_crud.html」を開くと f:id:idr_zz:20201106063715j:plain おお、ページが表示された！前回ターミナルから追加した一句「テストです　テストですったら　テストです」も表示されている！

※参考：【Express】body-parserでフォームからHerokuのPostgreSQLにCRUD（前編） - クモのようにコツコツと

Dev-toolsをのNetworkのHeaders f:id:idr_zz:20201106063946j:plain ページ読み込み時に「/fetch」のAPIを叩いていることがわかる。

Respons f:id:idr_zz:20201106064122j:plain 一句「テストです〜」が返ってきている。

データの追加

一句を追加する「JSや　ああJSや　JSや」 f:id:idr_zz:20201106064405j:plain

「送信」ボタンを押すと下に「過去の一句」に一句が追加された！ f:id:idr_zz:20201106064500j:plain

データの修正

もう一句追加「CSS　嗚呼CSS　CSS」 f:id:idr_zz:20201106064639j:plain

「修正」ボタンを押すと修正欄が表示される f:id:idr_zz:20201106064658j:plain

「嗚呼」を「ああ」に修正 f:id:idr_zz:20201106064737j:plain

「送信」ボタンを押すと修正された！ f:id:idr_zz:20201106064801j:plain

データの削除

一句「CSS〜」の「削除ボタンを押す f:id:idr_zz:20201106064801j:plain

一句が削除された！ f:id:idr_zz:20201106064946j:plain

ターミナルで表示

SELECT文でテーブルikkulistを表示してみる。

DATABASE=> SELECT * FROM ikkulist;

結果、タイムスタンプ付きで最新情報が表示される

 id |                   ikku                   |         createday          
----+------------------------------------------+----------------------------
  1 | テストです　テストですったら　テストです | 2020-11-04 08:31:23.219273
  3 | JSや　ああJSや　JSや                     | 2020-11-04 09:52:40.798696
(2 rows)

フォームからPostgreSQLへのCRUD操作が成功した！

「Control + C」でサーバを一旦閉じる

見えてきた課題：誰でもCRUDできちゃう問題

PostgreSQLでのCRUDを成功し、早速クラウドのHerokuにもデプロイしたいところだが、ここで新たに見えてきた課題がある。

このままHerokuにデプロイしちゃうと誰でもCRUDできちゃう
他人の投稿を後から修正、削除し放題（無秩序）
もしbotやスパムに利用された場合、HerokuのPostgreSQLのフリープランの1万行に至るかも？

この課題を解決するには、ユーザーごとにアカウントを作り、自分の投稿しかCRUDできないようにする必要がある。

これを解決するには「ユーザー認証」と言う仕組みが必要に感じた。そうすれば追加して投稿した本人しかCRUDできなくなる。

Node環境の認証設定は「Passport」というモジュールが定番なようだ

※参考：Passport.js
※参考：Passport | はじめに

他にもログインページ、全体の投稿一覧以外にユーザーのマイページもあったほうがよさそう。

DBも投稿のテーブル以外にユーザー情報のテーブルを作って、テーブル同士を連携（RDBのR、リレーションの部分）する必要もありそう。

ちょっと今回「ついで」でやれる範囲ではないので、別の方法を考えたい。

Readに一定期間で削除する設定を追加

一番簡単な方法は、消える系SNS見たく24時間とかで投稿が消えちゃう仕組み。アプリとしては成立しないが「体験版」的な位置づけ。

Read時に過去の投稿のタイムスタンプを調べて、24時間より以前の投稿はDeleteする。

// Read
app.get('/fetch', jsonParser, (req, res) => {
  const deleteSql = "DELETE FROM ikkulist WHERE createday < now() - interval '1 day'";
  const selectSql = "SELECT * FROM ikkulist";
  
  pool.query(deleteSql, (err, result) => {
    if (err) throw err;
    console.log(result);
    pool.query(selectSql, (err, result) => {  
      if (err) throw err;
  
      console.log(result.rows);
      res.send(result.rows);
    });
  });
});

前回、テーブルにタイムスタンプのカラムを追加したのはこの機能のため！

変数deleteSqlにDELETE文を追加

    const deleteSql = "DELETE FROM ikkulist WHERE createday < now() - interval '1 day'";

WHEREで絞り込み。createdayカラムで現在時刻now()引く1日1 dayより

PostgreSQLの事例

※参考：database - PostgreSQL deleteing old records from log tables - Server Fault
※参考：PostgreSQLで期間を絞って(○日前など)検索する方法 - くわこのpermission denied.

MySQLも似ているが、now()の書き方はPostgreSQLのみのようだった

※参考：https://web-creators-hub.com/%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9/mysql_date_del/

PostgreSQLの日付/時刻関数と演算子

※参考：https://www.postgresql.jp/document/9.4/html/functions-datetime.html

Read設定の外側をDerete設定で囲う（deleteSqlを実行）

    pool.query(deleteSql, (err, result) => {
      if (err) throw err;
      console.log(result);
          // Read設定
  });

動作確認する。ローカル環境でherokuを起動

$ heroku local web

ページをリロードするとターミナルに「DELETE」の結果が表示される。

5:04:03 web.1    |  Result {
5:04:03 web.1    |    command: 'DELETE',
5:04:03 web.1    |    rowCount: 0,
# （中略）
5:04:03 web.1    |  [
5:04:03 web.1    |    { id: 6, ikku: 'JSや　ああJSや　JSや', createday: 2020-11-04T01:44:39.057Z }
5:04:03 web.1    |  ]

まだ投稿から1日経ってないので投稿は削除されない（rowCount: 0）。

「Control + C」で一旦herokuを閉じる。

試しに1 dayを1 hourにしてみる。

  const deleteSql = "DELETE FROM ikkulist WHERE createday < now() - interval '1 hour'";

※参考：https://www.postgresql.jp/document/9.4/html/functions-datetime.html

再度、herokuを起動

$ heroku local web

ページをリロードするとDELETEの結果が表示される。

5:06:08 web.1    |  Listening on 5000
5:06:12 web.1    |  Result {
5:06:12 web.1    |    command: 'DELETE',
5:06:12 web.1    |    rowCount: 1,
# （中略）
5:06:12 web.1    |    rowAsArray: false
5:06:12 web.1    |  }

1件データが削除された！

「Control + C」でherokuを閉じる。

1 hourを1 dayに戻す。

  const deleteSql = "DELETE FROM ikkulist WHERE createday < now() - interval '1 day'";

GitHubにコミット

想定通りの動きができたのでGitHubにコミットをプッシュする。

下記の3つのコマンドを実行する。

$ git add .
$ git commit -m "CRUD処理を追加"
$ git push -u origin main

追加された！ f:id:idr_zz:20201106074325j:plain

※参考：CRUD処理を追加 · ryo-i/Express-Heroku-Test@45bb9e1 · GitHub

Herokuのデプロイを確認

GitHubとHerokuアプリは連携されているので、修正内容がHerokuにもデプロイされているはず。

※参考：【Express】HerokuとGitHubを連携して自動デプロイ（環境変数は除外） - クモのようにコツコツと

ページを読み込み

Herokuアプリを確認する

※参考：ExpressでHerokuのPstgreSQLとbody-parser連携

「/fetch_crud.html」を開くと一句のページが表示されている！ f:id:idr_zz:20201106074419j:plain

データを追加

一句「Reactや　ああReactや　Reactや」を入力 f:id:idr_zz:20201106074423j:plain

「送信」ボタンを押すとデータが追加された！ f:id:idr_zz:20201106074426j:plain

データを修正

もう1句「CSS　嗚呼CSS　CSS」を追加し f:id:idr_zz:20201106074433j:plain

「修正」ボタンを押すと修正欄が表示される f:id:idr_zz:20201106074436j:plain

「嗚呼」を「ああ」に修正 f:id:idr_zz:20201106074440j:plain

「送信」ボタンを押すと修正が反映される f:id:idr_zz:20201106074442j:plain

データを削除

「CSS〜」の「削除」を押すと f:id:idr_zz:20201106074442j:plain

データが削除された！ f:id:idr_zz:20201106074446j:plain

そして、想定通りであれば24時間以上経った投稿は次回のページ読み込み時に削除される。

Herokuアプリはこちら

※参考：ExpressでHerokuのPstgreSQLとbody-parser連携

ソース全体はGitHubを参照

※参考：GitHub - ryo-i/express_mysql_crud: ExpressでFetch APIとMySQLを連携してCRUDする

DB情報は環境変数に書いているため、DB情報は含まれない！

最後に

と言うことでbody-parserでフォームとHerokuのPostgreSQLを連携してCRUD操作しました！

フロントエンド側のFetch APIのコードは何も変えずに、バックエンド側でMySQLに繋ぐかPostgreSQLに繋ぐかを変えたことで、APIを接点とするブラウザとサーバの疎結合を体験できたのはよかったです。

ここに至るまでに長い道のりでしたー。バックエンドの知識が足りないため一つ一つ調べながら試行錯誤しながらやっとたどり着けた、と言う感じです。DBやAPIの動きはこれまでよりも実感としてイメージできるようになりました！

ただ、このままだと誰でも他人の投稿をCRUDできてしまうため、ユーザー認証機能の必要性を感じました。今回はページ読み込み時に24時間以上経った投稿を削除する機能を追加してデプロイしました。

ユーザー認証機能に伴い、DBも投稿のテーブルとユーザー情報のテーブルをリレーショナルに繋げる必要もあります。ここら辺は今後の課題としてまた取り組んで行きたく思います。

あと、MySQLとPostgreSQLでは同じSQL文でも細かいオプションはやはり書き方が違う部分があり、そこら辺は他のRDBもそうなのかなという気はします。そういった差異を吸収する意味でもORMを使うメリットがあるのかもです。

それではまた！

※参考：Node.js / Expressを習得するためにやったことまとめ
qiita.com

*1:事例は1つだが複数ある場合はカンマで区切る