School Proximity API

Node.js + Express + MySQL API to:

• add a school
• list schools sorted by nearest distance from a user location

Tech Stack

• Node.js
• Express.js
• MySQL
• mysql2 (DB driver)
• Postman collection

Project Structure

src/
  app.js
  server.js
  config/db.js
  db/schema.sql
  routes/school.routes.js
  controllers/school.controller.js
  validators/school.validators.js
  utils/distance.js
  middleware/errorHandler.js
docs/
  school-proximity.postman_collection.json

Environment Variables

Create .env in project root:

PORT=3000
DB_HOST=localhost
DB_PORT=3306
DB_USER=root
DB_PASSWORD=your_password
DB_NAME=school_management
DB_SSL_CA_PATH=./certs/aiven-ca.pem

Notes:

• DB_SSL_CA_PATH is required by current DB config and should point to a valid CA cert file.
• For Aiven MySQL, use the Aiven CA cert path here.
• For local Docker MySQL without TLS, either configure SSL in MySQL, or adjust src/config/db.js to conditionally disable SSL for local usage.

Local Setup

1. Install dependencies:

pnpm install

2. Create .env:

cp .env.example .env

3. Update .env with working DB values.

4. Start API:

pnpm start

On startup, schema bootstrap runs from src/db/schema.sql.

MySQL Setup (Docker)

Start MySQL container:

docker run --name school-mysql \
  -e MYSQL_ROOT_PASSWORD=rootpass \
  -e MYSQL_DATABASE=school_management \
  -p 3306:3306 \
  -d mysql:8.0

Check DB:

docker exec -it school-mysql mysql -uroot -prootpass -e "SHOW DATABASES;"

Database Schema

CREATE TABLE IF NOT EXISTS 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,
  INDEX idx_lat_lng (latitude, longitude)
);

API Endpoints

POST /addSchool

Request body:

{
  "name": "Green Valley School",
  "address": "123 Main Street",
  "latitude": 12.9716,
  "longitude": 77.5946
}

Success (201):

{
  "message": "School added successfully",
  "data": {
    "id": 1,
    "name": "Green Valley School",
    "address": "123 Main Street",
    "latitude": 12.9716,
    "longitude": 77.5946
  }
}

GET /listSchools?latitude=12.9716&longitude=77.5946

Success (200):

{
  "message": "Schools fetched successfully",
  "data": [
    {
      "id": 2,
      "name": "Near School",
      "address": "Near Address",
      "latitude": 12.9716,
      "longitude": 77.5946,
      "distanceKm": 0
    },
    {
      "id": 1,
      "name": "Far School",
      "address": "Far Address",
      "latitude": 28.7041,
      "longitude": 77.1025,
      "distanceKm": 1750.123
    }
  ]
}

Validation error (400):

{
  "message": "Validation failed",
  "errors": [
    "name is required and must be a non-empty string."
  ]
}

Postman

• Import: docs/school-proximity.postman_collection.json
• Collection variable: baseUrl (default http://localhost:3000)

Deploy (Render + Aiven)

1. Create MySQL service in Aiven.
2. Create database/user and collect connection values.
3. Download Aiven CA cert and set DB_SSL_CA_PATH on Render (or mount cert path in runtime).
4. Push repo to GitHub/GitLab/Bitbucket.
5. Create Render Web Service:
   • Build: pnpm install
   • Start: pnpm start
6. Add env vars in Render:
   • DB_HOST, DB_PORT, DB_USER, DB_PASSWORD, DB_NAME, DB_SSL_CA_PATH, PORT
7. Deploy and verify:
   • /addSchool
   • /listSchools