Round-Trip Database Engineering: Reverse Engineering Schemas into Editable Diagrams

Reverse Engineer Any Database into dbdiagram.io, PlantUML, Mermaid, or QuickDBD - Then Keep Designing

SchemaCrawler is a tool that connects to any database via JDBC drivers to generate editable schema outputs. Unlike standard documentation tools, it supports a round-trip workflow from live database to DDL generation.

Why This Matters

Most reverse-engineering tools produce static HTML reports or images that serve as ‘the end of the line,’ forcing engineers to manually redraw schemas when designing new features. This gap between production reality and design models leads to manual errors and inefficiency when translating proposed architectural changes back into executable SQL.

Key Insights

• SchemaCrawler supports four primary editable formats: DBML for dbdiagram.io, PlantUML for source control/IDEs, Mermaid for Markdown (GitHub/Notion), and QuickDBD for text-first editing.
• The DBML workflow allows for a complete cycle: Reverse Engineer $\rightarrow$ Edit Design $\rightarrow$ Generate DDL using the @dbml/cli tool.
• JDBC compatibility allows this workflow to be applied across diverse environments including PostgreSQL, MySQL, SQL Server, Oracle, DB2, and SQLite.

Working Examples

Exporting a SQLite database to DBML format using SchemaCrawler via Docker.

docker run \
--mount type=bind,source="$(pwd)",target=/home/schcrwlr/share \
--rm -it \
schemacrawler/schemacrawler \
/opt/schemacrawler/bin/schemacrawler.sh \
--server=sqlite \
--database=share/northwind.db \
--info-level=standard \
--command=script \
--script-language=python \
--script=dbml.py \
--output-file=share/northwind.dbml

Example of extending an exported schema in DBML to add a new table and relationship.

Table PlaylistTag {
PlaylistTagId integer [pk]
PlaylistId integer [not null]
TagName varchar [not null]
}
Ref: PlaylistTag.PlaylistId > Playlist.PlaylistId

Installing the DBML CLI and generating PostgreSQL DDL from a .dbml file.

npm install -g @dbml/cli
dbml2sql northwind.dbml --postgres

Practical Applications

• )), { // Corrected Practical Applications based on context’s logic:’’’’’’’’’’’’’’’’’’’’’’’’” { // Correction: The prompt asks for specific use cases and pitfalls derived from the context. Since the context doesn’t explicitly list failures at specific companies but describes the general engineering problem: }

[ { “use_case”: “Inheriting legacy databases where engineers need to model new features without breaking existing schema.”, “pitfall”: “Using static HTML reports (e.g., SchemaSpy) as the only documentation; results in manual re-entry of data when updating designs.” }, { “use_case”: “Collaborative architecture reviews using Markdown tools like GitHub or Notion.”, “pitfall”: “Manually redrawing diagrams from scratch instead of reverse engineering from production; leads to drift between documentation and actual implementation.” } ]

• references hosts sahaja

References:

• https://dev.to/sualeh/reverse-engineer-any-database-into-dbdiagramio-plantuml-mermaid-or-quickdbd-then-keep-25e7