SQLStudio v0.6.5 Technical Docs & Reference

Developer Docs: PROD-Safe Tuning, 12 Engines & 32 MCP Tools

Complete engineering reference covering Rust 1.100 builds, default EnvMode::Prod slow_diag, 1-click zero-PII local DEV+TEST twins, Zipf 100K seeding, 6-rule AST + ESR+INCLUDE index advisor, HMAC dual-gate promotion_verifications.json, 32 MCP CLI tools & Ed25519 signing.

01QUICKSTART & INSTALLATION

Quickstart, Binaries & Rust 1.100 Source Build

Built natively in Rust 1.100 (Edition 2024) + GPUI Community Edition without Electron overhead. Launch on macOS or Windows and initialize the built-in e-commerce SQLite demo DB in one click.

macOS & Windows Native Installers
  • • macOS: Apple Silicon (arm64) & Intel (x86_64) native Metal GPUI window + Info.plist sqlstudio:// scheme.
  • • Windows 10/11: x64 DirectX/Win32 native binary with Per-Monitor DPI v2 & HKCU protocol registration.
  • • Demo Seed DB: Auto-creates sample tables (users, orders, payments) inside $SQLSTUDIO_HOME for instant orphan/index audit testing.
Build from Source (Rust 1.100)
0212 DATABASE ENGINES MATRIX

7 Native Drivers + 5 Dialect & Probe Engines

PostgreSQL
PostgreSQL
PG · SQL
Native Driver

Read-only pg_stat_statements bottleneck scan, n_dead_tup bloat & cold-stats guard, ESR+INCLUDE advisor, loopback DEV+TEST twins & EXPLAIN (FORMAT JSON).

MySQL
MySQL
MY · SQL
Native Driver

Read-only performance_schema digest diagnostics, Seq Scan detection, ESR composite indexes, 6-rule AST rewrites & A/B benchmarking.

MariaDB
MariaDB
MA · SQL
Native Driver

MariaDB system catalogs, FK coverage & slow-query checks, local sandbox verification & bidirectional DDL.

SQLite
SQLite
SQ · SQL
Native Driver

Zero-prod-disk local DEV/TEST twin target, PRAGMA foreign_key_check, 100K-row Zipf seeding & EXPLAIN QUERY PLAN A/B compare.

DuckDB
DuckDB
DK · SQL
Native Driver

Zero-prod-disk analytical local DEV/TEST twin target, skewed dataset benchmarking & columnar execution plan profiling.

ClickHouse
ClickHouse
CH · SQL
Native Driver

HTTP interface & system catalog introspection, sorting key metadata inspection & read-only analytical execution.

Redis
Redis
RD · NOSQL
Native Driver

Native RESP protocol, db0..dbN switching, bounded SCAN keyspace browsing & FLUSHALL/FLUSHDB/KEYS approval gate.

SQL Server
SQL Server
MS · SQL
Dialect & Probe

URI parsing, T-SQL identifier quoting, dialect schema modeling & TCP/SSH tunnel connectivity probes.

Oracle
Oracle
OR · SQL
Dialect & Probe

Oracle dialect modeling, constraint validation, URI parsing & connectivity probing.

MongoDB
MongoDB
MG · NOSQL
Dialect & Probe

Document URI parsing, NoSQL schema modeling & network/SSH tunnel probes.

Elasticsearch
Elasticsearch
ES · NOSQL
Dialect & Probe

Index endpoint URI parsing, search mapping dialect & connectivity probe.

Cassandra
Cassandra
CS · NOSQL
Dialect & Probe

CQL keyspace modeling, partition key hints & cluster node connectivity probe.

03ENVMODE · LOCAL TWINS · AST · DUAL-GATE

Production-Safe Perf Tuning & HMAC Dual-Gate Workflow

Every new profile defaults to EnvMode::Prod (read_only = true). Optimization happens strictly on linked zero-PII local DEV+TEST twins; PROD DDL/indexes only unlock after both stages verify and pass a 15-minute manual desktop grant.

1. EnvMode::Prod & slow_diag

Non-intrusive read-only inspection across pg_stat_statements (with pg_stat_activity fallback) & MySQL performance_schema: flags Seq Scan tables, dead-tuple bloat (n_dead_tup >= 15% & >= 1,000), stale ANALYZE counters, and Root Blocker lock trees.

2. local_twin & Zipf 100K Seed

clone_prod_to_local_twin strips 100% of PII rows and clones DDL, FKs & indexes to embedded SQLite/DuckDB or strictly loopback PG/MySQL (linked_prod_id). seed_tuning_data populates Uniform, HotspotZipf (80/20), or SkewedStatusTime up to 100,000 rows/table + auto ANALYZE (blocked on PROD).

3. 6 AST Rules, ESR+INCLUDE & A/B Bench

Rewrites non-SARGable dates, arithmetic predicates, NOT IN -> NOT EXISTS, OR chains, deep OFFSET -> keyset cursors, and flags implicit casts/leading wildcards. Generates ESR (Equality->Sort->Range)+INCLUDE covering indexes + rollback DDL and benchmarks Before/After EXPLAIN timings & DiskSpill.

4. HMAC Dual-Stage Ledger (15m Grant)

promotion_verifications.json stores HMAC-SHA256 hashes of normalized SQL across linked DEV & TEST runs. Any PROD DDL/index executes only with Allowed/AuthorizedSingleUse after BOTH DEV ✓ + TEST ✓ pass and a 15-minute desktop manual approval is consumed.

04HONEST SIGNALS ARCHITECTURE

Honest Signals DBA Audit Rules

Every metric is grounded in real catalog or SQL anti-join telemetry. Timeouts, permission blocks, and un-analyzed tables surface explicitly instead of displaying fake zeros.

Real Anti-Join Orphan Checks (2500ms / 3M Guard)

SQLite runs PRAGMA foreign_key_check with InterruptHandle timeout. PostgreSQL executes WHERE NOT EXISTS anti-joins with statement_timeout = 2500ms and skips synchronous scans when reltuples > 3,000,000—setting checked = false (Unchecked), never 0.

Honest Index Usage & Cold-Stats Protection

PostgreSQL inspects pg_stat_user_indexes.idx_scan; cold/un-analyzed tables with idx_scan == 0 report insufficient stats (zero score penalty, no false DROP INDEX). SQLite marks Unused only after UsageLedger samples >= 5.

Duplicate vs. Prefix-Redundant Classification

Duplicate: same table, column order, uniqueness & partial predicate (non-PK). Redundant: non-unique, non-PK, non-partial index matching a strict leading prefix of a wider valid index. FK coverage checks leading columns case-insensitively.

Health Score Caps on Critical Findings

Whenever any Severity::Critical finding exists, total health score is hard-capped at <= 74, relationship subscore at <= 68, and index subscore at <= 88.

05MCP CLI DISTRIBUTION & DIAGNOSTICS

MCP CLI Distribution, MCPB Bundles & Doctor

All MCP CLI subcommands support --json structured output for CI pipelines, repository onboarding, and host troubleshooting.

sqlstudio --mcp
sqlstudio --mcp [--slim-tools] [--read-only] [--connection <name>]

Start MCP server over STDIO for Cursor, Claude Code, Codex, and local agents.

sqlstudio --mcp-http
sqlstudio --mcp-http [--port 15432] [--auth-token <token>]

Start Streamable HTTP server on 127.0.0.1:15432/mcp with Bearer auth (OS Keychain / SQLSTUDIO_MCP_TOKEN).

sqlstudio mcp doctor
sqlstudio mcp doctor [--json] [--project-dir <dir>] [--strict]

Audits binary path, 18 client configs, repo secret leaks, HTTP auth/probe, in-process self-test, DB probes, audit logs & license tier.

sqlstudio mcp pack-mcpb
sqlstudio mcp pack-mcpb --out <path.mcpb> [--bin <exe>] [--connection <name>] [--target <macos|windows>] [--no-binary] [--json]

Pure-Rust MCPB v0.2 bundle builder packaging manifest, icon & optional binary with SHA-256 verification.

sqlstudio mcp install-project
sqlstudio mcp install-project [--dir <repo>] [--client <claude-code|cursor|vscode|codex|gemini|all>] [--connection <name>] [--json]

Install secretless repository-scoped MCP configs using ${SQLSTUDIO_MCP_TOKEN} placeholders.

sqlstudio mcp deeplink
sqlstudio mcp deeplink [--client <cursor|vscode|all>] [--connection <name>] [--json]

Generate 1-click Cursor and VS Code MCP installation deeplink URLs.

06LICENSE CLI, DEEP LINKS & ED25519 SIGNING

License CLI, sqlstudio:// Deep Links & Ed25519 Trust Root

Pure-safe Rust RFC 8032 Ed25519 offline tokens (SQLS1.<payload>.<sig>). The trusted public key is embedded exclusively at compile time via SQLSTUDIO_BUILD_LICENSE_PUBKEY. USD-only Creem orders display your License Key on /checkout/success with 1-Click "Activate in SQLStudio App" (sqlstudio://license/activate?key=...), and Pro Perpetual stays unlocked offline forever whenever BUILD_RELEASE_EPOCH <= updates_until.

sqlstudio mcp doctor includes a read-only license diagnostic check (Pass) showing active tier, MCP surface mode, and trial countdown.
sqlstudio:// Custom URL Scheme & Deep-Link Routes
macOS Info.plist · Windows HKCU · GPUI on_open_urls
sqlstudio://license/activate?key=<CREEM_KEY>ActivateKey

Triggered by 1-Click "Activate in SQLStudio App" on /checkout/success and /activate. Validates the Creem USD key via your locally configured Worker gateway and stores a signed SQLS1. token (also accepts sqlstudio://activate?key=...).

sqlstudio://license/import?token=<SQLS1...>ImportToken

Imports an offline/air-gapped SQLS1.<payload>.<sig> token directly into the desktop workbench and verifies its RFC 8032 Ed25519 signature against the compile-time pubkey—zero network required.

sqlstudio://license/start-trialStartTrial

Immediately initializes the 7-day Pro trial in SQLStudio (protected by an HMAC-SHA256 tamper-proof clock) with no credit card required.

sqlstudio://licenseOpenLicenseSettings

Focuses the running GPUI workbench and opens the License & Activation panel directly (alias: sqlstudio://open/license).

Security Invariants: Untrusted `gateway`, `worker`, and `gateway_url` query parameters are intentionally ignored (activation always uses local config/keychain); strict UTF-8 %XX decoding preserves literal `+` characters in base64/SQLS1 tokens, and whitespace or control characters are rejected.
sqlstudio license status
sqlstudio license status [--json]

Inspect active license tier, status, machine fingerprint, trial countdown & embedded token claims.

sqlstudio license start-trial
sqlstudio license start-trial [--json]

Manually start a 7-day no-card Pro trial secured with HMAC-SHA256 clock-rollback protection.

sqlstudio license activate
sqlstudio license activate --key <CREEM_KEY> [--gateway <url>] [--name <device>] [--json]

Activate a Creem key against the Cloudflare Worker gateway and store a signed SQLS1. Ed25519 token.

sqlstudio license import
sqlstudio license import --token <SQLS1...> [--json]

Import an offline/air-gapped SQLS1.<payload>.<sig> token and verify Ed25519 signature locally.

sqlstudio license open-url <url>
sqlstudio license open-url [--url] <sqlstudio://...> [--headless] [--json]

Parse and execute an sqlstudio:// deep link (activate, import, start-trial, or license settings) from CLI or headless CI—ignoring untrusted gateway params while preserving literal + signs.

sqlstudio license register-protocol
sqlstudio license register-protocol [--dry-run] [--exe <path>] [--json]

Register or inspect OS url-scheme handling for sqlstudio:// (Windows HKCU reg.exe or macOS Info.plist / lsregister) with --dry-run inspection support.

sqlstudio license deactivate
sqlstudio license deactivate [--key <id>] [--instance-id <id>] [--gateway <url>] [--local] [--json]

Release device seat via gateway or clear local token (--local); trial history is never erased.

sqlstudio license keygen
sqlstudio license keygen [--json]

Generate a fresh 32-byte (64-hex) Ed25519 signing seed and compile-time public key pair.

sqlstudio license sign
sqlstudio license sign --seed <64hex> --email <email> --tier <pro|team> [--machine-fp <fp|self>] [--updates-until <epoch|none>] [--expires-at <epoch>] [--max-devices <n>] [--json]

Sign offline enterprise SQLS1. vouchers bound to tier, email, updates_until window & optional machine_fp.

07ENVIRONMENT VARIABLES & 8 LOCALES

Environment Variables & 8-Locale Reference

Powered by an atomic O(1) 8-locale engine. Use these environment variables to pin storage home, language, connection, tab, table, modal, or SQL autorun mode.

zh-CNzh-TWzh-SGja-JPko-KRen-SGen-USen-GB
VariableScopeAllowed / Example ValuesBehavior
SQLSTUDIO_HOMERuntime/tmp/sqlstudio | D:\SQLStudioDataOverrides storage directory for config.toml, demo DB, encrypted vault & MCP audit logs.
SQLSTUDIO_LANGRuntimezh-CN | zh-TW | zh-SG | ja-JP | ko-KR | en-SG | en-US | en-GBPins one of the 8 regional locales across GUI and CLI (--lang <code> also supported).
SQLSTUDIO_CONNECTIONRuntimedemo-sqlite | pg-prodPre-selects a connection ID or name on workbench launch.
SQLSTUDIO_TABRuntimeoverview | relationships | diagram | indexes | sql-lab | tableOpens a specific workbench tab immediately at startup.
SQLSTUDIO_TABLERuntimeorders | paymentsPre-selects a table when launching into table or diagram view.
SQLSTUDIO_MODALRuntimeddlAutomatically opens a target modal (such as ddl) on startup.
SQLSTUDIO_QUERYRuntime"SELECT * FROM orders LIMIT 10;"Pre-populates the SQL Lab editor buffer on startup.
SQLSTUDIO_AUTORUNRuntimerun | explain | analyzeAutomatically triggers run, explain, or analyze in SQL Lab on startup.
SQLSTUDIO_MCP_TOKENRuntimesk-mcp-...Bearer token fallback for Streamable HTTP MCP clients and `sqlstudio --mcp-http`.
SQLSTUDIO_BUILD_LICENSE_PUBKEYCompile-Time<64-char-hex>Compile-time only (`option_env!`) 64-hex Ed25519 public key trust root; runtime env vars are never read.