Atsulog#005
データの置き場所#
データは，すべてサーバーの data/ フォルダにある SQLite のファイルに保存しています．
data/
├── articles.db 記事とタグ
├── articles.sql articles.db のバックアップ (SQL のテキスト)
├── images.db アップロードした画像
├── images.sql images.db のバックアップ
└── link-cards.db リンクカードのキャッシュ
SQLite とは，データベース全体を 1 つのファイルとして扱う組み込み型のデータベースです．MySQL や PostgreSQL のように別のサーバープロセスを立てる必要がなく，アプリから直接ファイルを読み書きします．ドライバーも外部パッケージではなく，Node.js に組み込まれた node:sqlite モジュールを使っています．
データベースは用途ごとに 3 つのファイルに分けています．
articles.db：記事の本文，タイトル，カテゴリ，シリーズ，タグなどimages.db：記事に貼る画像のバイナリlink-cards.db：リンクカード用に取得した外部ページの情報
flowchart TD
subgraph data["data/"]
db[("articles.db")]
images[("images.db")]
cards[("link-cards.db")]
asql["articles.sql"]
isql["images.sql"]
end
editor["Editor (/edit)"] -->|"articles, images"| server["Next.js server"]
server -->|"pages, feed, images"| reader["Reader"]
web["External sites"] -->|"OGP"| server
server <-->|"read / write"| db
server <-->|"save / serve"| images
server <-->|"cache"| cards
db -->|"sqlite3 .dump"| asql
images -->|"sqlite3 .dump"| isql
asql -.->|"db:restore"| db
isql -.->|"db:restore"| images
asql -->|"git push"| remote["Git remote"]
isql -->|"git push (LFS)"| remote
このうち link-cards.db は，消えても取得し直せる キャッシュ です．キャッシュとは，取得に時間のかかるデータを手元に一時保存しておき，次からはそれを使う仕組みです．失っても困らないので，記事や画像とはファイルを分け，バックアップの対象からも外しています．
接続の開き方#
接続は app/lib/db/db.ts の openDatabase で開きます．
function openDatabase(fileName: string, schema: string): DatabaseSync {
globalForDb.databases ??= new Map(); // 開いた接続をファイル名ごとに保持
let db = globalForDb.databases.get(fileName);
if (!db) {
db = new DatabaseSync(path.join(DATA_DIR, fileName));
db.exec("PRAGMA journal_mode = WAL"); // 読み込みと書き込みを並行させる
db.exec("PRAGMA busy_timeout = 5000"); // ロック中なら最大 5 秒待つ
db.exec("PRAGMA foreign_keys = ON"); // 外部キー制約を検査する
db.exec(schema); // CREATE TABLE IF NOT EXISTS ...
globalForDb.databases.set(fileName, db);
}
return db;
}
ポイントは次の 3 つです．
- 最初に使われたときに開く：モジュールを読み込んだ時点ではなく，初めて呼ばれたときにファイルを開きます．これにより，
next buildの途中でデータベースファイルが作られることを防いでいます globalThisに保持する：開発中はファイルを保存するたびにモジュールが読み込み直されます（HMR，Hot Module Replacement）．普通の変数に入れておくと読み込み直しのたびに接続が増えるので，再読み込みでも消えないglobalThisに置いています- テーブルは毎回
IF NOT EXISTSで作る：初回はテーブルを作り，2 回目以降は何もしません．データベースファイルがなければ，その場で空のものができます
PRAGMA は SQLite の設定を変える命令です．WAL（Write-Ahead Logging）とは，変更をまず別ファイル（articles.db-wal）に追記し，後でまとめて本体へ反映する方式です．書き込み中でも読み込みが待たされないので，記事を保存している間も閲覧者のリクエストを処理できます．また，SQLite は外部キー制約を既定では検査しないため，foreign_keys = ON で明示的に有効にしています．
テーブルの設計#
articles.db には，記事を入れる articles と，記事に付けたタグを入れる article_tags の 2 つのテーブルがあります．2 つの関係を ER 図（Entity Relationship Diagram）にすると，次のようになります．ER 図とは，テーブルとその列，テーブル同士のつながりを表す図です．
erDiagram
direction LR
articles ||--o{ article_tags : "has"
articles {
TEXT slug PK
TEXT title
TEXT category
TEXT series
INTEGER series_order
TEXT content
TEXT created_at
TEXT modified_at
INTEGER published
}
article_tags {
TEXT slug PK, FK
TEXT tag PK
}
PK は 主キー（Primary Key），FK は 外部キー（Foreign Key）を表します．主キーとは，テーブルの中で行を 1 つに特定するための列で，同じ値の行は 2 つ存在できません．外部キーとは，別のテーブルの主キーを参照する列で，参照先に存在しない値は入れられません．線の両端の記号は，articles の 1 行に対して article_tags の行が 0 個以上あり，article_tags の 1 行は必ず 1 つの記事に属する，という関係を表しています．
SQL で書くと次のとおりです．
CREATE TABLE IF NOT EXISTS articles (
slug TEXT PRIMARY KEY, -- URL に使う ID (例: 20261101222711)
title TEXT NOT NULL,
category TEXT NOT NULL,
series TEXT, -- シリーズに属さなければ NULL
series_order INTEGER, -- シリーズ内での順番
content TEXT NOT NULL, -- Markdown の本文
created_at TEXT NOT NULL, -- ISO 8601 形式の日時
modified_at TEXT NOT NULL,
published INTEGER NOT NULL -- 1 なら公開，0 なら下書き
);
CREATE TABLE IF NOT EXISTS article_tags (
slug TEXT NOT NULL REFERENCES articles(slug),
tag TEXT NOT NULL,
PRIMARY KEY (slug, tag) -- 同じ記事に同じタグは 1 つだけ
);
CREATE INDEX IF NOT EXISTS article_tags_tag ON article_tags(tag);
記事とタグは多対多#
1 つの記事には複数のタグを付けられ，1 つのタグは複数の記事に付けられます．このように，どちらの側から見ても相手が複数ありうる関係を 多対多（many-to-many）の関係といいます．たとえば，実際の記事とタグは次のようにつながっています．
flowchart LR
a2["Atsulog#35;002"] --- next["Next.js"]
a3["Atsulog#35;003"] --- next
a4["Atsulog#35;004"] --- next
a4 --- jwt["JWT"]
a4 --- cookie["Cookie"]
Atsulog#004 から見るとタグは 3 つあり，Next.js から見ると記事は 3 つあります．
リレーショナルデータベースでは，1 つの列に複数の値を入れないのが原則です．articles に tags という列を作って "Cookie,JWT,Next.js" のような文字列を入れると，「Next.js のタグが付いた記事」を探すときに，すべての行の文字列を分解して調べることになります．そこで，記事とタグの 組み合わせ 1 つを 1 行 として article_tags に保存しています．上の例は，article_tags の中では次の 5 行になります．
slug tag
-------------- -------
20260604093802 Next.js
20260717160258 Next.js
20261101222711 Cookie
20261101222711 JWT
20261101222711 Next.js
このように，多対多の関係を組み合わせの行で表すテーブルを 中間テーブル（junction table）と呼びます．一般的な設計では，タグにも tags というテーブルを作って ID を振り，中間テーブルには記事の ID とタグの ID の組を入れます．Atsulog ではタグの名前そのものを値として入れており，tags テーブルは持っていません．タグの一覧が必要なときは，article_tags から SELECT DISTINCT tag で重複を除いて取り出しています．そのため，どの記事にも付いていないタグは存在しません．
article_tags の 2 つの列には，それぞれ次の役割があります．
- 主キー
(slug, tag)：2 つの列の組で 1 行を特定する 複合主キー で，同じ記事に同じタグが 2 回付くのを防ぐ - 外部キー
slug：articlesに存在しない記事のタグは入れられない - インデックス
article_tags_tag：タグから記事を探すためのもの
インデックス とは，列の値から行をすばやく探すための索引です．複合主キーにも自動でインデックスが作られますが，これは slug → tag の順に並んでいるので，「ある記事のタグ」は速く探せても，「あるタグの記事」は探せません．タグで絞り込む検索のために，tag 列だけのインデックスを別に作っています．
タグを 1 行にまとめて読む#
読み出すときは，サブクエリの json_group_array でタグを JSON の配列にまとめ，記事 1 件を 1 行で受け取ります．
SELECT a.slug, a.title, ...,
(SELECT json_group_array(tag ORDER BY tag) FROM article_tags t
WHERE t.slug = a.slug) AS tags -- 例: '["Next.js","SQLite"]'
FROM articles a
TypeScript 側では，この文字列を JSON.parse で配列に戻しています．
記事の作成と更新#
記事を書き込む処理は，すべて トランザクション の中で行います．トランザクションとは，複数の操作をひとまとまりにし，すべて成功するか，すべてなかったことにするかのどちらかにする仕組みです．記事の行とタグの行を別々に書き込むので，途中で失敗して片方だけが残ることを防いでいます．
export function transaction<T>(task: (db: DatabaseSync) => T): T {
const db = getArticleDb();
db.exec("BEGIN IMMEDIATE"); // 開始と同時に書き込みロックを取る
try {
const result = task(db);
db.exec("COMMIT"); // すべて成功したら確定
return result;
} catch (error: unknown) {
db.exec("ROLLBACK"); // 途中で失敗したらすべて取り消す
throw error;
}
}
BEGIN IMMEDIATE は，トランザクションの開始時点で書き込みのロックを取る書き方です．単なる BEGIN では最初の書き込みの時点までロックを取らないので，途中で別の書き込みとぶつかって失敗することがあります．
記事の取得と検索#
記事を読む処理は app/lib/article/article-repository.ts にまとめています．検索条件は，SQL の断片とそのパラメーターの組（Condition）として 1 つずつ作り，最後に AND でつなぎます．
/** A WHERE clause and its positional parameters. */
type Condition = { sql: string; params: SQLInputValue[] };
function combine(conditions: Condition[]): Condition {
return {
sql: conditions.map((c) => `(${c.sql})`).join(" AND "), // 条件を AND でつなぐ
params: conditions.flatMap((c) => c.params), // パラメーターも順に連結
};
}
値は SQL の文字列に直接埋め込まず，すべて ? の プレースホルダー で渡しています．プレースホルダーとは，SQL の中で値が入る位置を示す記号で，値はデータベース側で安全に埋め込まれます．入力に SQL の一部を混ぜて実行させる SQL インジェクション を防ぐための基本的な書き方です．
たとえば，シリーズで絞り込むときは次の Condition が作られます．
// シリーズ "atsulog" で絞り込む場合
{ sql: "a.series = ? COLLATE NOCASE", params: ["atsulog"] } // ? に "atsulog" が入る
また，読み込み関数はどれも最初に Next.js の connection() を呼んでいます．
export async function getArticleBySlug(slug: string) {
// Request time only: a build that opened the database would create it.
await connection(); // 実際のリクエストが来るまで待つ
// ...
}
Next.js はビルド時に，できるだけ多くのページを前もって HTML にしておこうとします．connection() を呼ぶと，そのページはリクエストのたびに描画するものとして扱われるので，ビルド中にデータベースが開かれることがなくなります．
バックアップ#
バックアップは，データベースを SQL のテキスト に書き出し（ダンプし），それを Git でリモートのリポジトリへ送る方式です．data/ フォルダ自体が，アプリ本体とは別の Git リポジトリになっています．
SQLite の .db ファイルはバイナリなので，Git では 1 行変えただけでもファイル全体が変わったものとして扱われます．sqlite3 articles.db .dump で書き出した SQL は，テーブルを作る文とデータを入れる INSERT 文が並ぶテキストなので，記事を 1 本足せば差分も INSERT 文 1 行分になります．.db ファイルは .gitignore で除外し，Git にはダンプだけを入れています．
この作業は data/git_autopush.sh が行い，サーバーの cron で 1 時間ごとに実行しています．cron とは，決まった時刻にコマンドを自動で実行する Linux の仕組みです．
flowchart TD
C["cron (hourly)"] --> Q{"PRAGMA quick_check<br>is ok?"}
Q -->|No| X["Abort"]
Q -->|Yes| D["Dump to .sql.tmp"]
D --> T{"Last line is<br>COMMIT;?"}
T -->|No| X
T -->|Yes| M["Rename to .sql"]
M --> G{"Any changes<br>in git?"}
G -->|No| S["Skip"]
G -->|Yes| P["git commit and push"]
.dump コマンドは，途中でエラーが起きても終了コードが 0（成功）になってしまいます．そこで，書き出す前に PRAGMA quick_check でデータベースが壊れていないかを確かめ，書き出した後もファイルの最終行が COMMIT; で終わっているかを確かめています．どちらかが失敗したら，一時ファイルを消して処理を止めるので，壊れたダンプで正しいバックアップを上書きすることはありません．
画像のダンプ images.sql は画像のバイナリを含むので，ほかのファイルよりずっと大きくなります．そのため，このファイルだけは .gitattributes で Git LFS（Large File Storage）の管理にしています．Git LFS とは，大きなファイルの中身を別の保管場所に置き，リポジトリには中身を指す小さなファイルだけを残す Git の拡張機能です．
復元#
復元は，ダンプの SQL を sqlite3 に流し込んで .db ファイルを作り直すだけです．
rm -f data/articles.db data/articles.db-wal data/articles.db-shm # 古いファイルを消す
sqlite3 data/articles.db <data/articles.sql # SQL から作り直す
WAL モードでは，本体のほかに -wal と -shm のファイルができます．古いものが残っていると新しい .db と食い違うので，先にまとめて消しています．ローカルでは npm run db:restore で，記事と画像の両方を作り直せます．