Express + MySQL + Prisma API Template — Beginner Guide ( From actual project )

This template shows a clean, modular way to build a production-ready REST API using:

• Express (HTTP server and routing)

• Prisma (type-safe ORM for MySQL)

• MySQL (database)

Only the API parts are covered below. Docker and other ops topics are intentionally ignored.

1. Big Picture

• Request flow: Client -> Express route -> Middleware (auth/validation) -> Controller -> Model (Prisma) -> MySQL -> JSON response

• Separation of concerns:

  • routes: URL paths and HTTP methods only

  • controllers: business logic and orchestration

  • models: database access using Prisma

  • middlewares: cross-cutting concerns (auth, uploads, response helpers)

  • prisma: database schema and migrations

• API versioning and domains:

  • Versioned under /api/v1

  • Split into admin and user modules

2. Folder Structure (API-focused)

• routes/

  • api/v1/admin: admin-facing endpoints grouped by feature (surveys, settings, charts, etc.)

  • api/v1/user: end-user endpoints grouped by feature (surveys, profile, feed, polls, quests, etc.)

• controllers/

  • api/v1/admin: controller functions for admin routes

  • api/v1/user: controller functions for user routes

• models/

  • api/v1/admin: Prisma data-access for admin features

  • api/v1/user: Prisma data-access for user features

• middlewares/

  • authentication/authorization, prisma client instance, upload handling, and JSON response helpers

• prisma/

  • schema.prisma: the full data model for MySQL

  • migrations/: auto-generated SQL migrations

• app.js

  • Express setup: parsers, static files, and routers mounted under /api/v1/admin and /api/v1/user

This layout enforces “routes -> controllers -> models” with middlewares beside them to keep code small and focused.

3. Request Lifecycle (per request)

• A request hits a route (e.g., /api/v1/user/main/survey/survey-list/:page)

• If protected, an auth middleware checks Authorization: Bearer

  • On success, it attaches identity and context (e.g., user_id) to res.locals

• The controller reads params/body, applies business rules, and calls a model function

• The model uses Prisma to query/update MySQL

• The controller returns a JSON object using a centralized response helper

4. Routing Strategy

• Prefixes:

  • /api/v1/admin/auth and /api/v1/admin/main for admin

  • /api/v1/user/auth and /api/v1/user/main for end users

• Feature grouping keeps endpoints discoverable (surveys, polls, quests, settings, etc.)

• Versioning (v1) allows future breaking changes without disrupting old clients

5. Authentication & Authorization

• Stateless session tokens:

  • On login, a random session_token is persisted on the user/admin record

  • Clients send token via Authorization: Bearer

• Middlewares validate tokens and populate res.locals (e.g., user_id, points, tickets)

• Controllers rely on res.locals for identity and authorization checks

6. Database Layer with Prisma

• A single Prisma client instance is shared across the app via middleware

• Models call prisma.. (findMany, findUnique, create, update, etc.)

• Queries often use:

  • filtering (flag/status fields used as soft-state gates)

  • includes/relations (e.g., Survey -> Questions -> Answers)

  • pagination via skip/take

• The schema in prisma/schema.prisma defines all tables and relations and is the source of truth

• Migrations in prisma/migrations are created and applied with the Prisma CLI

Typical workflow with Prisma:

• Edit prisma/schema.prisma to add/change models/fields

• Run migrations, then call the Prisma client in model functions

7. Response Conventions

• JSON responses follow a simple shape:

  • result: 1 (success) or 0 (failure)

  • message: human-readable status

  • data fields: context-specific (e.g., survey_list, page, user, etc.)

• A shared response helper ensures consistent headers and JSON bodies

• Global error handling includes:

  • 404 forwarding

  • File upload errors mapped to clear JSON messages

8. File Uploads (optional)

• A reusable upload middleware integrates Multer (temp storage) and S3

• Typical usage in a route: add the upload middleware, then process the uploaded file in the controller

• The helper also includes delete and content-type helpers

• Errors are translated into JSON so clients can react properly

9. Adding a New API Feature (step-by-step)

• Plan the endpoint: path, method, request body, response shape, auth rules

• Model: implement required Prisma queries/mutations under models/api/v1/(admin|user)

• Controller: validate inputs, call the model, build the JSON response; use res.locals for identity

• Route: mount under the correct router (admin/user) and attach the proper auth middleware

• Test: use curl/Postman; include Authorization headers when required

Example flow:

• routes -> controllers -> models -> prisma -> MySQL -> JSON

• Protect sensitive routes; rely on res.locals.user_id in writes/audits

10. Practical Tips for Beginners

• Keep controllers thin: push DB logic to models

• Parse integers from req.params/req.body before saving numeric fields

• Use skip/take for pagination; return the current page

• Prefer soft deletes/state via flag/status fields

• Store timestamps consistently (this template uses string Unix timestamps)

• Keep responses consistent (result/message) for easy client integration

• Put cross-cutting concerns in middlewares (auth, uploads, rate-limiting if added later)

11. Environment Setup (minimal)

• DATABASE_URL (MySQL connection string) for Prisma

• APP_PORT and APP_NAME for Express app

• For uploads: AWS_REGION, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY (if using S3)

12. How to Read the Codebase Efficiently

• Start in app.js to see where routers mount

• Explore routes/api/v1/(admin|user) to find endpoint paths

• Open controllers for business logic

• Open models for Prisma queries

• Refer to prisma/schema.prisma to understand data structures/relations

Following this structure, beginners can add endpoints quickly, keep logic organized, and evolve the database safely using Prisma migrations—while keeping the API clean, versioned, and secure.