Databases and CRUD
Making data outlive the browser - tables, rows and the four verbs of every app.
Before this: APIs and JSON
Objective
Understand persistent storage, direct an agent to build a Create-Read-Update-Delete application, and verify data truly survives restarts.
Vocabulary
The words to use when directing your agent.
- database
- Software that stores data durably and retrieves it efficiently.
- table
- A collection of same-shaped records - like a spreadsheet with enforced columns.
- row / record
- One entry in a table - one expense, one user.
- CRUD
- Create, Read, Update, Delete - the four operations nearly every app performs on data.
- schema
- The declared shape of your data: which fields exist and their types.
- query
- A request for data meeting certain conditions.
Understand it
Schema first, always
Decide the shape before the screens: an expense has amount (number), category (text), date (date), note (optional text). Four fields - that decision drives storage, forms, display and validation. Schema-first keeps the agent honest and your reviews fast.
Where the database lives at Level 1
Local options (SQLite file, browser storage backed by a tiny library) are fine now. The multi-user questions - who can touch whose rows - arrive with Level 2's servers. Do not solve sharing problems before having the sharing problem.
Core concepts
Persistence changes everything
Until now, closing the tab erased your app's memory. A database writes data to disk: restart, reboot, revisit tomorrow - it is still there. Persistence is the line between toys and tools.
CRUD is the grammar of apps
Notes apps: create notes, read them, edit, delete. Shops: products and orders. Feeds: posts. Once you see CRUD, you see that most apps differ in domain, not mechanics - which is why agents build them so well WHEN you specify the schema.
Examples
The restart test
Create an expense, close everything, reopen. Still there? Persistence verified. This thirty-second ritual is your database's most important test.
Update versus create confusion
Editing an existing expense must NOT insert a duplicate row. If your app grows a second identical entry after an edit, the update path is really creating - a classic agent bug worth hunting.
Common mistakes
Common mistake
Skipping the schema conversation and letting the agent improvise fields.
Fix
Declare the schema explicitly in your brief; make the agent echo it back before coding.
Common mistake
Trusting the UI as proof of saving.
Fix
Verify persistence by restarting, not by watching the list update on screen.
Working with your AI agent
Standing rule for CRUD projects: 'Echo the schema before implementing. After implementing, demonstrate each CRUD operation against the actual store and show me the evidence.' Screenshots of UI prove nothing; queries against data prove everything.
Build it
The module’s real project. Your agent builds; you direct and inspect.
Project: Expense Tracker v1
The flagship Level 1 build: a single-user expense tracker with full CRUD on a real persistent store, schema declared by you, verified by restarts - not screenshots.
Requirements
- Declared schema: amount (number), category (text), date (date), note (optional text)
- Full CRUD: add, view list sorted newest first, edit existing, delete with confirmation
- Data persists across full app restarts
- Total of all expenses visible and correct
- Validation from previous module applied: amount must be positive number, category required
Context for your agent
Build a single-user expense tracker with a persistent local store (SQLite file or equivalent durable option - propose options with trade-offs first). Schema fixed: amount positive number, category required text, date, optional note. Features: add, list newest-first, edit, delete-with-confirm, and a correct running total. Echo the schema and operation list before coding; after coding, demonstrate create/read/update/delete against the store itself, not the UI.
Verify it - twice
Check every item yourself first. Then hand your agent the prompt and compare verdicts.
Verification prompt - paste into your AI agent
Audit my Expense Tracker v1 as a skeptical senior engineer. Do not trust the UI - interrogate the store. Verify PASS or FAIL with evidence: (1) schema matches amount/category/date/note exactly, (2) all CRUD operations demonstrably work against persistence (perform them yourself if you can), (3) editing never duplicates rows, (4) totals are computed from stored data, not tracked separately, (5) validation rejects negative amounts and empty categories, (6) store access is parameterized, not string-concatenated. Then perform the restart test yourself and report the result honestly.
Run this against “Databases and CRUD” after your own checklist pass. Where the agent and you disagree, investigate before moving on.
Review questions
Answer out loud. If you cannot, revisit before moving on.
State your schema from memory and justify each field's type.
Why is the restart test stronger than checking the on-screen list?
What went wrong if editing creates a second identical row?
You can now
- Declare schemas and hold agents to them
- Build and verify full CRUD on persistent storage
- Prove persistence with restarts and store-level checks
Up next
Shipping Your First App