Visualizing SQLAlchemy Databases as Mermaid and Dot Diagrams using Paracelsus

SQLAlchemy is a powerful tool that lets Python developers define their database schemas using simple classes. Trying to figure out the entire schema of a database from code can be difficult though, which is why I made Paracelsus to turn those SQLAlchemy defined databases into diagrams that can be used to visualize the schema.

Paracelsus can create visualizations using either Mermaid or DOT format. Mermaid is one one of my favorite tools. Using Mermaid developers can create diagrams using pure text and embed those diagrams in their documentation. Mermaid has support on most VCS systems, including Github and Gitlab, so diagrams in your documentation will automatically render on those sites. You can also use the Mermaid CLI to create images directly.

DOT, on the other hand, was originally created for the GraphViz program. It is also a text based format, although it's not nearly as easy to read or manually edit than Mermaid. DOT files can be used by a variety of diagramming tools though, and has it's own tool (the dot cli) that can be used to generate images.

Example Database

SQLAlchemy turns Python classes into a declarative language for databases. This is a fancy way of saying that you define your SQL Tables as Python Classes, with the individual class attributes representing either metadata about the table (such as the table name) or individual columns in the table.

Tables that are in the same database should be defined by classes that all descend from the same Base class. This Base class comes from SQLAlchemy itself, and when you use it to create child classes that define tables it registers those tables in an internal database.

We're going to start off with our own example database. We'll place it in example_app/models.py, although it's also possible to split your models into multiple files.

from datetime import datetime
from uuid import uuid4

from sqlalchemy import Boolean, DateTime, ForeignKey, String, Text, Uuid
from sqlalchemy.orm import declarative_base, mapped_column


Base = declarative_base()

class User(Base):
    __tablename__ = "users"

    id = mapped_column(Uuid, primary_key=True, default=uuid4())
    display_name = mapped_column(String(100))
    created = mapped_column(DateTime, nullable=False, default=datetime.utcnow())

class Post(Base):
    __tablename__ = "posts"

    id = mapped_column(Uuid, primary_key=True, default=uuid4())
    author = mapped_column(ForeignKey(User.id), nullable=False)
    created = mapped_column(DateTime, nullable=False, default=datetime.utcnow())
    live = mapped_column(Boolean, default=False)
    content = mapped_column(Text, default="")

class Comment(Base):
    __tablename__ = "comments"

    id = mapped_column(Uuid, primary_key=True, default=uuid4())
    post = mapped_column(Uuid, ForeignKey(Post.id), default=uuid4())
    author = mapped_column(ForeignKey(User.id), nullable=False)
    created = mapped_column(DateTime, nullable=False, default=datetime.utcnow())
    live = mapped_column(Boolean, default=False)
    content = mapped_column(Text, default="")

Above we define an example database with three tables: users, posts, and comments. These tables are interconnected: the posts table has a column authors that links back to users, and the comments table points to both posts and users. With three tables this is a pretty simple database. For comparison the default WordPress database has 12 tables (with more possible with plugins), while the software that runs Wikipedia has 57 tables.

Generating Diagrams with Paracelsus

Before you can use Paracelsus you have to install it. Since it needs read your python code it's best to install it in the same environment that you're planning on using it in. Since Paracelsus is on PyPI it can be installed easily with pip.

pip install paracelsus

Now you can use the CLI to create diagrams. The paracelsus graph subcommand takes a single argument, the python path to the base class.

paracelsus graph example_app.models:Base > diagram.mmd

With that command, Paracelsus will populate the diagram.mdd file with the text describing the diagram. The nice thing about Mermaid diagrams is that they're meant to be human editable and readable, and the Entity Relationship Diagrams are very well documented (as are the other diagram types: seriously if you're not including Mermaid diagrams in your documentation already you should really look at it!).

erDiagram
 users {
  CHAR(32) id PK
  DATETIME created
  VARCHAR(100) display_name "nullable"
 }

 posts {
  CHAR(32) id PK
  CHAR(32) author FK
  TEXT content "nullable"
  DATETIME created
  BOOLEAN live "nullable"
 }

 comments {
  CHAR(32) id PK
  CHAR(32) author FK
  CHAR(32) post FK "nullable"
  TEXT content "nullable"
  DATETIME created
  BOOLEAN live "nullable"
 }

 users ||--o{ posts : author
 posts ||--o{ comments : post
 users ||--o{ comments : author

Once you have that text you can put it anywhere that renders Mermaid. For instance, I'm using the WP-Mermaid plugin on this blog to add Mermaid rendering. Using that I'm able to generate the following diagram. One of the nice things about directly rendering the diagram like this is that viewers can highlight and copy text fields (go ahead and try it).

Once you have your Mermaid code you can also use the Mermaid CLI to turn the diagram into SVG, PNG, or PDF files. The nice thing about this tool is that you can reconfigure it and even supply your own CSS files to customize how it looks. There are a lot of options available in the CLI that make it possible to tweak the look and feel of the diagrams to match where you're using them. I've even used Mermaid to generate the diagrams for my book, Terraform in Depth.

npx -p @mermaid-js/mermaid-cli mmdc -w 2000 -b transparent --cssFile png.css --configFile config.json -e png -i paracelsus.mmd -o paracelsus.png

Using the --format flag you can also output the diagrams in the GraphViz "dot" format. The nice thing about this format is that there are a lot of tools that are built to support it. You can use GraphViz itself to expand the diagram, or the dot CLI to convert the diagram to an image.

paracelsus graph example_app.models:Base --format dot | dot -Tpng > output.png

That command generates the diagram and pipes it to the dot CLI, which makes the following image.

Injecting Diagrams into Markdown with Paracelsus

Paracelsus was designed to keep documentation up to date. It has a feature that makes it easy to inject diagrams directly into an existing Markdown document. To start off you put two tags into your markdown file, <!-- BEGIN_SQLALCHEMY_DOCS --> and <!-- END_SQLALCHEMY_DOCS -->. When run Paracelsus injects the latest version of the diagram between those tags, erasing any existing content between them.

## Schema
<!-- BEGIN_SQLALCHEMY_DOCS -->

<!-- END_SQLALCHEMY_DOCS -->

The paracelsus inject command tags two arguments: the file to update, and the path to the base class (just like the graph command).

paracelsus inject README.md example_app.models:Base 

Once run the file will get updated to include the diagram inside of a markdown code block.

## Schema
<!-- BEGIN_SQLALCHEMY_DOCS -->
```mermaid
erDiagram
 users {
  CHAR(32) id PK
  DATETIME created
  VARCHAR(100) display_name "nullable"
 }

 posts {
  CHAR(32) id PK
  CHAR(32) author FK
  TEXT content "nullable"
  DATETIME created
  BOOLEAN live "nullable"
 }

 comments {
  CHAR(32) id PK
  CHAR(32) author FK
  CHAR(32) post FK "nullable"
  TEXT content "nullable"
  DATETIME created
  BOOLEAN live "nullable"
 }

 users ||--o{ posts : author
 posts ||--o{ comments : post
 users ||--o{ comments : author
```
<!-- END_SQLALCHEMY_DOCS -->

This gets rendered automatically on Github to create an interactive diagram. It looks the same as the one rendered by the WP-Mermaid plugin, with the extra ability to zoom in and out. Other systems, such as Gitlab, might render it with slightly different styles but will still use the same code mermaid library to create the same diagram structures.

The paracelsus inject command has a flag, --check, that can be used to see if any changes would have been made without actually modifying the file. This is useful for integrating with precommit hooks or CI systems to enforce that documentation always reflects the current version of the database schema.

Don't forget to follow this project on Github!

Rob's Awesome Python Template

Paracelsus was bootstrapped using Rob's Awesome Python Template, a Cookiecutter template I maintain and wrote about previously that allows me to rapidly prototype and build high quality Python projects. It utilizes modern Python practices and tools, while also having optional integrations with a variety of popular Python projects.