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

> Generate SQL INSERT statements for SQL Server, PostgreSQL, MySQL, and SQLite from the exercises dataset using a browser tool. No server-side processing needed.

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

---

**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`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/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`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/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`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/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`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/setup.html) (or [`index.html`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/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`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/setup.html) (lines 61-82).

```javascript
// 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`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/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`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/setup.html) and [`index.html`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/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`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/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`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/data/exercises.json).

### Can I modify the table schema before generation?

Yes. Edit the `DB_SQL` object in [`setup.html`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/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`](https://github.com/hasaneyldrm/exercises-dataset/blob/main/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`.