Next.jsで学ぶReact講座 90日コース | Next.jsでCRUDアプリ開発 - Day 51:データベース基礎

React Next.jsで学ぶReact講座90日コース
スポンサーリンク
スポンサーリンク
  1. Day 51:データベース基礎 ― Table・Row・Column・主キー・外部キーを理解する
  2. 今日のゴール
  3. なぜデータベースが必要なのか
    1. ReactのStateとデータベースは役割が違います
  4. Databaseとは
    1. 今回はリレーショナルデータベースを学びます
  5. Table・Row・Columnをひとつの表で理解する
    1. 記事テーブルの例
    2. Tableは「同じ種類のデータを集める表」です
    3. Rowは「データ1件分」です
    4. Columnは「各データが持つ項目」です
  6. Primary Keyとは
    1. 記事タイトルを識別子にしない理由
    2. 主キーの値はテーブルごとに考えます
    3. 主キーは必ず連番とは限りません
  7. 投稿者の情報を別テーブルへ分ける
    1. 投稿者情報を記事へ繰り返し保存した例
    2. usersテーブル
    3. postsテーブル
  8. Foreign Keyとは
    1. 外部キーは投稿者情報のコピーではありません
    2. 外部キーの値は重複できます
    3. 存在しない投稿者は参照させない
    4. 外部キーは必ず主キーだけを参照するのか
  9. NULLと制約も一緒に理解する
    1. NULLは「空文字」とは違います
    2. 今回使う制約
  10. SQLとは何かを理解する
  11. 実践:投稿者と記事のデータベースを作る
    1. 作成するファイル
  12. テーブルと初期データを定義する
    1. schema.sql
    2. 定義を順番に読み解く
    3. ON DELETE RESTRICTを付けた理由
    4. BEGINとCOMMITは何をしているのか
  13. SQLを実行して結果を確認する
    1. run_database.py
    2. 実行コマンド
  14. JOINで記事と投稿者を組み合わせる
  15. CRUDとのつながりを確認する
    1. Create:記事を追加する
    2. Read:記事を取得する
    3. Update:記事を更新する
    4. Delete:記事を削除する
    5. 更新と削除では、対象条件を必ず確認する
  16. 投稿者を削除するときの設計を考える
  17. Next.jsとデータベースはどのようにつながるのか
    1. 今回は接続方法を混ぜすぎない
  18. セキュリティで特に重要なこと
    1. 主キーはアクセス権限ではありません
    2. SQLへ入力値を文字列連結しない
    3. データベースファイルも公開してはいけません
  19. よくある誤解を整理する
  20. 自分で設計する練習
    1. 記事へコメントを追加する
    2. 多対多の関係を考える
  21. 設計メモのテンプレート
  22. Day 51のチェックリスト
  23. まとめ

Day 51:データベース基礎 ― Table・Row・Column・主キー・外部キーを理解する

Day 51からは、Next.jsでCRUDアプリを作るための学習に入ります。これまでは、配列に用意した記事や、外部APIから取得したデータを表示してきました。ここからは、アプリ自身のデータを保存し、追加・取得・更新・削除する仕組みを学びます。

今回は、データベースの基本用語を、ブログの記事と投稿者の関係を使って理解します。最後には、小さなデータベースを作り、主キーと外部キーがどのように働くかを確認します。

今日のゴール

今回の目標は、データベースに関する用語を暗記することではありません。「どの情報を、どの表へ保存し、どの値で関連付けるか」を説明できることです。

  • Database、Table、Row、Columnの違いを説明できます。
  • 記事を1件ずつ識別するために、なぜ主キーが必要か分かります。
  • 記事と投稿者を、外部キーで関連付けられます。
  • 存在しない投稿者の記事を登録させない仕組みを理解できます。
  • Next.jsの画面とデータベースの役割を区別できます。

今日はNext.jsへの接続よりも、その前提となるデータの構造に集中します。

なぜデータベースが必要なのか

これまでのブログでは、次のような配列を使っていました。

// ソースコードに直接用意した記事データです
const posts = [
  {
    id: 1,
    title: "Next.js入門",
  },
  {
    id: 2,
    title: "データベース入門",
  },
];
TypeScript

この方法でも表示の練習はできます。ただし、利用者が画面から記事を追加するアプリを作ると、次の問題を考える必要があります。

  • 追加した記事を、どこへ保存しますか。
  • アプリを再起動しても、記事を残せますか。
  • 複数の利用者が、同じ記事データを扱えますか。
  • 存在しない投稿者の記事を、誤って保存しないようにできますか。

今回の学習では、その保存先としてリレーショナルデータベースを使います。情報を表に分けて保存し、制約によってデータの整合性を守る考え方を学びます。主キーや外部キーなどの制約は、この整合性を保つための仕組みです。(🔗 postgresql.org)

ReactのStateとデータベースは役割が違います

入力欄の文字や、開いているメニューの状態などは、ReactのStateで管理できます。

一方、公開した記事や利用者情報など、アプリのデータとして保存したいものは、データベースなどの永続的な保存先で管理する設計を考えます。

今回のアプリでは、次のように分担します。

扱うもの今回の管理場所目的
入力中の記事タイトルReactのStateなど入力操作を画面へ反映します
保存済みの記事データベース記事を継続して管理します
記事の保存処理サーバー側の処理入力確認や権限確認を行います

Stateとデータベースは、どちらか一方だけを選ぶものではありません。入力中の値を画面で管理し、保存操作でデータベースへ反映する、という組み合わせになります。

Databaseとは

**Database(データベース)**は、関連するデータを整理して保存し、取り出したり更新したりするための仕組みです。

厳密には、保存されたデータの集合と、それを管理するソフトウェアは区別できます。管理するソフトウェアは、DBMS(データベース管理システム)と呼ばれます。

今回は、SQLを使って操作するSQLiteを実習に使います。SQLiteは、別のデータベースサーバーを起動せずに利用でき、通常のデータベースを単一ファイルとして保存できるデータベースエンジンです。(🔗 sqlite.org)

今回はリレーショナルデータベースを学びます

データベースにはさまざまな種類がありますが、今日は表にデータを保存し、表同士を関連付ける考え方を学びます。

今回作るブログでは、次の2つの表を使います。

  • 投稿者を保存するusers。
  • 記事を保存するposts。

最初から難しい専門用語を増やすより、「投稿者の一覧」と「記事の一覧」を別々に持つと考えると理解しやすくなります。

Table・Row・Columnをひとつの表で理解する

まず、記事を保存する表を見てみます。

記事テーブルの例

idtitleuser_id
1Next.js入門1
2データベース入門1
3SQL入門2

この表を使って、Table、Row、Columnを整理します。

用語日本語今回の例
Tableテーブル、表記事を保存するposts
Row行、レコード「Next.js入門」という記事1件
Column列、カラムid、title、user_id

テーブルでは列ごとの名前、データ型、制約を定義できます。今回のSQLiteでも、CREATE TABLEでそれらを指定します。(🔗 sqlite.org)

Tableは「同じ種類のデータを集める表」です

記事はposts、投稿者はusersのように、今回は対象ごとにテーブルを分けます。

「記事と投稿者とコメントを、何でもひとつの表へ入れる」と考えるのではなく、まずは何の情報を保存する表なのかを決めます。

Rowは「データ1件分」です

次の横1行が、記事1件に対応します。

idtitleuser_id
1Next.js入門1

JavaScriptの感覚では、記事1件のオブジェクトに近いものとして考えられます。

// テーブルの1行に対応するイメージです
const post = {
  id: 1,
  title: "Next.js入門",
  userId: 1,
};
TypeScript

ただし、SQLの列名とJavaScriptのプロパティ名は、自動的に同じ名前になるとは限りません。今回のSQLではuser_idを使い、JavaScriptではuserIdへ変換する設計も選べます。

Columnは「各データが持つ項目」です

title列は記事タイトル、user_id列は投稿者の識別子を保存します。

列には、名前だけでなく、どのような値を受け付けるかというルールも設定します。

今回であれば、次のような設計です。

列名保存するもの今回のルール
id記事の識別子整数の主キー
title記事タイトルNULL不可
body記事本文NULL不可
user_id投稿者の識別子投稿者を参照する整数、NULL不可

Primary Keyとは

Primary Key(主キー)は、テーブルの各行を一意に識別するためのキーです。

一般的な主キーの基本は、値が重複せず、未設定を表すNULLを許さないことです。また、主キーは1列だけでなく、複数列の組み合わせで定義することもできます。今回は、理解しやすい1列の整数主キーを使います。(🔗 postgresql.org)

記事タイトルを識別子にしない理由

次のように、同じタイトルの記事が2件ある場合を考えます。

idtitle
1Next.js入門
2Next.js入門

タイトルだけでは、どちらの記事を編集すればよいか決められません。

また、記事タイトルは後から変更される可能性があります。そこで今回は、記事タイトルとは別にidを用意します。

  • 記事の表示名はtitleで管理します。
  • 記事を識別する値はidで管理します。
  • 更新や削除では、対象のidを指定します。

主キーの値はテーブルごとに考えます

users.id = 1とposts.id = 1が同時に存在しても問題ありません。

それぞれ、投稿者テーブルの1番と、記事テーブルの1番を表すためです。

主キーが一意である必要がある範囲は、その主キーを持つテーブル内です。 今回は、データベース全体で共通の連番を作る設計ではありません。

主キーは必ず連番とは限りません

今回は整数を使いますが、主キーは「1、2、3と欠番なく並ぶ番号」という意味ではありません。

SQLiteのINTEGER PRIMARY KEYでは、INSERT時にIDを省略すると整数値を割り当てられます。ただし、それを「永久に欠番のない番号」や「過去の値を絶対に再利用しない番号」として扱わないようにします。(🔗 sqlite.org)

SQLiteの主キーには実装上の注意もあります

一般的な主キーの原則を、そのまますべてのSQLiteの定義へ当てはめられるとは限りません。SQLiteには、歴史的な互換性のため、一部の主キー定義でNULLを許す例外があります。

今回は、その混乱を避けるため、**id INTEGER PRIMARY KEY**という定義を使います。(🔗 sqlite.org)

投稿者の情報を別テーブルへ分ける

次に、記事と投稿者の関係を考えます。

最初は、記事の行へ投稿者名やメールアドレスを直接入れたくなるかもしれません。

投稿者情報を記事へ繰り返し保存した例

idtitleauthor_nameauthor_email
1Next.js入門葵🔗 aoi@example.com
2データベース入門葵🔗 aoi@example.com
3SQL入門蓮🔗 ren@example.com

この設計では、葵さんの情報が複数の記事に繰り返し保存されています。

たとえば投稿者名を変更する場合、関係するすべての記事を更新しなければなりません。更新漏れがあると、同じ人物なのに違う名前で表示される状態になります。

今回は、この重複を避けるため、投稿者を別テーブルへ分けます。

usersテーブル

idnameemail
1葵🔗 aoi@example.com
2蓮🔗 ren@example.com

postsテーブル

idtitleuser_id
1Next.js入門1
2データベース入門1
3SQL入門2

記事には投稿者名を繰り返し保存せず、投稿者のIDを保存します。

このように、関連する情報を分けて管理する考え方は、データの重複や更新漏れを減らす設計につながります。

Foreign Keyとは

Foreign Key(外部キー)は、ほかの行との参照関係を表し、その関係を制約として守るためのキーです。

今回は、posts.user_idからusers.idを参照します。外部キー制約を付けることで、参照先の整合性をデータベースで確認できます。(🔗 postgresql.org)

users.id ← posts.user_id

たとえば、次の記事を見ます。

idtitleuser_id
1Next.js入門1

user_idが1なので、usersテーブルのid = 1を参照します。今回のデータでは、葵さんが投稿者です。

外部キーは投稿者情報のコピーではありません

posts.user_idへ保存されるのは、投稿者の名前やメールアドレスではありません。

どの投稿者を参照するかを示す値です。

記事と投稿者名を一緒に表示するときは、後ほど説明するJOINなどで組み合わせます。

外部キーの値は重複できます

次の2つの記事は、どちらもuser_id = 1です。

idtitleuser_id
1Next.js入門1
2データベース入門1

これは、同じ投稿者が複数の記事を書いている状態です。

今回の関係は、投稿者1人に対して記事が複数ある「1対多」です。

外部キーは、主キーのように必ず重複禁止になるわけではありません。外部キー列へ別途UNIQUE制約を付けなければ、複数の行が同じ参照先を持てます。(🔗 sqlite.org)

存在しない投稿者は参照させない

もしusersにID999の投稿者がいなければ、次の記事は保存させたくありません。

idtitleuser_id
4不明な投稿者の記事999

外部キー制約を有効にすると、このような不整合を防げます。

ただし、user_idという列名を付けるだけでは外部キー制約になりません。 SQLなどで参照関係を定義する必要があります。(🔗 sqlite.org)

外部キーは必ず主キーだけを参照するのか

今回は主キーを参照しますが、外部キーは条件を満たす一意な列や列の組み合わせを参照することもできます。

初心者の段階では、まず**「記事のuser_idから、投稿者の主キーidを参照する」**という基本形を確実に理解します。(🔗 postgresql.org)

NULLと制約も一緒に理解する

テーブルの設計では、列名とデータ型だけでなく、値に対するルールも重要です。

NULLは「空文字」とは違います

NULLは、値が未設定・不明・該当なしなどであることを表すために使います。

一方、空文字''は、長さ0の文字列です。

そのため、NOT NULLを付けても、空文字まで自動的に禁止できるわけではありません。今回のSQLiteでも、NOT NULLと文字列の内容に対するチェックは別の制約です。(🔗 sqlite.org)

値今回の意味
'Next.js入門'タイトルが入っています
''文字列ですが、文字がありません
NULLタイトルの値が未設定です

今回使う制約

制約役割使用例
PRIMARY KEY行を一意に識別しますusers.id
FOREIGN KEY参照先との関係を守りますposts.user_id
NOT NULLNULLを禁止しますposts.title
UNIQUE値の重複を禁止しますusers.email
CHECK条件を満たすか確認しますタイトルの長さ

これらはSQLiteで定義できる制約です。アプリ側の入力確認と組み合わせ、データの整合性を守ります。(🔗 sqlite.org)

SQLとは何かを理解する

SQLは、今回のようなデータベースで、テーブルの作成やデータの取得・変更を指示するために使う言語です。

今日は、まず次の対応関係を覚えます。

操作SQLブログでの例
テーブルを作るCREATE TABLE記事テーブルを作ります
データを追加するINSERT記事を投稿します
データを取得するSELECT記事一覧を表示します
データを更新するUPDATE記事タイトルを編集します
データを削除するDELETE記事を削除します

後半の追加・取得・更新・削除が、CRUDアプリの基本操作になります。SQLiteでも、これらのSQL文を使って操作できます。(🔗 sqlite.org)

実践:投稿者と記事のデータベースを作る

今回は、SQLite用のSQLを用意し、Pythonから実行します。

PythonはSQLを動かすための補助として使います。Next.jsアプリをPythonへ置き換えるわけではありません。 Next.jsとの接続は、今後の学習で行います。

Pythonのsqlite3が利用できる環境を前提とします。通常のPython環境では標準ライブラリとして利用でき、追加のデータベースサーバーを用意せずに実習できます。(🔗 docs.python.org)

作成するファイル

day51-database/
├── schema.sql
└── run_database.py

実行後には、同じフォルダーへday51.dbが作られます。

テーブルと初期データを定義する

schema.sql

<em>-- テーブル作成と初期データ登録を、まとめて確定します</em>
BEGIN;

<em>-- 投稿者を保存するテーブルです</em>
CREATE TABLE users (
    <em>-- 投稿者を識別する主キーです</em>
    id INTEGER PRIMARY KEY,

    <em>-- 投稿者名は未設定にできません</em>
    name TEXT NOT NULL,

    <em>-- 今回はメールアドレスの重複を禁止します</em>
    email TEXT NOT NULL UNIQUE
);

<em>-- 記事を保存するテーブルです</em>
CREATE TABLE posts (
    <em>-- 記事を識別する主キーです</em>
    id INTEGER PRIMARY KEY,

    <em>-- タイトルは1〜100文字とします</em>
    title TEXT NOT NULL
        CHECK (length(trim(title)) BETWEEN 1 AND 100),

    <em>-- 本文は未設定にできません</em>
    body TEXT NOT NULL,

    <em>-- 投稿者は必ず指定する設計です</em>
    user_id INTEGER NOT NULL,

    <em>-- 投稿者を参照する外部キー制約です</em>
    FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE RESTRICT
);

<em>-- 参照される投稿者を先に登録します</em>
INSERT INTO users (id, name, email)
VALUES
    (1, '葵', 'aoi@example.com'),
    (2, '蓮', 'ren@example.com');

<em>-- 登録済みの投稿者を指定して記事を追加します</em>
INSERT INTO posts (id, title, body, user_id)
VALUES
    (1, 'Next.js入門', 'Next.jsの基本を学びます。', 1),
    (2, 'データベース入門', 'テーブルとキーを学びます。', 1),
    (3, 'SQL入門', 'SQLによるデータ取得を学びます。', 2);

<em>-- ここまでの変更を確定します</em>
COMMIT;
SQL

定義を順番に読み解く

CREATE TABLE usersは、投稿者を保存する表を作る指示です。

id INTEGER PRIMARY KEYは、整数の主キーを作っています。name TEXT NOT NULLは、名前を文字列として扱い、未設定を禁止します。

記事側の外部キーは、次の部分です。

-- user_idの値は、users.idに存在する必要があります
FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE RESTRICT
SQL

今回はuser_idへNOT NULLも付けています。外部キーだけでは「投稿者の指定が必須」という意味にはならないためです。SQLiteの外部キーでは、子側のキーがNULLの場合、対応する親行を要求しない動作があります。(🔗 sqlite.org)

ON DELETE RESTRICTを付けた理由

記事が存在する投稿者を削除すると、記事だけが残って投稿者不明になる可能性があります。

今回は、それを防ぐため、参照されている投稿者は削除できない設計にしています。SQLiteのRESTRICTは、このような親行の削除などを拒否します。(🔗 sqlite.org)

BEGINとCOMMITは何をしているのか

複数の処理をひとまとまりとして扱う仕組みを、トランザクションと呼びます。

今回のSQLでは、BEGINで開始し、COMMITで確定します。失敗した場合は、実行用プログラム側でROLLBACKして未確定の変更を戻します。SQLiteでは、これらの文でトランザクションを制御できます。(🔗 sqlite.org)

現時点では、「途中まで成功した不完全な状態を残さないために、まとめて扱う」と理解すれば十分です。

SQLを実行して結果を確認する

run_database.py

# SQLiteとファイル操作の機能を読み込みます
import sqlite3
from pathlib import Path

# スクリプトと同じフォルダーを基準にします
BASE_DIR = Path(__file__).resolve().parent
DB_PATH = BASE_DIR / "day51.db"
SCHEMA_PATH = BASE_DIR / "schema.sql"

# 学習用DBの意図しない上書きや二重登録を避けます
if DB_PATH.exists():
    raise SystemExit(
        "day51.dbは既に存在します。"
        "再作成する場合は、内容を確認して別名へ移動してください。"
    )

# ファイルとして保存されるSQLiteへ接続します
connection = sqlite3.connect(DB_PATH)

try:
    # SQLiteでは、接続ごとに外部キー制約を有効にします
    connection.execute("PRAGMA foreign_keys = ON")

    # 外部キー制約が有効になったことを確認します
    enabled = connection.execute(
        "PRAGMA foreign_keys"
    ).fetchone()[0]

    if enabled != 1:
        raise RuntimeError("外部キー制約を有効にできませんでした。")

    # テーブルと初期データを作成します
    schema = SCHEMA_PATH.read_text(encoding="utf-8")
    connection.executescript(schema)

    print("記事一覧と投稿者名")

    # 記事と投稿者を関連付けて取得します
    rows = connection.execute(
        """
        SELECT
            posts.id,
            posts.title,
            users.name AS author_name
        FROM posts
        JOIN users
            ON posts.user_id = users.id
        ORDER BY posts.id;
        """
    ).fetchall()

    for row in rows:
        print(row)

    print("\n存在しない投稿者の記事を登録してみます")

    try:
        # エラー時は、このブロックの未確定の変更を戻します
        with connection:
            connection.execute(
                """
                INSERT INTO posts (title, body, user_id)
                VALUES (?, ?, ?);
                """,
                ("外部キー確認", "学習用の本文です。", 999),
            )
    except sqlite3.IntegrityError:
        print("外部キー制約によって登録が拒否されました。")

    print("\nデータベースを作成しました。")
    print(DB_PATH)

except Exception:
    # 初期化などに失敗した場合、未確定の変更を戻します
    connection.rollback()
    raise

finally:
    # with connectionだけでは接続は閉じないため、明示します
    connection.close()
Python

SQLiteの外部キー制約は、設定を推測せず、各接続で明示的に有効化することが重要です。また、トランザクションの途中で切り替えようとしても有効にならないため、今回は開始前に設定しています。(🔗 sqlite.org)

Pythonの接続オブジェクトをwithで使うと、正常終了時の確定や例外時のロールバックを扱えます。ただし、それだけでは接続自体は閉じないため、最後にclose()しています。(🔗 docs.python.org)

実行コマンド

ターミナルで、2つのファイルを置いたフォルダーへ移動して実行します。

# PythonでSQLiteの実習を実行します
python run_database.py

# 環境によっては、代わりにこちらを使います
# python3 run_database.py

想定される表示は、次のとおりです。最後のファイルパスは、環境によって異なります。

記事一覧と投稿者名
(1, 'Next.js入門', '葵')
(2, 'データベース入門', '葵')
(3, 'SQL入門', '蓮')

存在しない投稿者の記事を登録してみます
外部キー制約によって登録が拒否されました。

データベースを作成しました。

このスクリプトは実行後に接続を閉じますが、day51.dbはファイルとして残ります。

再実行は、二重登録を避けるため停止する設計です。初期化の途中で失敗した場合もファイルが残ることがあるため、内容を確認してから別名へ移動し、再実行します。

JOINで記事と投稿者を組み合わせる

実習では、次のSQLを使いました。

-- 記事と、その投稿者の名前を取得します
SELECT
    posts.id,
    posts.title,
    users.name AS author_name
FROM posts
JOIN users
    ON posts.user_id = users.id
ORDER BY posts.id;
SQL

今回の意味は、次のようになります。

  • postsから記事を読みます。
  • posts.user_idとusers.idが一致する投稿者を組み合わせます。
  • 記事ID、記事タイトル、投稿者名を取り出します。
  • 投稿者名には、結果上の名前としてauthor_nameを付けます。

外部キーは、参照関係の整合性を守ります。一方、JOINは、取得時に情報を組み合わせる処理です。外部キーを定義しただけで、投稿者名が記事データへ自動的に付くわけではありません。 (🔗 sqlite.org)

CRUDとのつながりを確認する

ここからは、今後作るCRUDアプリに対応するSQLの形を確認します。

以下は、今回のテーブルに対する操作例です。実行する場合は、先ほど作ったday51.dbへの接続で行います。

Create:記事を追加する

-- IDは省略し、SQLiteに整数値を割り当ててもらいます
INSERT INTO posts (title, body, user_id)
VALUES (
    '新しい記事',
    '新しい記事の本文です。',
    1
);
SQL

投稿者ID1が存在するため、今回の外部キー条件を満たします。

Read:記事を取得する

-- 指定したIDの記事を取得します
SELECT
    id,
    title,
    body,
    user_id
FROM posts
WHERE id = 1;
SQL

WHERE id = 1は、「IDが1の行」という条件です。

Update:記事を更新する

-- IDが1の記事だけを更新します
UPDATE posts
SET title = 'Next.js入門・改訂版'
WHERE id = 1;
SQL

今回は、タイトルが同じ記事が複数あっても、主キーで対象を指定できます。

Delete:記事を削除する

-- IDが3の記事だけを削除します
DELETE FROM posts
WHERE id = 3;
SQL

これらが、今後画面から実行する追加・取得・更新・削除の基本形です。Pythonのsqlite3でも、SQLを接続へ渡して操作します。(🔗 docs.python.org)

更新と削除では、対象条件を必ず確認する

UPDATEやDELETEでWHEREを省略すると、全行が対象になります。

学習中から、先に同じ条件のSELECTで対象を確認し、それから更新・削除する習慣を付けます。

-- まず、操作対象を確認します
SELECT id, title
FROM posts
WHERE id = 3;

-- 確認した対象だけを削除します
DELETE FROM posts
WHERE id = 3;
SQL

実際のアプリでは、対象が正しいことに加え、操作権限があることもサーバー側で確認します。Next.jsでも、変更処理の入口で認証と認可を確認する設計が必要です。(🔗 nextjs.org)

投稿者を削除するときの設計を考える

記事と投稿者を関連付けると、「投稿者を削除したら記事をどうするか」という判断が必要になります。

方針動作今回のブログでの意味
RESTRICT関連する記事があれば削除を拒否します記事を残したまま投稿者だけ消す操作を防ぎます
CASCADE関連する記事も削除します投稿者削除と同時に記事も削除します
SET NULL記事の投稿者をNULLにします投稿者不明の状態で記事を残します

SQLiteは、これらの外部キーアクションをサポートしています。なお、SET NULLを使うには、対象列がNULLを許せる設計である必要があります。(🔗 sqlite.org)

今回は、記事の消失を避けるため、RESTRICTを採用しました。

CASCADEは便利だから付けるものではありません。どのデータまで消えるべきかを決めてから使います。

Next.jsとデータベースはどのようにつながるのか

今日作ったテーブルは、今後のNext.jsアプリから利用する保存先のイメージです。

ブラウザーの画面
    ↓
サーバー側の処理
    ↓
データベース操作
    ↓
users・posts

Next.jsでは、Server Componentでデータを取得したり、Server ActionやRoute Handlerを通じて変更を受け付けたりする構成を取れます。サーバー側のデータアクセスでは、必要な権限を確認し、画面へ返す項目を絞ることが重要です。(🔗 nextjs.org)

今回は接続方法を混ぜすぎない

Day 51では、テーブルとキーの理解を優先します。

  • データベースをどう起動するか。
  • Next.jsからどのライブラリで接続するか。
  • ORMを使うか。
  • 公開環境でどこへ保存するか。

これらは次の段階で決めます。

今回のSQLite用SQLを、そのまますべてのデータベース製品へコピーできるとは限りません。自動採番、データ型、制約の細部などには実装差があるため、採用する製品に合わせて確認します。(🔗 sqlite.org)

セキュリティで特に重要なこと

主キーはアクセス権限ではありません

URLが/posts/1だからといって、その記事を誰でも編集してよいわけではありません。

主キーは対象を識別する値です。「どの記事か」と「その人が操作してよいか」は別の問題です。

また、外部キーが確認するのは参照先の存在などの整合性です。user_id = 1が有効でも、その利用者が投稿者1として操作してよいことまでは証明しません。

Next.jsの変更処理では、ブラウザーから送られた投稿者IDをそのまま信用せず、認証済みの利用者情報と権限をサーバー側で確認します。(🔗 nextjs.org)

SQLへ入力値を文字列連結しない

今回のPythonコードでは、次のように?を使っています。

# 値をSQL文字列へ直接埋め込まず、別の引数で渡します
connection.execute(
    """
    INSERT INTO posts (title, body, user_id)
    VALUES (?, ?, ?);
    """,
    (title, body, user_id),
)
Python

このように、SQLの構造と入力値を分けて渡す方法を使います。Pythonのsqlite3では、値のバインドにプレースホルダーを利用できます。(🔗 docs.python.org)

ただし、入力値の型・長さ・権限などの確認が不要になるわけではありません。

データベースファイルも公開してはいけません

今回のday51.dbは、学習用のローカルファイルです。

今後、個人情報などを保存する場合は、公開フォルダーへ置いたり、意図せず配布したりしない管理が必要です。今回の実習でも、本物の個人情報や秘密情報は入れず、掲載したダミーデータだけを使います。

よくある誤解を整理する

誤解正しい整理
データベースは表そのものです今回はデータベースの中に複数のテーブルを作ります
Rowは項目名ですRowはデータ1件分で、項目がColumnです
idという名前なら主キーです主キー制約を定義する必要があります
user_idという名前なら外部キーです外部キー制約を定義する必要があります
外部キーは重複できません今回のような1対多では、同じ投稿者IDを複数記事で使えます
外部キーがあれば認可もできますデータの整合性と操作権限は別です
NOT NULLで空文字も防げます空文字を禁止したい場合は別の確認が必要です
IDは必ず欠番なく並びます行の識別に使う値であり、件数や連続番号とは別です
外部キーを付ければ名前も取得できます取得時にはJOINなどで組み合わせます

主キー・外部キー・NULL・各制約の動作は、定義とデータベースの仕様に従います。名前だけで動作が決まるわけではありません。(🔗 sqlite.org)

自分で設計する練習

記事へコメントを追加する

次の条件で、commentsテーブルを考えます。

  • コメント1件を識別できます。
  • コメント本文を保存します。
  • どの記事へのコメントか分かります。
  • 誰が書いたコメントか分かります。

今回の方針なら、次のような列を候補にできます。

列名役割キー
idコメントの識別子主キー
bodyコメント本文なし
post_idコメント先の記事外部キー
user_idコメントした投稿者外部キー

この表をもとに、次の点も考えます。

  • 記事が存在しなければ、コメントを登録できないようにしますか。
  • 投稿者が存在しなければ、コメントを登録できないようにしますか。
  • 記事を削除したら、コメントも削除しますか。
  • 投稿者を削除したら、コメントは残しますか。

列を並べるだけでなく、関連先が消えたときにどうするかまで考えると、設計が具体的になります。

多対多の関係を考える

記事に複数のカテゴリーを付けたい場合、今回の投稿者と記事の関係とは異なる構造になります。

たとえば「ひとつの記事に複数のカテゴリーがあり、ひとつのカテゴリーに複数の記事がある」なら、中間テーブルを使う設計を考えられます。

posts
  ↓
post_categories
  ↑
categories

中間テーブルには、post_idとcategory_idを持たせます。両方を合わせた複合主キーにすれば、同じ組み合わせの重複を防ぐ設計にできます。複数列による主キーや外部キーも定義できます。(🔗 postgresql.org)

今回は詳細な実装までは不要です。「関係によって、必要な表の数が変わる」と覚えておきます。

設計メモのテンプレート

新しいCRUDアプリを考えるときは、次のようなメモから始められます。

【作るもの】
投稿者ごとに記事を管理するブログ

【保存する対象】
・投稿者
・記事

【users】
・id:主キー
・name:名前、未設定不可
・email:メールアドレス、未設定不可、重複不可

【posts】
・id:主キー
・title:タイトル、1〜100文字
・body:本文、未設定不可
・user_id:users.idを参照、未設定不可

【関係】
・投稿者1人に対して記事は複数
・記事1件には投稿者を1人指定

【削除ルール】
・記事がある投稿者は、そのままでは削除できない

【操作権限】
・記事の存在確認と、編集権限の確認を分ける
・投稿者IDをブラウザー入力だけで決めない

このような設計ができれば、次にSQLやORMで表現するときも、目的がぶれにくくなります。

Day 51のチェックリスト

  • DatabaseとTableの違いを説明できます。
  • Rowがデータ1件分、Columnが項目であると説明できます。
  • 記事タイトルとは別に主キーを持たせる理由を説明できます。
  • 外部キーで投稿者と記事を関連付けられます。
  • 外部キーの値が重複してもよいケースを理解しています。
  • 外部キーと認証・認可が別の仕組みだと理解しています。
  • NULLと空文字を区別できます。
  • 投稿者を先に登録し、その後に記事を登録する理由を説明できます。
  • 投稿者削除時の動作を設計できます。
  • 追加・取得・更新・削除が、どのデータへ作用するか説明できます。

まとめ

Day 51では、データベースを構成するTable、Row、Columnと、データの識別・関連付けに使うPrimary Key、Foreign Keyを学びました。

主キーは「その行を識別するもの」、外部キーは「参照関係の整合性を守るもの」です。今回のブログでは、投稿者と記事を別々のテーブルへ分け、記事のuser_idから投稿者のidを参照しました。

Next.jsでCRUDアプリを作るときも、最初に考えるのは画面だけではありません。何を保存し、何を必須にし、何と関連付け、削除時にどう扱うかを決めることが、安全で変更しやすいアプリの土台になります。

タイトルとURLをコピーしました