The EDUCRM backend (the server behind FaceClass, an attendance and management system for coaching centres) runs on NestJS 11 with Fastify, and it has no ORM. Every query is parameterized SQL through the pg driver. This post explains that choice honestly: it wasn't a performance fix for a measured bottleneck. It was a design decision, and it came with trade-offs.
What the backend does
Twelve feature modules: students, batches, courses, enrollments, semesters, attendance, face images, fees, leave applications, tickets, dashboard and super-admin, plus auth and coaching-centre domains.
Why raw SQL
- I wanted to see every query. Attendance and fee reports join several tables. Writing the SQL myself meant no hidden N+1 loads and explicit column lists.
- The schema was changing fast. I wrote 15 hand-written SQL migrations (fees and instalments, semesters and marks, invoices, tickets and audit, alerts) instead of generated ones, so each change was reviewable as plain SQL.
- Repositories keep it contained. Queries live in
*.repository.tsfiles behind a single database service, so controllers and services never build SQL strings.
Multi-tenancy and roles
Each coaching centre is a tenant. Tenant-owned tables carry a coaching_id, and every repository query filters on it. Access is controlled with JWT authentication plus role guards for three roles: ADMIN, COACHING and STUDENT.
The weakness is that tenant filtering is enforced in application code. One forgotten WHERE coaching_id = $1 would leak data. If I rebuilt it I'd add Postgres row-level security as a second line of defence. I've since done exactly that in a separate project (Slotwise).
Where Redis fits
Only in one place: rate limiting phone OTP logins, per phone number and per IP, so nobody can spam SMS or brute-force codes. No caching layer, because nothing I measured needed one.
What I'd do differently
- Tests. The project shipped with only scaffold tests. Raw SQL needs integration tests against a real Postgres more than ORM code does, because the compiler checks nothing inside a query string.
- Measure before calling anything a "hot path". I never profiled these endpoints under load, so I can't claim a latency improvement, and this post doesn't.
- Row-level security for tenant isolation, as above.
Takeaway
Skipping the ORM bought me clarity and full control over the schema, at the cost of discipline I didn't fully apply (tests). It's a reasonable trade for a small team if you back it with integration tests and database-level tenant isolation.