CRUD stands for Create, Read, Update, Delete. Most backend features are variations: create a user, read a list of products, update an order, delete a draft. Mapping these to HTTP methods and SQL gives you a consistent mental model.

Create → POST (HTTP) and INSERT (SQL). Read → GET and SELECT. Update → PUT or PATCH and UPDATE. Delete → DELETE and DELETE. Your API layer receives the request, validates input, then runs the right SQL (or calls an ORM method that does).

Client sends POST /users with a body (e.g. { "email": "a@b.com", "name": "Alice" }). Server validates (required fields, format), then INSERT INTO users (email, name) VALUES ($1, $2) RETURNING id, email, name, created_at and returns 201 Created with the new resource. Return the created resource (or at least id) so the client can use it.

Handle duplicates (e.g. unique email): return 409 Conflict or 422 Unprocessable Entity with a clear error message. Never expose raw DB errors to the client.

GET /users/123 loads one record: **SELECT * FROM users WHERE id = $1. If not found, 404 Not Found. GET /users returns a list: SELECT * FROM users ORDER BY created_at DESC LIMIT 20 OFFSET 0. Use LIMIT and OFFSET (or cursor) for pagination** so you do not return millions of rows.

Filtering: GET /users?status=active maps to WHERE status = $1. Sorting: ?sort=created_at&order=desc. Validate and sanitize query params to avoid injection and abuse.

PATCH /users/123 (partial update): client sends only changed fields. Build UPDATE users SET name = $1, updated_at = NOW() WHERE id = $2 from the payload. PUT often means "replace entire resource"; use when the client sends the full representation. Return 200 OK with the updated resource or 204 No Content.

DELETE /users/123 runs DELETE FROM users WHERE id = $1. Return 204 No Content on success or 404 if the resource did not exist. For soft delete, use UPDATE to set deleted_at and filter it out on read.

Validate every input: type, length, format (e.g. email), business rules (e.g. end date after start date). Return 400 Bad Request with a structured error body (e.g. { "errors": [{ "field": "email", "message": "Invalid format" }] }).

Use an ORM or query builder (e.g. Prisma, SQLAlchemy, GORM) to avoid writing raw SQL for simple CRUD and to get parameterization for free. For complex queries, raw SQL is fine; keep it in one place and test it.