How to Import the Exercises Dataset into PostgreSQL: Complete Guide

To import the exercises dataset into PostgreSQL, open the setup.html file in any modern browser, click "Generate .sql" to download the SQL script, and execute it with psql -f exercises.sql.

The hasaneyldrm/exercises-dataset repository provides a production-ready workflow for importing 1,324 fitness exercises directly into PostgreSQL. This guide walks through the browser-based SQL generation process and the exact commands needed to populate your database with structured workout data, including multilingual instructions and media references.

Generate the SQL Import Script

The repository ships with setup.html, an interactive developer guide located in the repository root that automates SQL script creation. This file contains JavaScript logic that parses data/exercises.json and generates a complete PostgreSQL dump file.

1. Clone or navigate to the repository:

   git clone https://github.com/hasaneyldrm/exercises-dataset.git
   cd exercises-dataset

2. Open setup.html in any modern web browser:

   open setup.html  # macOS
   
   # OR
   
   xdg-open setup.html  # Linux
   

3. Scroll to the Database Setup section and click the "Generate .sql" button. The browser will download exercises.sql, containing a CREATE TABLE statement and individual INSERT statements for all 1,324 exercises.

Create the Target Database

While the generated script creates the table automatically, you must ensure the target database exists before running the import:

createdb fitness_app

If you prefer to inspect the schema manually, the table structure aligns with data/exercises.schema.json and uses JSONB columns for flexible multilingual content:

CREATE TABLE exercises (
    id VARCHAR PRIMARY KEY,
    name VARCHAR NOT NULL,
    category VARCHAR,
    body_part VARCHAR,
    equipment VARCHAR,
    instructions JSONB,
    instruction_steps JSONB,
    muscle_group VARCHAR,
    secondary_muscles JSONB,
    target VARCHAR,
    media_id VARCHAR,
    image VARCHAR,
    gif_url VARCHAR,
    attribution VARCHAR,
    created_at TIMESTAMPTZ
);

Execute the Import

Run the downloaded script using the PostgreSQL command-line client:

psql -U <your_username> -d fitness_app -f exercises.sql

This command executes all statements in a single transaction, populating the exercises table with rows from data/exercises.json. The import preserves relative paths to media files (stored in images/ and videos/ directories) within the image and gif_url columns.

Verify the Import

Confirm successful loading with these diagnostic queries:

-- Check total row count
SELECT COUNT(*) AS total_exercises FROM exercises;

-- View available categories
SELECT DISTINCT category FROM exercises ORDER BY category;

-- Sample exercise with English instructions
SELECT id, name, instructions->>'en' AS instructions_en 
FROM exercises 
LIMIT 5;

The count should return 1324, matching the total records in the source repository.

Query the Data Programmatically

After you import the exercises dataset into PostgreSQL, access it using Python and psycopg2:

import psycopg2
import json

conn = psycopg2.connect(
    dbname="fitness_app",
    user="postgres",
    password="YOUR_PASSWORD",
    host="localhost"
)

cur = conn.cursor()
cur.execute("""
    SELECT id, name, instructions->>'en' AS en_instr 
    FROM exercises 
    LIMIT 5
""")

for row in cur.fetchall():
    print(f"{row[0]:4} | {row[1]:30} | {row[2][:60]}…")

cur.close()
conn.close()

To export a specific exercise to JSON directly from PostgreSQL:

COPY (
    SELECT *
    FROM exercises
    WHERE id = '0001'
) TO PROGRAM 'jq -c . > exercise_0001.json';

Summary

• setup.html generates a complete exercises.sql file containing both schema and data for 1,324 exercises.
• The import uses standard PostgreSQL JSONB columns to store multilingual instructions and muscle groups flexibly.
• Execute the import with psql -U <user> -d <db> -f exercises.sql to load all records in one transaction.
• Media references in the image and gif_url columns point to files in the repository's images/ and videos/ directories.
• The schema is formally defined in data/exercises.schema.json and mirrors the JSON structure of data/exercises.json.

Frequently Asked Questions

What is the fastest way to import the exercises dataset into PostgreSQL?

The fastest method is using the browser-based setup.html tool provided in the repository. Opening this file and clicking "Generate .sql" produces a ready-to-run SQL script that includes both table creation and data insertion statements, eliminating manual schema mapping or ETL scripting.

How is the exercises table structured in PostgreSQL?

The table uses VARCHAR for identifiers and categorical data, with JSONB columns for complex nested structures like instructions, instruction_steps, and secondary_muscles. This schema, defined in data/exercises.schema.json, allows storage of multilingual content without requiring separate translation tables.

Can I import the dataset without using the browser-based setup.html?

Yes, though it requires manual work. You would need to parse data/exercises.json (containing the 1,324 exercise objects) and construct equivalent INSERT statements, or use a tool like jq combined with psql's \copy command to load the JSON directly into a table matching the schema specifications.

Does the dataset include the actual media files like images and GIFs?

The PostgreSQL import includes only references to media files via the image and gif_url columns, which contain relative paths like images/0001.jpg and videos/0001.gif. The actual media files reside in the repository's images/ and videos/ directories and must be served or copied separately to your static file server.

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