SQL to ER Diagram — Full Analysis

2026-06-15 · source · source ↗ · raw capture · wiki page

Live hosted version: https://sqltoerdiagram.com/

Overview

SQL to ER Diagram is a client-side web application that parses SQL CREATE TABLE / ALTER TABLE statements and renders interactive entity-relationship diagrams in an HTML canvas. It runs entirely in the browser — no backend, no accounts, no uploads. Users paste SQL, get an interactive diagram, drag tables around, edit names/types inline, add annotations (sticky notes, group boxes), and export to PNG/SVG. Projects can be saved/loaded as JSON files or shared via URL hash encoding.

The codebase is 3,176 lines of vanilla JavaScript across 12 source files, with one dependency (@dagrejs/dagre for graph layout) and Vite as the build tool.

Architecture

The app follows a single-page MVC-ish pattern with clear module boundaries:

Key Techniques

1. Source-Span-Preserving Parser (Bidirectional Editing)

The parser doesn't just extract a model — it records the exact character offsets of every identifier in the original SQL. When the user renames a column on the canvas, edit.js does a surgical text splice: only the bytes for that specific identifier change. Comments, formatting, and unsupported clauses are preserved verbatim.

// From parser.js: column definition records name and type spans
const col = {
  name: colName,
  type: prettyType(type),
  typeRaw: type,
  pk: /\bprimary\s+key\b/.test(rest),
  nn: /\bnot\s+null\b/.test(rest),
  nameSpan: [ns, ne],        // absolute offsets for the bare identifier
  typeSpan,                   // absolute offsets for the type
};

Non-overlapping splices are applied right-to-left so earlier offsets stay valid:

// From edit.js: applySplices
function applySplices(sql, splices) {
  const sorted = splices.slice().sort((a, b) => b.start - a.start);
  let out = sql;
  for (const s of sorted) {
    out = out.slice(0, s.start) + s.text + out.slice(s.end);
  }
  return out;
}

2. Comment Blanking for Offset Stability

Comments are replaced with equal-length whitespace (newlines preserved) rather than stripped. This keeps all character offsets in the "blanked" SQL identical to the original:

// From parser.js: blankComments
// Replace '--' comments and '/* */' blocks with spaces, newlines preserved
if (c === '-' && c2 === '-') {
  while (i < n && sql[i] !== '\n') { out += ' '; i++; }
}

3. Canvas Bitmap Caching

Each table is rendered once to an offscreen canvas, then blitted via drawImage(). The per-frame cost for hundreds of tables is just drawImage calls + edge drawing — no per-glyph text layout at render time:

// From diagram.js: _bitmap
_bitmap(t) {
  let bm = this.bitmaps.get(t.key);
  if (!bm) { bm = rasterizeTable(t, this.theme, this.dpr); this.bitmaps.set(t.key, bm); }
  return bm;
}

4. Hub-Aware Dagre Layout Tuning

Edges are weighted by the degree of their endpoint tables (capped at 12), so "hub" tables (high fan-in/fan-out) pull related tables into adjacent ranks rather than scattering them across the layout:

// From layout.js
const hubness = Math.max(degree.get(from) || 0, degree.get(to) || 0);
const weight = 1 + Math.min(hubness, 12);
g.setEdge(from, to, { weight, minlen: 1 }, 'e' + e++);

5. Dialect-Aware Type Autocomplete

When editing a column type inline, a <datalist> element provides type suggestions based on the selected SQL dialect. Each dialect also has a default type for new columns (e.g., text for PostgreSQL, varchar(255) for MySQL).

6. Share Without Servers

Projects are JSON-serialized, optionally deflate-compressed via the CompressionStream API, base64url-encoded, and stored in the URL hash (#s=…). Browsers never send the hash to the server, so sharing requires no backend. Falls back to raw base64 when CompressionStream is unavailable.

7. Viewport Culling

The render loop computes the visible world-coordinate viewport and skips tables, edges, and annotations that lie entirely outside it (with a 50px margin). This keeps the render loop fast even with many tables zoomed in on a small area.

Design Decisions

Optimize for: Smooth interaction on large schemas → Trade off: Bitmap quality at extreme zoom

Tables are rasterized once at the device pixel ratio (capped at 2x). This means bitmaps look crisp at most zoom levels but may appear soft at extreme zoom. The trade-off is deliberate: re-rasterizing on every zoom change would make interaction janky.

Optimize for: Zero-backend simplicity → Trade off: No collaboration, no persistence beyond localStorage

Everything runs in the browser. Share links encode the entire project in the URL hash. This means no accounts, no servers, no data retention concerns — but also no multi-user collaboration, no cloud sync, and URL length limits for very large schemas.

Optimize for: Vanilla JS → Trade off: Manual state management

No React/Vue/Svelte. The app uses raw DOM manipulation with localStorage for persistence and Map-based caches for derived data. This keeps the bundle tiny and avoids framework churn, but means state synchronization between editor and canvas is manual (and carefully orchestrated).

Optimize for: Tolerant parsing → Trade off: May accept malformed SQL

The parser is explicitly "tolerant of MySQL, Postgres and SQL-Server-ish dialects." It handles backticks, double-quotes, and bracket delimiters. This maximizes usability across SQL flavors but means it may silently accept some malformed DDL that a strict parser would reject.

Optimize for: Canvas rendering → Trade off: No semantic DOM accessibility

Tables are rendered as pixels on a canvas, not as DOM elements. This enables smooth zooming/panning with bitmap caching but sacrifices screen-reader accessibility and text selection within the diagram area.

Comparison Notes