Permanently Storing Experiment Data with Databases

So far you've managed sample data in arrays within an Express server. The problem — when you restart the server, all data disappears. Arrays only exist in memory.

In the lab, if you jot results on sticky notes they'll blow away, but if you write them in a lab notebook they're permanent. A database is the lab notebook of web development — even if the server shuts down or the computer restarts, data remains safe.

Files vs Databases

Earlier you learned to read and write CSV files with Node.js. Files can store data too, so why do we need databases?

If you have 10 experiment samples, Excel is enough. But when you need to find "blood samples registered in March 2024 with OD above 1.0" from 100,000 sample records — files are painful, while databases do it in a single line.

SQL: The Language for Talking to Databases

SQL (Structured Query Language) is the language for telling a database "give me this data," "save this," or "delete this."

SQL is among the easiest programming languages. It reads like English:

SELECT name, od FROM samples WHERE status = 'fail';

This single line means "from the samples table, get the name and od of rows where status is fail." Just as you search papers on PubMed by combining keywords, SQL searches data by combining conditions.

Tables: The Structure of Data

In a database, data is stored inside tables. The structure is nearly identical to an Excel spreadsheet:

samples table
┌────┬──────────────┬──────┬────────┬────────────┐
│ id │ name         │ od   │ status │ created_at │
├────┼──────────────┼──────┼────────┼────────────┤
│  1 │ Blood-A      │ 1.85 │ pass   │ 2026-03-01 │
│  2 │ Tissue-B     │ 0.42 │ fail   │ 2026-03-02 │
│  3 │ Serum-C      │ 2.10 │ pass   │ 2026-03-03 │
│  4 │ Plasma-D     │ 0.15 │ fail   │ 2026-03-03 │
└────┴──────────────┴──────┴────────┴────────────┘

Terminology:

• Row = one record. All information about a single sample
• Column = one field. A data category like name, OD value, or status
• Schema = the blueprint of a table. Which columns exist and what data type each one holds

CRUD: The 4 Basic Data Operations

The core of every information system is CRUD — Create, Read, Update, Delete. A lab LIMS is ultimately a combination of these four operations.

CREATE — Making a Table

CREATE TABLE samples (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  od DECIMAL(5, 2),
  status VARCHAR(10) DEFAULT 'pending',
  created_at DATE
);

• INT — integer (id, sample number)
• VARCHAR(100) — string up to 100 characters (sample name)
• DECIMAL(5, 2) — number with decimals (OD value: 2 decimal places)
• AUTO_INCREMENT — number increases automatically when new rows are added
• PRIMARY KEY — this column uniquely identifies each row
• NOT NULL — empty values are not allowed

It's like creating blank fields in a protocol: "Reaction temperature: ___°C, Time: ___min." Defining structure first prevents wrong data from entering later.

INSERT — Adding Data

INSERT INTO samples (name, od, status, created_at) VALUES
  ('Blood-A', 1.85, 'pass', '2026-03-01'),
  ('Tissue-B', 0.42, 'fail', '2026-03-02'),
  ('Serum-C', 2.10, 'pass', '2026-03-03');

id is AUTO_INCREMENT, so it's automatically assigned as 1, 2, 3 without manual input.

SELECT — Retrieving Data (The Most Used Command)

-- Get everything
SELECT * FROM samples;

-- Specific columns only
SELECT name, od FROM samples;

-- Conditional search
SELECT * FROM samples WHERE status = 'fail';

-- OD 1.0 or above, sorted by OD descending
SELECT name, od FROM samples WHERE od >= 1.0 ORDER BY od DESC;

-- Count records
SELECT COUNT(*) FROM samples WHERE status = 'pass';

SQL's power lies in combining conditions. Instead of opening a file and comparing records one by one in a for loop, you just state the conditions and the database finds them optimally.

UPDATE — Modifying Data

-- Update sample id 2 with re-measured OD value
UPDATE samples SET od = 0.98, status = 'pass' WHERE id = 2;

If you forget the WHERE condition, all rows get modified. It's like trying to correct one sample's result in a lab notebook and accidentally overwriting everything. Always double-check the WHERE clause with UPDATE and DELETE.

DELETE — Removing Data

-- Delete a specific sample
DELETE FROM samples WHERE id = 4;

-- Delete all failed samples (caution!)
DELETE FROM samples WHERE status = 'fail';

Relational Databases: Connecting Tables

"Relational" means tables can be related to each other. Linking a samples table with a researchers table:

researchers table             samples table
┌────┬──────────┐          ┌────┬──────────┬───────────────┐
│ id │ name     │          │ id │ name     │ researcher_id │
├────┼──────────┤          ├────┼──────────┼───────────────┤
│  1 │ Dr. Kim  │◄─────────│  1 │ Blood-A  │             1 │
│  2 │ Dr. Park │◄─────────│  2 │ Tissue-B │             2 │
└────┴──────────┘          │  3 │ Serum-C  │             1 │
                           └────┴──────────┴───────────────┘

researcher_id is the key connecting the two tables. With this structure:

SELECT samples.name, researchers.name
FROM samples
JOIN researchers ON samples.researcher_id = researchers.id
WHERE researchers.name = 'Dr. Kim';

"Show me only the samples registered by Dr. Kim" — similar to using VLOOKUP in Excel, but works instantly even with millions of records.

Which Database to Use

Projects like BioPlayground use Supabase (PostgreSQL-based). SQL syntax is over 90% identical across MySQL and PostgreSQL, so learning one makes the others easy to pick up.

Try It Yourself (Faded Example)

Fill in the blanks to complete a SQL query that searches for QC-failed samples.

 name, od
FROM 
 status = 'fail'
ORDER BY od ;

Common Errors & Solutions

Q: Table 'database.samples' doesn't exist error

You haven't run CREATE TABLE yet, or you're connected to a different database. Run SHOW TABLES; to see the table list in the current database.

Q: I tried to UPDATE one row but everything changed

You forgot the WHERE condition. UPDATE samples SET status = 'pass' changes every row's status to pass. Always add a condition like WHERE id = 2. Build the habit of running a SELECT with the same WHERE condition first to verify which rows will be affected before making important changes.

Q: What's the difference between VARCHAR and TEXT?

VARCHAR(100) stores up to 100 characters with a specified length. TEXT has virtually no length limit. Short data like sample names or statuses suit VARCHAR; experiment notes or long descriptions suit TEXT.

Q: Do SQL commands have to be uppercase?

No. SELECT and select are the same. Uppercase is just convention. Writing SQL keywords in uppercase and table/column names in lowercase makes code easier to read.

Q: Is there a GUI tool to view MySQL instead of the terminal?

Connecting via mysql -u root -p in the terminal is the default, but GUI clients let you visually inspect table structures and data.

For beginners, start with Sequel Ace (Mac) or MySQL Workbench (all platforms). Once you're comfortable typing SQL commands, use a GUI as a supplement to see data at a glance while working.