dump2json

Convert MySQL/MariaDB or PostgreSQL dump files into JSON, one file per table.

Takes a .sql dump, imports it into a local temporary database, then exports every table as a beautified JSON array of objects. Handles large dumps via streaming imports, auto-detects the database engine, and cleans up after itself.

Features

• 🐬 MySQL / MariaDB and 🐘 PostgreSQL support
• 🔍 Auto-detection of dump type; just point it at a .sql file
• 📁 One JSON file per table , arrays of objects, pretty-printed by default
• 🚀 Streaming import via native CLI clients; handles multi-GB dumps
• 📊 Progress bars for import and export phases
• 🧹 Automatic cleanup of temporary databases (opt-out with --no-cleanup)
• 🔧 Server lifecycle , can start/stop DB servers if needed
• 💊 Doctor command , diagnose your environment: dump2json doctor
• ⚡ Global install , npm install -g dump2json
• 🛡️ Graceful error handling with actionable suggestions

Quick start

# Install globally
npm install -g dump2json

# Convert a dump file
dump2json ./backup.sql

# Output lands in ./<dbname>/ with one JSON file per table:
#   ./mydb/users.json
#   ./mydb/posts.json
#   ./mydb/_schema.json

Installation

From npm (recommended)

npm install -g dump2json

From source

git clone https://gitlab.com/frvnkenstein/dump2json.git
cd dump2json
npm install
npm run build
npm link          # makes `dump2json` available globally

Prerequisites

• Node.js ≥ 18
• A running database server (MySQL/MariaDB or PostgreSQL), or the --install flag to attempt automatic installation via your system package manager.

Usage

dump2json <file> [options]

Arguments:
  <file>                  path to the .sql dump file

Options:
  -t, --type <type>       force database type: auto, mysql, or postgres (default: auto)
  -H, --host <host>       database server host (default: 127.0.0.1)
  -P, --port <port>       database server port (default: 3306 for MySQL, 5432 for PG)
  -u, --user <user>       database user (default: root)
  -p, --password <pass>   database password
  -o, --output <dir>      output directory (default: current working directory)
  -n, --name <name>       force output / database name
  --install               allow automatic installation of database servers
  --no-cleanup            keep the temporary database after exit
  --no-pretty             output compact JSON instead of pretty-printed
  --batch-size <n>        rows to fetch per batch during export (default: 1000)
  -v, --verbose           enable detailed logging
  -h, --help              display help
  -V, --version           display version

Commands:
  doctor                  diagnose environment and database servers

Examples

# Auto-detect everything, output to ./dbname/
dump2json ./backup.sql

# Force PostgreSQL, custom user, verbose logging
dump2json dump.sql -t postgres -u admin --verbose

# Allow auto-install of DB server, custom output dir
dump2json dump.sql --install -o ./exports -n mydata

# Keep the temp DB for manual inspection
dump2json dump.sql --no-cleanup

# Compact (single-line) JSON output
dump2json dump.sql --no-pretty

# Doctor , check your environment
dump2json doctor
dump2json doctor -u myuser -p mypass --verbose

Output format

For a database named mydb with tables users and posts:

mydb/
├── users.json        # [{ "id": 1, "name": "Alice", ... }, ...]
├── posts.json        # [{ "id": 1, "title": "Hello", ... }, ...]
└── _schema.json      # Column metadata and row counts

Pretty mode (default)

[
  {
    "id": 1,
    "name": "Alice",
    "email": "[email protected]",
    "created_at": "2024-01-15T10:30:00.000Z"
  },
  {
    "id": 2,
    "name": "Bob",
    "email": null,
    "created_at": "2024-02-20T14:00:00.000Z"
  }
]

Compact mode (--no-pretty)

[{"id":1,"name":"Alice","email":"[email protected]","created_at":"2024-01-15T10:30:00.000Z"},{"id":2,"name":"Bob","email":null,"created_at":"2024-02-20T14:00:00.000Z"}]

Special type handling

| Database type | JSON representation | |---|---| | NULL | null | | DATE / TIMESTAMP | ISO-8601 string | | BLOB / BYTEA | "__dump2json_b64:<base64>" | | BIGINT (> Number.MAX_SAFE_INTEGER) | "__dump2json_bigint:<string>" | | JSON / JSONB | Inline parsed object |

How it works

Dump File (.sql)
      │
      ▼
  Detector ─── type (mysql/postgres) + db name
      │
      ▼
  Server Check ─── find/install/start DB server
      │
      ▼
  Import ─── mysql or psql CLI streams dump → temp DB
      │
      ▼
  Export ─── SELECT each table, write JSON array
      │
      ▼
  Cleanup ─── drop temp DB, stop server (if we started it)

1. Detect: Reads the first 64 KB of the dump to determine the engine type and extract the database name.
2. Server: Checks if the required server is installed and running. Can optionally install (--install) and start it.
3. Import: Spawns the native CLI (mysql or psql) and pipes the dump file into a uniquely-named temporary database.
4. Export: Queries every table in batches, sanitises column values, and writes pretty-printed JSON arrays to disk.
5. Cleanup: Drops the temporary database and stops any server process the tool started (unless --no-cleanup).

Doctor

The doctor command checks your environment for database server compatibility:

$ dump2json doctor

🔍  dump2json doctor

Checking your environment for database server compatibility...

── System ──
  Platform         linux / x64
  OS               Linux 6.17.0
  Node.js          v22.7.0
  npm              10.8.0

── Database servers ──

  🐬 MySQL / MariaDB
    ✔ Installed
    Binary          /usr/bin/mysql
    Version         mysql  Ver 8.0.36
    ✔ Port 3306 reachable on 127.0.0.1
    ✔ Authentication OK (user: root)

  🐘 PostgreSQL
    ✔ Installed
    Binary          /usr/bin/psql
    Version         psql (PostgreSQL) 16.3
    ✔ Port 5432 reachable on 127.0.0.1
    ✔ Authentication OK (user: root)

── Verdict ──
  ✅ Ready , MySQL and PostgreSQL available.

  Example:  dump2json ./backup.sql --verbose

Graceful error handling

dump2json provides actionable suggestions when things go wrong:

• DB server not installed → shows install commands for your platform, or use --install
• Connection refused → checks if the service is running, suggests systemctl start
• Authentication failed → suggests checking credentials
• Syntax error during import → suggests verifying the dump type or forcing with --type
• Missing CLI client → suggests installing mysql-client or postgresql-client
• Ctrl+C → SIGINT handler drops temp DB and stops self-started servers before exiting

Development

git clone https://gitlab.com/frvnkenstein/dump2json.git
cd dump2json
npm install

# Run in development mode (no build needed)
npm run dev -- ../backup.sql --verbose

# Run tests
npm test

# Watch mode
npm run test:watch

# Build
npm run build

# Doctor
npm run dev -- doctor

License

MIT , see LICENSE.