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

今回はWebアプリを作る上で必要不可欠なデータベースについて学びます。SQLを使って、実際にsqliteを操作してみましょう。

Homeブログ一覧【第5回】 データベースを触ってみよう

はじめに

今まで、第1,2回でHTMLとCSSとJavascript、第3,4回でGitとGithubを学びました。 これを使えば実際に動くWebアプリを作ることができます。 しかし、実際に実用的なWebアプリを作るには、情報が書き換わったり、保存・登録ができるようにする必要がありますよね。

例えばTwitterを考えてみましょう。 Twitterは、ツイートを投稿したり、他の人のツイートを見たりすることができます。

この投稿という行動では、ツイートをTwitter社のサーバーに保存するという「書き込み」の行為が行われています。

一方で、閲覧という行動では、Twitter社のサーバーからツイートを取得して、自分の画面に表示するという「読み込み」の行為が行われています。

これらの行為を実現するためには、データベースという仕組みが必要になります。

このデータベースは2分探索やハッシュテーブル、計算量最適化などの競技プログラミングで学ぶアルゴリズム的知識が活かせるので、競プロ勢にはおすすめです（Web勢の勝手な思い込み）。

データベースとは

データベースとは、データを保存するための仕組みです。 データベースを使うことで、データを保存したり、取得したり、更新したりすることができます。

いろいろなデータベースがあり、それぞれ異なる保存形式やパフォーマンス、機能を持っています。 これらを説明し始めるとキリがないので、今回はsqliteというデータベースを使ってデータベースの基本的な操作を学びます。

このデータベースはみなさんが普段使うようなExcelと似たようなもので、データを表形式・単一のファイルで保存することができます。 単一のファイルで保存することができるので、データベースを扱うためのサーバーを用意する必要がなく、初心者には扱いやすいデータベースです。

sqliteをインストールしよう

まずは、sqliteをインストールしましょう。 ターミナルを開き、以下のコマンドを実行してください。

# WSL2/Ubuntuの場合です、他の環境の場合は調べてください
sudo apt install sqlite3

関係データベース

データベースには、いろいろな種類があります。 今回は、sqliteが対応している形式である関係データベース（リレーショナルデータベース）という種類のデータベースを使います。

関係データベースでは、データを表形式で保存します。 この表のことをテーブルと呼びます。 また、テーブルの中の一つ一つのデータのことをレコードと呼びます。

例えば、Twitterのツイートを保存するテーブルを作るとします。 このテーブルには、ツイートの内容や投稿者の情報などが保存されます。

このテーブルは、以下のような形式で保存されます。

idcontentuser_idcreated_at
1あけましておめでとう！32023-01-01 00:00:00
2今年もよろしくお願いします！22023-01-01 00:00:01
3今年こそは痩せるぞ！12023-01-01 00:00:02

後で使うのでこのテーブルをツイートテーブルと呼ぶことにします。 このように、テーブルは列（カラム）と行（レコード）で構成されています。

それぞれの列は、カラム名とデータ型を持っています。

カラム名は、その列に保存されるデータの種類を表しています。この例では、idはツイートのID、contentはツイートの内容、user_idはツイートを投稿したユーザーのID、created_atはツイートを投稿した日時を表しています。

また、データ型は、その列に保存されるデータの種類を表しています。この例では、idは整数、contentは文字列、user_idは整数、created_atは日時を表しています。

リレーション

関係データベースでは、複数のテーブルを作ることができます。 このテーブル同士の関係をリレーションと呼びます。

次に、ツイートを投稿したユーザーの情報を保存するテーブルを作るとします。

このテーブルは、以下のような形式で保存されます。

idnameemailcreated_at
1佐藤太郎[email protected]2022-08-15 00:00:00
2鈴木次郎[email protected]2022-09-03 00:00:00
3田中三郎[email protected]2022-10-11 00:00:00

このテーブルをユーザーテーブルと呼ぶことにします。

実はツイートテーブルのuser_idは、ユーザーテーブルのidに対応しています。 このようにテーブルのある列と別のテーブルのある列が対応している関係をリレーションと呼びます。

では問題です。

ツイートid=1のツイートを投稿したユーザーは誰でしょうか？

答え

ツイートid=1のツイートを投稿したユーザーは、ユーザーid=3のユーザーです。 ユーザーid=3のユーザーの名前は、田中三郎です。

このように、リレーションを使うことで、複数のテーブルを組み合わせてデータを取得することができます。 こうやって一つ一つのテーブルとそのリレーションを組み合わせてデータを保存することで、複雑なデータを保存することができます。

SQL (基礎)

データベースを操作するためには、SQLという言語を使います。 SQLは、データベースに対して、データの取得や保存、更新などの操作を行うための言語です。

まずは、SQLの基本的な操作を学びましょう。

準備

最初に、データベースを操作するための準備をします。 ターミナルを開き、以下のコマンドを実行してください。

# データベースファイルを作成
touch database.db

# データベースファイルを開く
sqlite3 database.db

テーブルの作成

まずは、テーブルを作成してみましょう。 さっきのツイートテーブルを作成してみます。

CREATE TABLE tweets (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  content TEXT NOT NULL,
  user_id INTEGER NOT NULL,
  created_at DATETIME NOT NULL
);

このコマンドを実行すると、tweetsという名前のテーブルが作成されます。 確認してみましょう。

# テーブル一覧を表示
.tables

# テーブルの構造を表示
.schema tweets

CREATE TABLE <テーブル名> (<カラム名> <データ型> <制約>, ...);という形式でテーブルを作成することができます。

<カラム名> <データ型> <制約>の部分をカラム定義と呼びます。

<カラム名>は、その列の名前を表しています。

<データ型>は、その列に保存されるデータの種類を表しています。 INTEGERは整数、TEXTは文字列、DATETIMEは日時を表しています。

<制約>には、以下のようなものがあります。

  • PRIMARY KEY：主キーを設定します。主キーは、そのテーブルのレコードを一意に識別するためのものです。主キーは、INTEGER型の列に設定することが多いです。
  • AUTOINCREMENT：主キーを自動で増やします。主キーを設定した列に設定することが多いです。
  • NOT NULL：その列には、必ず値を入れる必要があります。
  • UNIQUE：その列には、同じ値を入れることができません。
  • DEFAULT <デフォルト値>：その列に値が入っていない場合、デフォルト値を入れます。

レコードの取得

次に、レコードを取得してみましょう。 さっきのツイートテーブルから、ツイートを取得してみます。

SELECT * FROM tweets;

SELECT * FROM <テーブル名>;という形式でレコードを取得することができます。

*は、全ての列を表しています。 SELECT <カラム名>, ... FROM <テーブル名>;という形式で、特定の列を取得することもできます。 SELECT content FROM tweets;みたいな感じですね。

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

SELECT * FROM tweets;

今のところ、レコードがありませんね。なにも表示されません。

レコードの挿入

次に、レコードを挿入してみましょう。 さっきのツイートテーブルに、ツイートを挿入してみます。

INSERT INTO tweets (content, user_id, created_at) VALUES ('あけましておめでとう！', 3, '2023-01-01 00:00:00');
INSERT INTO tweets (content, user_id, created_at) VALUES ('今年もよろしくお願いします！', 2, '2023-01-01 00:00:01');
INSERT INTO tweets (content, user_id, created_at) VALUES ('今年こそは痩せるぞ！', 1, '2023-01-01 00:00:02');

INSERT INTO <テーブル名> (<カラム名>, ...) VALUES (<値>, ...);という形式でレコードを挿入することができます。

これで、ツイートテーブルにレコードが挿入されました。 確認してみましょう。

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

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

レコードの更新

次に、レコードを更新してみましょう。 さっきのツイートテーブルの、ツイートid=1の内容を更新してみます。

UPDATE tweets SET content = 'あけおめ！' WHERE id = 1;

UPDATE <テーブル名> SET <カラム名> = <値>, ... WHERE <条件>;という形式でレコードを更新することができます。

これで、ツイートテーブルのツイートid=1の内容が更新されました。

確認してみましょう。

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

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

レコードの削除

最後に、レコードを削除してみましょう。 さっきのツイートテーブルの、ツイートid=1を削除してみます。

DELETE FROM tweets WHERE id = 1;

DELETE FROM <テーブル名> WHERE <条件>;という形式でレコードを削除することができます。

これで、ツイートテーブルのツイートid=1が削除されました。

確認してみましょう。

SELECT * FROM tweets;

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

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

SQL (応用)

次にユーザーテーブルを作成してみましょう。

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  created_at DATETIME NOT NULL
);

このテーブルに、ユーザーを挿入してみましょう。

INSERT INTO users (name, email, created_at) VALUES ('佐藤太郎', '[email protected]', '2022-08-15 00:00:00');
INSERT INTO users (name, email, created_at) VALUES ('鈴木次郎', '[email protected]', '2022-09-03 00:00:00');
INSERT INTO users (name, email, created_at) VALUES ('田中三郎', '[email protected]', '2022-10-11 00:00:00');

これで、ユーザーテーブルにレコードが挿入されました。 確認してみましょう。

SELECT * FROM users;

1|佐藤太郎|[email protected]|2022-08-15 00:00:00
2|鈴木次郎|[email protected]|2022-09-03 00:00:00
3|田中三郎|[email protected]|2022-10-11 00:00:00

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

レコードの取得 (応用)

さっきの問題を思い出してみましょう。

ツイートid=1のツイートを投稿したユーザーは誰でしょうか？

という問題です。

これをSQLで表してみましょう。まず2つのクエリに分解してみます。

ツイートid=1のツイートを取得する

SELECT * FROM tweets WHERE id = 1;

ユーザーid=3のユーザー名を取得する

SELECT name FROM users WHERE id = 3;

これらを組み合わせて、ツイートid=1のツイートを投稿したユーザーを取得するクエリを作成します。

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

これで、ツイートid=1のツイートを投稿したユーザーを取得することができました。

ここで、SELECT user_id FROM tweets WHERE id = 1というクエリをサブクエリと呼びます。 サブクエリは、クエリの中に入れ子になっているクエリのことを指します。

アプリケーションとの連携

今までは、データベースを直接操作していました。 しかし、実際には直接データベースを操作することはほとんどありません。

例えば、ツイートを投稿しようとする場合

  1. ツイートを投稿する
  2. ツイートをデータベースに保存する
  3. フォローしているユーザーを取得する
  4. フォローしているユーザーから通知をONにしているユーザーを取得する
  5. 通知をONにしているユーザーに通知を送る
  6. ツイートを表示する

みたいな感じでSQLだけで解決できない複雑な処理を行うために、アプリケーションを作成してデータベースを操作することが多いです。 また、直接データベースがアクセスできる環境を作ると、SQLインジェクションなどのセキュリティ上の問題がより発生しやすくなるので、アプリケーションを作成してデータベースを操作することが多いです。

3層アーキテクチャ

アプリケーションを作成するときには、3層アーキテクチャという考え方を使うことが多いです。

3層アーキテクチャでは、アプリケーションを3つの層に分けて考えます。

  • プレゼンテーション層
  • アプリケーション層
  • データベース層

プレゼンテーション層

プレゼンテーション層は、ユーザーとのやり取りを行う層です。 例えば、ツイートを投稿する画面や、ツイートを表示する画面などがあります。

俗にいうフロントエンドです。サイトやスマホアプリなどが該当します。

アプリケーション層

アプリケーション層は、プレゼンテーション層とデータベース層の橋渡しを行う層です。書き込みや読み込み、定期的な処理などを行います。

例えば、ツイートを投稿する画面でツイートを投稿したときに、ツイートをデータベースに保存する処理を行います。

データベース層

言わずもがなですね。

Webアプリケーションを作ろう！と思ったら、まずは3層アーキテクチャを意識してみましょう。 これが一番ベーシックなアーキテクチャです。

バックエンドは、アプリケーション層とデータベース層を指すことが多いです。

Node.jsの用意

JavaScriptはブラウザで動かせる言語でしたが、Node.jsを使うことで同様にサーバーでも動かすことができます。 今回はNode.jsを使ってアプリケーションを作成していきます。

Ubuntuの場合は、以下のコマンドを実行してNode.jsをインストールしてください。

curl -fsSL https://deb.nodesource.com/setup_lts.x | sudo -E bash - && sudo apt-get install -y nodejs

ほかの環境の場合は、Node.jsの公式サイトを参考にしてください。

データベースとの連携

データベースとの連携は、アプリケーション層で行います。 SQLをネットワーク経由でデータベースに送信して、データベースを操作するみたいな感じです。 （sqliteはファイルベースなので、ネットワーク経由ではなくファイル経由でデータベースを操作しますが）

新しいディレクトリを作成します。 ログを残しておくために、gitを初期化しておきましょう。

git init

.gitというディレクトリが作成されていることが確認できればOKです。

npm init -y

として、package.jsonを作成します。 これはNode.jsのプロジェクトの設定ファイルです。 ライブラリのバージョン管理や、プロジェクトの設定ができます。

次に、データベースを操作するためのライブラリをインストールします。

npm install sqlite3

次に、index.jsを作成します。

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

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

db.close();

db.close()は、データベースとの接続を切断する関数です。これは、データベースとの接続を切断しないと、データベースファイルを削除したり、別のプログラムからデータベースにアクセスできなくなったりするので、必ずプログラムの最後に書いておきましょう。

それでは、実行してみましょう。

node index.js

今回は、データベースに接続してすぐに切断しているので、何も表示されません。 しかし、新しくdatabase.dbというファイルが作成されていることが確認できます。 ちゃんとデータベースと接続できていることが確認できました。

次に、database.dbは頻繁にデータが書き変わるので、これをgitで管理してしまうと毎回のコミットでデータベースの変更がコミットされてしまいます。 そのため、.gitignoreにdatabase.dbを追加しておきましょう。

また、node_modulesも頻繁に変更され、さらにファイル数が多いので、これも.gitignoreに追加しておきましょう。

.gitignoreファイルを作成して、以下のように書きます。

.gitignore
database.db
node_modules

こうすることで、gitで管理されなくなります。

データベースと接続するという機能ができたので、コミットしておきましょう。

git add .
git commit -m "データベースと接続する"

【第4回】 Git/Githubを使って共同開発をしてみよう

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