4 ms·

Show HN: Easily Visualize Your SQLAlchemy Data Models in a Nice SVG Diagram

79 points by eigenvalue 3y ago

adr1an 3y ago
Nice. I've been using Django-schema-viewer and it's been great. This makes me wonder if its UI could be decoupled from Django...
- eigenvalue 3y ago
  Cool. Yeah it would seem to limit the audience a lot that it's for Django only and also has to be integrated into an existing Django project for it to work. I wanted a very small and simple tool that worked generically for any SQLAlchemy models. The core code of my tool is literally ~90 lines of Python-- the other ~200 lines are all the sample data models I include. That's because all the real heavy lifting is done by graphviz.
eigenvalue 3y ago
I made this last week for fun, but I think it could be useful for people. If your application has pretty complicated SQLalchemy data models (and the app I'm working on now has gotten super complicated, with dozens of models with complex interconnections), then this very easily generates an attractive SVG that you can open in the browser showing all your tables and how they are connected. You can be up and running with this in less than a minute— just open it in Google Colab, replace the sample data models with your own, and run the notebook, and you should see the SVG displayed almost immediately at the bottom. I attempted to also generate an HTML file containing the embedded SVG as well as some JS to enable nice interactive hover-on effects (I wanted to highlight the border of the table you hover over and then change all the outgoing connections to other tables to be red, and all incoming connections to that table to be green), but it wasn't working and started turning into a slog after an hour. If anyone knows how to do this, please submit a PR!
- ants_a 3y ago
  Nice, I hacked together something similar many moons ago, it looks like some fine folks have kept on maintaining and improving it: https://github.com/fschulze/sqlalchemy_schemadisplay https://github.com/fschulze/sqlalchemy_schemadisplay Useful to have in a CICD pipeline so you always have an up-to-date schema diagram available.
  - eigenvalue 3y ago
    Cool, I'm surprised I couldn't find this when I was looking for something like this originally. Probably because I was using different terminology-- "data model" instead of "schema," and "visualize" instead of "display".
talboren 3y ago
gonna give it a try today, been wondering how come i never came across anything like that actually (been using the different clients tools)
- eigenvalue 3y ago
  Yeah, it was a bit weird to me that I couldn’t find anything like this either, which is why I made it. Seems like all the visualization tools out there go from the database tables directly, but that defeats the cross-database portability of SQLAlchemy which is one of its best features: it works equally well without any changes on SQLite, Postgres, etc.
  - michaelteter 3y ago
    Your tool looks great. In regards to difficulty finding alternatives, many database management tools (like DataGrip and Dbeaver) have ER diagram generators that work for all the types of databases the tools work for... so basically vendor agnostic. Other tools like Visio (blast from the past) do too.
    - eigenvalue 3y ago
      Sure, I meant something that could take SQLAlchemy data models as input and that was totally open source and free.
jhoechtl 3y ago
Would rather use D2 https://d2lang.com/tour/sql-tables https://d2lang.com/tour/sql-tables for this task
- eigenvalue 3y ago
  If you already have done the work of defining a bunch of SQLAlchemy models, using D2 would be a lot more time consuming.
NoboruWataya 3y ago
Looks good. I guess this only works for models defined using ORM and not schemas defined using the tools in SQLAlchemy Core?
jjice 3y ago
I see this is using Graphviz. The first time I used DOT, I was writing a parser and wanted to visualize my AST. It was fantastic and so easy to write, especially if you have a wrapper library. It's such a great tool for being able to visualize your graphs.
- eigenvalue 3y ago
  Yes, Graphviz is doing all the actual hard work here. It's an awesome library.
arsalanb 3y ago
This is great, especially to introduce new devs to models. I use (and love) TablePlus (https://tableplus.com/ https://tableplus.com/) which has a diagram generator plugin that does the same!
- riffic 3y ago
  I don't have a formal compsci background but one of the most enlightening things I've undertaken recently was a course involving data modeling. this seems like an important exercise many overlook.
throwaw33333434 3y ago
>>> from sqlalchemy_data_model_visualizer import generate_data_model_diagram python3.11/site-packages/sqlalchemy_data_model_visualizer.py", line 5, in <module> from sqlalchemy.orm import sessionmaker, declarative_base, relationship ImportError: cannot import name 'declarative_base' from 'sqlalchemy.orm' doesn't work with the older sqlalchemy
- throwaw33333434 3y ago
  from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy.ext.declarative import declarative_base

79 points by eigenvalue 3y ago

Nice. I've been using Django-schema-viewer and it's been great. This makes me wonder if its UI could be decoupled from Django...

eigenvalue 3y ago
Cool. Yeah it would seem to limit the audience a lot that it's for Django only and also has to be integrated into an existing Django project for it to work. I wanted a very small and simple tool that worked generically for any SQLAlchemy models. The core code of my tool is literally ~90 lines of Python-- the other ~200 lines are all the sample data models I include. That's because all the real heavy lifting is done by graphviz.

eigenvalue 3y ago

I made this last week for fun, but I think it could be useful for people. If your application has pretty complicated SQLalchemy data models (and the app I'm working on now has gotten super complicated, with dozens of models with complex interconnections), then this very easily generates an attractive SVG that you can open in the browser showing all your tables and how they are connected. You can be up and running with this in less than a minute— just open it in Google Colab, replace the sample data models with your own, and run the notebook, and you should see the SVG displayed almost immediately at the bottom. I attempted to also generate an HTML file containing the embedded SVG as well as some JS to enable nice interactive hover-on effects (I wanted to highlight the border of the table you hover over and then change all the outgoing connections to other tables to be red, and all incoming connections to that table to be green), but it wasn't working and started turning into a slog after an hour. If anyone knows how to do this, please submit a PR!

ants_a 3y ago
Nice, I hacked together something similar many moons ago, it looks like some fine folks have kept on maintaining and improving it: https://github.com/fschulze/sqlalchemy_schemadisplay https://github.com/fschulze/sqlalchemy_schemadisplay Useful to have in a CICD pipeline so you always have an up-to-date schema diagram available.
- eigenvalue 3y ago
  Cool, I'm surprised I couldn't find this when I was looking for something like this originally. Probably because I was using different terminology-- "data model" instead of "schema," and "visualize" instead of "display".

talboren 3y ago

gonna give it a try today, been wondering how come i never came across anything like that actually (been using the different clients tools)

eigenvalue 3y ago
Yeah, it was a bit weird to me that I couldn’t find anything like this either, which is why I made it. Seems like all the visualization tools out there go from the database tables directly, but that defeats the cross-database portability of SQLAlchemy which is one of its best features: it works equally well without any changes on SQLite, Postgres, etc.
- michaelteter 3y ago
  Your tool looks great. In regards to difficulty finding alternatives, many database management tools (like DataGrip and Dbeaver) have ER diagram generators that work for all the types of databases the tools work for... so basically vendor agnostic. Other tools like Visio (blast from the past) do too.
  - eigenvalue 3y ago
    Sure, I meant something that could take SQLAlchemy data models as input and that was totally open source and free.

jhoechtl 3y ago

Would rather use D2 https://d2lang.com/tour/sql-tables https://d2lang.com/tour/sql-tables for this task

eigenvalue 3y ago
If you already have done the work of defining a bunch of SQLAlchemy models, using D2 would be a lot more time consuming.

NoboruWataya 3y ago

Looks good. I guess this only works for models defined using ORM and not schemas defined using the tools in SQLAlchemy Core?

jjice 3y ago

I see this is using Graphviz. The first time I used DOT, I was writing a parser and wanted to visualize my AST. It was fantastic and so easy to write, especially if you have a wrapper library. It's such a great tool for being able to visualize your graphs.

eigenvalue 3y ago
Yes, Graphviz is doing all the actual hard work here. It's an awesome library.

arsalanb 3y ago

This is great, especially to introduce new devs to models. I use (and love) TablePlus (https://tableplus.com/ https://tableplus.com/) which has a diagram generator plugin that does the same!

riffic 3y ago
I don't have a formal compsci background but one of the most enlightening things I've undertaken recently was a course involving data modeling. this seems like an important exercise many overlook.

throwaw33333434 3y ago

>>> from sqlalchemy_data_model_visualizer import generate_data_model_diagram python3.11/site-packages/sqlalchemy_data_model_visualizer.py", line 5, in <module> from sqlalchemy.orm import sessionmaker, declarative_base, relationship ImportError: cannot import name 'declarative_base' from 'sqlalchemy.orm' doesn't work with the older sqlalchemy

throwaw33333434 3y ago
from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy.ext.declarative import declarative_base