erdscope

Generate a self-contained, interactive ER diagram — and an Excel table-definition workbook — from a live MySQL, PostgreSQL, or SQLite database, with a single-file, zero-dependency Python CLI.

pip install erdscope
erdscope demo   # no database of your own? try it right now — opens a sample diagram

erdscope mysql://readonly@127.0.0.1:3306/myapp_production -o erd.html
erdscope postgres://readonly@127.0.0.1:5432/myapp_production -o erd.html
erdscope sqlite:///path/to/app.db -o erd.html

Three input sources — any one of them is enough (a database is no longer required):

• Database (MySQL / PostgreSQL / SQLite) — the source of truth for tables, columns, comments, indexes, and real foreign keys.
• Application code (--models: Rails / Prisma / Django) — adds association semantics the database cannot express (has_many :through, polymorphic, ...), and can stand on its own when there is no DB to point at.
• Config file (tables:) — declare or patch a schema by hand: add tables, columns, indexes, and associations, or override and delete what the DB or code got wrong.

They merge in that order — database → code → config, each layer refining the previous (the database wins on physical facts like column types; code and config win on associations and logical names; config always has the final say). With no database URL, nothing is connected and no password is prompted.

Demo

Try the live demo → — a small e-commerce schema with comments, indexes, real FKs and an inferred relation. Everything below is one self-contained HTML file.

Regenerate it anytime with python3 docs/gen_demo.py.

Read the user manual → — installation, CLI/config reference, a full viewer guide, and troubleshooting. (日本語版 →)

Install & usage

pip install erdscope (or pipx install erdscope) gives you the erdscope command. Prefer not to install anything? erd.py is a single, dependency-free file — grab it and run it with any Python 3.9+:

curl -O https://raw.githubusercontent.com/orapli/erdscope/main/erd.py
python3 erd.py ...   # identical to the erdscope command below

No database of your own to point it at yet? erdscope demo builds a small sample e-commerce SQLite database in a temp directory, generates the diagram, and opens it in your browser — nothing to download or set up:

erdscope demo

It's a normal run under the hood, so every other flag still applies (erdscope demo --excel defs.xlsx, erdscope demo --only 'order*', ...); add --no-open to skip launching the browser.

erdscope mysql://readonly@127.0.0.1:3306/myapp_production -o erd.html

# PostgreSQL: same thing (schema defaults to public; override with ?schema=name)
erdscope postgres://readonly@127.0.0.1:5432/myapp_production -o erd.html

# SQLite: just point at the file (no server, nothing to install — uses stdlib sqlite3)
erdscope sqlite:///path/to/app.db -o erd.html

# enrich with association semantics parsed from application code (optional)
erdscope mysql://readonly@127.0.0.1:3306/myapp_production \
        --models /path/to/rails/app -o erd.html

# also write a table-definition workbook
erdscope mysql://readonly@127.0.0.1:3306/myapp_production \
        --excel table_definitions.xlsx -o erd.html

# no database — generate straight from application code
erdscope --models /path/to/rails/app -o erd.html

# no database — generate from a hand-written config schema (see Config file below)
erdscope --config schema.yml -o erd.html

# multiple code sources merge in order, later wins (--models is repeatable)
erdscope --models /path/to/rails/app --models /path/to/schema.prisma -o erd.html

Cloned the repository instead of installing from PyPI? A ready-to-run SQLite sample (the same schema erdscope demo uses) also ships committed in examples/ — python3 erd.py sqlite:///examples/demo_shop.db -o shop.html. See examples/README.md.

Behind a bastion? Open an SSH tunnel first and point at localhost:

ssh -N -L 3307:db-host:3306 bastion &
erdscope mysql://readonly@127.0.0.1:3307/myapp_production -o erd.html

Use a read-only account, and leave the password out of the URL — if it's not in MYSQL_PWD either, you'll be prompted for it (hidden input, never touches argv or shell history). See Dependencies below for how the DB connection itself is made.

Options

Config file

Once the flag list above gets long, put it in a config file instead — .erdscope.json (or .yml/.yaml with PyYAML) next to where you run the tool is picked up automatically. Most keys mirror a CLI option (an explicit flag always wins over the config); the DB connection is config-only as engine/host/port/user/database — deliberately with no password field — and relations manually declares relations no FK, code, or inference can find. engine can also be "sqlite", in which case database is a local file path (not a database name) and host/port/user don't apply. See the Config file chapter of the manual for the full key list and semantics, and erdscope.example.yml for a fully annotated sample based on the live demo's schema.

Config as a schema source

Beyond settings, the config can carry a tables: section that is itself a full input source — enough to generate a diagram with no database and no code at all, or to patch what the other sources produce. It merges as the highest-priority layer, so it can add new tables/columns/indexes/associations, override attributes, or delete them:

title: billing
tables:
  customers:
    comment: Customer accounts
    primary_key: id
    columns:
      - { name: id,    type: bigint,  primary: true }
      - { name: email, type: varchar, nullable: false, comment: Login address }
    associations:
      - { type: has_many, name: invoices, target: invoices }
  invoices:
    columns:
      - { name: id,          type: bigint, primary: true }
      - { name: customer_id, type: bigint }
    associations:
      - { type: belongs_to, name: customer, target: customers, foreign_key: customer_id }

Patch an existing DB/code result instead of declaring from scratch — override a column, delete a stale one, drop a whole table, or replace a table's column list wholesale:

tables:
  orders:
    columns:
      - { name: status,      comment: Order status }   # override just this attribute
      - { name: legacy_flag, drop: true }              # delete a column
  temp_scratch:
    drop: true                                         # delete a whole table
  reports:
    columns_mode: replace                              # discard lower-layer columns first
    columns:
      - { name: id,   type: bigint, primary: true }
      - { name: body, type: text }

Config is validated in two passes: syntactically at load (unknown keys, bad types, malformed operations) and semantically at run time (a drop/reference must point at something that actually exists once all sources are merged) — typos never pass silently. The full tables: schema, the drop/replace operations, and the precedence rules are documented in the manual.

Dependencies

erd.py runs with zero required dependencies — everything below is optional, and the tool degrades gracefully (falls back, or fails with a clear message) when a piece is missing. If you installed via pip, extras pull them in for you: pip install 'erdscope[mysql]' (PyMySQL), 'erdscope[postgres]' (psycopg), 'erdscope[yaml]' (PyYAML), or 'erdscope[all]'.

Excel output (--excel) needs none of these — it's written directly via the stdlib zipfile/XML, not a spreadsheet library.

Test-only, and only if you run that particular suite:

What you get

Feature highlights — each link goes to the relevant manual chapter:

• Database truth — tables, columns (full SQL types, defaults, extras), table and column comments, indexes, and real FK constraints, read from the database catalog (information_schema on MySQL, pg_catalog on PostgreSQL)
• Code semantics on top — --models merges Rails / Prisma / Django associations; declared, DB-FK, and inferred edges stay visually distinct
• Interactive exploration — focus with depth and dependency direction, two-level hiding, table and column search (with regex/case toggles), a non-filtering Highlight search that survives into exports, named views, share links
• Readable layouts — viewport-aware packing with crossing reduction, drag-to-snap with guide lines, multi-select align/distribute, Auto-tidy, layout undo/redo
• Exports — PNG (2x), SVG, Mermaid, and PlantUML, each with its own copy and download buttons and image options, plus the Excel workbook (customizable via --excel-template)
• Logical names — a table's DB comment doubles as a searchable logical name (e.g. users（Customer accounts）), with independent display modes for the live view and exports
• Extras — a built-in ? shortcuts/help popup, dark mode, print stylesheet, resizable/collapsible panes

Tests

python3 -m unittest discover -s tests -v

The IR builders and the Excel writer are covered by pure unit tests; the overlay parsers have minimal fixtures under tests/fixture_*. No database is required to run the tests.

tests/test_e2e.py drives the generated HTML's client-side JS (grid layout, multi-select align/distribute, drag-to-snap, Auto-tidy) in a real headless browser. It's optional and skips itself if not set up:

pip install playwright && playwright install chromium
python3 -m unittest tests.test_e2e -v

Performance

Measured against synthetic SQLite schemas (benchmarks/gen_schema.py, ~2 FK edges per table) at three sizes:

Notes: measured in a sandboxed Linux environment against headless Chromium (Playwright), not dedicated hardware — treat these as relative/order-of-magnitude numbers rather than absolute guarantees for your machine. "CLI gen" and the browser timings are each the median of 3 runs; Python RSS/JS heap are single best-effort samples. Generation itself (erd.py sqlite:///... -o out.html) stays fast at every size tested — SQLite parsing isn't the bottleneck.

Recommendation: the constraint at scale is the browser-side interactive re-layout, not generation. Linear interpolation between the 300- and 1,000-table measurements above puts the 1-second crossing at roughly 970 tables; we round that down to ~900 tables as a deliberately conservative rule of thumb, since these are single-environment numbers (initial paint itself stays comfortably under 3 seconds through 1,000 tables). Past that rough threshold, narrow the diagram with --only/--exclude instead of rendering the whole schema at once — see the manual's Large schemas section for details.

Reproduce or re-run at other sizes with:

python3 benchmarks/gen_schema.py --tables 300 --out /tmp/bench_300.db
python3 benchmarks/run_bench.py --tables 100,300,1000   # generates, benchmarks, prints JSON + a summary table

run_bench.py is a manual script (not part of the unittest suite) — see its docstring for the full methodology and the Playwright venv it expects.

Extending

Everything downstream (UI, layouts, exports) consumes the intermediate representation documented at the top of erd.py. Both input layers are pluggable: a database adapter turns a URL scheme into that shape, and a framework overlay turns an application-code project into it. Each is a small class registered under the scheme / project kind it handles, so adding one never touches the dispatch code.

Custom adapters and overlays (a plugin file)

Write a plain Python file that subclasses the base and registers itself, then load it with --adapter path/to/plugin.py (or config adapters: [...]). No need to rebuild erd.py — the plugin registers into the running process, and a plugin works the same against the single-file erd.py or a pip install erdscope.

# my_sqlite.py — a custom database adapter
from erd import DBAdapter, register_adapter, mysql_ir

@register_adapter
class SqliteAdapter(DBAdapter):
    schemes = ('sqlite',)     # the URL scheme(s) it answers to
    name = 'sqlite'           # provider id recorded in the output
    label = 'SQLite'          # pretty name for the progress line

    def fetch(self, url):
        # ...read the schema for `url` and return the IR (the `tables` dict).
        # mysql_ir() builds it from information_schema-shaped rows for you.
        return mysql_ir(table_rows, col_rows, fk_rows, index_rows)

python3 erd.py sqlite:///app.db --adapter my_sqlite.py -o erd.html

A framework overlay is the same idea against a --models path:

# my_framework.py
from erd import FrameworkOverlay, register_overlay, make_provider_result

@register_overlay
class SequelizeOverlay(FrameworkOverlay):
    name = 'sequelize'
    priority = 5              # lower detect()s first; first match wins

    def detect(self, root):
        return root.is_dir() and (root / 'models' / 'index.js').exists()

    def build(self, root, table_map):
        tables = ...          # parse `root` into the IR
        return make_provider_result('framework', 'sequelize', tables, location=str(root))

The built-in db/mysql.py, db/postgres.py and frameworks/{rails,prisma,django}.py are the working examples of both patterns.

Building erd.py

The shipped erd.py is a build artifact: the development source lives under src/erdscope/. The Python is organised into concern-named fragments (ir.py, merge.py, providers.py, exporters.py, config.py, cli.py, …) plus two self-assembling folders — db/ (the DB adapters) and frameworks/ (the overlays) — whose files are all included automatically (base.py first, then sorted), so adding a built-in adapter or overlay is just dropping a new file in the folder. They are concatenated in order, and the ~3,600-line embedded viewer (HTML/CSS/JS) lives in viewer.html, out of the Python. Edit the relevant file, then regenerate the single file:

python3 tools/build_single_file.py          # rewrites erd.py from the source
python3 tools/build_single_file.py --check   # CI-style check that erd.py is in sync

The fragments are an amalgamation of one flat module (SQLite-style) — no cross-module imports — and the viewer is inlined into a one-line sentinel, so the whole build is pure textual assembly and erd.py stays a self-contained, zero-dependency single file: grabbing and running it, or pip install erdscope, is unchanged. CI runs the --check above, so a hand-edit of erd.py or a forgotten rebuild fails the build.

License

MIT