【第6回】 データベースとアプリケーションサーバーを連携させてみよう

前回Node.jsのサーバーをセットアップするところまでやったので、今回はNode.jsのサーバーを使ってデータベースを操作してみます。

Homeブログ一覧【第6回】 データベースとアプリケーションサーバーを連携させてみよう

おさらい

前回は、データベースの種類とデータベースの基本的な操作について学びました。

関係データベースでは、データを表形式で保存します。 テーブルと呼ばれる表の中に、レコードと呼ばれる一つ一つのデータが保存されます。 Excelだと、シートがテーブルに、行がレコードに相当します。

操作にはSQLという言語を使います。

テーブル操作

  • CREATE TABLEでテーブルを作成

レコード操作

  • INSERT INTOでレコードを追加
  • SELECTでレコードを取得
  • UPDATEでレコードを更新
  • DELETEでレコードを削除

レコードの操作4つは、Create, Read, Update, Deleteを頭文字にとってCRUDと呼ばれることがあります。

3層アーキテクチャという構成を学びました。 Webアプリを作るときは、データ層とアプリケーション層、プレゼンテーション層の3つの層に分けて作ると良いです。

名前説明例
データ層データベースやファイルなどのデータを扱う層データベース
アプリケーション層データ層からデータを取得して、加工したり、データを更新したりする層PHPやRuby、Python、Node.jsなどで作るサーバー
プレゼンテーション層ユーザーとやり取りする層HTMLやCSS、Javascriptなどのブラウザ、スマホアプリ

アプリケーション作成の続き-アプリケーション編

それでは前回DBとの連携をするためのセットアップができたので、今回は実際にプログラム（サーバー）を書いてDBを操作してみましょう。

ユーザーとツイートのテーブルを作成

まず、queries.jsを作成します。

queries.js
const Tweets = {
    createTable: `
        CREATE TABLE IF NOT EXISTS tweets (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            content TEXT NOT NULL,
            user_id INTEGER NOT NULL,
            created_at DATETIME NOT NULL
        );
    `,
};

const Users = {
    createTable: `
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT NOT NULL,
            created_at DATETIME NOT NULL
        );
    `,
};

module.exports = {
    Tweets,
    Users,
};

使うクエリを一つのファイルにまとめておくと後で見やすくなるため、今回はqueries.jsというファイルにクエリをまとめておきます。

IF NOT EXISTSというのは、テーブルが存在しない場合にのみテーブルを作成するという意味です。 これをつけておくことで最初にこのファイルを実行したときにテーブルが作成され、2回目以降はテーブルが存在するのでテーブルが作成されないようにします。

これで、TweetsとUsersという2つのテーブルを作成するクエリが定義されました。

次に、index.jsを以下のように編集します。

index.js
const sqlite3 = require('sqlite3').verbose();
const queries = require('./queries');

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

db.serialize(() => {
    db.run(queries.Tweets.createTable);
    db.run(queries.Users.createTable);
});

db.close();

require('./queries')でqueries.jsを読み込みます。 queries.Tweets.createTableは、queries.jsで定義したTweetsの中のcreateTableという関数を呼び出しています。

余談

プログラムにおいてファイル分割はとても重要な概念です。 たとえば一つのファイルの中に1000行のコードが書かれていると、どこに何が書かれているのかわかりにくくなりますよね？

そのため、プログラムを書くときは、一つのファイルには一つの機能だけを書くようにしましょう。 この考え方は単一責任の原則と呼ばれています。

こう言った設計の仕方を勉強するのもプログラミングの醍醐味で、どうやったら他人が読みやすいコード設計ができるというリーダブルコードという着眼点をもつと、プログラミングがもっと楽しくなります。

ただし、言語によっては適切な分割をしていれば機能を集約したほうがいいという考え方をしている場合もあります。 最近ではJSでもコロケーションと言う、機能は違うけど同じような役割（ドメイン）のコードを一つのファイルにまとめるという考え方が流行っていて、その環境やプロジェクトによって適切な分割の仕方は変わってきます。

db.serializeの中に書いた処理は、順番に実行されます。 db.runは、SQLを実行する関数です。

では実行してみましょう。

node index.js

database.dbが作成されていれば成功です。 database.dbを開いて、テーブルが作成されていることを確認してみましょう。

sqlite3 database.db

を実行して、sqliteのコンソールに入ります。

.tables

を実行すると、テーブルの一覧が表示されます。

tweets  users

と表示されれば成功です。

それではテーブルを作成する機能ができたので、コミットしておきましょう。

git add .
git commit -m "ユーザーとツイートのテーブルを作成"

確認のため、git logを実行してみましょう。

git log --oneline

--onelineをつけると、1コミットごとに1行で表示されます。（覚えなくても大丈夫です）

<id> (HEAD -> main) ユーザーとツイートのテーブルを作成
<id> データベースと接続する

ユーザーを作成できるようにする

次に、ユーザーを作成できるようにしてみましょう。 queries.jsを以下のように編集します。

queries.js
const Users = {
    createTable: `
        CREATE TABLE users IF NOT EXISTS (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT NOT NULL,
            created_at DATETIME NOT NULL
        );
    `,
    create: `INSERT INTO users (name, email, created_at) VALUES (?, ?, ?);`,
};

そして、index.jsを以下のように編集します。

index.js
const sqlite3 = require('sqlite3').verbose();
const queries = require('./queries');

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

db.serialize(() => {
    db.run(queries.Tweets.createTable);
    db.run(queries.Users.createTable);

    db.run(queries.Users.create, 'りんご太郎', '[email protected]', '2022-08-15 00:00:00');
    db.run(queries.Users.create, 'みかん次郎', '[email protected]', '2022-08-15 00:00:01');
    db.run(queries.Users.create, 'ぶどう三郎', '[email protected]', '2022-08-15 00:00:02');
});

db.close();

?は、後から値を埋め込むためのプレースホルダと呼ばれるものです。

?を含むクエリに関する補足

User.createのクエリを

create(name, email, created_at): `INSERT INTO users (name, email, created_at) VALUES ('${name}', '${email}', '${created_at}');`,

にしないの？と思うかもしれません。

しかし、このようにすると、「SQLインジェクション」というセキュリティ上の問題が発生します。 たとえば、nameに'); DROP TABLE users; --という文字列を入れると、出力されるクエリは以下のようになります。

INSERT INTO users (name, email, created_at) VALUES (''); DROP TABLE users; --', '[email protected]', '2023-05-21 00:00:00');

これにより、本来意図していないテーブルの削除が行われてしまいます。 そのため、?を使って、SQLインジェクションを防ぐようにしましょう。 これを使えば攻撃性のある命令に使われる特殊文字'; --などを命令ではなくそのまま文字列として埋め込むことができます。

では実行してみましょう。

node index.js

database.dbを開いて、ユーザーが作成されていることを確認してみましょう。

sqlite3 database.db

SELECT * FROM users;

1|りんご太郎|[email protected]|2022-08-15 00:00:00
2|みかん次郎|[email protected]|2022-08-15 00:00:01
3|ぶどう三郎|[email protected]|2022-08-15 00:00:02

と表示されれば成功です。

それではユーザーを作成できるようにする機能ができたので、コミットしておきましょう。

git add .
git commit -m "ユーザーを作成できるようにする"

ツイートを作成できるようにする

次に、ツイートを作成できるようにしてみましょう。

queries.jsを以下のように編集します。

queries.js
const Tweets = {
    createTable: `
        CREATE TABLE tweets IF NOT EXISTS (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            content TEXT NOT NULL,
            user_id INTEGER NOT NULL,
            created_at DATETIME NOT NULL
        );
    `,
    create: `INSERT INTO tweets (content, user_id, created_at) VALUES (?, ?, ?);`,
};

そして、index.jsを以下のように編集します。

index.js
const sqlite3 = require('sqlite3').verbose();
const queries = require('./queries');

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

db.serialize(() => {
    db.run(queries.Tweets.createTable);
    db.run(queries.Users.createTable);

    db.run(queries.Users.create, 'りんご太郎', '[email protected]', '2022-08-15 00:00:00');
    db.run(queries.Users.create, 'みかん次郎', '[email protected]', '2022-08-15 00:00:01');
    db.run(queries.Users.create, 'ぶどう三郎', '[email protected]', '2022-08-15 00:00:02');

    db.run(queries.Tweets.create, 'あけおめ！', 3, '2023-01-01 00:00:00');
    db.run(queries.Tweets.create, '今年もよろしくお願いします！', 2, '2023-01-01 00:00:01');
    db.run(queries.Tweets.create, '今年こそは痩せるぞ！', 1, '2023-01-01 00:00:02');
});

db.close();

では実行してみましょう。 そのまえに、database.dbを削除しておきましょう。

rm database.db

こうしないと先ほど作ったUserレコードが被ってしまいます。

node index.js

database.dbを開いて、ツイートが作成されていることを確認してみましょう。

sqlite3 database.db

SELECT * FROM tweets;

1|あけおめ！|3|2023-01-01 00:00:00
2|今年もよろしくお願いします！|2|2023-01-01 00:00:01
3|今年こそは痩せるぞ！|1|2023-01-01 00:00:02

と表示されれば成功です。

それではツイートを作成できるようにする機能ができたので、コミットしておきましょう。

git add .
git commit -m "ツイートを作成できるようにする"

ツイート一覧やユーザー一覧を取得できるようにする

次に、ツイート一覧やユーザー一覧を取得できるようにしてみましょう。 これもSELECT文を使って処理を書くだけです。

queries.jsを以下のように編集します。

queries.js
const Tweets = {
    createTable: `
            CREATE TABLE IF NOT EXISTS tweets (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                content TEXT NOT NULL,
                user_id INTEGER NOT NULL,
                created_at DATETIME NOT NULL
            );
        `,
    create: `INSERT INTO tweets (content, user_id, created_at) VALUES (?, ?, ?);`,
    findAll: `SELECT * FROM tweets;`,
};

const Users = {
    createTable: `
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT NOT NULL,
            created_at DATETIME NOT NULL
        );
    `,
    create: `INSERT INTO users (name, email, created_at) VALUES (?, ?, ?);`,
    findAll: `SELECT * FROM users;`,
};

module.exports = {
    Tweets,
    Users,
};

そして、index.jsを以下のように編集します。

index.js
db.serialize(() => {
    ...

    db.all(queries.Tweets.findAll, (err, rows) => {
        console.log(rows);
    });

    db.all(queries.Users.findAll, (err, rows) => {
        console.log(rows);
    });
});

db.allは、SQLを実行して結果を全て取得する関数です。

今回は全てのツイートと全てのユーザーを取得するので、Tweets.findAllとUsers.findAllという名前にしました。

最後の引数(err, row) => {}は、コールバック関数と呼ばれるものです。 コールバック関数は、その処理が終わった後に実行される関数です。 今回は取得するという処理が終わったら、引数に渡されるrowを表示するようにしています。

これで、ツイート一覧やユーザー一覧を取得できるようになりました。

それでは実行してみましょう。（database.dbを削除しておきましょう）

node index.js

null
[
    {
        id: 1,
        content: 'あけおめ！',
        user_id: 3,
        created_at: '2023-01-01 00:00:00'
    },
    ...
]
null
[
    {
        id: 1,
        name: 'りんご太郎',
        email: '[email protected]',
        created_at: '2022-08-15 00:00:00'
    },
    ...
]

と表示されれば成功です。

またコミットしておきましょう。

git add .
git commit -m "ツイート一覧やユーザー一覧を取得できるようにする"

ユーザーとツイートを紐付けて取得する

次に、ユーザーとツイートを紐付ける機能を作成してみましょう。 前回やった、「ツイートid=1のツイートを投稿したユーザーは誰でしょうか？」という問題を表現します。 ただ、名前以外も欲しいので、ユーザーの情報を全て取得することにします。

これを実現するSQLは

SELECT * FROM users WHERE id = (SELECT user_id FROM tweets WHERE id = 1);

となります。 前回のクエリでnameだけを取得していたので、今回は*を使って全ての情報を取得します。

これをqueries.jsに追加します。

queries.js
const Users = {
    createTable: `
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT NOT NULL,
            created_at DATETIME NOT NULL
        );
    `,
    create: `INSERT INTO users (name, email, created_at) VALUES (?, ?, ?);`,
    findAll: `SELECT * FROM users;`,
    findByTweetId: `SELECT * FROM users WHERE id = (SELECT user_id FROM tweets WHERE id = ?);`,
};

今回はTweetIdからUserを取得するので、Users.findByTweetIdという名前にしました。

そして、index.jsを以下のように編集します。

index.js
db.serialize(() => {
    ...

    db.get(queries.Users.findByTweetId, 1, (err, row) => {
        console.log(row);
    });

    db.get(queries.Users.findByTweetId, 4, (err, row) => {
        console.log(row);
    });
});

db.allは、SQLを実行して結果を全て取得する関数です。

このDBユーザーとツイートが1:多の関係になってます。 なのでツイートを書いたユーザーは必ず一人になります。 そのため、db.getを使っています。

それでは実行してみましょう。（database.dbを削除しておきましょう）

node index.js

{
  id: 3,
  name: 'ぶどう三郎',
  email: '[email protected]',
  created_at: '2022-08-15 00:00:02'
}
undefined

と表示されれば成功です。 上の方はうまくいっているのがわかりますが、下の方はundefinedが表示されています。 そうです、ツイートid=4は存在しないので、そういう時はundefinedが返されることもわかりました。

またコミットしておきましょう。

git add .
git commit -m "ユーザーとツイートを紐付けて取得する"

ここまできてやっと操作は終わりです。

コミットログを見てみましょう。

git log --oneline

<id> (HEAD -> main) ユーザーとツイートを紐付けて取得する
<id> ツイート一覧やユーザー一覧を取得できるようにする
<id> ユーザーを作成できるようにする
<id> ユーザーとツイートのテーブルを作成
<id> データベースと接続する

このように、コミットログを見るだけでどのような機能を追加したかがわかるようになりました。

【第5回】 データベースを触ってみよう

【第7回】サイトから閲覧や投稿ができるアプリケーションを作ろう（閲覧編）