How to Set Up a REST API for the Exercises Dataset Using Express.js

You can set up a REST API for the exercises dataset by implementing the Express.js architecture defined in the repository's setup.html wizard, which provides ready-made specifications for endpoints handling pagination, filtering by category/body part, and random selection using parameterized SQL queries against PostgreSQL, MySQL, or SQLite.

The hasaneyldrm/exercises-dataset repository contains 1,324 exercise records and ships with a developer-setup wizard in setup.html that defines a complete Express.js API contract. By following this specification, you can build a production-ready REST API with proper error handling, CORS support, and database abstraction layers. This guide walks through the exact implementation based on the source code and LLM prompts provided in the repository.

Architecture Overview

The API follows a layered architecture defined in the Express.js entry of setup.html → FRAMEWORK_META:

Layer	Responsibility	Technology
HTTP Server	Request handling, CORS, logging	Express.js
Router	Endpoint mapping	Express Router
Controller	Validation and response shaping	JavaScript
Data Access	Parameterized SQL execution	pg, mysql2, or better-sqlite3
Database	Storage for 1,324 exercise rows	PostgreSQL/MySQL/SQLite

The contract enforces environment-driven configuration via DATABASE_URL, parameterized queries to prevent injection, standardized JSON pagination metadata (total, page, limit, totalPages), and comprehensive error handling (400/404/500).

Step-by-Step Implementation

1. Clone and Install Dependencies

Clone the repository and install the Node.js packages specified in setup.html:

git clone https://github.com/hasaneyldrm/exercises-dataset.git
cd exercises-dataset
npm init -y
npm install express pg mysql2 better-sqlite3 cors dotenv

The dependency list (express, pg / mysql2 / better-sqlite3, cors, dotenv) comes directly from the Express.js meta block in setup.html → FRAMEWORK_META.

2. Configure Environment Variables

Create a .env file in the project root:

DATABASE_URL=postgresql://user:pass@localhost:5432/exercises
ALLOWED_ORIGINS=https://myapp.com
PORT=3000

The DATABASE_URL is required for the connection pool, while ALLOWED_ORIGINS optionally restricts CORS access.

3. Set Up the Database Schema

Create the database using the SQL templates from setup.html → DB_SQL. For PostgreSQL:

CREATE TABLE exercises (
  id                VARCHAR(10)  PRIMARY KEY,
  name              VARCHAR(255) NOT NULL,
  category          VARCHAR(100),
  body_part         VARCHAR(100),
  equipment         VARCHAR(100),
  instructions_en   TEXT,
  instructions_es   TEXT,
  instructions_it   TEXT,
  instructions_tr   TEXT,
  instructions_ru   TEXT,
  instructions_zh   TEXT,
  instructions_hi   TEXT,
  instructions_pl   TEXT,
  instructions_ko   TEXT,
  muscle_group      VARCHAR(100),
  secondary_muscles JSONB,
  target            VARCHAR(100),
  image             VARCHAR(500),
  gif_url           VARCHAR(500),
  created_at        TIMESTAMPTZ
);

Populate the table using the data from data/exercises.json (1,324 records) via the INSERT generator provided in the setup.html UI.

4. Implement the Express Server

Create server.js implementing the contract from setup.html:

// server.js – Express.js API for the Exercises Dataset
require('dotenv').config();
const express = require('express');
const cors = require('cors');
const { Pool } = require('pg');

// Configuration
const PORT = process.env.PORT || 3000;
const DB_URL = process.env.DATABASE_URL;
const ALLOWED_ORIGINS = process.env.ALLOWED_ORIGINS?.split(',') || ['*'];

const app = express();
app.use(cors({ origin: ALLOWED_ORIGINS }));
app.use(express.json());

const pool = new Pool({ connectionString: DB_URL });

function parseIntOrDefault(value, def) {
  const n = parseInt(value, 10);
  return Number.isNaN(n) ? def : n;
}

// GET /exercises/:id – single exercise
app.get('/exercises/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { rows } = await pool.query('SELECT * FROM exercises WHERE id = $1', [id]);
    if (rows.length === 0) return res.status(404).json({ error: 'Exercise not found' });
    res.json(rows[0]);
  } catch (e) {
    console.error(e);
    res.status(500).json({ error: 'Internal server error' });
  }
});

// GET /exercises – paginated & filtered list
app.get('/exercises', async (req, res) => {
  try {
    const page = Math.max(parseIntOrDefault(req.query.page, 1), 1);
    const limit = Math.min(parseIntOrDefault(req.query.limit, 20), 100);
    const offset = (page - 1) * limit;

    const filters = [];
    const values = [];
    let idx = 1;
    const fields = ['category', 'body_part', 'equipment', 'muscle_group', 'target'];
    
    for (const f of fields) {
      if (req.query[f]) {
        filters.push(`${f} ILIKE $${idx}`);
        values.push(`%${req.query[f]}%`);
        idx++;
      }
    }
    const where = filters.length ? `WHERE ${filters.join(' AND ')}` : '';

    const countRes = await pool.query(`SELECT COUNT(*) FROM exercises ${where}`, values);
    const total = parseInt(countRes.rows[0].count, 10);
    const totalPages = Math.ceil(total / limit);

    const dataRes = await pool.query(
      `SELECT * FROM exercises ${where} ORDER BY id LIMIT $${idx} OFFSET $${idx + 1}`,
      [...values, limit, offset]
    );

    res.json({
      data: dataRes.rows,
      total,
      page,
      limit,
      totalPages,
    });
  } catch (e) {
    console.error(e);
    res.status(500).json({ error: 'Internal server error' });
  }
});

// GET /exercises/random – single random exercise
app.get('/exercises/random', async (req, res) => {
  try {
    const { rows } = await pool.query('SELECT * FROM exercises ORDER BY RANDOM() LIMIT 1');
    res.json(rows[0]);
  } catch (e) {
    console.error(e);
    res.status(500).json({ error: 'Internal server error' });
  }
});

// GET /categories – unique sorted categories
app.get('/categories', async (req, res) => {
  try {
    const { rows } = await pool.query('SELECT DISTINCT category FROM exercises ORDER BY category');
    res.json(rows.map(r => r.category));
  } catch (e) {
    console.error(e);
    res.status(500).json({ error: 'Internal server error' });
  }
});

// GET /body-parts – unique sorted body parts
app.get('/body-parts', async (req, res) => {
  try {
    const { rows } = await pool.query('SELECT DISTINCT body_part FROM exercises ORDER BY body_part');
    res.json(rows.map(r => r.body_part));
  } catch (e) {
    console.error(e);
    res.status(500).json({ error: 'Internal server error' });
  }
});

// GET /equipment – unique sorted equipment types
app.get('/equipment', async (req, res) => {
  try {
    const { rows } = await pool.query('SELECT DISTINCT equipment FROM exercises ORDER BY equipment');
    res.json(rows.map(r => r.equipment));
  } catch (e) {
    console.error(e);
    res.status(500).json({ error: 'Internal server error' });
  }
});

app.listen(PORT, () => {
  console.log(`Express API listening on http://localhost:${PORT}`);
});

Testing the API

Test your endpoints using cURL (matching the templates in setup.html → API_TEMPLATES.curl):

# Single exercise

curl -s "http://localhost:3000/exercises/0001" | python3 -m json.tool

# Paginated list with filters

curl -s "http://localhost:3000/exercises?category=Strength&page=1&limit=20" | python3 -m json.tool

# Random exercise

curl -s "http://localhost:3000/exercises/random" | python3 -m json.tool

For browser-based clients, use the fetch pattern from setup.html → API_TEMPLATES.js:

const BASE_URL = 'http://localhost:3000';

async function getExercise(id) {
  const res = await fetch(`${BASE_URL}/exercises/${id}`);
  if (!res.ok) throw new Error(`HTTP ${res.status}`);
  return res.json();
}

async function getFiltered({ page = 1, limit = 20, category } = {}) {
  const params = new URLSearchParams({ page, limit });
  if (category) params.set('category', category);
  const res = await fetch(`${BASE_URL}/exercises?${params}`);
  return res.json();
}

Summary

• The Express.js API contract is defined in setup.html → FRAMEWORK_META, specifying required packages (express, pg/mysql2/better-sqlite3, cors, dotenv) and endpoint behavior.
• The implementation uses parameterized SQL queries to prevent injection attacks, as required by the repository specification.
• Environment variables (DATABASE_URL, ALLOWED_ORIGINS, PORT) drive configuration without hardcoded credentials.
• The API provides pagination metadata (total, page, limit, totalPages) and supports filtering by category, body_part, equipment, muscle_group, and target.
• Reference data resides in data/exercises.json (1,324 records) with schema validation available in data/exercises.schema.json.

Frequently Asked Questions

What database engines does the exercises dataset API support?

According to the setup.html → FRAMEWORK_META configuration, the API supports PostgreSQL, MySQL, SQL Server, and SQLite. The implementation uses driver-specific packages (pg, mysql2, better-sqlite3) with parameterized queries adapted to each engine's syntax.

How does the API handle SQL injection prevention?

The implementation strictly uses parameterized queries with placeholder tokens (e.g., $1, $2 for PostgreSQL) instead of string interpolation. This approach is explicitly mandated in the setup.html contract, ensuring user-supplied filter values are never directly concatenated into SQL statements.

Can I use the JSON file directly instead of setting up a database?

While data/exercises.json contains all 1,324 exercise records ready for import, the Express.js API contract in setup.html is designed for SQL databases to enable efficient filtering, pagination, and random selection. For a file-based approach, you would need to load the JSON into memory and implement filtering logic manually, diverging from the provided architecture.

What are the pagination limits enforced by the API?

The API defaults to 20 items per page with a maximum limit of 100 items per request, as implemented in the parseIntOrDefault logic within the controller. The totalPages value is calculated server-side using Math.ceil(total / limit) and included in every paginated response.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}