School Management API

A Node.js REST API for managing school data with proximity-based sorting functionality. Built with Express.js, MySQL, and featuring geographical distance calculations.

🚀 Features

• Add Schools: Add new schools with name, address, and coordinates
• List Schools: Retrieve all schools sorted by proximity to user location
• Data Validation: Comprehensive input validation for all endpoints
• Geographical Sorting: Haversine formula for accurate distance calculations
• Security: Helmet.js for security headers and CORS support
• Logging: Morgan for HTTP request logging
• Error Handling: Comprehensive error handling and validation

📋 Prerequisites

• Node.js (v14 or higher)
• MySQL (v8.0 or higher)
• npm or yarn

🛠️ Installation

1. Clone the repository

   git clone <repository-url>
   cd school-management-api

2. Install dependencies

   npm install

3. Set up environment variables

   cp env.example .env

   Edit .env file with your database credentials:

   DB_HOST=localhost
   DB_USER=root
   DB_PASSWORD=your_password
   DB_NAME=school_management
   DB_PORT=3306
   PORT=3000
   NODE_ENV=development

4. Create MySQL database

   CREATE DATABASE school_management;

5. Start the server

   # Development mode with auto-restart
   npm run dev
   
   # Production mode
   npm start

📚 API Documentation

Base URL

http://localhost:3000

Endpoints

1. Add School

POST /addSchool

Add a new school to the database.

Request Body:

{
  "name": "Delhi Public School",
  "address": "123 Main Street, New Delhi, India",
  "latitude": 28.7041,
  "longitude": 77.1025
}

Response:

{
  "success": true,
  "message": "School added successfully",
  "data": {
    "id": 1,
    "name": "Delhi Public School",
    "address": "123 Main Street, New Delhi, India",
    "latitude": 28.7041,
    "longitude": 77.1025,
    "created_at": "2024-01-15T10:30:00.000Z",
    "updated_at": "2024-01-15T10:30:00.000Z"
  }
}

2. List Schools

GET /listSchools?latitude=28.7041&longitude=77.1025

Get all schools sorted by proximity to the specified coordinates.

Query Parameters:

• latitude (required): User's latitude (-90 to 90)
• longitude (required): User's longitude (-180 to 180)

Response:

{
  "success": true,
  "message": "Schools retrieved successfully",
  "data": [
    {
      "id": 1,
      "name": "Delhi Public School",
      "address": "123 Main Street, New Delhi, India",
      "latitude": 28.7041,
      "longitude": 77.1025,
      "created_at": "2024-01-15T10:30:00.000Z",
      "updated_at": "2024-01-15T10:30:00.000Z",
      "distance": 0.0
    },
    {
      "id": 2,
      "name": "Mumbai International School",
      "address": "456 Marine Drive, Mumbai, India",
      "latitude": 19.0760,
      "longitude": 72.8777,
      "created_at": "2024-01-15T10:35:00.000Z",
      "updated_at": "2024-01-15T10:35:00.000Z",
      "distance": 1156.8
    }
  ],
  "total": 2,
  "userLocation": {
    "latitude": 28.7041,
    "longitude": 77.1025
  }
}

3. Get School by ID

GET /school/:id

Get a specific school by its ID.

Response:

{
  "success": true,
  "message": "School retrieved successfully",
  "data": {
    "id": 1,
    "name": "Delhi Public School",
    "address": "123 Main Street, New Delhi, India",
    "latitude": 28.7041,
    "longitude": 77.1025,
    "created_at": "2024-01-15T10:30:00.000Z",
    "updated_at": "2024-01-15T10:30:00.000Z"
  }
}

4. Health Check

GET /health

Check if the API is running.

Response:

{
  "success": true,
  "message": "School Management API is running",
  "timestamp": "2024-01-15T10:30:00.000Z",
  "environment": "development"
}

🔧 Validation Rules

Add School Validation

• name: Required, 2-255 characters
• address: Required, 5-500 characters
• latitude: Required, float between -90 and 90
• longitude: Required, float between -180 and 180

List Schools Validation

• latitude: Required, float between -90 and 90
• longitude: Required, float between -180 and 180

🗄️ Database Schema

CREATE TABLE schools (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address VARCHAR(500) NOT NULL,
  latitude FLOAT NOT NULL,
  longitude FLOAT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

🧮 Distance Calculation

The API uses the Haversine formula to calculate geographical distances between coordinates:

const R = 6371; // Earth's radius in kilometers
const dLat = (lat2 - lat1) * Math.PI / 180;
const dLon = (lon2 - lon1) * Math.PI / 180;
const a = Math.sin(dLat/2) * Math.sin(dLat/2) + 
         Math.cos(lat1 * Math.PI / 180) * Math.cos(lat2 * Math.PI / 180) * 
         Math.sin(dLon/2) * Math.sin(dLon/2);
const c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));
const distance = R * c;

🚀 Deployment

Local Development

npm run dev

Production

npm start

Environment Variables for Production

NODE_ENV=production
PORT=3000
DB_HOST=your-production-db-host
DB_USER=your-production-db-user
DB_PASSWORD=your-production-db-password
DB_NAME=your-production-db-name
DB_PORT=3306

📁 Project Structure

school-management-api/
├── config/
│   └── database.js          # Database configuration
├── controllers/
│   └── schoolController.js  # Business logic
├── middleware/
│   └── validation.js        # Input validation
├── routes/
│   └── schoolRoutes.js      # API routes
├── utils/
│   └── distanceCalculator.js # Distance calculation utilities
├── server.js                # Main server file
├── package.json             # Dependencies
├── env.example              # Environment variables template
└── README.md               # Documentation

🧪 Testing

Using Postman

1. Add School Request:

   • Method: POST
   • URL: http://localhost:3000/addSchool
   • Headers: Content-Type: application/json
   • Body:

   {
     "name": "Test School",
     "address": "Test Address, Test City",
     "latitude": 28.7041,
     "longitude": 77.1025
   }

2. List Schools Request:

   • Method: GET
   • URL: http://localhost:3000/listSchools?latitude=28.7041&longitude=77.1025

Using cURL

# Add a school
curl -X POST http://localhost:3000/addSchool \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Test School",
    "address": "Test Address, Test City",
    "latitude": 28.7041,
    "longitude": 77.1025
  }'

# List schools
curl "http://localhost:3000/listSchools?latitude=28.7041&longitude=77.1025"

🔒 Security Features

• Helmet.js: Security headers
• CORS: Cross-origin resource sharing
• Input Validation: Comprehensive validation using express-validator
• SQL Injection Protection: Parameterized queries with mysql2
• Error Handling: Secure error responses

📝 Error Handling

The API returns consistent error responses:

{
  "success": false,
  "message": "Error description",
  "errors": [
    {
      "field": "latitude",
      "message": "Latitude must be a valid number between -90 and 90",
      "value": "invalid"
    }
  ]
}

🤝 Contributing

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests if applicable
5. Submit a pull request

📄 License

This project is licensed under the MIT License.

📞 Support

For support, please open an issue in the repository or contact the development team.