Laboratory Inventory System — Find Reagent Locations Instantly with SQL + Index + Express

After completing this topic

You will be able to create your own mini LIMS (Laboratory Information Management System) as a web application by combining the SQL schema, DB index, and tool concept of the Express server that you learned in the textbook. You will gain a practical understanding of why adding a single index can make searches 5,000 times faster, and why a relational schema is more advantageous for collaboration than Excel.

This article is a general educational example. Real-world LIMS (such as Benchling and LabWare) have much more extensive functionality, but the core data model and API patterns are the same as what you learn here.

"Where are the antibodies I bought yesterday?" — The Limitations of Excel

Let's say your lab manages its inventory in the following way:

• Reagent list: 공용_재고.xlsx (Google Drive)
• Refrigerator location: Each person remembers it individually.
• Expiration date: You have to check the label directly.
• Ordering history: Search through emails.

The practical problems with this approach:

Problem 1: Simultaneous editing conflicts. If 5 people open the same Excel file, someone's edits will be lost. Google Sheets improves this, but it's still not completely safe.

Problem 2: Search speed. Searching for 5,000 reagents in Excel using filters takes several seconds, and it's difficult to get an exact match. "P53 antibody" and "anti-p53 antibody" are treated as different items.

Problem 3: Awkward representation of relationships. If a reagent is divided into multiple refrigerators, and each location has a different batch with different expiration dates, expressing this in Excel requires complex merged cells.

Problem 4: Automation is impossible. It's difficult to implement automated logic in Excel, such as "automatically notify when a reagent has 30 days left before expiration" or "request an order when the minimum stock level is reached."

The real solution is a relational database and a web API. Store the inventory in PostgreSQL and provide search and update APIs with an Express web server. The client can be a web app, a CLI, or a Slack bot.

From Black Box to Components — Unpacking a LIMS

Yes, components are key.

Component 1: SQL Schema Design

The principle of a relational schema is to separate each concept into its own table and represent relationships with foreign keys. When applied to a lab inventory:

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,        -- e.g., "Fridge A", "-80 Freezer 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
);

The power of this 3-table schema is that it naturally represents the situation where one reagent can exist in multiple lots, in multiple locations.

Component 2: Indexes

An index is a data structure that speeds up searches on a specific column. B-tree indexes are used by default.

Search without an index:

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

If this query runs on a table with 5,000 rows, it will perform a full table scan. It reads an average of 2,500 rows and checks for a match. This takes milliseconds to tens of milliseconds.

Adding an index:

CREATE INDEX idx_reagents_name ON reagents(name);

Now, the same query will perform an index scan. It uses a B-tree search, which takes O(log n) time. In a table with 5,000 rows, it finds the exact location in 12 steps or fewer. This takes microseconds.

For 5,000 rows, the difference is not huge, but for 50 million rows, a full table scan might take seconds, while an index scan still takes less than a millisecond.

Indexes for partial matching searches are different:

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

This is a trigram index using the pg_trgm extension, which allows you to quickly search for partial matches, such as LIKE '%p53%'.

Index for expiring soon queries:

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

An index with a WHERE clause is a partial index, which only includes rows that match the condition in the index, making the index smaller.

Component 3: Express Server Skeleton

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"));

Component 4: Parameterized Queries

The astute reader will have noticed the use of $1 instead of ${search} in the code above. This is the standard for SQL injection prevention.

// Dangerous code
const query = `SELECT * FROM reagents WHERE name = '${userInput}'`;

// Safe code
const query = "SELECT * FROM reagents WHERE name = $1";
const result = await pool.query(query, [userInput]);

In the second form, $1 cannot be part of the SQL syntax. Even if the user enters '; DROP TABLE reagents; --, it will only be treated as a string value.

Combine Four Pieces — Practical Search Logic

Now, let's implement a practical scenario. When searching for "p53 antibody," we want to retrieve a list of related reagents along with each reagent's lot, location, and expiration date, all in one go.

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 });
});

This code combines the reagent list and each reagent's lot into a single query and returns the result. This pattern, using json_agg, is a standard for assembling REST API responses.

The following code calls this API from the frontend:

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);
});

Fading — Three Blanks for You to Fill

Blank 1: Expiration Alert

An endpoint that automatically finds lots expiring within the next 30 days.

app.get("/api/lots/expiring", async (req, res) => {
  const { days = 30 } = req.query;
  
  const query = `
    -- TODO: Return lots that satisfy the following conditions
    -- 1. expiration_date is within the next :days
    -- 2. Join with reagent name and location information
    -- 3. Sort by expiration date
  `;
  
  // TODO: Execute pool.query and return the result
});

Hint:

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

If this query is executed frequently, the idx_lots_expiration index created earlier will be effective.

Blank 2: Inventory Reduction Transaction

When a reagent is used, decrease the quantity of the lot, and automatically mark it as depleted if it falls below or equals zero. These two steps must succeed or fail simultaneously.

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 to retrieve the current quantity (row lock)
    // TODO 2: Check if the quantity is greater than or equal to amount. If not, throw an error.
    // TODO 3: Update to decrease the quantity
    // TODO 4: If the quantity is 0, set is_opened to true (or a separate status column)
    
    await client.query("COMMIT");
    // Return the result
  } catch (e) {
    await client.query("ROLLBACK");
    res.status(400).json({ error: e.message });
  } finally {
    client.release();
  }
});

Hint: FOR UPDATE locks the row until the transaction ends, preventing other transactions from modifying the same row.

Blank 3: Search Optimization Index

ILIKE '%anything%' is not accelerated by a standard B-tree index. Enable a trigram index and measure the performance.

-- TODO 1: Enable the pg_trgm extension
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- TODO 2: Create a trigram index on the reagents name
CREATE INDEX idx_reagents_name_trgm ON reagents USING GIN (name gin_trgm_ops);

Verify performance:

-- Compare before and after applying the index
EXPLAIN ANALYZE
SELECT * FROM reagents WHERE name ILIKE '%p53%';

Goal: Response time of 10ms or less on a table with 10,000 or more rows.

Reflection: How does this LIMS differ from a production system?

Audit Trail: A production LIMS logs all data changes. It tracks who used it, when, and how much. This is essential for complying with GLP (Good Laboratory Practice)/GMP regulations. Methods: The pgaudit extension, or updated_by/updated_at columns and triggers.

Barcode/QR Scanning: In a production environment, each batch has a barcode, and scanners are used for immediate lookups. Adding an endpoint to your system that prints batch IDs as QR codes would greatly increase its practicality.

Access Control: A production system divides access permissions based on user roles. For example, students can only view data, postdocs can register and modify data, and the PI can approve orders. A standard approach: the authenticator role in the pg schema + JWT token validation.

Web UI: You have only created an API server. A production system provides a desktop-app-like UX using an SPA like React/Vue. Alternatively, you can quickly attach a simple UI using a Python framework like Streamlit/Dash.

Synchronization/Mobile: A production system may require offline editing and synchronization, as well as mobile app support. Stacks like Supabase or PouchDB are suitable for this.

Expansion Project

1. Slack Bot Integration: When /reagent p53 is entered in Slack, it calls your API and displays the results in the channel.

2. Ordering Workflow: Automatically generate an order request ticket when the minimum stock level is reached. Store vendor information and automatically generate an order PDF.

3. Usage Statistics: A dashboard displaying how much of each reagent was used each month. This can be used for budget planning.

4. Integration with Electronic Lab Notebook: When recording an experiment, the reagents used are automatically deducted from the inventory. Integration in the form of a Benchling API.

Component Guide for This Project

• [F] SQL Schema Design: Normalization, foreign keys, and representation of relationships. A 3-table inventory model.
• [F] DB Indexing: B-tree, partial indexes, and GIN trigram indexes. Performance verification using EXPLAIN ANALYZE.
• [W] Express Server: Routing, middleware, and JSON body parsing.
• [W] DB Connection Pool: Connection reuse using pg.Pool.

[F] = Components you implement yourself / [W] = Tool concepts provided with complete code.