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

> Learn to set up a REST API for the exercises dataset using Express.js. Implement the wizard for pagination, filtering, and random selection with SQL queries.

- Repository: [Hasan Emir Yıldırım/exercises-dataset](https://github.com/hasaneyldrm/exercises-dataset)
- Tags: how-to-guide
- Published: 2026-08-01

---

**You can set up a REST API for the exercises dataset by implementing the Express.js architecture defined in the repository's [`setup.html`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/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`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/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`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/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`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/setup.html):

```bash
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`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/setup.html) → `FRAMEWORK_META`.

### 2. Configure Environment Variables

Create a `.env` file in the project root:

```env
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`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/setup.html) → `DB_SQL`. For PostgreSQL:

```sql
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`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/data/exercises.json) (1,324 records) via the INSERT generator provided in the [`setup.html`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/setup.html) UI.

### 4. Implement the Express Server

Create [`server.js`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/server.js) implementing the contract from [`setup.html`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/setup.html):

```javascript
// 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`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/setup.html) → `API_TEMPLATES.curl`):

```bash

# 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`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/setup.html) → [`API_TEMPLATES.js`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/API_TEMPLATES.js):

```javascript
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`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/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`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/data/exercises.json) (1,324 records) with schema validation available in [`data/exercises.schema.json`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/data/exercises.schema.json).

## Frequently Asked Questions

### What database engines does the exercises dataset API support?

According to the [`setup.html`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/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`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/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`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/data/exercises.json) contains all 1,324 exercise records ready for import, the Express.js API contract in [`setup.html`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/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.