BioPlayground

🧬
목록으로

실험실 재고 시스템 — SQL + 인덱스 + Express로 시약 위치 즉시 찾기

엑셀로 관리하던 시약·항체·프라이머 재고를 웹 앱으로. SQL 스키마 설계, 인덱스 최적화, RESTful API를 실전 코드로 배웁니다.

심화
|
120분
|
검증 완료 (2026-07)
실험실 재고LIMS시약 관리SQL 인덱스Express 서버REST APIPostgreSQL
진행률0/16 (0%)

실험실 재고 시스템 — SQL + 인덱스 + Express로 시약 위치 즉시 찾기

이 토픽을 마치면

교과서에서 배운 SQL 스키마, DB 인덱스, 그리고 도구 개념 Express 서버 를 엮어서, 여러분의 실험실 재고를 웹 앱으로 관리하는 미니 LIMS(Laboratory Information Management System)를 직접 만들 수 있습니다. 왜 인덱스 하나 걸었을 뿐인데 검색이 5,000배 빨라지는지, 왜 관계형 스키마가 엑셀보다 협업에 유리한지 코드로 이해하게 됩니다.

이 글은 교육용 일반 예제 입니다. 실전 LIMS(Benchling, LabWare 등)는 훨씬 광범위한 기능을 갖지만, 그 심장부의 데이터 모델과 API 패턴은 여기서 배우는 것과 같습니다.

"어제 사놓은 항체 어디 있죠?" — 엑셀의 한계

여러분의 실험실이 다음 방식으로 재고를 관리한다고 합시다.

  • 시약 목록: 공용_재고.xlsx (구글 드라이브)
  • 냉장고 위치: 각자 머릿속
  • 유효기간: 라벨을 직접 봐야 함
  • 발주 이력: 이메일 검색

이 방식의 실전 문제:

문제 1: 동시 편집 충돌. 5명이 같은 엑셀을 열면 누군가의 수정이 사라집니다. 구글 시트가 개선하지만, 여전히 완전 안전하지 않습니다.

문제 2: 검색 속도. 시약 5,000종을 엑셀에서 필터로 찾기는 몇 초는 걸리고 정확한 매칭이 어렵습니다. "P53 항체"와 "anti-p53 antibody"가 다른 것으로 취급됩니다.

문제 3: 관계 표현이 어색합니다. 한 시약이 여러 냉장고에 나뉘어 있고, 각 위치마다 다른 유효기간의 배치가 있는 구조를 엑셀로 표현하려면 복잡한 병합 셀이 생깁니다.

문제 4: 자동화가 불가능합니다. "유효기간 30일 남은 시약 자동 알림", "재고 최소치 도달 시 발주 요청" 같은 자동 로직을 엑셀로 넣기가 힘듭니다.

진짜 접근은 관계형 데이터베이스와 웹 API입니다. PostgreSQL로 재고를 저장하고, Express 웹 서버로 검색·수정 API를 제공합니다. 클라이언트는 웹 앱, CLI, Slack 봇 어떤 것이든 될 수 있습니다.

블랙박스에서 부품으로 — LIMS 열어보기

네 부품이 핵심입니다.

부품 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; -- 를 입력해도 그 문자열은 문자열 값으로만 처리됩니다.

네 부품을 합쳐 — 실전 검색 로직

이제 실전 시나리오를 구현합니다. "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);
});

페이딩 — 여러분이 채워야 할 세 빈칸

빈칸 1: 만료 임박 알림

앞으로 30일 안에 만료되는 로트들을 자동으로 찾는 엔드포인트.

javascript
app.get("/api/lots/expiring", async (req, res) => {
  const { days = 30 } = req.query;
  
  const query = `
    -- TODO: 다음 조건을 만족하는 로트 반환
    -- 1. expiration_date가 앞으로 :days 일 이내
    -- 2. 시약 이름, 위치 정보 조인
    -- 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 인덱스로 가속되지 않습니다. trigram 인덱스를 활성화하고 성능을 측정하세요.

sql
-- TODO 1: pg_trgm 확장 활성화
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- TODO 2: reagents 이름에 trigram 인덱스 생성
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 같은 파이썬 프레임워크로 간단한 UI를 빠르게 붙일 수도 있습니다.

동기화·모바일: 실전은 오프라인 편집 후 동기화, 모바일 앱 지원 등을 요구할 수 있습니다. Supabase나 PouchDB 같은 스택이 이에 맞습니다.

확장 프로젝트

1. Slack 봇 통합: /reagent p53 을 Slack에서 치면 여러분의 API를 호출해 결과를 채널에 표시.

2. 발주 워크플로: 최소 재고 미달 시 자동으로 발주 요청 티켓 생성. Vendor 정보 저장 후 발주서 PDF 자동 생성.

3. 사용량 통계: 매월 어떤 시약이 얼마나 소진됐는지 대시보드. 예산 계획에 활용.

4. 실험 노트북 연동: 실험을 기록할 때 사용한 시약을 자동으로 재고에서 차감. Benchling API 형태의 통합.

이 편의 부품 지도

  • [F] SQL 스키마 설계: 정규화, 외래 키, 관계 표현. 3-테이블 재고 모델.
  • [F] DB 인덱스: B-tree, 부분 인덱스, GIN trigram 인덱스. EXPLAIN ANALYZE로 성능 확인.
  • [W] Express 서버: 라우팅, 미들웨어, JSON body 파싱.
  • [W] DB 연결 풀: pg.Pool 로 연결 재사용.

[F] = 여러분이 직접 구현 / [W] = 완성 코드로 제공한 도구 개념.