SQL ER - Generate ER diagram from sql script

An AI-Generated VS Code extension that reads the currently-open .sql file and renders an interactive ER (entity-relationship) diagram of its schema — tables as cards, foreign keys as lines with crow's-foot cardinality markers.

Features

• One-click ER diagrams. Open a .sql file and run SQL ER: Show ER Diagram (or click the 🔗 icon in the editor title bar). The diagram opens in a side panel.
• Crow's-foot cardinality. 1:N relationships render with a three-toed claw on the "many" end and a triangle arrowhead on the "one" end; 1:1 relationships get a triangle on both ends.
• Column badges. Primary keys (PK), foreign keys (FK), and indexed columns (IDX) are tagged directly in the table card; NOT NULL columns are visually distinguished from nullable ones.
• Dialect auto-detection. The Auto setting (default) detects PostgreSQL / MySQL / SQLite / MariaDB / T-SQL / BigQuery / Snowflake from SQL fingerprints — no manual dialect choice needed for most schemas. The resolved dialect is shown in the toolbar and can be overridden per-file.
• Save & restore layouts. Drag tables where you want them, pick a layout, set the dialect — then click Save in the toolbar to persist the view to a <filename>.sql.json sidecar next to the .sql. Next time you open the diagram, your arrangement comes back; new/renamed tables fall back to auto-layout.
• ALTER TABLE foreign keys. Both inline REFERENCES on column definitions, table-level FOREIGN KEY constraints, and post-hoc ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY are recognized as relationships.
• Multiple layouts. Switch between breadthfirst, CoSE (force-directed), grid, concentric, and circle layouts from the toolbar dropdown.
• Interactive exploration. Click a table to highlight it and its immediate relationships, fading the rest; click the background to clear. Scroll to zoom, drag to pan.
• Live updates. Re-renders automatically whenever you save the file or change dialect.
• Theme-aware. Follows your VS Code light/dark theme out of the box.

Usage

1. Open a .sql file containing CREATE TABLE (and optional ALTER TABLE) statements.
2. Run SQL ER: Show ER Diagram from the Command Palette, or click the ER icon in the editor title bar.
3. The ER diagram renders in a side panel.
   • Click a table to highlight it and its relationships.
   • Use the toolbar dropdowns to switch layout or dialect.
   • Click Save to persist positions, layout choice, dialect, zoom, and pan to <filename>.sql.json.
   • Scroll to zoom, drag empty space to pan, drag tables to reposition them.

Sample schemas to try:

• examples/schema.sql — PostgreSQL schema with multi-table, multi-level foreign keys.
• examples/mysql_schema.sql — MySQL schema including ALTER TABLE ... ADD CONSTRAINT FKs.

Commands

Configuration

The layout sidecar file

Clicking Save writes <filename>.sql.json next to the .sql file, for example schema.sql → schema.sql.json:

{
    "version": 1,
    "dialect": "MySQL", // dialect used to parse this file
    "layout": "breadthfirst", // selected layout
    "zoom": 1.12, // viewport zoom level
    "pan": { "x": 42, "y": 87 },
    "positions": {
        // per-table positions, keyed by table name
        "users": { "x": 120, "y": 80 },
        "orders": { "x": 400, "y": 90 },
        "order_items": { "x": 680, "y": 240 }
    }
}

On open:

• Tables present in both the sidecar and the current SQL are snapped to their saved positions.
• Tables new to the SQL (no saved position) are laid out by the selected algorithm.
• Tables in the sidecar that no longer exist in the SQL are ignored.

The extension never writes .gitignore entries for these files — commit them or ignore them as you like.

Cardinality notation

Edges end in either a triangle (meaning "one") or a three-toed crow's foot (meaning "many"):

Cardinality is inferred purely from the schema: if every foreign-key column on the child table is part of a PRIMARY KEY or UNIQUE constraint, the relationship is 1:1; otherwise it is 1:N.

Development

yarn install      # install dependencies (Volta pins Node 22.23.2 + yarn 1.22.22)
yarn build        # copy webview assets into media/ + bundle src/extension.ts → dist/extension.js
yarn watch        # rebuild on change (esbuild --watch)
yarn typecheck    # tsc --noEmit — the type-check gate
yarn format       # Prettier — format all files
yarn format:check # Prettier — check without writing
yarn package      # production minified bundle (runs on vscode:prepublish)
yarn vsce package # build an installable .vsix

Press F5 in VS Code to launch an Extension Development Host with the extension loaded, then open examples/schema.sql and run SQL ER: Show ER Diagram.

Architecture

The extension has two halves that communicate over postMessage:

1. Extension host (Node, bundled by esbuild → dist/extension.js) — owns the lifecycle, reads the document, parses SQL, and persists layout.
   • src/sqlParser.ts — node-sql-parser wrapper, dialect enum, auto-detection.
   • src/erModel.ts — AST → ER model adapter. This is the only file that knows node-sql-parser's AST shape, so swapping parsers later only touches this file. Handles CREATE TABLE, inline REFERENCES, table-level FOREIGN KEY, and ALTER TABLE ... ADD CONSTRAINT.
   • src/cytoscapeElements.ts — ER model → Cytoscape graph (nodes per table, edges per FK), with cardinality inference and arrow-shape selection.
   • src/erLayoutStore.ts — read/write the .sql.json sidecar via vscode.workspace.fs (remote-safe); returns null on missing/corrupt file instead of throwing.
   • src/extension.ts — activation, commands, singleton ERDiagramPanel, webview lifecycle, message bridge, theme, dialect switching, save command.
2. Webview (a VS Code WebviewPanel) — pure client-side rendering with Cytoscape.js. Receives a fully-built Cytoscape graph JSON and only draws it.
   • src/webviewHtml.ts — generates the webview HTML with a per-panel nonce CSP and asWebviewUri script/style tags.
   • media/webview.js — Cytoscape init, theme palette, HTML card templates, tap-to-highlight, two-phase layout restore (layout all → snap saved positions → restore viewport), and the custom crow's-foot arrow shape (injected via Cytoscape renderer prototype patching before cytoscape() parses its stylesheet, so the custom shape name passes enum validation).
   • media/webview.css — VS Code CSS variable–driven styling for the toolbar and table cards.
   • media/cytoscape.min.js / media/cytoscape-node-html-label.min.js — third-party bundles copied from node_modules by yarn copy-assets (gitignored; regenerated by yarn build).

The SQL parser's AST is deliberately isolated in src/erModel.ts; everything downstream (graph building, rendering) operates on the simple ERModel interface.

Requirements

• VS Code ^1.134.0
• A .sql file (obviously)