# Jam SQL Studio - Complete Documentation > A modern, AI-native SQL IDE for macOS, Windows, and Linux. Free for personal use. Supports SQL Server (incl. Azure SQL), PostgreSQL, MySQL/MariaDB, Oracle, SQLite, and Azure Data Explorer (Kusto). This file is hand-maintained to summarize the Jam SQL Studio documentation at https://jamsql.com/docs/ — it is not auto-generated, so it can lag the live docs. For the current, authoritative version of anything below, follow the linked docs page or visit https://jamsql.com/docs/ --- ## Getting Started Install Jam SQL Studio on macOS, Windows, or Linux. Create your first database connection using SQL Server authentication, Windows authentication, or Microsoft Entra ID. No account required for the free Personal tier. Supported databases: Microsoft SQL Server (including Azure SQL), PostgreSQL (including Amazon RDS), MySQL 5.7+/8.x, MariaDB 10.x/11.x, Oracle Database 11g/12c/19c/21c/23c, SQLite, and Azure Data Explorer (Kusto) clusters / App Insights / Log Analytics for read-only KQL analytics. Download: https://jamsql.com/#download --- ## Query Editor Write and execute SQL queries with IntelliSense autocomplete. Features include: - Execute all queries (Cmd+E / Ctrl+E) or selected text only - IntelliSense for tables, columns, functions, keywords, and snippets - Multiple result sets with tab navigation - Inline result editing (UPDATE/INSERT/DELETE) - Export results to CSV, JSON, or XLSX - Transaction management: Auto, Manual, and Smart Commit modes - Query history with tab restoration - File-backed tabs that sync to .sql files --- ## SQL Notebooks Jupyter-compatible (.ipynb) notebooks that combine executable SQL and JavaScript cells with Markdown: - Code (SQL), JavaScript, and Markdown cell types, run individually or top-to-bottom - Cells share a pinned database connection, including temp tables and session variables - Save/open standard .ipynb files - AI-assisted editing and MCP tool control (add/run/convert/move cells) --- ## Table Explorer Browse and filter table data with an intuitive grid interface: - Column filtering and sorting - Inline cell editing with validation - Foreign key navigation with preview popovers - Row details panel with JSON preview - Data export in multiple formats - Pagination controls --- ## Import Data Import CSV, TSV, Excel (.xlsx), JSON, or NDJSON files into a new or existing table on SQL Server, PostgreSQL, MySQL, Oracle, or SQLite (Azure Data Explorer is read-only and not an import target): - Four-step wizard in a workspace tab: Source, Target, Columns, Import - Create a new table with column types inferred from the file, or append to an existing table (optionally emptying it first) - Column mapping: include/exclude file columns, edit mapped types, set fixed values for unmapped target columns, filter which rows import - Error policy per import: stop at the first error and roll back, or skip invalid rows into a bounded error report (row, column, value, reason) - Generate script instead of executing — get the table DDL plus INSERT statements as a reviewable SQL script - Post-import suggestions for loose foreign key, JSON, and enum declarations inferred from the data (stored in MetaInfo, never in the database schema) --- ## JSON Columns Work with JSON/JSONB columns as first-class data across PostgreSQL, MySQL, SQL Server, Oracle, and SQLite: - Declare a column as JSON to get a JSONPath filter chip with sub-operators - Peek popover shows the shape of the JSON, including nested/array paths - Structure is cached per column so autocomplete suggests real observed paths - Loose foreign keys can target a JSON path, not just a column --- ## Enum Columns Declare any column as an enum to get a dropdown filter, sourced from whichever the engine actually has: - Native enums (MySQL ENUM, PostgreSQL pg_enum) - CHECK constraints - Sampled distinct values when neither exists - Capped at 200 distinct values, with a truncated-list indicator --- ## Loose Foreign Keys Virtual (a.k.a. logical/unenforced) foreign keys — declare a column-to-column reference without touching the schema: - Works across PostgreSQL (incl. PG18 NOT ENFORCED), SQL Server, MySQL, Oracle, and SQLite - Polymorphic dispatch (one column referencing different target tables by discriminator) - Target filters, and a display ("label") column for the referenced row - Exportable/importable as JSON so teams can share definitions --- ## Table Designer Create and modify tables visually: - Add/remove/reorder columns - Set data types, nullability, defaults - Define primary keys and foreign keys - Create indexes and constraints - Generate CREATE TABLE scripts - Engine-aware syntax for MSSQL, PostgreSQL, MySQL --- ## Schema Compare Compare database schemas across connections: - Side-by-side DDL diff view - Select individual changes to include - Generate ALTER/CREATE/DROP sync scripts - Compare tables, views, procedures, functions - Filter by object type or change type --- ## Data Compare Compare table data between databases: - See added, modified, and deleted rows - Generate INSERT/UPDATE/DELETE sync scripts - Compare by primary key or custom key columns - Filter by change type - Export comparison results --- ## Cross-Engine Database Migration Compare and migrate tables and data across different engine types (e.g. SQL Server to PostgreSQL), not just within the same engine: - Canonical type mapping so schema/data compare works across SQL Server, PostgreSQL, MySQL, Oracle, and SQLite - Flags what's a clean automatic port vs. what needs a manual follow-up (procedural code, engine-specific types) - Companion guides for finishing a PostgreSQL schema after migration and T-SQL vs. PL/pgSQL differences --- ## Execution Plans Visualize and analyze query execution plans: - Tree view and graph view modes - Operator cost breakdown - Plan comparison side-by-side - Import/export plans (.sqlplan format) - Search and highlight expensive operations - Node detail panel with properties --- ## Schema Overview Interactive graph visualization of your entire database schema: - Tables, views, procedures, and functions as connected nodes - Foreign key relationships rendered as arrows - Dependency arrows between objects - Zoom, pan, and search - Filter by schema or object type --- ## Dependency Viewer Visualize object dependencies: - Tree view and graph view - See what tables, views, and procedures reference each other - Upstream and downstream dependencies - Useful before making schema changes --- ## Data Profiling Profile any column or table: - Row counts, distinct-value counts, quartiles, histograms, and top values - Sample mode (fast, client-side) or whole-table server-side mode - Works from Table Explorer or Query Editor result columns --- ## Spatial / PostGIS View and filter geometry/geography columns on a map across PostgreSQL/PostGIS, MySQL, SQL Server, Oracle, and SQLite: - Peek-popover maps and a dedicated Query Editor Map view - Table Explorer map pane with bounding-box, distance, and draw-an-area spatial filters - Standalone Spatial Explorer workspace - Curved-geometry rendering (PostgreSQL, Oracle, SQLite, SQL Server; not applicable to MySQL); basemap on by default --- ## Database Blueprint Keep a database's DDL as a folder of .sql files, versioned with Git: - Two-way sync: refresh the folder from the live database, or apply folder edits back to the database (reuses Schema Compare) - Three-way merge and a Git panel for reviewing folder changes - Works with SQL Server, PostgreSQL, MySQL, Oracle, or SQLite --- ## Azure Data Explorer (Kusto / KQL) Connect to Azure Data Explorer (ADX) clusters, Application Insights, and Log Analytics workspaces: - Run KQL with schema-aware IntelliSense in the Query Editor and SQL Notebooks - Browse tables read-only in Table Explorer, including enum, JSON-path, and loose-foreign-key filter chips - Chart results directly from a `| render` query - Import saved Kusto connections from Azure Data Studio --- ## AI Integrations & MCP Jam SQL Studio supports AI coding assistants in two ways: ### AI Workspace (Inside the App) - Query tabs sync to .sql files - Auto-generated CLAUDE.md context file - Schema export to .schema/ directory - Results history to .history/ directory - Knowledge pack in .knowledge/ directory - Integrated terminal with connection context ### MCP Server (Outside the App) - Localhost-only (127.0.0.1), bearer-token authentication, pairs via `jam-sql pair` - Three permission levels: **block** (default, nothing runs) → **read-only** (SELECT/EXPLAIN only) → **confirm** (read-only queries run; every write or DDL statement executes only after you approve it in an in-app dialog, per statement) - Dozens of MCP tools spanning saved-connection management, schema introspection, SQL execution, query/table/notebook UI control, schema/data compare, Database Blueprint sync, transaction control, and (Oracle) PL/SQL debugging — see `/.well-known/mcp/server-card.json` for the current list - Resource snapshots for AI context: workspace, connections, tabs, editor, results, and explorer-selection state, plus the app manifest/capabilities/user-guide knowledge pack - Full audit logging Works with Claude Desktop, Claude Code, OpenCode, Codex CLI, and other MCP-capable agents (client-agnostic over the MCP protocol; ChatGPT's connector model is not currently supported). ### Representative MCP Tools - connections_list: List saved connections (no secrets) - query_execute: Execute SQL under the connection's permission policy — read-only, or writes/DDL after per-statement approval - ui_open_tab / ui_focus_tab / ui_close_tab: Open, switch, and close query/table/notebook tabs - ui_set_editor_text: Write SQL to a query tab's editor - ui_lock_tab / ui_unlock_tab: Prevent edit conflicts while the AI is working - table_set_filter / table_set_sort / table_set_pagination / table_get_snapshot: Drive the Table Explorer grid - notebook_run_cell / notebook_run_all: Execute notebook cells - schema_compare_start / data_compare_start: Kick off a schema or data comparison ### Representative MCP Resources - workspace_snapshot: Active workspace state - connections_snapshot: Connection status - tabs_snapshot: Open tabs and editors - editor_snapshot: Active editor content - results_snapshot: Query results preview - selection_snapshot: Selected explorer item - app_manifest: App version and features - app_capabilities: Supported workflows - app_user_guide: How-to documentation --- ## Connections Manage database connections: - SQL Server authentication, Windows authentication, Microsoft Entra ID (device code flow, MFA-friendly) - PostgreSQL (password, md5, scram-sha-256), MySQL/MariaDB, SQLite (file-based) - Oracle Database (service name or SID, SYSDBA/SYSOPER roles, wallet support) - Azure Data Explorer (Kusto) — Service Principal, Azure CLI, or interactive Entra sign-in - Browse Azure — pick ADX clusters, App Insights, Log Analytics, Azure SQL, and PostgreSQL flexible servers straight from a signed-in Azure account - Import saved connections from Azure Data Studio, VS Code, SSMS, DBeaver, DataGrip, TablePlus, MySQL Workbench, Navicat, pgAdmin, and standard config files - Detect local Postgres/MySQL/SQL Server/Oracle databases running in Docker and add them in one click - Paste a connection string (ADO.NET, URL, JDBC, EZConnect, libpq, SQLite path) to prefill the form - Persistent token caching - Connection groups and favorites --- ## Backup & Restore Database backup and restore operations: - Export/import .bacpac files - Create .bak backups for MSSQL - PostgreSQL support via pg_dump and pg_restore - Progress tracking --- ## Charts Create charts from query results: - Bar, line, pie, area, and scatter charts - Configure axes and series - Export as SVG or PNG - Auto-detect chart-friendly result sets --- ## Sessions Session persistence and workspace management: - Auto-save workspace state - Resume with all tabs, connections, and queries - Multiple workspace support --- ## Snippets SQL snippet templates: - Built-in templates for common SQL patterns - Custom snippets per database engine - Trigger snippets via IntelliSense - Parameterized placeholders --- ## Clone Clone databases and tables: - Clone entire databases with one click - Clone individual tables - Copy schema, data, and security objects - Same server or cross-server cloning --- ## Security Security manager: - View and manage database users and roles - Permission management - Login management for SQL Server --- ## Scripting Script generation for database objects: - Generate CREATE, DROP, SELECT, INSERT, UPDATE, DELETE scripts - Tables, views, procedures, and databases - Engine-aware syntax (MSSQL, PostgreSQL, MySQL) - Script to clipboard or new editor tab --- ## PL/SQL Debugger Debug Oracle PL/SQL packages, procedures, and functions cross-platform: - Breakpoints, step over/into/out - Watch expressions and local variable inspection - Call stack navigation - Works on macOS, Windows, and Linux (no Oracle client install required) --- ## Package Explorer Browse every Oracle PL/SQL package across all schemas: - Filter by name or schema - Open package spec/body source in the editor - Track invalid packages --- ## Tablespace Manager Oracle tablespace administration: - View tablespaces, datafiles, usage, and autoextend settings - Create, alter, and drop tablespaces - Resize datafiles --- ## Filegroup Manager SQL Server filegroup administration: - View filegroups and files - Create, alter, and drop filegroups - Track file usage and growth --- ## Agent Jobs Manage SQL Server Agent jobs from macOS, Windows, or Linux: - List, enable/disable, start/stop jobs - View job history, schedules, and step details - No SSMS or Windows host required --- ## Session Browser View and manage active database sessions: - List active sessions with CPU, memory, and blocking info - Inspect the currently executing SQL - Kill sessions (with confirmation) --- ## Performance Dashboard Real-time database performance metrics: - Live CPU, memory, I/O, and wait-stat charts - Top queries by cost - Connection and session overview --- ## Keyboard Shortcuts Common shortcuts (macOS / Windows): - Execute query: Cmd+E / Ctrl+E - New query tab: Cmd+N / Ctrl+N - Save: Cmd+S / Ctrl+S - Close tab: Cmd+W / Ctrl+W - Toggle sidebar: Cmd+B / Ctrl+B - Command palette: Cmd+Shift+P / Ctrl+Shift+P - Find/Replace: Cmd+F / Ctrl+F - Comment line: Cmd+/ / Ctrl+/ --- ## Pricing - **Personal (Free)**: Unlimited connections, IntelliSense, query execution, table explorer, charting, execution plans, and schema/data compare (up to 200 objects / 10,000 rows per table). No account required. - **Pro Monthly ($9.99/mo)**: Everything in Personal, plus AI Workspace sync, multiple named sessions, no size caps on Schema Compare/Data Compare/export/clone/backup, priority support, and a commercial-use license. - **Pro Yearly ($99/yr)**: Same as Pro Monthly, save 17%. - **Enterprise**: Custom pricing, volume licensing, SSO/SAML (coming soon), dedicated account management. All plans include a 14-day free trial of Pro features. --- ## Links - Website: https://jamsql.com/ - Documentation: https://jamsql.com/docs/ - Download: https://jamsql.com/#download - Support: support@jamsql.com