Ship an API with auth and a real database

The brief

Build and deploy a small REST API with real authentication and a real database. Not a tutorial to-do app — something with at least two related entities, ownership rules, and one operation that is not plain CRUD.

Good candidates: a bookmark manager with tags and full-text search; an expense splitter with groups and balances; a habit tracker with streak calculation. Two entities, one relationship, one piece of genuine logic.

Small scope is the point. The learning is in the surrounding 80%, not the features.

The checklist

Data layer

• Postgres (or MySQL) — not SQLite, because you want to deal with connections and pooling.
• Migrations in version control, applied by a command, never by hand.
• Foreign keys with real constraints. NOT NULL where it should be.
• Indexes on foreign keys and on anything you filter by. Confirm with EXPLAIN.
• A connection pool, sized deliberately. See networking.

Auth

• Registration and login; passwords hashed with argon2/bcrypt.
• Sessions or short-lived JWTs plus a revocable refresh token — and be able to explain your choice. See OAuth, JWT and PKCE.
• Tokens in httpOnly, Secure, SameSite cookies if a browser is involved.
• Authorisation on every endpoint, scoped to the resource. WHERE id = ? AND user_id = ?. Test it by trying to read another user's row.
• Rate limiting on login and registration. See rate limiting.
• Logout that actually invalidates.

API design

• Correct status codes: 200, 201, 400, 401, 403, 404, 409, 422, 429, 5xx.
• Consistent error body with a stable machine-readable code and a request_id.
• Schema validation on every input.
• Pagination with a maximum page size on every list.
• Idempotency key support on at least one write endpoint.
• /health that checks the database, not just that the process is alive.

Operations

• Dockerfile, multi-stage, non-root user. See containers.
• docker compose up gives a working local stack including the database.
• CI running tests on every pull request.
• Deployed with HTTPS on a real domain.
• Structured JSON logs with a request id on every line. See observability.
• Config from environment variables; no secrets in git.
• Timeouts on every outbound call. See failure handling.

Tests

• Integration tests hitting real endpoints against a test database.
• One test per auth rule — specifically, that user A cannot touch user B's data.
• The full suite runs in CI in under five minutes.

The three exercises that teach the most

Once it works, break it deliberately:

1. Try to break your own authorisation. Log in as user A, get a token, and call every endpoint with user B's resource ids. Every one must return 403 or 404. Most people find at least one hole here, in their own code, which is the lesson.
2. Kill the database while the API is running. What does the client see? Is it a clear 503, or a 30-second hang and a stack trace? Fix it.
3. Fire 200 concurrent requests at a write endpoint. Watch the connection pool. Find the point where it queues.

Prove you know it

Send someone the URL and let them create an account. If it works from a machine that is not yours, on a network that is not yours, you have shipped an API — which is a materially different claim from having written one.