Overview
This skill provides comprehensive guidelines for building secure, consistent, and modern API endpoints in Next.js applications using TypeScript, with MariaDB as the database.
Core Principles
1. Security First
- Always validate session tokens
- Always check permissions before executing actions
- Never expose sensitive data in responses
- Always sanitize and validate user input
- Always use parameterized queries to prevent SQL injection
2. Consistency
- Use consistent naming conventions
- Follow RESTful principles
- Maintain consistent error handling
- Use standard HTTP status codes
3. Performance
- Use database indexes appropriately
- Implement pagination for list endpoints
- Use caching where appropriate
- Optimize queries before implementation
Pre-Development Checklist
Before writing any API endpoint code, ALWAYS complete these steps:
Step 1: Database Schema Verification
Use the MariaDB MCP server to:
# Check if tables exist
SHOW TABLES LIKE 'table_name';
# Verify table structure
DESCRIBE table_name;
# Check indexes
SHOW INDEX FROM table_name;
# Verify foreign key relationships
SELECT
TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'your_table';
Step 2: Permission Validation
- Identify which permission(s) are required
- Check the
/src/constants/permissions.tsfile for available permissions - Decide between
withAuth,withPermission, orwithAnyPermission
Step 3: Query Planning
- Write the SQL query first
- Test it using the MariaDB MCP server
- Check query performance with
EXPLAIN - Verify it returns expected results
API Endpoint Structure
File Organization
src/app/api/
├── resource/
│ ├── route.ts # GET (list), POST (create)
│ └── [id]/
│ ├── route.ts # GET (single), PUT (update), DELETE
│ └── sub-resource/
│ └── route.ts # Nested resources
Standard Endpoint Template
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { Permissions } from '@/constants/permissions';
import { withPermission } from '@/lib/auth/wrappers';
// Force dynamic rendering
export const dynamic = 'force-dynamic';
// GET /api/resource - List resources
export async function GET(request: NextRequest) {
return withPermission(Permissions.RESOURCE_VIEW)(async (req, session) => {
try {
// 1. Parse and validate query parameters
const searchParams = request.nextUrl.searchParams;
const page = parseInt(searchParams.get('page') || '1');
const pageSize = parseInt(searchParams.get('pageSize') || '50');
// Validate parameters
if (page < 1 || pageSize < 1 || pageSize > 100) {
return NextResponse.json(
{ error: 'Invalid pagination parameters' },
{ status: 400 }
);
}
// 2. Build query with proper escaping
const offset = (page - 1) * pageSize;
// 3. Get total count
const countResult = await query(
'SELECT COUNT(*) as total FROM resource WHERE is_active = 1'
);
const total = countResult[0]?.total || 0;
// 4. Get paginated data
const data = await query(
`SELECT id, name, created_at, updated_at
FROM resource
WHERE is_active = 1
ORDER BY created_at DESC
LIMIT ? OFFSET ?`,
[pageSize, offset]
);
// 5. Return structured response
return NextResponse.json({
data,
pagination: {
total,
page,
pageSize,
totalPages: Math.ceil(total / pageSize)
}
});
} catch (error) {
console.error('Error fetching resources:', error);
return NextResponse.json(
{
error: 'Internal server error',
details: error instanceof Error ? error.message : 'Unknown error'
},
{ status: 500 }
);
}
})(request);
}
// POST /api/resource - Create resource
export async function POST(request: NextRequest) {
return withPermission(Permissions.RESOURCE_CREATE)(async (req, session) => {
try {
// 1. Parse and validate request body
const body = await req.json();
const { name, description } = body;
// 2. Validate required fields
if (!name || name.trim().length === 0) {
return NextResponse.json(
{ error: 'Name is required' },
{ status: 400 }
);
}
// 3. Additional validation
if (name.length > 255) {
return NextResponse.json(
{ error: 'Name must be 255 characters or less' },
{ status: 400 }
);
}
// 4. Check for duplicates
const existing = await query(
'SELECT id FROM resource WHERE name = ? AND is_active = 1',
[name]
);
if (existing && existing.length > 0) {
return NextResponse.json(
{ error: 'Resource with this name already exists' },
{ status: 409 }
);
}
// 5. Insert with proper error handling
const result = await query(
`INSERT INTO resource (name, description, created_by, created_at)
VALUES (?, ?, ?, NOW())`,
[name, description || null, session.jwt.email]
);
const insertId = (result as any).insertId;
// 6. Fetch and return created resource
const created = await query(
'SELECT * FROM resource WHERE id = ?',
[insertId]
);
return NextResponse.json({
success: true,
message: 'Resource created successfully',
data: created[0]
}, { status: 201 });
} catch (error) {
console.error('Error creating resource:', error);
return NextResponse.json(
{ error: 'Failed to create resource' },
{ status: 500 }
);
}
})(request);
}
Endpoint with Parameters
// PUT /api/resource/[id] - Update resource
export async function PUT(
request: NextRequest,
{ params }: { params: Promise<{ id: string }> }
) {
return withPermission(Permissions.RESOURCE_UPDATE)(async (req, session) => {
try {
// 1. Await and validate params
const { id } = await params;
const resourceId = parseInt(id);
if (isNaN(resourceId)) {
return NextResponse.json(
{ error: 'Invalid resource ID' },
{ status: 400 }
);
}
// 2. Check if resource exists
const existing = await query(
'SELECT id FROM resource WHERE id = ? AND is_active = 1',
[resourceId]
);
if (!existing || existing.length === 0) {
return NextResponse.json(
{ error: 'Resource not found' },
{ status: 404 }
);
}
// 3. Parse and validate body
const body = await req.json();
// 4. Build dynamic update query
const updates: string[] = [];
const values: any[] = [];
if (body.name !== undefined) {
updates.push('name = ?');
values.push(body.name);
}
if (body.description !== undefined) {
updates.push('description = ?');
values.push(body.description);
}
if (updates.length === 0) {
return NextResponse.json(
{ error: 'No fields to update' },
{ status: 400 }
);
}
// Always update metadata
updates.push('updated_at = NOW()');
updates.push('updated_by = ?');
values.push(session.jwt.email);
// Add WHERE clause parameter
values.push(resourceId);
// 5. Execute update
await query(
`UPDATE resource SET ${updates.join(', ')} WHERE id = ?`,
values
);
// 6. Return updated resource
const updated = await query(
'SELECT * FROM resource WHERE id = ?',
[resourceId]
);
return NextResponse.json({
success: true,
message: 'Resource updated successfully',
data: updated[0]
});
} catch (error) {
console.error('Error updating resource:', error);
return NextResponse.json(
{ error: 'Failed to update resource' },
{ status: 500 }
);
}
})(request);
}
// DELETE /api/resource/[id] - Delete resource
export async function DELETE(
request: NextRequest,
{ params }: { params: Promise<{ id: string }> }
) {
return withPermission(Permissions.RESOURCE_DELETE)(async (req, session) => {
try {
const { id } = await params;
const resourceId = parseInt(id);
if (isNaN(resourceId)) {
return NextResponse.json(
{ error: 'Invalid resource ID' },
{ status: 400 }
);
}
// Check if resource exists
const existing = await query(
'SELECT id FROM resource WHERE id = ? AND is_active = 1',
[resourceId]
);
if (!existing || existing.length === 0) {
return NextResponse.json(
{ error: 'Resource not found' },
{ status: 404 }
);
}
// Soft delete (preferred) or hard delete
await query(
'UPDATE resource SET is_active = 0, updated_by = ?, updated_at = NOW() WHERE id = ?',
[session.jwt.email, resourceId]
);
// OR for hard delete:
// await query('DELETE FROM resource WHERE id = ?', [resourceId]);
return NextResponse.json({
success: true,
message: 'Resource deleted successfully'
});
} catch (error) {
console.error('Error deleting resource:', error);
return NextResponse.json(
{ error: 'Failed to delete resource' },
{ status: 500 }
);
}
})(request);
}
Security Guidelines
1. Authentication & Authorization
Use the Right Wrapper
// No permissions required (just authentication)
import { withAuth } from '@/lib/auth/wrappers';
export const GET = withAuth(async (request, session) => {
// session contains user info and permissions
});
// Single permission required
import { withPermission } from '@/lib/auth/wrappers';
export const POST = withPermission(Permissions.RESOURCE_CREATE)(
async (request, session) => {
// Only executes if user has permission
}
);
// Any of multiple permissions
import { withAnyPermission } from '@/lib/auth/wrappers';
export const PUT = withAnyPermission(
Permissions.RESOURCE_UPDATE,
Permissions.ADMIN
)(async (request, session) => {
// Executes if user has either permission
});
2. Input Validation
// Always validate input
function validateEmail(email: string): boolean {
const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
return emailRegex.test(email);
}
function validateDateFormat(date: string): boolean {
const dateRegex = /^\d{4}-\d{2}-\d{2}$/;
return dateRegex.test(date);
}
// Example usage
if (body.email && !validateEmail(body.email)) {
return NextResponse.json(
{ error: 'Invalid email format' },
{ status: 400 }
);
}
// Validate numeric inputs
const id = parseInt(params.id);
if (isNaN(id) || id < 1) {
return NextResponse.json(
{ error: 'Invalid ID' },
{ status: 400 }
);
}
// Validate enums
const validStatuses = ['active', 'inactive', 'pending'];
if (body.status && !validStatuses.includes(body.status)) {
return NextResponse.json(
{ error: `Status must be one of: ${validStatuses.join(', ')}` },
{ status: 400 }
);
}
3. SQL Injection Prevention
// ✅ CORRECT - Use parameterized queries
const result = await query(
'SELECT * FROM users WHERE email = ? AND status = ?',
[email, status]
);
// ❌ WRONG - Never concatenate user input
const result = await query(
`SELECT * FROM users WHERE email = '${email}'` // DANGEROUS!
);
// ✅ CORRECT - Dynamic WHERE clauses
const whereClauses: string[] = [];
const params: any[] = [];
if (search) {
whereClauses.push('name LIKE ?');
params.push(`%${search}%`);
}
if (status) {
whereClauses.push('status = ?');
params.push(status);
}
const whereClause = whereClauses.length > 0
? `WHERE ${whereClauses.join(' AND ')}`
: '';
const result = await query(
`SELECT * FROM users ${whereClause}`,
params
);
4. Data Exposure Prevention
// ❌ WRONG - Exposing sensitive data
return NextResponse.json({
user: {
id: user.id,
email: user.email,
password: user.password, // NEVER!
ssn: user.ssn // NEVER!
}
});
// ✅ CORRECT - Only expose necessary fields
return NextResponse.json({
user: {
id: user.id,
email: user.email,
name: user.name,
role: user.role
}
});
// ✅ CORRECT - Use SELECT to limit fields
const users = await query(
`SELECT id, email, name, role, created_at
FROM users
WHERE is_active = 1`
);
Database Best Practices
1. Use Transactions for Multiple Operations
import pool from '@/lib/db';
async function createProjectWithTasks(projectData, tasks) {
const connection = await pool.getConnection();
try {
await connection.beginTransaction();
// Insert project
const [projectResult] = await connection.query(
'INSERT INTO projects (title, created_by) VALUES (?, ?)',
[projectData.title, projectData.created_by]
);
const projectId = (projectResult as any).insertId;
// Insert tasks
for (const task of tasks) {
await connection.query(
'INSERT INTO tasks (project_id, title) VALUES (?, ?)',
[projectId, task.title]
);
}
await connection.commit();
return projectId;
} catch (error) {
await connection.rollback();
throw error;
} finally {
connection.release();
}
}
2. Optimize Queries
// Before implementing, check query performance
// Use MariaDB MCP server:
EXPLAIN SELECT ... FROM ... WHERE ...;
// Add indexes for frequently queried columns
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_project_status ON projects(status_id);
CREATE INDEX idx_created_at ON projects(created_at);
// Use LIMIT for large datasets
const results = await query(
'SELECT * FROM large_table ORDER BY created_at DESC LIMIT 100'
);
// Implement pagination
const offset = (page - 1) * pageSize;
const results = await query(
'SELECT * FROM table ORDER BY id DESC LIMIT ? OFFSET ?',
[pageSize, offset]
);
3. Handle NULL Values Properly
// Use COALESCE for default values
const results = await query(
`SELECT
id,
name,
COALESCE(description, '') as description,
COALESCE(priority, 'Medium') as priority
FROM tasks`
);
// Handle NULL in INSERT/UPDATE
await query(
'INSERT INTO projects (title, description) VALUES (?, ?)',
[title, description || null] // Convert empty string to NULL
);
4. Date Handling
// Format dates for MySQL
function formatDateForMySQL(dateValue: string | null): string | null {
if (!dateValue) return null;
// Validate format
if (!/^\d{4}-\d{2}-\d{2}$/.test(dateValue)) {
const date = new Date(dateValue);
if (isNaN(date.getTime())) return null;
return date.toISOString().split('T')[0];
}
return dateValue;
}
// Use NOW() for timestamps
await query(
'UPDATE resource SET updated_at = NOW() WHERE id = ?',
[id]
);
// Use CURRENT_TIMESTAMP as default in schema
CREATE TABLE example (
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
Error Handling
Standard Error Response Format
// Client errors (4xx)
return NextResponse.json(
{
error: 'User-friendly error message',
details: 'More specific details if needed',
code: 'ERROR_CODE' // Optional
},
{ status: 400 } // or 401, 403, 404, 409, etc.
);
// Server errors (5xx)
return NextResponse.json(
{
error: 'Internal server error',
details: process.env.NODE_ENV === 'development'
? error.message
: undefined
},
{ status: 500 }
);
HTTP Status Codes
- 200 OK: Successful GET, PUT, PATCH
- 201 Created: Successful POST
- 204 No Content: Successful DELETE (no body)
- 400 Bad Request: Invalid input
- 401 Unauthorized: Missing/invalid authentication
- 403 Forbidden: Valid auth but insufficient permissions
- 404 Not Found: Resource doesn't exist
- 409 Conflict: Duplicate resource
- 422 Unprocessable Entity: Validation failed
- 500 Internal Server Error: Server error
- 503 Service Unavailable: Service temporarily unavailable
Testing Your API
1. Manual Testing Checklist
- Test with valid data
- Test with missing required fields
- Test with invalid data types
- Test with SQL injection attempts
- Test with XSS attempts
- Test with extremely long strings
- Test without authentication
- Test with wrong permissions
- Test with non-existent IDs
- Test pagination edge cases
- Test concurrent requests
2. Using MariaDB MCP for Verification
# Verify data was inserted
SELECT * FROM table_name WHERE id = last_insert_id;
# Check relationships
SELECT t1.*, t2.*
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.table1_id
WHERE t1.id = ?;
# Verify soft deletes
SELECT * FROM table_name WHERE is_active = 0;
# Check data integrity
SELECT COUNT(*) FROM table_name WHERE field IS NULL;
Common Patterns
Pattern 1: Batch Operations
export async function POST(request: NextRequest) {
return withPermission(Permissions.BATCH_UPDATE)(async (req, session) => {
const { items } = await req.json();
if (!Array.isArray(items) || items.length === 0) {
return NextResponse.json(
{ error: 'Items array is required' },
{ status: 400 }
);
}
// Validate all items first
for (const item of items) {
if (!item.id || !item.value) {
return NextResponse.json(
{ error: 'Each item must have id and value' },
{ status: 400 }
);
}
}
// Use transaction for batch
const connection = await pool.getConnection();
try {
await connection.beginTransaction();
for (const item of items) {
await connection.query(
'UPDATE table SET value = ? WHERE id = ?',
[item.value, item.id]
);
}
await connection.commit();
return NextResponse.json({
success: true,
message: `Updated ${items.length} items`
});
} catch (error) {
await connection.rollback();
throw error;
} finally {
connection.release();
}
})(request);
}
Pattern 2: Filtered List with Search
export async function GET(request: NextRequest) {
return withPermission(Permissions.VIEW_LIST)(async (req, session) => {
const searchParams = request.nextUrl.searchParams;
const search = searchParams.get('search');
const status = searchParams.get('status');
const page = parseInt(searchParams.get('page') || '1');
const pageSize = parseInt(searchParams.get('pageSize') || '50');
// Build dynamic query
const whereClauses: string[] = ['is_active = 1'];
const params: any[] = [];
if (search) {
whereClauses.push('(name LIKE ? OR description LIKE ?)');
params.push(`%${search}%`, `%${search}%`);
}
if (status) {
whereClauses.push('status = ?');
params.push(status);
}
const whereClause = whereClauses.join(' AND ');
// Get count
const countResult = await query(
`SELECT COUNT(*) as total FROM resource WHERE ${whereClause}`,
params
);
const total = countResult[0]?.total || 0;
// Get data
const offset = (page - 1) * pageSize;
const data = await query(
`SELECT * FROM resource
WHERE ${whereClause}
ORDER BY created_at DESC
LIMIT ? OFFSET ?`,
[...params, pageSize, offset]
);
return NextResponse.json({
data,
pagination: {
total,
page,
pageSize,
totalPages: Math.ceil(total / pageSize)
}
});
})(request);
}
Pattern 3: Hierarchical Data
// Get parent with children
export async function GET(
request: NextRequest,
{ params }: { params: Promise<{ id: string }> }
) {
return withPermission(Permissions.VIEW_DETAILS)(async (req, session) => {
const { id } = await params;
const parentId = parseInt(id);
// Get parent
const parent = await query(
'SELECT * FROM parent_table WHERE id = ?',
[parentId]
);
if (!parent || parent.length === 0) {
return NextResponse.json(
{ error: 'Parent not found' },
{ status: 404 }
);
}
// Get children
const children = await query(
'SELECT * FROM child_table WHERE parent_id = ? ORDER BY display_order',
[parentId]
);
return NextResponse.json({
data: {
...parent[0],
children
}
});
})(request);
}
Checklist Before Committing
- Verified database schema using MariaDB MCP
- Checked all tables and columns exist
- Verified foreign key relationships
- Tested SQL queries with EXPLAIN
- Added appropriate indexes
- Used parameterized queries (no SQL injection)
- Validated all user inputs
- Added permission checks
- Used correct HTTP status codes
- Handled errors properly
- Added descriptive error messages
- Implemented pagination if listing data
- Used transactions for multi-step operations
- Tested with various inputs (valid, invalid, edge cases)
- Removed console.logs (except error logging)
- Added comments for complex logic
- Verified no sensitive data exposure
Quick Reference
Essential Imports
import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { Permissions } from '@/constants/permissions';
import { withAuth, withPermission, withAnyPermission } from '@/lib/auth/wrappers';
import pool from '@/lib/db'; // For transactions
Common Query Patterns
// Single row
const [row] = await query('SELECT * FROM table WHERE id = ?', [id]);
// Multiple rows
const rows = await query('SELECT * FROM table WHERE status = ?', [status]);
// Insert
const result = await query('INSERT INTO table (field) VALUES (?)', [value]);
const insertId = (result as any).insertId;
// Update
await query('UPDATE table SET field = ? WHERE id = ?', [value, id]);
// Delete/Soft Delete
await query('UPDATE table SET is_active = 0 WHERE id = ?', [id]);
await query('DELETE FROM table WHERE id = ?', [id]);
Response Templates
// Success with data
return NextResponse.json({ data, success: true });
// Success with message
return NextResponse.json({ success: true, message: 'Operation completed' });
// Created
return NextResponse.json({ data, success: true }, { status: 201 });
// Error
return NextResponse.json({ error: 'Error message' }, { status: 400 });
Remember
- Security is not optional - Always validate, always check permissions
- Test your queries - Use MariaDB MCP before writing code
- Think about scale - Use pagination, indexes, and efficient queries
- Handle errors gracefully - Users should get helpful messages
- Be consistent - Follow these patterns across all endpoints
- Document as you go - Add comments for complex logic
This skill ensures every API endpoint you create is secure, performant, and maintainable.