CRUD from Button to Row
Almost every screen in Sprout does one of four things to a plant: add it, show it, change it or remove it. Those four are known as CRUD: create, read, update, delete. Each one has a matching HTTP method, route and SQL statement, and once you see the pattern, most of an app's plumbing becomes predictable.
The rule
One user action: one method, one route, one SQL statement.
| User action | Method and route | SQL | Success |
|---|---|---|---|
| Add a plant | POST /api/plants |
INSERT ... RETURNING * |
201 and the new plant |
| See my plants | GET /api/plants |
SELECT ... WHERE owner_id = $1 |
200 and an array |
| See one plant | GET /api/plants/:id |
SELECT ... WHERE id = $1 AND owner_id = $2 |
200, or 404 |
| Change a plant | PATCH /api/plants/:id |
UPDATE ... WHERE id = $1 AND owner_id = $2 RETURNING * |
200 and the plant, or 404 |
| Remove a plant | DELETE /api/plants/:id |
DELETE ... WHERE id = $1 AND owner_id = $2 |
204 and no body, or 404 |
Every statement that touches one plant also matches the owner. That single AND owner_id = ... is what stops one user from reading or changing another's plants; Section 4 looks at it closely.
Here is the update route in full:
app.patch('/api/plants/:id', async (req, res) => {
const { rows } = await pool.query(
'UPDATE plants SET name = $1 WHERE id = $2 AND owner_id = $3 RETURNING *',
[req.body.name, req.params.id, req.user.id]
);
if (rows.length === 0) return res.status(404).json({ error: 'Plant not found' });
res.json(rows[0]);
});If no row matched, the plant either does not exist or is not this user's, and either way the answer is 404. Otherwise the updated row goes back to the client, which swaps it into its list.
PATCH changes some fields and leaves the rest alone. Its cousin PUT replaces the whole plant, so a PUT without water_every_days would be asking to erase that field. For editing a form of a few fields, PATCH is usually what you mean.
On the client, the same row of the table drives the code: after a successful DELETE /api/plants/7, remove plant 7 from state; after a successful PATCH, replace plant 7 with the server's version.
The shortcut
Say the action as a verb on a noun, then fill in the table row. "Remove plant 7": the verb is DELETE, the noun is /api/plants/7, the SQL is a DELETE matching id 7 and the owner, and success is 204. Write the row first; the code almost writes itself.
The button
Asha changes Fern from every 3 days to every 4. The client sends
PATCH /api/plants/7with{"water_every_days": 4}.
Drills
Answer fast — trust the rule.
Which method matches "remove plant 7"?
An UPDATE ... WHERE id = $2 AND owner_id = $3 RETURNING * returns zero rows. What should the API answer?
What is the usual success response to a DELETE?
Which request matches "show me all my plants"?
Remember
- One user action: one method, one route, one SQL statement.
- Shortcut: say it as a verb on a noun and fill in the table row before writing code.
- Trap: forgetting the owner in
WHERE. Without it, any user can reach any plant by guessing an id.