ALTER TABLE を手で打たない — ORMとマイグレーション

ORMとマイグレーション。コードの書類とデータベース、変更履歴を並べたカード。

ここまでで、APIは一通り完成しました。そして、app.js はこうなっています。

前回: 速いハッシュは使わない — パスワード保存とbcrypt 作業場所はVMの中(Ubuntu)です。前回までの ~/server/app.js と appdb をそのまま使います。

bash
$ grep -c "pool.query" ~/server/app.js
9

9箇所にSQLの文字列が散っています。 しかも、その多くが同じ形です。

"SELECT id, title, done, created_at FROM tasks WHERE id = ?"
"SELECT id, title, done, created_at FROM tasks WHERE user_id = ? ORDER BY id"

ここで、tasks に「締切」の列を1つ足すことを考えてください。

  1. sudo mysql に入って ALTER TABLE tasks ADD COLUMN due_date DATE; を打つ
  2. app.js の中の SELECT id, title, done, created_at を全部探して、due_date を足す
  3. 1箇所でも直し忘れると、そこだけ締切が返らない

この回は、この2つを別々に片づけます。 2番目が ORM、1番目が マイグレーションです。

対象レベル: 初級〜中級 / 区分: 必修

目次

この記事でわかること

  • ORMは魔法ではないこと(最後にSQLを組み立てているだけで、それは表示させて目で見られる)
  • ALTER TABLE を手で打ってはいけない理由(戻せない・他人と揃わない・別の環境で再現できない)
  • マイグレーションツールは必ず権限の壁にぶつかること(アプリ用ユーザーには表を作る力が無い、が正しい)
  • ORMを使わないほうがいい場面があること(複雑な集計、性能が要るところ)

この記事でやること

  1. 道具を1つ選ぶ(Prisma / Sequelize / Knex / TypeORM を比べて、理由を決める)
  2. マイグレーション専用のユーザーを作る(appuser は CRUD のまま触らない)
  3. すでにある表を、マイグレーションの出発点として登録する
  4. 列を1つ足して、戻す(migrate:latest と migrate:rollback)
  5. 生成されたSQLを表示させて、何が起きているか目で見る

先に知る言葉

この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。

言葉意味
ORMObject-Relational Mapping。データベースの行を、プログラムのオブジェクトとして扱えるようにする道具。SQLを自分で書かずに済む
クエリビルダORMより手前の道具。SQLを部品で組み立てる。行をオブジェクトに変換するところまではやらない
スキーマテーブルの構造。どんな列があって、どんな型か
マイグレーションスキーマの変更を1つずつファイルにして、順番に適用していくやり方。「テーブルの変更履歴をコードで持つ」
ロールバック適用したマイグレーションを1つ戻すこと
ベースラインすでに表がある状態から、マイグレーション管理を始めるための出発点
DDL / DMLDDL=表そのものを作る・変える命令(CREATE ALTER DROP)。DML=中のデータを触る命令(SELECT INSERT UPDATE DELETE)

DDLとDMLの区別が、この記事の裏テーマです。 MySQLの回で作った appuser には、DMLの4つしか与えていません。 そしてマイグレーションはDDLです。 つまり、appuser ではマイグレーションが動きません。 これは設定ミスではなく、そう作ったからです。あとで正面から片づけます。

ORMとは何か、そして何ではないか

ORMは、こういうものです。

// いままで
const [rows] = await pool.query(
  "SELECT id, title, done, created_at FROM tasks WHERE user_id = ? ORDER BY id",
  [req.session.userId]
);

// ORM / クエリビルダ
const rows = await knex("tasks").where({ user_id: req.session.userId }).orderBy("id");

何が変わったか。

いままでORM
SQLの文字列コードの中に埋まっている無い
列を足したとき文字列を全部探して直すselect を書いていなければ、自動で増える
打ち間違い動かして初めて分かる一部は動かす前に分かる
データベースを替えるとき方言を全部書き直す設定を1行変えるだけのことが多い

ORMは魔法ではありません

ここが、この記事で一番大事なところです。

ORMは、最終的にSQLの文字列を組み立てて、データベースに送っています。 あなたが書かなくなっただけで、SQLが消えたわけではありません。

これは、確かめられます。 あとで実際に表示させます。

そして、この事実から2つのことが言えます。

  1. SQLを知らないままORMを使うと、何が起きているか分からなくなる。 遅いときに直せません
  2. ORMが下手なSQLを作ることがある。 その代表が N+1問題です(後述)

だから順番が大事です。 前々回でSQLを手で打ったあとで、ORMに来ています。逆にすると、一生「動いてるからいい」から出られません。

マイグレーションとは何か

もう1つの問題です。

前回、あなたはこれを手で打ちました。

ALTER TABLE tasks ADD COLUMN user_id INT NULL;

動きました。 では、何が困るのか。3つあります。

① 別の環境で同じ状態を作れない

このVMの appdb は、いま「正しい形」をしています。

では、もう1台VMを立てて、同じアプリを動かすことになったとき、どうしますか。

この記事とコマンド履歴を読み返して、手で打ち直します。 CREATE TABLE を3つと、ALTER TABLE を1つ。順番も合わせて。

打ち忘れが1つあれば、そのVMだけ動きません。 そして、打ち忘れたことに気づくのは、動かして落ちたときです。

② 戻せない

ALTER TABLE に「元に戻す」はありません。

ADD COLUMN の反対は DROP COLUMN ですが、それは自分で思い出して打つものです。打つとデータが消えます。

③ 他人と揃わない

2人で開発したら、こうなります。

  • あなたが user_id を足した
  • 相手のPCの appdb には、その列が無い
  • 相手が最新のコードを取ってくると、Unknown column 'user_id' で落ちる

「あ、SQL打つの言い忘れてた」が、毎回起きます。

マイグレーションは、この3つを1つのやり方で解決します。

スキーマの変更を、1回ぶんずつファイルにして、コードと一緒に置く。

– 別の環境では、そのファイルを上から順に適用するだけで同じ形になる – 各ファイルに「戻すときの処理」も一緒に書くので、戻せる – ファイルはコードと一緒に配られるので、他人と揃う

そして「どこまで適用したか」は、データベース自身が覚えます。 専用のテーブルが1枚できて、そこに適用済みのファイル名が入ります。だから2回流しても二重に適用されません。

道具を選ぶ

Node.js で使われているものは、大きく4つです。

何をするもの特徴この順路で見たときの難点
PrismaORM + マイグレーション独自のスキーマ言語で表を定義し、そこから型付きのクライアントを生成する。いま一番人気「戻す」が素直に無い(新しい変更を足して打ち消す考え方)。既存DBからの開始に手順が要る
SequelizeORM + マイグレーション歴史が長い。モデル定義とマイグレーションが別々に要る同じことを2箇所に書くので、初めてだとズレて混乱しやすい
TypeORMORM + マイグレーションTypeScript前提。クラスに印を付けて表にするTypeScriptをまだやっていない
Knexクエリビルダ + マイグレーションSQLに近い形で書く。生成されるSQLが読めるORMではない(行をオブジェクトに変換しない)

この記事で選ぶのは Knex です

そして、Knexは厳密にはORMではありません。 クエリビルダです。それを承知で選びます。

選んだ理由は3つです。

  1. 生成されるSQLが1対1で読める。 .where({ done: false }) が where `done` = ? になる、が目で追えます。「ORMは魔法ではない」を確かめるのに、これ以上の教材がありません
  2. migrate:rollback で本当に戻る。 各マイグレーションに up と down を書くので、戻す処理を自分で書くことになります。 これが一番勉強になります
  3. いま入っている mysql2 の上でそのまま動く。 追加で入れるのは knex だけ。JavaScriptのままで、生成コードもありません

選ばなかった理由も、はっきり書きます。

Prisma は、実務では非常に有力です。 型が効くので、列名の打ち間違いが書いている最中に分かります。次にTypeScriptをやったら、そのとき試す価値があります。 ここで選ばなかったのは、「戻す」の実演がしにくいのと、独自のスキーマ言語が1枚挟まって、SQLとの対応が見えにくくなるからです。この順路の目的は「仕組みが分かること」なので、いまは見えるほうを取ります。

道具は乗り換えられます。 大事なのは、「マイグレーションという考え方」が身につくことです。それはPrismaでもSequelizeでも同じです。

① 先に権限の壁を片づける

やってみると分かるので、先に1回ぶつけます。

bash
$ cd ~/server
$ npm install knex

設定ファイルを作ります。

bash
$ nano ~/server/knexfile.js
module.exports = {
  development: {
    client: "mysql2",
    connection: {
      host: "127.0.0.1",
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DB_NAME,
    },
    migrations: {
      directory: "./migrations",
    },
  },
};

development という段が1枚挟まっているのは、Knexの決まりです。production を別に書けるようになっています。指定しなければ development が使われます。 接続先は process.env から読んでいます。 ファイルにパスワードを書いていないのは、前々回と同じ理由です。

からのマイグレーションを1つ作って、流してみます。

bash
$ mkdir -p ~/server/migrations
$ node --env-file=.env node_modules/knex/bin/cli.js migrate:make init
$ node --env-file=.env node_modules/knex/bin/cli.js migrate:latest
migration failed with error: create table `knex_migrations` ...
ER_TABLEACCESS_DENIED_ERROR: CREATE command denied to user 'appuser'@'localhost' for table 'knex_migrations'

失敗します。そして、これは正しい失敗です。

Knexは、まず「どこまで適用したか」を記録するテーブルを自分で作ろうとします(knex_migrations と knex_migrations_lock)。 CREATE TABLE は DDL です。 appuser にはDMLの4つしか与えていないので、当然拒否されます。

[スクリーンショット: 01_権限で失敗.png] migrate:latest が CREATE command denied to user 'appuser'@'localhost' で止まっている画面を撮ってください。この失敗が出るのが正常なので、そのまま撮って構いません。

やってはいけない直し方

検索すると、こう書いてあることがあります。

GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';

動きます。そして、これをやると前々回の話が全部無駄になります。

appuser は、Webからのリクエストを処理するユーザーです。もし SQLインジェクションの穴が1つ残っていたら、DROP TABLE まで通るようになります。

「マイグレーションのために権限を足す」は、順番が逆です。 マイグレーションを実行するのは、アプリではありません。あなたです。 だったら、それ専用のユーザーを作るのが筋です。

マイグレーション専用のユーザーを作る

bash
$ openssl rand -base64 18
$ sudo mysql
CREATE USER 'appmigrator'@'localhost' IDENTIFIED BY 'いま作った文字列';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX, REFERENCES
  ON appdb.* TO 'appmigrator'@'localhost';
FLUSH PRIVILEGES;
与えた権限なぜ要るか
CREATE / ALTER / DROP表を作る・変える・消す。 マイグレーションの本体
INDEX索引を張る・外す
REFERENCES外部キー(表と表を関連づける)を張るのに要る
SELECT / INSERT / …適用済みの記録を読み書きするため

ON appdb.* のままです。 他のデータベースには相変わらず触れません。root を使わないのは、ここでも同じです。

専用の .env を分けます。

bash
$ nano ~/server/.env.migrate
DB_USER=appmigrator
DB_PASSWORD=いま作った文字列
DB_NAME=appdb
bash
$ echo ".env.migrate" >> ~/server/.gitignore

同じ knexfile.js を、違う .env で読ませています。 アプリを動かすときは .env(appuser)、マイグレーションを流すときは .env.migrate(appmigrator)。 設定ファイルは1枚、繋ぐ人が2人、という形です。「誰として繋ぐか」がコマンドで決まるのが要点です。

流し直します。

bash
$ node --env-file=.env.migrate node_modules/knex/bin/cli.js migrate:latest
Batch 1 run: 1 migrations

通りました。 記録用の表ができています。

bash
$ sudo mysql -e "SHOW TABLES FROM appdb;"
+-----------------------+
| Tables_in_appdb       |
+-----------------------+
| knex_migrations       |
| knex_migrations_lock  |
| sessions              |
| tasks                 |
| users                 |
+-----------------------+

② すでに表がある、という現実

普通の入門記事は、からのデータベースから始まります。 現実はそうではありません。

tasks も users も sessions も、もう手で作ってあります。 ここから始めるには、2つのやり方があります。

やり方中身いつ使うか
イントロスペクションいまのDBを読み取って、定義ファイルを自動生成する表が何十枚もある既存システム
ベースライン「いまの形」を最初の1本目として書いて、すでに適用済みという扱いにする表が数枚。いまのあなた

表が3枚なので、ベースラインを手で書きます。 そのほうが中身を理解できます。

1本目 — いまの形をそのまま書く

さっき作った空のファイルを開きます。 migrations/ の中の ..._init.js です。

bash
$ ls ~/server/migrations/
$ nano ~/server/migrations/*_init.js

中身を、まるごとこれにします。

exports.up = async function (knex) {
  if (!(await knex.schema.hasTable("users"))) {
    await knex.schema.createTable("users", (t) => {
      t.increments("id");
      t.string("email", 255).notNullable().unique();
      t.string("password_hash", 255).notNullable();
      t.timestamp("created_at").notNullable().defaultTo(knex.fn.now());
    });
  }

  if (!(await knex.schema.hasTable("tasks"))) {
    await knex.schema.createTable("tasks", (t) => {
      t.increments("id");
      t.string("title", 200).notNullable();
      t.boolean("done").notNullable().defaultTo(false);
      t.timestamp("created_at").notNullable().defaultTo(knex.fn.now());
      t.integer("user_id").nullable();
    });
  }

  if (!(await knex.schema.hasTable("sessions"))) {
    await knex.raw(`
      CREATE TABLE sessions (
        session_id VARCHAR(128) COLLATE utf8mb4_bin NOT NULL,
        expires    INT UNSIGNED NOT NULL,
        data       MEDIUMTEXT COLLATE utf8mb4_bin,
        PRIMARY KEY (session_id)
      )
    `);
  }
};

exports.down = async function (knex) {
  await knex.schema.dropTableIfExists("sessions");
  await knex.schema.dropTableIfExists("tasks");
  await knex.schema.dropTableIfExists("users");
};

3つ、読みどころがあります。

書き方意味
hasTable で囲んでいるすでにある表は作らない。 これでベースラインになる。からのDBに流せば、ちゃんと3枚できる
t.increments("id")INT AUTO_INCREMENT PRIMARY KEY と同じ。前々回に手で書いたものと、1対1で対応している
knex.raw を混ぜているsessions は COLLATE utf8mb4_bin が要るので、素直にSQLで書いた。 無理に道具の書き方に寄せない

down の順番に注目してください。 up と逆順に消しています。 表の関連づけ(外部キー)を張ると、参照されている表は先に消せません。 戻す処理は、必ず作った逆順に書く、と覚えてください。

もう1本目は適用済みなので、流しても何も起きません。 確かめます。

bash
$ node --env-file=.env.migrate node_modules/knex/bin/cli.js migrate:status
Found 1 Completed Migration file/files.
20260902103000_init.js

③ 前に進める — 列を1つ足す

冒頭の「締切を足す」を、今度は正しくやります。

bash
$ node --env-file=.env.migrate node_modules/knex/bin/cli.js migrate:make add_due_date_to_tasks
Created Migration: .../migrations/20260902104500_add_due_date_to_tasks.js

先頭の数字が日時です。 これが適用の順番になります。だからファイル名を書き換えてはいけません。

bash
$ nano ~/server/migrations/*_add_due_date_to_tasks.js
exports.up = function (knex) {
  return knex.schema.alterTable("tasks", (t) => {
    t.date("due_date").nullable();
  });
};

exports.down = function (knex) {
  return knex.schema.alterTable("tasks", (t) => {
    t.dropColumn("due_date");
  });
};

nullable() にしています。 前回 user_id を足したときと同じ理由です。 すでに入っている行に締切が無いので、notNullable() にすると既存の行が違反して、マイグレーション自体が失敗します。 後から列を足すときは、既存の行をどうするかを必ず考える。 マイグレーションでも変わりません。

流します。

bash
$ node --env-file=.env.migrate node_modules/knex/bin/cli.js migrate:latest
Batch 2 run: 1 migrations

確かめます。

bash
$ sudo mysql -e "DESCRIBE appdb.tasks;"
+------------+--------------+------+-----+-------------------+
| Field      | Type         | Null | Key | Default           |
+------------+--------------+------+-----+-------------------+
| id         | int          | NO   | PRI | NULL              |
| title      | varchar(200) | NO   |     | NULL              |
| done       | tinyint(1)   | NO   |     | 0                 |
| created_at | timestamp    | NO   |     | CURRENT_TIMESTAMP |
| user_id    | int          | YES  |     | NULL              |
| due_date   | date         | YES  |     | NULL              |
+------------+--------------+------+-----+-------------------+

[スクリーンショット: 02_migrate_latest.png] migrate:latest が Batch 2 run: 1 migrations と出て、そのあと DESCRIBE appdb.tasks; に due_date が増えている画面を撮ってください。

④ 戻す — ロールバック

ここをやらない入門記事が多いのですが、一番大事です。

bash
$ node --env-file=.env.migrate node_modules/knex/bin/cli.js migrate:rollback
Batch 2 rolled back: 1 migrations
bash
$ sudo mysql -e "DESCRIBE appdb.tasks;" | grep due_date

何も出ません。列が消えています。

もう一度進めれば、また戻ります。

bash
$ node --env-file=.env.migrate node_modules/knex/bin/cli.js migrate:latest

[スクリーンショット: 03_rollback.png] migrate:rollback → DESCRIBE で due_date が消えている → migrate:latest → また出てくる、の3手を1枚に撮ってください。

「戻せる」の意味を、正確に理解してください

ここを誤解すると、本番でデータを飛ばします。

ロールバックが戻すのは「テーブルの形」だけです。「データ」は戻りません。

dropColumn で列を消せば、そこに入っていた値は消えます。 もう一度 migrate:latest しても、列は空で戻ってきます。

戻る戻らない
ADD COLUMN を戻す列の形その列に入っていた値
DROP TABLE を戻す表の形中身のデータ全部

だから、本番でのロールバックは「気軽な取り消し」ではありません。

練習のいまのうちに、rollback を何度も打ってください。 そして本番では、戻す前に必ずバックアップを取る、と覚えてください。down を書いておく価値は、「安全に戻せる」ことではなく、「戻す手順が、思いつきではなくコードとして残っている」ことです。

down を書くのが面倒でも、必ず書いてください。

exports.down = function (knex) {
  // これでは「戻る」と書いてあるのに戻りません
};

空の down は、嘘です。 rollback は成功したように見えて、何も起きません。 そしてその嘘に気づくのは、本当に戻したいときです。

⑤ 生成されたSQLを、目で見る

約束したところです。 ORMが何をしているのかを表示させます。

bash
$ nano ~/server/show-sql.js
const config = require("./knexfile").development;
const knex = require("knex")(config);

const q1 = knex("tasks").select("id", "title").where({ user_id: 1, done: false }).orderBy("id");
console.log(q1.toSQL().toNative());

const q2 = knex("tasks").where({ id: 3, user_id: 1 }).update({ done: true });
console.log(q2.toSQL().toNative());

const q3 = knex("tasks").where({ id: 3 }).del();
console.log(q3.toSQL().toNative());

knex.destroy();
bash
$ node --env-file=.env show-sql.js
{
  sql: 'select `id`, `title` from `tasks` where `user_id` = ? and `done` = ? order by `id` asc',
  bindings: [ 1, false ]
}
{
  sql: 'update `tasks` set `done` = ? where `id` = ? and `user_id` = ?',
  bindings: [ true, 3, 1 ]
}
{
  sql: 'delete from `tasks` where `id` = ? and `user_id` = ?',
  bindings: [ 3, 1 ]
}

これが「ORMは魔法ではない」の中身です。

  • .where({ ... }) は、WHERE a = ? AND b = ? を組み立てているだけ
  • 値は ? に入っている。 SQLインジェクションの対策は、あなたが手で書いていたのと同じことを、道具がやっているだけです
  • 1行のJavaScriptが、1行のSQLになる。 隠れて余計なことをしていません

[スクリーンショット: 04_生成されたSQL.png] node --env-file=.env show-sql.js の出力を撮ってください。? が並んでいるところが読めれば十分です。

動かしている最中のSQLも出せます

app.js にこれを足すと、飛んだSQLが全部ログに出ます。

knex.on("query", (q) => console.log("[SQL]", q.sql, q.bindings));

これは、遅い原因を探すときの最初の一手です。 「1ページ開いただけで、SQLが200本飛んでいた」が目で見えます。 それが次に書く N+1問題 です。 開発中だけ出してください。 本番で出すと、ログにデータが流れます。

⑥ app.js を書き換える

pool を knex に替えます。 app.js の pool を作っている部分を、こうします。

const knexConfig = require("./knexfile").development;
const knex = require("knex")(knexConfig);

そのうえで、/api/tasks の4本を置き換えます。

// 一覧(自分のものだけ)
app.get("/api/tasks", requireLogin, async (req, res) => {
  try {
    const rows = await knex("tasks").where({ user_id: req.session.userId }).orderBy("id");
    res.json(rows);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "データベースに繋がりません" });
  }
});

// 追加
app.post("/api/tasks", requireLogin, async (req, res) => {
  if (!req.body.title) {
    return res.status(400).json({ error: "title は必須です" });
  }
  try {
    const [id] = await knex("tasks").insert({
      title: req.body.title,
      user_id: req.session.userId,
    });
    const row = await knex("tasks").where({ id }).first();
    res.status(201).json(row);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "保存できませんでした" });
  }
});

// 削除
app.delete("/api/tasks/:id", requireLogin, async (req, res) => {
  try {
    const count = await knex("tasks")
      .where({ id: req.params.id, user_id: req.session.userId })
      .del();
    if (count === 0) {
      return res.status(404).json({ error: "そのタスクはありません" });
    }
    res.status(204).end();
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "削除できませんでした" });
  }
});

変わったところを、前回のコードと突き合わせてください。

前回今回
const [rows] = await pool.query(...)const rows = await knex(...) ([rows] の分解が要らない)
result.insertIdconst [id] = await ...insert()(MySQLでは追加したIDが配列で返る)
result.affectedRows === 0count === 0(del() と update() は件数そのものを返す)
WHERE id = ? AND user_id = ?.where({ id, user_id })

SELECT の列を書いていないので、due_date は勝手に付いてきます。 冒頭で「9箇所直す」と言った問題が、ここで消えました。 これがORMを使う一番の実利です。

ただし、select を省くと password_hash のような列も一緒に返ります。 users を返すときは、必ず select で列を指定してください。 SELECT * は楽ですが、うっかり見せてはいけない列を返します。 表と、返していいものは、別の話です。

動かします。

bash
$ node --env-file=.env app.js
bash
$ curl -s -c cookie.txt -X POST http://localhost:3000/api/login \
    -H "Content-Type: application/json" \
    -d '{"email":"me@example.com","password":"pass12345"}' | jq
$ curl -s -b cookie.txt http://localhost:3000/api/tasks | jq

due_date: null が付いて返れば成功です。

アプリは .env(appuser)で動いていることを確認してください。 appmigrator でアプリを動かしてはいけません。 分けた意味がなくなります。

⑦ ORMを使わないほうがいい場面

ORMは万能ではありません。 使わない判断も、道具のうちです。

① 複雑な集計

「ユーザーごとの、タスク総数と完了数」を出したいとします。

const [rows] = await knex.raw(`
  SELECT u.email,
         COUNT(t.id)  AS total,
         SUM(t.done)  AS done_count
  FROM users u
  LEFT JOIN tasks t ON t.user_id = u.id
  GROUP BY u.id, u.email
`);
console.log(rows);

これをORMの書き方で組み立てると、SQLより長く、読みにくくなります。

判断の目安は「SQLで書いたほうが短く読めるか」です。 JOIN が2つ以上、GROUP BY や副問い合わせが入ったら、raw に切り替える。それでいいです。

raw を使うときも、値は必ず ? に渡します。 knex.raw("... WHERE id = ?", [id]) の形です。文字列を繋いだ瞬間に、SQLインジェクションに戻ります。

② N+1問題

ORMで一番よく起きる性能事故です。

const users = await knex("users").select("id", "email");     // ← 1本
for (const u of users) {
  u.tasks = await knex("tasks").where({ user_id: u.id });    // ← 人数ぶん
}

ユーザーが100人いたら、SQLが101本飛びます。 これが N+1 です。

1本で済みます。

const rows = await knex("users")
  .leftJoin("tasks", "tasks.user_id", "users.id")
  .select("users.email", "tasks.id as task_id", "tasks.title");

ORMは、ループの中でクエリを書いても止めてくれません。 「1件ずつ取る」がプログラムとして自然に書けてしまうので、むしろSQLを直接書くより起きやすいくらいです。 さっきの knex.on("query", ...) を入れておけば、ログに101本並ぶので一発で分かります。

③ 性能が要るところ

索引の効き方まで面倒を見たいクエリは、SQLで書いてください。

ORMが出すSQLは、多くの場合「悪くない」程度です。ここぞという1本を詰めるときは、自分で書いたほうが速く、確実です。

まとめると、こうです。

場面使うもの
普通のCRUD(この記事の9割)ORM / クエリビルダ
JOIN が多い集計raw でSQL
性能を詰める1本raw でSQL
スキーマの変更必ずマイグレーション(ここだけは例外なし)

つまずいたところ

症状原因
CREATE command denied to user 'appuser'正しい失敗。 appuser はDMLだけ。.env.migrate で appmigrator として流す
npx knex migrate:latest が Access denied for user ''@'localhost'npx では .env が読まれない。 node --env-file=.env.migrate node_modules/knex/bin/cli.js ... の形で打つ
Cannot find module 'mysql'knexfile.js の client が mysql になっている。mysql2 にする
migrate:latest が何もせず終わるすでに適用済み。 migrate:status で確認する
ロールバックしたのにデータが戻らない仕様。 ロールバックが戻すのは形だけ。値は消えている
Unknown column 'due_date' が別のVMで出るそのVMで migrate:latest を流していない。 コードを配ったら、必ず流す
Migration table is already locked前回の実行が途中で落ちた。migrate:unlock で外す
ファイル名を直したら二重に適用されたファイル名が適用済みの記録そのもの。 作ったあとは絶対に名前を変えない
password_hash がAPIから返ってきたselect で列を指定していない。 users を返すときは必ず指定する

後片づけ

この回で足したもの。

~/server/knexfile.js
~/server/migrations/
~/server/show-sql.js
~/server/.env.migrate
appdb の knex_migrations / knex_migrations_lock
appmigrator(MySQLのユーザー)

SQL確認用のファイルは、もう要りません。

bash
$ rm -f ~/server/show-sql.js

全部やり直したいとき。

bash
$ node --env-file=.env.migrate node_modules/knex/bin/cli.js migrate:rollback --all

マイグレーション管理そのものをやめるとき。

DROP TABLE knex_migrations;
DROP TABLE knex_migrations_lock;
DROP USER 'appmigrator'@'localhost';

~/server/ と appdb は残してください。 次回、同じデータを何度も読みに行かない話(キャッシュ)に進みます。

まとめ

  • 問題は2つある。 SQLの文字列がコードに散ることと、テーブルの変更が手打ちで残らないこと。ORMが前者、マイグレーションが後者
  • ORMは魔法ではない。 最後にSQLを組み立てているだけで、.toSQL().toNative() で目で見られる
  • SQLを知らないままORMに入らない。 遅いときに直せなくなる
  • ALTER TABLE を手で打たない。 別の環境で再現できず、戻せず、他人と揃わない
  • マイグレーションは「変更1回ぶん」をファイルにする。 どこまで適用したかはデータベース自身が記録する
  • up と down を必ずセットで書く。 空の down は嘘で、本当に戻したいときに気づく
  • ロールバックが戻すのは「形」だけ。「データ」は戻らない
  • マイグレーションはDDL、アプリはDML。 だから appuser では流せないのが正しい
  • 権限が足りないとき、アプリ用ユーザーに権限を足さない。 専用のユーザーを作って、.env を分ける
  • 既存の表からは「ベースライン」で始める。 hasTable で囲えば、既存でも新規でも同じファイルが使える
  • JOIN が多い集計と、性能を詰める1本は raw でSQLを書く。 ただし値は ? に渡す
  • ループの中でクエリを書かない(N+1)。 knex.on("query", ...) を出しておけば一目で分かる

参考リンク


次に読む

この記事は 【第5段階】バックエンドとデータベース の 11 / 29 本目です。

記事
← 前の記事速いハッシュは使わない — パスワード保存とbcrypt
→ 次の記事数秒のクエリを数ミリ秒にする — SQLの到達作品

上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。

全体の順路は はじめに にあります。

roadmap.sh のどこをやっているか

Backend›Databases
  • ORMs(生成されるSQLを読む)
  • Migrations(スキーマの変更をコードで持つ)
  • Database Access Control(DDL と DML の分離)

この記事は、上の項目を実際に手を動かして確かめる回です。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

目次