core

@khanakia/sql-schema-core

Framework-agnostic SQL database schema parser, ER-diagram layout engine, and URL share codec. Zero React. Zero backend. Pure TypeScript that runs in Node, browsers, edge runtimes and web workers — the engine behind SQL Schema Visualizer.

Parse PostgreSQL / MySQL / SQLite / ANSI CREATE TABLE DDL into a typed schema model, lay it out as a graph, and compress a whole schema into a shareable URL token — all client-side.

---

Why

Most SQL parsers are single-dialect, strict, and throw on the first vendor clause they don't recognise (ENGINE=InnoDB, AUTOINCREMENT, backticks). @khanakia/sql-schema-core is deliberately tolerant: it skips what it can't understand and still produces a useful diagram, surfacing problems as warnings[] instead of exceptions. Perfect for visualizers, docs generators, migration tools, lint rules, and LLM pipelines.

Install

npm i @khanakia/sql-schema-core
# or: pnpm add @khanakia/sql-schema-core

ESM-only, ships its own .d.ts. Only runtime dependency: @dagrejs/dagre.

Quick start

import { parseSchema, layoutGraph, encodeSql, decodeSql } from '@khanakia/sql-schema-core'

const sql = `
  CREATE TABLE customers ( id SERIAL PRIMARY KEY, email VARCHAR(255) NOT NULL UNIQUE );
  CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id)
  );
`

const schema = parseSchema(sql)
schema.tables           // [{ name:'customers', columns:[...] }, { name:'orders', ... }]
schema.foreignKeys      // [{ fromTable:'orders', fromColumn:'customer_id', toTable:'customers', toColumn:'id' }]
schema.warnings         // string[] — never throws

// Lay it out (framework-agnostic: ids in, positions out)
const positions = layoutGraph(
  schema.tables.map(t => ({ id: t.name, columns: t.columns.length })),
  schema.foreignKeys.map(fk => ({ source: fk.fromTable, target: fk.toTable })),
  { direction: 'LR' },
)
positions.get('orders') // { x, y }

// Shareable, compressed URL token (deflate-raw + base64url, ~2.9x on SQL)
const token = await encodeSql(sql)        // URL-fragment safe
const back  = await decodeSql(token)      // original SQL (or null)

How it works

flowchart LR
  A["SQL DDL text"] --> B["stripComments<br/>quote-aware"]
  B --> C["splitStatements<br/>paren/quote depth"]
  C --> D{"statement?"}
  D -->|CREATE TABLE| E["columns + PK/FK/UNIQUE/DEFAULT"]
  D -->|ALTER ADD FK| F["foreign keys"]
  E --> G["associateComments<br/>re-attach comments"]
  F --> G
  G --> H["Schema: tables · foreignKeys · warnings"]
  H --> I["layoutGraph · dagre"]
  I --> J["Map id → x,y"]
  A -. encodeSql .-> K["deflate-raw + base64url token"]
  K -. decodeSql .-> A

Loading

API

parseSchema(sql: string): Schema

Tolerant multi-dialect parser. Handles PostgreSQL, MySQL, SQLite and generic ANSI: backticks, "quotes", [brackets], schema-qualified names, AUTO_INCREMENT/AUTOINCREMENT, UNSIGNED, composite keys, inline + table-level FOREIGN KEY, ALTER TABLE … ADD FOREIGN KEY, and -- / # / /* */ comments. Never throws.

interface Schema {
  tables: Table[]
  foreignKeys: ForeignKey[]
  warnings: string[]
  groupAnnotations: Record<string, string[]>   // name -> table names (from `-- @group:`)
}
interface Table  { name: string; columns: Column[]; comment?: string }
interface Column {
  name: string; type: string; nullable: boolean
  pk: boolean; unique: boolean
  fk?: { table: string; column: string }
  default?: string; comment?: string
}
interface ForeignKey { fromTable: string; fromColumn: string; toTable: string; toColumn: string }

/* @doc */ rich descriptions

Multi-paragraph markdown bodies, attributed by paren depth: a /* @doc … */ block OUTSIDE the parens (above CREATE TABLE) attaches to the table; INSIDE the parens (above a column line) attaches to that column. Same-line /* @doc … */ works too. Multiple blocks for the same target concatenate with a blank line between. Bodies are dedented (longest common leading whitespace stripped) so authors can indent inside parens naturally.

/* @doc
# users

The **authoritative** account table. Soft-deletes go to `users_deleted`.
*/
CREATE TABLE users (
  id    SERIAL PRIMARY KEY,
  /* @doc
  ## email
  Lower-cased at app layer. The canonical login key.
  */
  email VARCHAR(255) NOT NULL UNIQUE
);

Bodies land on Table.description / Column.description. Plain block comments without the @doc marker remain regular comments (no description). The parser also handles same-line single /* @doc body */.

-- @group: annotations

Place a comment immediately above (or on the same line as) a CREATE TABLE to declare group membership. A table can belong to multiple groups — comma-separate the names. Multiple -- @group: lines stacked above one CREATE accumulate.

-- @group: auth, audit
CREATE TABLE users ( id SERIAL PRIMARY KEY );

CREATE TABLE invoices ( id SERIAL PRIMARY KEY );   -- @group: billing

The map preserves the order each table was first added per group. Group names are trimmed, deduped, and capped at 60 chars (empties dropped). UI consumers (e.g. @khanakia/sql-schema-react) surface these as read-only groups alongside any user-managed ones.

layoutGraph(nodes, edges, options?): Map<string, Point>

Directed-graph layout via dagre. Pure: takes { id, columns }[] + { source, target }[], returns top-left { x, y } per id. Cycles and self-references are handled (dagre breaks them for ranking, all edges preserved).

layoutGraph(nodes, edges, {
  direction?: 'LR' | 'TB',                       // default 'LR'
  collapsed?: Record<string, true>,              // ids rendered header-only
  commentsInline?: boolean,                      // taller height estimate
  sizes?: Map<string, { width; height }>,        // real measured sizes win
})

encodeSql(sql) / decodeSql(token)

Promise-based, native CompressionStream('deflate-raw') + base64url — zero deps, no WASM, ~2.9× on SQL. Token is URL-fragment safe. decodeSql returns null for blank/garbage input.

encodeGroups({groups, activeGroup}) / decodeGroups(token)

Optional sibling codec for table-group share data, lives at &g= next to #s= in the URL fragment. Two-param design (additive) — viewers without group support keep decoding #s= as before. Encoder returns null for empty-default payloads so the &g= param can be omitted. Decoder is tolerant: filters non-string members, dedups, drops dangling activeGroup.

samples: Sample[]

Ready-made commented example schemas: e-commerce, blog, SaaS, relationship-notation demo, mini-ERP (groups demo), social network, project management (MySQL), library catalogue (SQLite), banking ledger. Several use -- @group: annotations.

Supported SQL

Dialect	Notes
PostgreSQL	SERIAL, TIMESTAMPTZ, schema prefixes, array types
MySQL	backticks, AUTO_INCREMENT, ENGINE=, UNSIGNED
SQLite	AUTOINCREMENT, minimal types
ANSI / generic	best-effort; unknown clauses skipped, not fatal
Annotations	-- @group: name (one-liner tags) and /* @doc … */ (multi-line markdown bodies) — see the API section. Both attach to tables or columns by position.

Limitations

Not a full SQL grammar. Generated columns, partition clauses, deeply nested parens and CTE-in-DDL parse loosely. Comment continuation lines can anchor one row off. These are accepted trade-offs for a tolerant visual tool — see CONTEXT.md.

License

MIT © khanakia · Part of sql-schema-visualizer.