RDBMS and Web API Gotchas for Frontend-Focused Full-Stack Developers

AI-GENERATED published: October 30, 2025 estimate: 3 min read view-cnt: 0 views

Introduction

Moving from frontend to full-stack? The backend has different rules. Here are the critical gotchas around databases and APIs that trip up frontend developers.

Database Schema Design Gotchas

Normalization vs Denormalization: Frontend state is denormalized (nested objects). Databases prefer normalization (separate tables, foreign keys) for transactional data. However, denormalization makes sense for read-heavy scenarios, reporting tables, or when you need to avoid expensive joins in high-traffic queries.

-- Normalized
Users: id, name, email
Posts: id, user_id, content

-- Denormalized
PostSummaries: id, user_name, user_email, post_count, last_posted_at

Indexes Are Not Automatic: Unlike JavaScript objects, database lookups without indexes are slow. Always index foreign keys and frequently queried columns.

CREATE INDEX idx_posts_user_id ON posts(user_id);

Transactional vs Reference Data: Transactional data (orders, posts, messages) changes frequently and needs strict integrity. Reference data (countries, categories, statuses) changes rarely and can be cached. Treat them differently: reference data can often be denormalized or cached aggressively.

NULL Is Not Undefined: NULL requires explicit handling. WHERE column = value won’t match NULLs. Use IS NULL or IS NOT NULL.

Backend Patterns for Frontend Devs

Repository Pattern: Abstract data access behind interfaces. Your business logic shouldn’t contain raw SQL queries—it should call methods like userRepository.findById(123). This makes testing easier and decouples your code from the database.

DTOs vs Domain Models: Never expose database entities directly via APIs. Use Data Transfer Objects (DTOs) to control what fields are sent to clients. Your User table might have password_hash, but your API response should use a UserDTO without sensitive fields.

Service Layer: Controllers should be thin -— they handle HTTP concerns (parsing requests, setting status codes). Business logic belongs in service classes. This separation prevents bloated controllers and makes logic reusable across different endpoints.

Web API Design Gotchas

HTTP Methods Matter: GET must be idempotent and safe (no side effects). POST creates, PUT/PATCH updates. Don’t use GET for mutations—it breaks caching and browser prefetch.

Status Codes Are Documentation: 200 means success with body, 201 means created, 204 means success without body. 400 vs 422 vs 500 tells clients what went wrong and whether to retry.

Pagination Is Not Optional: Returning unbounded arrays (/api/posts) will eventually kill your server. Always paginate:

/api/posts?page=1&limit=20

Return metadata: { data: [], page: 1, total: 150 }

API Versioning From Day One: /api/v1/posts lets you evolve your API without breaking clients. You can’t unpublish breaking changes.

Debugging Techniques

SQL Query Logging: Enable slow query logs. Frontend devs debug with console.log; backend devs debug with query execution plans.

EXPLAIN ANALYZE SELECT * FROM posts WHERE user_id = 123;

Database Transactions: Multiple database operations should succeed or fail together. Frontend doesn’t have this concept—backend does:

// Without transaction: user created, profile creation fails, incomplete data
// With transaction: both user and profile created or neither exists

API Request Logging: Log every request with timestamp, user, endpoint, duration. Grep logs to debug production issues.

Canonical References

Database Design: Database Design for Mere Mortals by Michael Hernandez
SQL Mastery: SQL Performance Explained by Markus Winand (use-the-index-luke.com)
API Design: REST API Design Rulebook by Mark Massé
API Patterns: Roy Fielding’s REST dissertation (ics.uci.edu/~fielding/pubs/dissertation/rest_arch_style.htm)
Transactions: PostgreSQL Documentation on ACID (postgresql.org/docs/current/tutorial-transactions.html)

Conclusion

Backend development rewards understanding primitives: normalization, indexes, transactions, HTTP semantics. Master these, and you’ll avoid the common pitfalls that plague frontend-turned-fullstack developers.

JT's Weblog