backend · starter · 4d
Todo CRUD with SQLite
Your second backend: a todos API where the todos live in SQLite, not memory — so they survive a restart and you learn what persistence actually costs.
Deliverable
A Node + SQLite todos API (GET/POST/PATCH/DELETE) with file-backed persistence, input validation, and correct status codes — the smallest honest backend you can deploy.
Milestones
0/5 · 0%- 01Schema and migration
Create the SQLite file and the todos table with a migration, not a manual sqlite3 shell command you forget to re-run. The table has id (INTEGER PRIMARY KEY AUTOINCREMENT), title (TEXT NOT NULL), done (INTEGER 0/1), and created_at (TEXT). The migration is a .sql file applied on startup if not yet applied — so a fresh clone or a restart always yields the same schema. Use a migrations table or a version check so the same migration never runs twice. Prove it: delete the .db file, restart, and GET /todos returns an empty array — the migration re-created the table automatically.
Definition of done- A .sql migration creates the todos table on first start; restarting with an existing .db does not re-run it or duplicate rows.
- Deleting the .db and restarting yields 200 with an empty todos list — schema is recreated automatically.
Self-review
Show the migration file and delete-then-restart test. A reviewer checks the migration is idempotent and the .db is a file, not :memory:.
- 02Create and list todos
Wire POST /todos and GET /todos against the real database with parameterized queries — never string-interpolate user input into SQL. POST validates title (non-empty string, max 200 chars) with zod, inserts with a parameterized INSERT, and returns 201 with the created row including its DB-generated id. GET lists all todos ordered by created_at descending. Use 400 for a missing or empty title, not 500 — and the 400 body names the failing field. Prove SQL injection is closed: POST a title containing `' OR 1=1 --` and show it is stored as a literal string, not executed.
Definition of done- POST /todos with a valid title → 201 with the row and DB id; missing/empty title → 400 with field error; title with SQL payload is stored literally.
- GET /todos returns all rows from SQLite ordered by created_at, not from an in-memory array.
Self-review
Show the SQL-injection test (literal storage) and 400 vs 201. A reviewer checks queries are parameterized (no template strings with user input).
- 03Read, update, delete one todo
Close the CRUD loop with single-resource routes: GET /todos/:id returns one todo or 404, PATCH /todos/:id updates title/done (validating the body the same way as POST), and DELETE /todos/:id removes it. 404 means the id does not exist — not 400, because the request was well-formed but the address was wrong. PATCH is partial (either field may be present), but if title is present it must still be non-empty. After DELETE, a GET on the same id is 404. All handlers are async and use parameterized queries; after each operation, the change is visible via GET /todos.
Definition of done- GET /todos/:id → 200 or 404, PATCH validates and returns 200, DELETE → 204/200 and subsequent GET is 404.
- PATCH with empty title → 400, not 200; operating on a missing id → 404, not 500.
Self-review
Show PATCH empty-title → 400 and missing-id → 404. A reviewer checks PATCH is partial and handlers use parameterized queries, not string concat.
- 04Prove persistence
Prove the todos survive a restart — the whole point of SQLite. Stop the server, start it again, and GET /todos returns the same rows with the same ids. Then add one more todo and show its id is the next auto-increment, not a reset counter. Explain why an in-memory array would have lost everything: the process's heap is gone, only the file remains. As a stretch, show what happens if the .db file is on a read-only filesystem — the server should fail to start with a clear error, not serve empty lists silently.
Definition of done- Stopping and restarting the server preserves all todos; a new todo gets the next auto-increment id, not 1 again.
- You can explain why in-memory would be lost and what the file guarantees.
Self-review
Show restart preserves rows and next id increments. A reviewer checks the .db is file-backed and the explanation distinguishes heap vs file.
- 05Validation budget and errors
Make error handling intentional, not accidental. Every 400 names the failing field and constraint ('title: must be non-empty string ≤200'), not a generic 'bad request'. Every 404 body says which id was not found. No handler ever throws an unhandled exception that becomes a 500 — wrap DB calls and return a proper error. Add a tiny request log (method, path, status, duration) so you can see which errors are validation (400) vs missing (404) vs server (500). Measure: fire 10 sequential POSTs with alternating valid/invalid titles and assert the status sequence is 201,400,201,400… with correct bodies.
Definition of done- All 400 bodies name the field and constraint; all 404 bodies name the missing id; no valid request ever returns 500.
- A 10-request valid/invalid sequence yields the expected 201/400 alternation with correct bodies and a request log distinguishes 400 vs 404.
Self-review
Show 400 body with field message and 404 with id, plus the 10-request sequence. A reviewer checks no handler leaks an unhandled exception as 500.
Starter
fallowlone/skein-projects
projects/todo-crud-sqlite
- README.md
- src/todos.ts
- test/todos.test.ts
npx degit fallowlone/skein-projects/projects/todo-crud-sqlite todo-crud-sqlite Implement the stubs, then run the tests until they pass: bun test
Fork the repo and push your work — the grade workflow runs the suite plus static checks on your own runners.
Rubric
| Junior | Mid | Senior | |
|---|---|---|---|
| SQL correctness & injection safety | Uses string concatenation for SQL with user input; a title containing a quote breaks the query or injects. | All queries are parameterized (? placeholders); a title with SQL payload is stored literally, not executed. | Can explain why parameterized queries are not just string escaping and where even they wouldn't save you (e.g. dynamic ORDER BY column name needs an allowlist). |
| Validation & status codes | Missing title returns 500 or a generic 400; 404 vs 400 are confused. | 400 names the field and constraint, 404 names the missing id, and the two are never confused. | The 400 body is machine-readable (field + constraint) so a client can surface a form error without parsing; PUT-equivalent PATCH respects idempotency semantics. |
| Persistence & migrations | Todos live in memory; restart loses them. No migration — schema is created by hand. | SQLite file persists todos across restarts; a migration file creates the schema idempotently on first start. | Can reason about what breaks when the file is on a read-only FS or when two processes open the same file (SQLite locking), and why a migrations table is needed beyond IF NOT EXISTS. |
Reference walkthrough (spoiler)
Why SQLite for a starter: it's a single file, no server, no network, no credentials. The durability is just 'bytes on disk' — the simplest persistence you can reason about before Postgres's WAL, replication, or connection pools.
Parameterized queries vs string interpolation: `db.prepare('INSERT INTO todos (title) VALUES (?)').run(title)` sends the SQL and the value on separate channels; the value is never parsed as SQL. Concatenation `... VALUES ('${title}')` parses the title as SQL and breaks on quotes or injection.
Make it senior
- Add a database-level UNIQUE constraint on title and return 409 Conflict on duplicate, with a clear field error.
- Add a search query ?q= that filters todos by title substring using a parameterized LIKE, with no injection.