Reverse-engineer an existing database into an interactive ER diagram — then generate the SQL back out.
Point it at a .accdb file or a SQL Server instance and get a complete, editable diagram:
tables, data types, primary keys, foreign keys, indexes and field descriptions.
Rearrange it by dragging. Then export CREATE TABLE, a migration script, or thousands of
rows of realistic fake data — for SQL Server, PostgreSQL, MySQL, SQLite, Oracle or Access.
Runs entirely on your machine. No account, no cloud, no telemetry. Interface in English and Portuguese, switchable with one click.
Actual SVG export from the tool — crow's-foot notation, dashed lines for nullable FKs, zero edge crossings.
git clone https://github.com/jaderkayque/database-diagram-studio.git
cd database-diagram-studio
python -m pip install -r requirements.txt
python server.pyYour browser opens at http://127.0.0.1:8777. On Windows you can just double-click run.bat.
Nothing to import yet? Click Load example to load a sample schema and try the whole pipeline in ten seconds.
The interface opens in the language of your browser and the globe button in the toolbar toggles between English and Portuguese at any time.
Microsoft Access (.accdb / .mdb) — drag the file onto the drop zone, or give it a path
and a password. It reads every table with the original column order, data types, lengths,
precision/scale, nullability, defaults, primary keys, unique constraints, indexes,
relationships with their referential rules, and the description of every field.
SQL Server — fill in server and database, or just paste a connection string; the app parses the common formats and fills the rest in for you:
Server=tcp:myserver,1433;Database=Sales;User Id=sa;Password=***;
sqlserver://user:pass@host:1433/MyDatabase
MYPC\SQLEXPRESS
host,1433/MyDatabase
Windows or SQL authentication, Test connection and List databases buttons, and an
optional schema filter. It reads tables, columns (including IDENTITY and computed columns),
defaults, primary keys in key order, UNIQUE constraints, indexes, per-column CHECK
constraints, composite foreign keys with ON DELETE/ON UPDATE, and descriptions stored in
the MS_Description extended property.
Credentials are used in memory to open the connection and are never written to disk.
| Action | How |
|---|---|
| Move a table | drag the header (snaps to grid; hold Alt for free movement) |
| Create a relationship | drag the blue dot next to a field onto a field in another table |
| Pan / zoom | drag the background / mouse wheel |
| Select many | Shift + drag on the background |
| Rename | double-click the header, or F2 |
| Reorder fields | drag them in the right-hand panel |
| Resize a table | drag its right edge |
| Pick a layout | Arrange button in the toolbar (or Ctrl+Shift+L) |
| Switch language | globe button in the toolbar (EN ⇄ PT) |
| Delete | Del on whatever is selected |
The right panel edits everything the diagram carries: type, length/precision, nullability,
primary key, unique, auto-increment, default expression, CHECK rule and description — plus
constraint names, composite key pairs and referential actions on relationships.
Undo/redo, autosave, .dbdiag.json project files, and SVG/PNG export are all built in.
Three tabs, six dialects — SQL Server, PostgreSQL, MySQL, SQLite, Oracle, Access:
CREATE TABLE— full script in dependency order: schemas, tables, primary keys, unique constraints, indexes, foreign keys, and field descriptions in each engine's native form (sp_addextendedproperty,COMMENT ON,COMMENT=).- Migration (
ALTER) — diffs the imported database against the current diagram and writes the upgrade: new tables, added and altered columns, primary-key changes, dropped and created foreign keys, updated descriptions. DestructiveDROPs are opt-in. - Fake data —
INSERTs that respect your constraints and your foreign keys.
Types and defaults are translated across dialects: IDENTITY(1,1) becomes SERIAL,
AUTO_INCREMENT or GENERATED BY DEFAULT AS IDENTITY; GETDATE() becomes
CURRENT_TIMESTAMP; NEWID() becomes gen_random_uuid().
Most tools can't reverse-engineer .accdb files, and there's a good reason. The Microsoft
Access ODBC driver does not implement the ODBC metadata functions you need:
>>> cursor.primaryKeys(table="Employee")
Error: ('IM001', '[Microsoft][ODBC Driver Manager]
Driver does not support this function (SQLPrimaryKeys)')
>>> cursor.foreignKeys(table="Department")
Error: ('IM001', '[Microsoft][ODBC Driver Manager]
Driver does not support this function (SQLForeignKeys)')The usual fallback — reading the MSysRelationships system table — is blocked by permissions
on a default file, and the ODBC driver rejects the GRANT statement that would unblock it.
So this project reads Access through two layers at once:
| Layer | Provides |
|---|---|
ODBC (pyodbc) |
table list, original column order, types, sizes |
ADOX over ADO (pywin32 + ACE OLEDB) |
primary keys, foreign keys with delete/update rules, unique constraints, indexes, auto-increment flags, field descriptions |
ADOX returns columns alphabetically, so column order comes from ODBC and everything else from
ADOX, merged by name. There's a third wrinkle: the Access ODBC driver returns the field
remarks buffer with garbage after the NUL terminator, which crashes pyodbc's UTF-16
decoding outright — so the reader retries the connection forcing cp1252 and truncates at the
NUL. When ADOX isn't available it degrades to index heuristics and MSysRelationships.
None of this is documented in one place. If you've ever tried to diagram an Access database
programmatically, this is the file you want: app/introspect/access.py.
A diagram is only useful if you can read it, so the layout engine doesn't just place boxes — it scores the result. After every run it replays the exact orthogonal route the canvas will draw and counts three things: how many relationship lines cross, how many pass over a table, and how many run glued to each other.
Eight layouts ship: Layered, Star (most-connected table in the center, others in rings), Fill the screen, Organic (force-directed), Circle, Tree, Alphabetical grid, and Automatic — which runs the candidates, scores each and applies the winner.
Real output from a 21-table ERP-shaped model:
| Layout | Crossings | Lines over tables | Score |
|---|---|---|---|
| Layered | 0 | 0 | 0.0 |
| Alphabetical grid | 10 | 1 | 14.1 |
| Star | 0 | 6 | 15.0 |
| Organic | 0 | 6 | 15.0 |
| Fill the screen | 3 | 6 | 18.4 |
| Tree | 1 | 7 | 18.9 |
| Circle | 3 | 14 | 38.0 |
That scoring found a real bug. Plain longest-path layering pushed lookup tables into column 0 even when their only neighbour sat six columns away, so every one of those edges crossed the whole diagram. Adding a pass that pulls each table to the latest column before its dependents took that model from 11 crossings and 28 lines over tables down to 0 and 0.
Compare all layouts shows the whole table in the app and applies any row with one click.
The generator infers content from the column type, the column name, and the table's
context — name in a products table is not the same thing as name in a customers table.
INSERT INTO [dbo].[Cliente] ([Nome], [CPF], [Email], [Telefone], [Cidade], [UF], [Ativo]) VALUES
('Paulo Costa Martins', '355.680.869-23', 'caio.fernandes450@example.com', '(71) 97201-1754', 'Salvador', 'BA', 1),
('Aurora Serviços Ltda', '116.830.395-80', 'queila.rodrigues60@mail.com', '(43) 91763-7682', 'Porto Alegre', 'RS', 1);- Valid Brazilian CPF and CNPJ, with correct check digits — not random digits
- Emails, phone numbers, postal codes, street addresses, people and company names
- City and state agree within the same row
- Dates in plausible ranges per column role (birth date, created at, updated at)
- Money, quantities and percentages inside the bounds of their type
NOT NULL, maximum length,UNIQUEandIDENTITYall respected
Tables are inserted in topological order and every foreign key only ever gets a value that exists in the parent table. FK cycles are detected and reported. A seed value makes the whole output reproducible: same seed, same data.
The vocabulary follows the interface language: English names, cities and tax IDs in English mode, Brazilian ones (with valid CPF/CNPJ) in Portuguese mode. The comments in every generated script follow it too. Adding another locale is a single dictionary in
app/generate/seed.py.
| Editor, layouts, SQL generation | Python 3.9+ — standard library only, works anywhere |
| Importing databases | Windows + pyodbc and the matching ODBC driver |
| SQL Server import | ODBC Driver 17 or 18 for SQL Server |
| Access import | Microsoft Access Database Engine — same architecture as your Python (64-bit Python needs the 64-bit engine). Already present if Microsoft Office is installed. |
| Full Access metadata | pywin32, for the ADOX layer described above |
The top bar tells you what was detected on your machine.
Stated plainly, so you know what you're getting:
- Two interface languages ship today: English and Portuguese (pt-BR). Adding a third is
one dictionary in
web/js/i18n.js— no other file needs to change. - Database import is Windows-only. It goes through ODBC and, for Access, COM. The editor and all three SQL generators are pure Python and run anywhere.
- Access needs the ACE engine installed. ODBC drivers are registered machine-wide in the Windows registry; they cannot be bundled with an application.
- Only SQL Server and Access can be imported today. PostgreSQL and MySQL importers are
planned and the introspection interface is already isolated in
app/introspect/. - Views, stored procedures, triggers and partitioning are not modelled.
- SQL Server introspection has been exercised against the catalog views but not against a wide range of production servers. Bug reports very welcome.
server.py local HTTP server (stdlib) + API routes
app/
model.py schema model, normalisation, dependency sort
typemap.py normalised types and per-dialect mapping
sqltext.py comment strings for the generated scripts
introspect/
access.py .accdb / .mdb reader (ODBC + ADOX)
sqlserver.py SQL Server reader (sys.* + extended properties)
generate/
ddl.py CREATE TABLE, keys, indexes, descriptions
diff.py migration script (ALTER)
seed.py fake data generator
web/
index.html UI
js/canvas.js rendering, pan/zoom, dragging, relationship creation
js/layouts.js layout engine and crossing measurement
js/i18n.js interface dictionaries (en / pt-BR)
js/inspector.js property panel
js/exporter.js SVG/PNG export
No build step, no bundler, no node_modules. Edit a file, refresh the page.
- More interface languages (Spanish, French — one dictionary each)
- PostgreSQL and MySQL importers
- More locales for the fake-data generator
- Portable single-file
.exe(PyInstaller) with a pure-Python TDS client, so SQL Server import works with nothing installed - Static browser-only build for hosting the editor and generators on the web
- Import from a
.sqlscript
Issues and pull requests are welcome. The most valuable contributions right now are new database importers, extra interface languages and locales for the fake-data generator — each one is self-contained in a single file.
To run from source: clone, pip install -r requirements.txt, python server.py. That's it.
MIT.