Data Storage & Databases

From Flat Files to Full Databases

"The right question isn't 'which database is best?' It's 'what does my application actually need?'"

CSE 135 — Full Overview | Review Questions

← → sections • ↓ more detail

Section 1The Storage Landscape

Six storage options — parallel choices, not a ranking.

Storage Options at a Glance

Storage Type	Setup	Query Power	Concurrency	Best For
In-Memory	None	Code-level	Single process	Prototyping, caching
Flat Files	Minimal	Read/parse entire file	Poor — race conditions	Tutorials, config
SQLite	Low	Full SQL	Single writer	Mobile, embedded, dev
PostgreSQL/MySQL	Medium	Full SQL + extensions	Excellent — ACID	Production web apps
MongoDB	Medium	Document queries	Good — built for scale	Flexible schemas
Redis	Medium	Key-value	Excellent — atomic	Caching, sessions

NO SERVER REQUIRED REQUIRES A SERVER In-Memory Flat Files SQLite PostgreSQL MongoDB Redis

These aren't rungs on a ladder — they're parallel choices. Don't reach for PostgreSQL when a JSON file will do, or use a JSON file when you need transactions.

Read full section →

Section 2Flat-File Storage

Read file, parse JSON, modify array, write file back — the simplest persistence.

The Read-Modify-Write Cycle

Request comes in: 1. Read entire file from disk ┌──────────────┐ ─────────────────────────────▶ │ items.json │ │ [{"id":1, │ 2. Parse JSON into memory │ "name":"…"}│ items = JSON.parse(data) │ ... │ └──────────────┘ 3. Modify array in memory items.push(newItem) 4. Write entire file back ┌──────────────┐ ─────────────────────────────▶ │ items.json │ │ [updated │ │ contents] │ └──────────────┘ RACE CONDITION: Two requests read → modify → write simultaneously = second write overwrites first write's changes

Pros	Cons
Zero setup — no database server	No query language — load entire file
Human-readable — open and see data	Race conditions with concurrent writes
Easy to debug — just look at JSON	No indexing — slow with large data
Portable — copy one file	No relationships — no JOINs
Great for learning CRUD	Rewrites entire file on every change

Flat files are fine for prototyping and tutorials. The moment you need concurrent users or complex queries, it's time for a real database.

Read full section →

Section 3Relational Databases & SQL

Tables, rows, foreign keys, ACID transactions — the backbone of web applications.

Schema & CRUD-to-SQL-to-REST Mapping

The Stories Table

Column	Type	Constraints	Purpose
`id`	`SERIAL`	`PRIMARY KEY`	Auto-incrementing unique ID
`title`	`VARCHAR(200)`	`NOT NULL`	Required, max 200 chars
`description`	`TEXT`	(nullable)	Optional longer text
`priority`	`VARCHAR(10)`	`DEFAULT 'medium'`	low / medium / high
`status`	`VARCHAR(20)`	`DEFAULT 'todo'`	todo / in-progress / done
`created_at`	`TIMESTAMP`	`DEFAULT CURRENT_TIMESTAMP`	Auto-set on creation

Three-Layer Mapping

CRUD	SQL Statement	HTTP Method	REST Endpoint
Create	`INSERT INTO`	POST	`/api/stories`
Read (all)	`SELECT * FROM`	GET	`/api/stories`
Read (one)	`SELECT ... WHERE id=$1`	GET	`/api/stories/:id`
Update	`UPDATE ... SET ... WHERE`	PUT / PATCH	`/api/stories/:id`
Delete	`DELETE FROM ... WHERE`	DELETE	`/api/stories/:id`

Read full section →

SQL Examples & RETURNING

-- CREATE: Insert a new story INSERT INTO stories (title, description, priority) VALUES ('Build login page', 'Add auth', 'high') RETURNING *; -- READ: Get all stories SELECT * FROM stories ORDER BY created_at DESC; -- READ: Filter stories SELECT * FROM stories WHERE status = 'todo' AND priority = 'high'; -- UPDATE: Change status UPDATE stories SET status = 'in-progress' WHERE id = 1 RETURNING *; -- DELETE: Remove a story DELETE FROM stories WHERE id = 1 RETURNING *;

The RETURNING * clause is a PostgreSQL feature that returns affected rows after INSERT/UPDATE/DELETE — no second query needed. Extremely useful in REST APIs: return the created/updated resource in the response directly.

Read full section →

Section 4Connecting from Code

Node.js (pg) vs PHP (PDO) — same database, different client libraries.

Node.js vs PHP — Side by Side

Concept	Node.js (`pg`)	PHP (PDO)
Install	`npm install pg`	Built-in (`pdo_pgsql` extension)
Connection	`new Pool({host, database, ...})`	`new PDO($dsn, $user, $password)`
Simple query	`pool.query('SELECT ...')`	`$pdo->query('SELECT ...')`
Parameterized	`pool.query('...$1...', [val])`	`$stmt->execute(['id' => $val])`
Placeholders	Positional: `$1`, `$2`, `$3`	Named: `:id`, `:title` or `?`
Get rows	`result.rows`	`$stmt->fetchAll()`
Connection pooling	Built into `Pool`	Persistent connections or external pooler
Error handling	`try/catch` with async/await	`try/catch` with `PDO::ERRMODE_EXCEPTION`

Never hardcode database passwords in source code. Use environment variables (process.env.DB_PASSWORD / getenv('DB_PASSWORD')). Bots scan public repos for leaked credentials.

Read full section →

Section 5SQL Injection

The #1 web vulnerability — and the most preventable.

The Attack

Normal input: alice Query: SELECT * FROM users WHERE username = 'alice' ✓ OK Malicious input: ' OR '1'='1' -- Query: SELECT * FROM users WHERE username = '' OR '1'='1' --' What the database sees: username = '' → false OR '1'='1' → always true! -- → comment (ignores the rest) Result: Returns ALL users. Attacker is "logged in."

Even worse input: '; DROP TABLE stories; -- Query: SELECT * FROM users WHERE username = ''; DROP TABLE stories; --' Database executes: 1. SELECT * FROM users WHERE username = '' (returns nothing) 2. DROP TABLE stories (deletes your table!) 3. --' (rest is a comment)

SQL injection is the #1 web vulnerability (OWASP Top 10). It can expose data, bypass auth, delete tables, and in some cases execute OS commands on the database server.

Read full section →

The Fix: Parameterized Queries

String Concatenation (VULNERABLE): Your code: "SELECT * FROM users WHERE name = '" + input + "'" Database sees: One big string to parse as SQL Problem: Input becomes part of the SQL structure Parameterized Query (SAFE): Step 1 - PREPARE: Database parses "SELECT * FROM users WHERE name = $1" SQL structure is locked in — one condition, one value Step 2 - EXECUTE: Database receives $1 = "'; DROP TABLE users; --" Treated as a data value, period. Cannot change the query structure.

WRONG — Concatenation	RIGHT — Parameterized
pool.query(`...WHERE id = ${id}`) Attacker sends: `1; DROP TABLE stories` Query executes the DROP!	`pool.query('...WHERE id = $1', [id])` Attacker sends: `1; DROP TABLE stories` DB sees id = '1; DROP TABLE stories' (string value, no damage)
`$pdo->query("...WHERE id = " . $_GET['id'])` Same vulnerability in PHP	`$stmt = $pdo->prepare('...WHERE id = :id')` `$stmt->execute(['id' => $_GET['id']])` Safe in PHP

If you only remember one thing: never put user input directly into a SQL string. Always use parameterized queries ($1, $2 in pg) or prepared statements (:name in PDO).

Read full section →

Section 6NoSQL / Document Databases

Flexible JSON-like documents instead of rigid table rows.

SQL vs NoSQL

Terminology Mapping

SQL Concept	MongoDB Equivalent
Database	Database
Table	Collection
Row	Document
Column	Field
`SELECT`	`find()`
`INSERT`	`insertOne()`
`UPDATE`	`updateOne()`
`DELETE`	`deleteOne()`
`JOIN`	Embedded doc or `$lookup`

When to Choose

Choose SQL (PostgreSQL) When	Choose NoSQL (MongoDB) When
Data has consistent, well-defined structure	Data structure varies between records
You need JOINs across related data	Related data is naturally nested
ACID transactions are critical	Eventual consistency is acceptable
Complex queries with aggregation	Simple lookups by key or basic filters
Data integrity constraints matter	Schema flexibility is more important

Don't choose based on hype. Many teams used MongoDB for everything circa 2012–2015 — including clearly relational data. Choose based on your data's natural shape, not the latest trend.

Read full section →

Section 7ORMs & Query Builders

Mapping tables to classes — convenience vs understanding.

Raw SQL vs ORM

Operation	Raw SQL	ORM (Sequelize / Eloquent)
Get all	`SELECT * FROM stories`	`Story.findAll()` / `Story::all()`
Get by ID	`SELECT ... WHERE id = $1`	`Story.findByPk(id)` / `Story::find($id)`
Create	`INSERT INTO stories ...`	`Story.create({title})` / `Story::create([...])`
Update	`UPDATE ... SET ... WHERE`	`story.update({status})` / `$story->update([...])`
Delete	`DELETE FROM ... WHERE`	`story.destroy()` / `$story->delete()`

Active Record vs Data Mapper

Pattern	How It Works	Examples	Trade-off
Active Record	Object saves itself: `story.save()`	Sequelize, Eloquent	Simple; harder to test
Data Mapper	Separate mapper handles persistence	Prisma, Doctrine	More code; cleaner separation

Popular ORMs

Language	Tool	Type
Node.js	Sequelize	ORM (Active Record)
Node.js	Prisma	ORM (Data Mapper-ish)
Node.js	Knex	Query Builder
PHP	Eloquent	ORM (Active Record)
PHP	Doctrine	ORM (Data Mapper)

ORMs are convenient but hide what's happening. Learn raw SQL first, then use an ORM when you understand what it generates. The "N+1 query problem" is a common trap for ORM-only developers.

Read full section →

Section 8Choosing Your Storage

Match the tool to the problem — not the hype.

Decision Framework

Use Case	Recommended	Why
Learning CRUD / tutorials	Flat files or in-memory	Zero setup; focus on HTTP and REST
Personal project / prototype	SQLite	Real SQL, no server, one file
Production web app	PostgreSQL	ACID, battle-tested, rich ecosystem
CMS / content management	PostgreSQL or MongoDB	Depends on content structure
Sessions / caching	Redis	Blazing fast, built-in TTL
Analytics / event logging	Log files → PostgreSQL	Append-only capture; SQL analysis
Mobile with offline	SQLite + PostgreSQL	SQLite offline, sync to server
Microservices	Mix per service	"Polyglot persistence"

In MVC, the Model layer is where all database logic lives. The model handles validation, storage, retrieval, and business rules — the controller just coordinates. See MVC Overview.

Read full section →

SummaryKey Takeaways

9 concepts of data storage in one table.

Data Storage at a Glance

Concept	Key Takeaway
Storage Landscape	Six options (in-memory, flat files, SQLite, PostgreSQL, MongoDB, Redis) — parallel choices, not a ranking
Flat Files	Read/write entire JSON file; zero setup; no concurrency; fine for tutorials, bad for production
Relational Databases	Tables, rows, columns; SQL for CRUD; foreign keys; ACID transactions for integrity
SQL ↔ CRUD ↔ REST	INSERT=POST, SELECT=GET, UPDATE=PUT/PATCH, DELETE=DELETE — same pattern at every layer
Connecting from Code	Node.js `pg` with Pool + `$1`; PHP PDO with `:name` placeholders
SQL Injection	#1 web vulnerability; string concatenation is the cause; parameterized queries are the fix
NoSQL / MongoDB	Flexible documents; good for varied schemas; use SQL when you need JOINs and transactions
ORMs	Map tables to objects (Sequelize, Prisma, Eloquent, Doctrine); learn raw SQL first
Choosing Storage	Match tool to problem; PostgreSQL is the safe default; don't over-engineer for tutorials

Read the full Database overview → | Review Questions →