SQLXP

sqlxp is a CLI tool for exporting SQL query results to multiple file formats.

Features

• Multiple databases: PostgreSQL, MySQL, SQLite
• Multiple formats: CSV, TSV, JSON (row/column/array), JSON Lines, XML, HTML, Markdown
• Pretty-print JSON: Human-readable JSON output with indentation
• Memory-efficient: Streaming exports for large datasets
• Connection profiles: Save and reuse database connections
• Flexible query input: Command-line, file, or stdin
• Verbose mode: Track export progress and statistics
• Cross-platform: Works on Linux, macOS, and Windows

Installation

Make sure you have Go installed, then:

git clone https://github.com/skabillium/sqlxp.git
cd sqlxp
go mod tidy
go build -o sqlxp ./cmd

Quick Start

Export a query result to CSV:

sqlxp query -q 'SELECT * FROM users' -o users.csv ./database.db

Use a connection profile:

sqlxp query -p prod -q 'SELECT * FROM orders' -o orders.csv

Export with verbose output:

sqlxp query -p prod -q 'SELECT * FROM logs' -o logs.jsonl -V

Usage

Running Queries

sqlxp accepts read-only statements such as SELECT, WITH, SHOW, DESCRIBE, DESC, and EXPLAIN. Use --allow-write or set allow_write: true in the config file to permit non-read statements.

Basic query with connection string:

sqlxp query -q "SELECT * FROM users" -o users.csv ./mydb.db

Using individual connection parameters:

sqlxp query -D postgres -h localhost -P 5432 -u admin -w secret -d myapp \
  -q "SELECT * FROM users" -o users.csv

Query from a file:

sqlxp query -p prod -f query.sql -o results.csv

Query from stdin (pipe):

echo "SELECT * FROM users" | sqlxp query -p prod -o users.csv
# or
cat query.sql | sqlxp query -p prod -o results.csv

Query with heredoc:

sqlxp query -p prod -o results.csv << 'EOF'
SELECT id, name, email
FROM users
WHERE active = true
LIMIT 100
EOF

Print to stdout (default when --out is omitted):

sqlxp query -q "SELECT * FROM users" ./mydb.db

Output Formats

CSV (default):

sqlxp query -q "SELECT * FROM users" -o users.csv ./mydb.db

TSV (tab-separated):

sqlxp query -q "SELECT * FROM users" -o users.tsv ./mydb.db
# or
sqlxp query -q "SELECT * FROM users" --format tsv ./mydb.db

JSON Lines (recommended for large datasets):

sqlxp query -q "SELECT * FROM users" -o users.jsonl ./mydb.db

JSON (with orientation):

# Row orientation (array of objects) - default
sqlxp query -q "SELECT * FROM users" -o users.json --orientation row ./mydb.db

# Column orientation
sqlxp query -q "SELECT * FROM users" -o users.json --orientation column ./mydb.db

# Array orientation (array of arrays)
sqlxp query -q "SELECT * FROM users" -o users.json --orientation array ./mydb.db

# Pretty-printed JSON (human-readable with indentation)
sqlxp query -q "SELECT * FROM users" --format json --pretty ./mydb.db

HTML (with CSS styling):

# Basic HTML table
sqlxp query -q "SELECT * FROM users" -o users.html ./mydb.db

# With CSS classes
sqlxp query -q "SELECT * FROM users" -o users.html --html-table-class "data-table" --html-header-class "header" ./mydb.db

# With inline styles
sqlxp query -q "SELECT * FROM users" --format html --html-cell-style "padding: 8px; border: 1px solid #ccc;" ./mydb.db

Markdown (for documentation):

# Basic markdown table
sqlxp query -q "SELECT * FROM users" -o users.md ./mydb.db

# Custom NULL value
sqlxp query -q "SELECT * FROM users" --format markdown --markdown-null-value "N/A" ./mydb.db

Connection Profiles

List profiles

sqlxp profiles list

Show profile details

sqlxp profiles show prod

Add a new profile

Using flags:

sqlxp profiles add staging \
  --driver postgres \
  --host staging.example.com \
  --port 5432 \
  --user admin \
  --password secret \
  --database myapp

Using a connection string:

sqlxp profiles add staging \
  --connection "postgres://admin:secret@staging.example.com:5432/myapp"

Interactive mode (prompts for each field):

sqlxp profiles add staging

Remove a profile

sqlxp profiles remove staging
# or skip confirmation
sqlxp profiles remove staging --force

Configuration

Show config file path

sqlxp config path

Initialize config file

# Create in default location (~/.config/sqlxp/config.yaml)
sqlxp config init

# Create in specific location
sqlxp config init --path ./sqlxp.yaml

# Overwrite existing config
sqlxp config init --force

Config file format

allow_write: false

profiles:
  prod:
    driver: postgres
    host: db.example.com
    port: 5432
    user: admin
    password: secret
    database: myapp

  local:
    driver: sqlite3
    connection: ./dev.db

  staging:
    driver: mysql
    host: staging.example.com
    port: 3306
    user: admin
    password: ${STAGING_DB_PASSWORD}
    database: myapp

Config file locations

The tool searches for config files in this order:

1. --config <path> flag
2. ./sqlxp.yaml (current directory)
3. $SQLXP_CONFIG environment variable
4. ~/.config/sqlxp/config.yaml (XDG standard)
5. OS-specific config directory

Verbose Mode

Get detailed information about the export:

sqlxp query -p prod -q "SELECT * FROM users" -o users.csv -V

Output:

Config: /Users/you/.config/sqlxp/config.yaml
Database: postgres
Query: SELECT * FROM users
Output: users.csv (csv)
Exported 1,234 rows in 0.45s

Flags Reference

Connection Flags

Flag	Short	Description
--config		Path to config file
--profile	-p	Connection profile to use
--driver	-D	Database driver (postgres/mysql/sqlite3)
--host		Database host (default: localhost)
--port	-P	Database port
--user	-u	Database user
--password	-w	Database password
--database	-d	Database name
--connection	-c	Database connection string

Query Flags

Flag	Short	Description
--query	-q	Query to execute (takes precedence)
--file	-f	File to read query from (.sql)
--allow-write		Allow non-read queries to run
(stdin)		Read query from stdin (pipe or heredoc)

Query Priority: --query flag > --file flag > stdin

Output Flags

Flag	Short	Description
--out	-o	Output file
--format		Output format (csv/tsv/json/jsonl/xml/html/markdown)
--orientation		JSON orientation (row/column/array)
--delimiter		CSV delimiter (default: ,)
--clrf		Use \r\n as line terminator
--print		Print to stdout (default when --out is empty)
--pretty		Pretty-print JSON/XML output with indentation
--xml-root		Root element name for XML output (default: rows)
--xml-row		Row element name for XML output (default: row)
--xml-namespace		XML namespace URI for root element
--xml-omit-declaration		Omit XML declaration header
--html-table-class		CSS class for HTML table
--html-table-style		Inline CSS style for HTML table
--html-header-class		CSS class for HTML table header
--html-header-style		Inline CSS style for HTML table header
--html-row-class		CSS class for HTML table rows
--html-row-style		Inline CSS style for HTML table rows
--html-cell-class		CSS class for HTML table cells
--html-cell-style		Inline CSS style for HTML table cells
--html-null-value		Value to display for NULL cells (default: NULL)
--markdown-null-value		Value to display for NULL cells in markdown (default: NULL)

Other Flags

Flag	Short	Description
--ping		Validate connection without running query
--verbose	-V	Print export details
--progress		Show export progress (row count)
--timeout		Query timeout (e.g., 30, 30s, 5m, 2h, 1d)
--help	-h	Show help
--version	-v	Print version number

Commands

Query

sqlxp query [flags]

Run a SQL query and export results.

sqlxp (no subcommand) prints the help output.

Version Command

sqlxp --version
# or
sqlxp -v

Config Commands

sqlxp config path                    # Show config file path
sqlxp config init [--path <file>]    # Initialize config file

Profile Commands

sqlxp profiles list                  # List all profiles
sqlxp profiles show <name>           # Show profile details
sqlxp profiles add <name> [flags]    # Add new profile
sqlxp profiles remove <name>         # Remove profile

Examples

Export PostgreSQL table to CSV

sqlxp query -p prod -q "SELECT * FROM users WHERE active = true" -o active_users.csv

Export MySQL query to JSON Lines

sqlxp query -D mysql -h localhost -P 3306 -u root -w pass -d mydb \
  -q "SELECT * FROM orders WHERE date > '2024-01-01'" -o orders.jsonl

Export SQLite with verbose output

sqlxp query -q "SELECT * FROM logs" -o logs.csv ./app.db -V

Stream large dataset to stdout

sqlxp query -p prod -q "SELECT * FROM large_table" --format jsonl | \
  jq -r '.user_id' | sort | uniq

Create and use a profile

# Create profile
sqlxp profiles add mydb --driver postgres --host localhost \
  --port 5432 --user admin --password secret --database myapp

# Use profile
sqlxp query -p mydb -q "SELECT * FROM users" -o users.csv

Pipe queries from scripts

# Generate and execute a dynamic query
cat << 'SQL' | sqlxp query -p analytics -o report.csv
SELECT 
  date,
  COUNT(*) as orders,
  SUM(amount) as revenue
FROM orders
WHERE date >= '2024-01-01'
GROUP BY date
ORDER BY date
SQL

Pretty-print JSON for debugging

# Check query results in human-readable format
echo "SELECT * FROM users LIMIT 5" | \
  sqlxp query -p prod --format json --pretty

Query with timeout

Set query timeout using smart format:

# Plain seconds (backward compatible)
sqlxp query -q "SELECT * FROM users" --timeout 30 ./database.db

# With time units
sqlxp query -q "SELECT * FROM users" --timeout 30s ./database.db
sqlxp query -q "SELECT * FROM users" --timeout 5m ./database.db
sqlxp query -q "SELECT * FROM users" --timeout 2h ./database.db
sqlxp query -q "SELECT * FROM users" --timeout 1d ./database.db

Query with timeout

Set query timeout using smart format:

# Plain seconds (backward compatible)
sqlxp query -q "SELECT * FROM users" --timeout 30

# With units
sqlxp query -q "SELECT * FROM users" --timeout 30s
sqlxp query -q "SELECT * FROM users" --timeout 5m
sqlxp query -q "SELECT * FROM users" --timeout 2h

Process query results with jq

# Extract specific fields from JSON output
cat query.sql | sqlxp query -p prod --format jsonl | \
  jq -r '.email' | sort | uniq

Performance Tips

Tip

For large datasets (>100K rows), use streaming formats:

• CSV: Fast, compact, streaming
• JSON Lines: Streaming, easy to process line-by-line
• TSV: Like CSV but tab-separated
• HTML: Streaming, good for reports

Avoid for large datasets:

• JSON Column orientation: Buffers all data in memory

Building from Source

git clone https://github.com/skabillium/sqlxp.git
cd sqlxp
go mod tidy
go build -o sqlxp ./cmd

License

MIT License - see LICENSE file for details