How to Generate SQL INSERT Statements for Different Databases from the Exercises Dataset

The exercises-dataset repository ships a browser-based tool that converts the JSON exercise catalog into ready-to-run INSERT statements for SQL Server, PostgreSQL, MySQL, and SQLite without requiring any server-side processing.

The exercises-dataset repository by hasaneyldrm contains a comprehensive fitness catalog stored in data/exercises.json. To streamline database seeding, the project includes a self-contained HTML interface that generates dialect-specific SQL files entirely within the browser. This guide explains how to use the built-in generator and how to replicate the logic programmatically for automation pipelines.

Understanding the SQL Generation Architecture

The generation logic resides entirely in setup.html as client-side JavaScript. The constant DB_SQL (lines 38-126) stores distinct CREATE TABLE templates and value placeholders for each supported database engine. When you click "Generate INSERT SQL", the script iterates over every entry in data/exercises.json and applies string substitution to produce 1,324 INSERT statements matching your selected dialect.

Database-Specific Templates in DB_SQL

The DB_SQL object defines the schema and insertion format for each engine:

• SQL Server – Uses NVARCHAR types and DATETIME2 (lines 38-60)
• PostgreSQL – Uses VARCHAR with TIMESTAMPTZ (lines 61-82)
• MySQL – Uses standard types with DATETIME (lines 84-104)
• SQLite – Uses TEXT and INTEGER affinity (lines 106-126)

Each template includes both the table creation statement and a placeholder pattern for the INSERT syntax, ensuring proper data type handling across engines.

Method 1: Generate SQL Using the Web Interface

The simplest approach requires only a web browser and works offline after cloning the repository.

1. Open setup.html (or index.html) in your browser.
2. Select your target database using the database tabs (SQL Server, PostgreSQL, MySQL, or SQLite).
3. Click Generate INSERT SQL.

The browser immediately downloads a file named exercises_<db>.sql containing the CREATE TABLE statement followed by all 1,324 INSERT rows. Because the generation happens client-side, no data is uploaded to any server.

Method 2: Programmatic Generation with Node.js

For CI/CD pipelines or automated workflows, extract the DB_SQL logic into a Node.js script. The following example generates PostgreSQL-compatible statements using the same column order defined in setup.html (lines 61-82).

// generate-postgres.js
const fs = require('fs');
const path = require('path');

const data = JSON.parse(
  fs.readFileSync(path.resolve(__dirname, 'data/exercises.json'), 'utf-8')
);

function escape(val) {
  return typeof val === 'string'
    ? `'${val.replace(/'/g, "''")}'`
    : val === null ? 'NULL' : val;
}

// Column order mirrors the DB_SQL.postgresql template in setup.html
const cols = [
  'id','name','category','body_part','equipment',
  'instructions_en','instructions_es','instructions_it','instructions_tr',
  'instructions_ru','instructions_zh','instructions_hi','instructions_pl',
  'instructions_ko','muscle_group','secondary_muscles','target',
  'image','gif_url','created_at'
];

const rows = data.map(item => {
  const vals = [
    item.id,
    item.name,
    item.category,
    item.body_part,
    item.equipment,
    item.instructions?.en,
    item.instructions?.es,
    item.instructions?.it,
    item.instructions?.tr,
    item.instructions?.ru,
    item.instructions?.zh,
    item.instructions?.hi,
    item.instructions?.pl,
    item.instructions?.ko,
    item.muscle_group,
    JSON.stringify(item.secondary_muscles || []),
    item.target,
    item.image,
    item.gif_url,
    item.created_at || new Date().toISOString()
  ].map(escape).join(', ');
  return `INSERT INTO exercises (${cols.join(', ')}) VALUES (${vals});`;
});

fs.writeFileSync(
  path.resolve(__dirname, 'exercises_postgres.sql'),
  rows.join('\n')
);
console.log('✔️  PostgreSQL INSERT file generated');

Run the script with node generate-postgres.js to produce a file identical in structure to the UI's PostgreSQL export.

Key Implementation Details

The insertion logic in setup.html (lines 555-564) concatenates column lists with escaped values using JavaScript string interpolation. Each database dialect receives appropriate escaping:

• PostgreSQL – Single quotes are doubled to handle string literals safely.
• MySQL – Supports backslash escaping for special characters.
• SQL Server – Uses standard T-SQL INSERT syntax with explicit column lists.
• SQLite – Accepts generic format with TEXT affinity for JSON arrays like secondary_muscles.

The column order remains consistent across all dialects, ensuring that the DB_SQL templates align perfectly with the generated value lists. The secondary_muscles field, stored as a JSON array in the source data, is stringified before insertion to maintain compatibility with standard text columns.

Summary

• The exercises-dataset repository provides client-side SQL generation via setup.html and index.html.
• Four database dialects are supported: SQL Server (MSSQL), PostgreSQL, MySQL, and SQLite.
• Method 1: Use the browser interface for one-off exports of exercises_<db>.sql files.
• Method 2: Adapt the DB_SQL logic into Node.js scripts for automated pipeline integration.
• Line references: CREATE TABLE templates appear at lines 38-126 in setup.html; the dynamic generation logic resides at lines 555-564.

Frequently Asked Questions

Does the SQL generator require a backend server?

No. The tool runs entirely in the browser using client-side JavaScript. The DB_SQL constants and generation functions ship with the HTML files, allowing offline operation immediately after cloning the repository.

How many exercises are included in the generated SQL?

The script generates 1,324 INSERT statements, covering the complete dataset stored in data/exercises.json.

Can I modify the table schema before generation?

Yes. Edit the DB_SQL object in setup.html (lines 38-126) to adjust data types or add constraints. Ensure you also update the column list in the generation logic (around line 555) if you change the field structure.

Is the JSON data embedded in the HTML file or loaded dynamically?

The script reads from data/exercises.json directly. When using the web interface, the browser loads this file from the local filesystem or web server hosting the repository. The Node.js example reads it explicitly via fs.readFileSync.

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