Schools API Assignment by Educase India

This project is about creating a simple Schools API using Node.js, Express, and MySQL. It lets you add schools, list them, and sort them by distance from the user.

📂 Folder Structure

Educase_Assignment/
│── index.js # Main entry point
│── db.js # Database connection and table setup
│── routes/
│ └── schoolRoutes.js # All school-related routes
│── controllers/
│ └── schoolController.js # Route logic (add school, list schools)
│── utils/
│ └── validateSchool.js # Validation function
| └── calculateDistance.js # Calculate distance between user and schools
│── package.json

Routes

1. Add School

• Endpoint: /addSchool
• Method: POST
• Data needed: name, address, latitude, longitude
• Use: Adds a new school to the database

Payload:

{
  "name": "DPS Delhi",
  "address": "New Delhi, India",
  "latitude": 28.7041,
  "longitude": 77.1025
}

Response:

{
  "message": "School added successfully!"
}

2. List Schools

• Endpoint: /listSchools?lat=&lon=
• Method: GET
• Query: User’s latitude and longitude
• Use: Returns all schools sorted by distance from the user

Response:

{
  "data": [
    {
      "id": 1,
      "name": "New Delhi Public School",
      "address": "RK Puram, New Delhi",
      "latitude": 28.5672,
      "longitude": 77.21
    }
  ]
}

Tech Stack

Backend: Node.js + Express

Database: MySQL (Aiven cloud instance with SSL)

Libraries: mysql2, dotenv, nodemon

Setup & Installation

1. Clone the repo:

git clone https://github.com/Talish1234/Educase_Assignment.git
cd Educase_Assignment

2. Install dependencies:

npm install

3. Create a .env file:

DB_HOST=mysql-xxxx.aivencloud.com
DB_PORT=11738
DB_USER=avnadmin
DB_PASSWORD=your_aiven_password
DB_NAME=school_db

4. Place the Aiven ca.pem file in ./certs/ca.pem.

5. Start the server:

npm run start

6. Server will run at:

Server running on http://localhost:5000

Approach

1. Database Layer (db.js)

• Uses mysql2/promise with SSL.
• Ensures the schools table is created on startup.

2. Controller Layer (schoolController.js)

• Handles business logic (validation, insertion, proximity calculation).
• Uses the Haversine formula to calculate distance between user and schools.

3. Routes (schoolRoutes.js)

• Defines endpoints /addSchool and /listSchool