Introduction
Optimizing Database Queries: From N+1 to Eager Loading — this is one of the most requested tutorials by our community. Whether you're a beginner looking to build your first real project or an experienced developer exploring new tools, this guide will walk you through everything step by step.
By the end of this article, you'll have a working implementation and a solid understanding of the architecture decisions behind it.
Why This Matters
Building real projects is the fastest way to level up as a developer. Tutorials teach syntax; projects teach engineering. Here's what you'll learn:
- Architecture decisions — why we structure things this way
- Best practices — patterns used in production applications
- Common pitfalls — mistakes that trip up even experienced developers
- Performance considerations — making it fast, not just functional
Prerequisites
Before starting, make sure you have:
- Node.js 18+ installed (check with
node --version) - Git for version control
- A code editor (VS Code recommended)
- Basic familiarity with JavaScript/TypeScript
- Terminal/command line comfort
Project Setup
Let's start by setting up our project structure:
# Create project directory
mkdir optimizing-database-queries-from-n1-to-eager-loading
cd optimizing-database-queries-from-n1-to-eager-loading
Initialize the project
npm init -y
Install core dependencies
npm install express dotenv cors
npm install -D typescript @types/node @types/express nodemonProject Structure
optimizing-database-queries-from-n1-to-eager-loading/
├── src/
│ ├── config/
│ │ └── index.ts # Configuration management
│ ├── controllers/
│ │ └── main.controller.ts # Request handlers
│ ├── middleware/
│ │ ├── auth.ts # Authentication
│ │ ├── errorHandler.ts # Global error handling
│ │ └── validation.ts # Input validation
│ ├── models/
│ │ └── index.ts # Data models
│ ├── routes/
│ │ └── index.ts # Route definitions
│ ├── services/
│ │ └── main.service.ts # Business logic
│ ├── utils/
│ │ └── helpers.ts # Utility functions
│ └── app.ts # Application entry
├── tests/
│ └── main.test.ts # Test suite
├── .env.example
├── tsconfig.json
└── package.jsonCore Implementation
Step 1: Configuration
// src/config/index.ts
import dotenv from 'dotenv';
dotenv.config();
export const config = {
port: parseInt(process.env.PORT || '3000', 10),
nodeEnv: process.env.NODE_ENV || 'development',
database: {
url: process.env.DATABASE_URL || '',
poolSize: parseInt(process.env.DB_POOL_SIZE || '10', 10),
},
cors: {
origin: process.env.CORS_ORIGIN || '*',
},
rateLimit: {
windowMs: 15 60 1000, // 15 minutes
max: 100,
},
} as const;
Step 2: Error Handling
// src/middleware/errorHandler.ts
import { Request, Response, NextFunction } from 'express';
export class AppError extends Error {
constructor(
public statusCode: number,
public message: string,
public isOperational = true
) {
super(message);
Object.setPrototypeOf(this, AppError.prototype);
}
}
export function errorHandler(
err: Error,
req: Request,
res: Response,
next: NextFunction
) {
if (err instanceof AppError) {
return res.status(err.statusCode).json({
success: false,
error: err.message,
});
}
console.error('Unexpected error:', err);
res.status(500).json({
success: false,
error: 'Internal server error',
});
}
Step 3: Business Logic
// src/services/main.service.ts
export class MainService {
private cache = new Map<string, any>();
async processRequest(data: Record<string, any>) {
const cacheKey = JSON.stringify(data);
// Check cache first
if (this.cache.has(cacheKey)) {
return { ...this.cache.get(cacheKey), cached: true };
}
// Process the request
const result = await this.executeLogic(data);
// Cache the result
this.cache.set(cacheKey, result);
// Prevent cache from growing unbounded
if (this.cache.size > 1000) {
const firstKey = this.cache.keys().next().value;
if (firstKey) this.cache.delete(firstKey);
}
return { ...result, cached: false };
}
private async executeLogic(data: Record<string, any>) {
// Core business logic here
return {
success: true,
processed: true,
timestamp: new Date().toISOString(),
data,
};
}
}
Step 4: Route Handler
// src/controllers/main.controller.ts
import { Request, Response, NextFunction } from 'express';
import { MainService } from '../services/main.service';
import { AppError } from '../middleware/errorHandler';
const service = new MainService();
export async function handleRequest(
req: Request,
res: Response,
next: NextFunction
) {
try {
const result = await service.processRequest(req.body);
res.json({ success: true, data: result });
} catch (error) {
next(new AppError(500, 'Processing failed'));
}
}
export async function healthCheck(req: Request, res: Response) {
res.json({
status: 'healthy',
uptime: process.uptime(),
timestamp: new Date().toISOString(),
});
}
Testing
Always test your code. Here's a basic test setup:
// tests/main.test.ts
import { MainService } from '../src/services/main.service';
describe('MainService', () => {
let service: MainService;
beforeEach(() => {
service = new MainService();
});
it('should process a request successfully', async () => {
const result = await service.processRequest({ key: 'value' });
expect(result.success).toBe(true);
expect(result.processed).toBe(true);
expect(result.cached).toBe(false);
});
it('should return cached results on second call', async () => {
const data = { key: 'test' };
await service.processRequest(data);
const cached = await service.processRequest(data);
expect(cached.cached).toBe(true);
});
it('should handle empty input', async () => {
const result = await service.processRequest({});
expect(result.success).toBe(true);
});
});
Deployment Checklist
Before going to production:
- [ ] Set all environment variables
- [ ] Enable HTTPS
- [ ] Configure rate limiting
- [ ] Set up error monitoring (Sentry, etc.)
- [ ] Add health check endpoint
- [ ] Configure CORS properly
- [ ] Enable gzip compression
- [ ] Set up logging (structured JSON logs)
- [ ] Add request ID tracking
- [ ] Configure database connection pooling