Visualising SQL db schema diagrams using eralchemy and graphviz wasm

Although I don’t post here any where near as regularly as I used to, the days of my hoped for OUsefulness are now on a pretty strict countdown, so things will be as they are — possibly a last flurry of activity as I try to make things “safe” and “handover” any that may OUtlive my OUseful time, as I start to look for opportunities anew, and maybe a return to some open data tinkering and data storytelling projects…

A few days ago, I reminded myself of an open issue around the IPython magic we use for generating schema diagrams for SQL dbs in our Jupyter notebooks. This uses Graphviz installed as an O/S package to render SVG or png diagrams (I think?) from a schema generated from a connected db using a found package that I suspect hasn’t been updated for years.

I’ve had an open issue for a year or so making a note that eralchemy provides a rather more actively maintained package for generating ERDs from connected databases, although it doesn’t have any associated magic, is geared up for CLI rather than python API use, and it also makes use of Graphviz installed on the o/s to render dot scripts to schema diagrams.

(It also claims to generate mermaid diagram script, but that didn’t look valid to me?)

Anyway, I thought i’d have a quick tinker, and came up with the typoed repo https://github.com/innovationOUtside/erlachemy_schemadisplay_anywidget_magic which:

• creates a simple hacky Python API around eralchemy that Claude.ai generated for me from the original CLI code;
• includes a custom IPython magic class that lets you use IPython magic to generate schema diagram specifications, e.g. in dot language, from a db given the db connection string;
• uses my innovationOUtside/jupyter_anywidget_graphviz package to render dot code to SVG in the browser (i.e. without requiring Graphviz installed to you computer, or a graphviz server/server connection to do the rendering).

In my head, that’s another thing ticked off the list of things we can offer that don’t necessarily require a server or O/S installed package, and that, as such, we may be able to offer to students in secure environments, or students working offline who can’t install s/w onto their machines, etc etc.

PS I wonder… can we get eralchemy to work as it is with a connection to my innovationOUtside/jupyter_anywidget_pglite widget, or would we need to do some fettling around the connection? Or maybe the pglite instance doesn’t respond to or return all the things eralchemy needs? [UPDATE – my half-baked sqlalchemy connection is not quite rich enough. Maybe one to work on?]