BioPlayground

一覧へ

実験室の在庫システム — SQL + インデックス + Express で試薬の場所を即座に見つける

エクセルで管理していた試薬・抗体・プライマーの在庫をウェブアプリに。SQL スキーマ設計、インデックス最適化、RESTful API を実践的なコードで学びます。

上級
|
120分
|
検証済み (2026-07)
実験室の在庫LIMS試薬管理SQL インデックスExpress サーバーREST APIPostgreSQL
進捗0/19 (0%)

実験室の在庫システム — SQL + インデックス + Express で試薬の場所を即座に見つける

このトピックを終えると

教科書で学んだ SQL スキーマ、DB インデックス、そしてツール概念である Express サーバー を組み合わせて、実験室の在庫をウェブアプリで管理するミニ LIMS(Laboratory Information Management System)を自分で作れるようになります。なぜインデックスを一つ張っただけで検索が5,000倍速くなるのか、なぜリレーショナルスキーマがエクセルよりも協業に有利なのかをコードで理解できるようになります。

この記事は 教育用の一般例 です。実際の LIMS(Benchling、LabWare など)は遥かに広範な機能を持ちますが、その心臓部にあるデータモデルと API パターンはここで学ぶものと同じです。

「昨日買った抗体はどこにありますか?」 — エクセルの限界

みなさんの実験室が次のような方法で在庫を管理していたとしましょう。

  • 試薬リスト: 共用_在庫.xlsx(Google Drive)
  • 冷蔵庫の位置: それぞれの頭の中
  • 有効期限: ラベルを直接見る必要がある
  • 発注履歴: メール検索

この方式の実践的な問題:

問題 1: 同時編集の衝突。5人が同じエクセルを開くと、誰かの修正が消えます。Google シートが改善していますが、それでも完全に安全ではありません。

問題 2: 検索速度。試薬5,000種類をエクセルのフィルタで探すのは数秒かかり、正確なマッチングが難しいです。「P53 抗体」と「anti-p53 antibody」が別のものとして扱われます。

問題 3: 関係の表現がぎこちなくなります。ある試薬が複数の冷蔵庫に分かれていて、各位置ごとに異なる有効期限のバッチがある構造をエクセルで表現しようとすると、複雑なセル結合が発生します。

問題 4: 自動化が不可能です。「有効期限まで30日以内の試薬を自動通知」「在庫が最少値に到達したら発注要求」のような自動ロジックをエクセルに入れるのは困難です。

本当のアプローチはリレーショナルデータベースとウェブ API です。PostgreSQL で在庫を保存し、Express ウェブサーバーで検索・修正 API を提供します。クライアントはウェブアプリ、CLI、Slack ボット、どんなものでも構いません。

ブラックボックスから部品へ — LIMS を開けてみる

4つの部品が核心です。

部品 1: SQL スキーマ設計

リレーショナルスキーマの原則は 各概念を一つのテーブルに分離し、関係を外部キーで表現する ことです。実験室の在庫に適用すると:

sql
CREATE TABLE reagents (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    catalog_number TEXT,
    vendor TEXT,
    cas_number TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE storage_locations (
    id SERIAL PRIMARY KEY,
    room TEXT NOT NULL,
    unit TEXT NOT NULL,        -- 例: "冷蔵庫 A"、"-80 冷凍庫 2"
    shelf TEXT,
    temperature_c INTEGER
);

CREATE TABLE inventory_lots (
    id SERIAL PRIMARY KEY,
    reagent_id INTEGER REFERENCES reagents(id) ON DELETE CASCADE,
    location_id INTEGER REFERENCES storage_locations(id),
    lot_number TEXT,
    quantity NUMERIC NOT NULL,
    unit TEXT NOT NULL,        -- "mL"、"μg"、"vial"
    expiration_date DATE,
    received_date DATE DEFAULT CURRENT_DATE,
    is_opened BOOLEAN DEFAULT FALSE,
    notes TEXT
);

この 3 テーブルスキーマの力は、一つの試薬が複数のロットで複数の位置にある状況 を自然に表現することです。

部品 2: インデックス

インデックス は特定の列の検索を速くするデータ構造です。基本的に B-tree インデックスが使われます。

インデックスなしの検索:

sql
SELECT * FROM reagents WHERE name = 'anti-p53';

このクエリが 5,000 行のテーブルで実行されると 全体スキャン(Sequential Scan) です。平均 2,500 行を読んでマッチングを確認します。かかる時間 = 数ミリ秒から数十ミリ秒。

インデックス追加:

sql
CREATE INDEX idx_reagents_name ON reagents(name);

同じクエリが今度は インデックススキャン です。B-tree 検索で O(log n) 時間。5,000 行で 12 回以内で正確な位置を見つけます。かかる時間 = マイクロ秒単位。

5,000 行では大きな差ではありませんが、5,000 万行になると全体スキャンは数秒、インデックススキャンは依然としてミリ秒未満です。

部分マッチング検索 のためのインデックスは異なります:

sql
CREATE INDEX idx_reagents_name_trgm ON reagents USING GIN (name gin_trgm_ops);

これは pg_trgm 拡張を利用した トライグラム(trigram)インデックス で、LIKE '%p53%' のような部分マッチングも高速に検索できます。

期限切れ間近の照会インデックス:

sql
CREATE INDEX idx_lots_expiration ON inventory_lots(expiration_date)
  WHERE expiration_date IS NOT NULL;

WHERE 節があるインデックスは 部分インデックス で、条件に合う行だけがインデックスに含まれるためインデックスサイズが小さくなります。

部品 3: Express サーバーの骨格

javascript
import express from "express";
import pg from "pg";

const app = express();
const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL
});

app.use(express.json());

app.get("/api/reagents", async (req, res) => {
  const { search } = req.query;
  
  let query = "SELECT * FROM reagents";
  const params = [];
  
  if (search) {
    query += " WHERE name ILIKE $1 OR catalog_number ILIKE $1";
    params.push(`%${search}%`);
  }
  
  query += " ORDER BY name LIMIT 100";
  
  const result = await pool.query(query, params);
  res.json({ reagents: result.rows });
});

app.get("/api/reagents/:id/lots", async (req, res) => {
  const { id } = req.params;
  const result = await pool.query(
    `SELECT il.*, sl.room, sl.unit, sl.shelf
       FROM inventory_lots il
       JOIN storage_locations sl ON il.location_id = sl.id
      WHERE il.reagent_id = $1
      ORDER BY il.expiration_date NULLS LAST`,
    [id]
  );
  res.json({ lots: result.rows });
});

app.post("/api/lots", async (req, res) => {
  const { reagent_id, location_id, lot_number, quantity, unit, expiration_date } = req.body;
  
  const result = await pool.query(
    `INSERT INTO inventory_lots
       (reagent_id, location_id, lot_number, quantity, unit, expiration_date)
     VALUES ($1, $2, $3, $4, $5, $6)
     RETURNING *`,
    [reagent_id, location_id, lot_number, quantity, unit, expiration_date]
  );
  res.status(201).json({ lot: result.rows[0] });
});

app.listen(3000, () => console.log("LIMS server on http://localhost:3000"));

部品 4: パラメータ化されたクエリ

注意深い読者は上のコードで ${search} の代わりに $1 を使用したことに気づいたでしょう。これが SQL injection 防御 の標準です。

javascript
// 危険なコード
const query = `SELECT * FROM reagents WHERE name = '${userInput}'`;

// 安全なコード
const query = "SELECT * FROM reagents WHERE name = $1";
const result = await pool.query(query, [userInput]);

二つ目の形式で $1 は絶対に SQL 構文の一部にはなりません。ユーザーが '; DROP TABLE reagents; -- を入力しても、その文字列は文字列値としてだけ処理されます。

4つの部品を組み合わせて — 実践的な検索ロジック

これで実践シナリオを実装します。「p53 抗体」を検索すると、関連する試薬リスト + 各試薬のロット + 位置 + 有効期限まで一度に得たいです。

javascript
app.get("/api/search", async (req, res) => {
  const { q } = req.query;
  
  if (!q || q.length < 2) {
    return res.json({ results: [] });
  }
  
  const query = `
    SELECT
      r.id, r.name, r.catalog_number, r.vendor,
      COALESCE(json_agg(
        json_build_object(
          'lot_id', il.id,
          'lot_number', il.lot_number,
          'quantity', il.quantity,
          'unit', il.unit,
          'expiration_date', il.expiration_date,
          'location', sl.room || ' / ' || sl.unit || ' / ' || COALESCE(sl.shelf, '')
        ) ORDER BY il.expiration_date NULLS LAST
      ) FILTER (WHERE il.id IS NOT NULL), '[]') AS lots
    FROM reagents r
    LEFT JOIN inventory_lots il ON il.reagent_id = r.id
    LEFT JOIN storage_locations sl ON il.location_id = sl.id
    WHERE r.name ILIKE $1 OR r.catalog_number ILIKE $1
    GROUP BY r.id
    ORDER BY r.name
    LIMIT 50
  `;
  
  const result = await pool.query(query, [`%${q}%`]);
  res.json({ results: result.rows });
});

一度のクエリで試薬リストと各試薬のロットを組み合わせて返します。json_agg を利用したこのパターンが REST API レスポンス組み立ての標準です。

フロントエンドでこの API を呼び出すコード:

javascript
async function searchReagent(query) {
  const response = await fetch(`/api/search?q=${encodeURIComponent(query)}`);
  const data = await response.json();
  return data.results;
}

document.getElementById("search").addEventListener("input", async (e) => {
  const results = await searchReagent(e.target.value);
  renderResults(results);
});

フェーディング — みなさんが埋める3つの空欄

空欄 1: 期限切れ間近の通知

これから 30 日以内に有効期限が切れるロットを自動で見つけるエンドポイント。

javascript
app.get("/api/lots/expiring", async (req, res) => {
  const { days = 30 } = req.query;
  
  const query = `
    -- TODO: 次の条件を満たすロットを返す
    -- 1. expiration_date がこれから :days 日以内
    -- 2. 試薬名、位置情報を join
    -- 3. 迫っている順に整列
  `;
  
  // TODO: pool.query 実行後、結果を返す
});

ヒント:

sql
WHERE expiration_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '1 day' * $1

この照会が頻繁に実行されるなら、先に作った idx_lots_expiration インデックスが効果的です。

空欄 2: 在庫減少トランザクション

試薬を使用する時にロットの数量を減少させ、0 以下になったら自動で消尽処理します。この二つのステップが 同時に成功するか同時に失敗する 必要があります。

javascript
app.post("/api/lots/:id/consume", async (req, res) => {
  const { id } = req.params;
  const { amount } = req.body;
  
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    
    // TODO 1: SELECT ... FOR UPDATE で現在の数量を照会 (row lock)
    // TODO 2: 数量が amount 以上か確認。違えばエラー
    // TODO 3: UPDATE で数量を減少
    // TODO 4: 数量が 0 なら is_opened を true に (または別個の状態列)
    
    await client.query("COMMIT");
    // 結果を返す
  } catch (e) {
    await client.query("ROLLBACK");
    res.status(400).json({ error: e.message });
  } finally {
    client.release();
  }
});

ヒント: FOR UPDATE はトランザクション終了までに他のトランザクションが同じ行を修正できないようにロックします。

空欄 3: 検索最適化インデックス

ILIKE '%anything%' は基本 B-tree インデックスでは加速しません。トライグラムインデックス を有効化して性能を測定してください。

sql
-- TODO 1: pg_trgm 拡張を有効化
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- TODO 2: reagents 名にトライグラムインデックス生成
CREATE INDEX idx_reagents_name_trgm ON reagents USING GIN (name gin_trgm_ops);

性能確認:

sql
-- インデックス適用前/後比較
EXPLAIN ANALYZE
SELECT * FROM reagents WHERE name ILIKE '%p53%';

目標: 1 万行以上で 10ms 以下の応答。

省察 — この LIMS が実践システムとどう違うか

監査ログ(Audit Trail): 実践 LIMS は全てのデータ変更をログとして残します。誰がいつどれだけ使用したかを追跡。GLP(Good Laboratory Practice)/GMP 規制対応に必須。方法: pgaudit 拡張、または updated_by/updated_at 列とトリガー。

バーコード/QR スキャン: 実践では各ロットにバーコードを付けてスキャナーで即座に照会。みなさんのシステムでもロット ID を QR で印刷するエンドポイントを追加すると実用性が大きく高まります。

権限管理: 実践ではユーザー役割別にアクセス権限を分けます。学生は照会だけ、ポスドクは登録・修正、PI は発注承認など。標準的アプローチ: pg スキーマの authenticator 役割 + JWT トークン検証。

ウェブ UI: みなさんが作ったのは API サーバーだけです。実践では React/Vue のような SPA でデスクトップアプリのような UX を提供。または Streamlit/Dash のような Python フレームワークで簡単な UI を素早く付けることもできます。

同期・モバイル: 実践ではオフライン編集後の同期、モバイルアプリ支援などを要求する場合があります。Supabase や PouchDB のようなスタックがこれに合います。

拡張プロジェクト

1. Slack ボット統合: /reagent p53 を Slack で打つと、みなさんの API を呼び出して結果をチャンネルに表示。

2. 発注ワークフロー: 最少在庫未達時に自動で発注要求チケット生成。Vendor 情報保存後、発注書 PDF 自動生成。

3. 使用量統計: 毎月どの試薬がどれだけ消尽されたかのダッシュボード。予算計画に活用。

4. 実験ノートブック連動: 実験を記録する時に使用した試薬を自動で在庫から差し引く。Benchling API 形態の統合。

この編の部品マップ

  • [F] SQL スキーマ設計: 正規化、外部キー、関係表現。3 テーブル在庫モデル。
  • [F] DB インデックス: B-tree、部分インデックス、GIN トライグラムインデックス。EXPLAIN ANALYZE で性能確認。
  • [W] Express サーバー: ルーティング、ミドルウェア、JSON body パーシング。
  • [W] DB 接続プール: pg.Pool で接続再利用。

[F] = みなさんが直接実装 / [W] = 完成コードとして提供したツール概念。

💬 質問・コメント

0件のコメント

ログインせずに投稿できます。ゲスト投稿は投稿者自身で編集・削除できません。

0/2000

読み込み中...