It also adds a severe amount of visual clutter between the code. Individual preference I’m sure, but I’d prefer less comments, I think. Or maybe my IDE just needs to collapse comments inside functions automatically.
This is very cool. Reminds me of my days 13 years ago using dot to draw complex planaarized graph diagrams before switching to physics / springs models, graph embeddings, and other cool things.
I hope you don't mind? If you don't want ur code there let me know and I'll sadly but obediently take it down and just link to it from someplace on there I can readily find. :)
Likewise, the niche pressure for me came from SQLite being agnostic to a canonical form for SQL `.schema`. I did not need to get into parsing every flavor.
My opinion will probably not be popular, but by making the mirror you are helping with creating this monopoly.
I see the solution in creating small single/few page(s) landing site and linking to the code and releases, being it to self/hosted Gitea, Forgejo, Gitlab, GitHub...
Somebody upthread[1] also made a GitHub mirror. I appreciate that different people have different comfort-levels with the centralisation of services like GitHub, but luckily it's really easy for people to copy a Git repo to a host they're more comfortable with, like GitHub or SourceHut or even making a local clone.
I have not considered outputting to MermaidJS, but (from a quick glance at that documentation) it looks like the same "SQL template" technique should work. Actually doing it is left as an exercise for the reader. :)
A place I worked at during the dot-com era had a large format printer[0] and the DBAs would occasionally print database schema diagram posters that they would hang on the walls. It was amazingly useful, especially as we staffed up and had a lot of new employees.
@thristian - can you specify a paper size?
[0] That once the marketing department found out about, was always out of ink.
So far as I can tell, GraphViz does not allow you to specify a paper size. However, if you render to SVG, you can open the result in Inkscape and rearrange things fairly easily. That's not quite as convenient as having it done automatically, but GraphViz can struggle with laying out a complex schema even when assuming infinite space - some amount of hand-tweaking is going to be necessary regardless.
1. Takes in a .dot file
2. Presents a simple UI for selecting which tables/relationships you want in the final diagram
3. Lets you highlight a table and add all directly related tables to the selected tables
4. Lets you select two tables and adds the tables for the shortest route between the tables
5. Lets you assign colors to tables/relationships for the final diagram
6. Optionally shows only key fields in the final diagram
7. Generates the necessary graph source and copies it to the clipboard, and loads either of two GraphViz pages to let you paste the source and see the graph.
If that would be of interest to anyone I'd be happy to post it.
Tried it on SQLite's own Fossil repo, which is a kind of SQLite db too.
The resulting diagram shows no relationship arrows.
Turns out the Fossil's schema uses REFERENCES clause with a table name only; I guess, this points to table's primary key by default. Apparently, the diagram generator requires explicit column names.
Huh. The syntax diagram in the documentation[1] suggests this is possible, but the documentation on foreign keys[2] does not mention that syntax, or how it's interpreted.
Thanks for the idea! I have a repo that (ab)uses Gitlab CI to periodically produce an SQLite database from a bunch of other data sources and this is a great addition to the README
Haha, I'm abusing gitlab pipeline minutes to run a periodical cypress task to test signup+login in production on my pet-project :) Scheduled pipelines for the win!
no, in my case only when the Migrations/ folder changes (you can specify that in .gitlab-ci.yml or using come commandline-fu). I'm using EF core as an ORM, thats why it is also easy to create an empty SQLite DB from the sources.
This seems very clever. I’ve enjoyed abusing SQL, too. And note that abuse is the developer’s term for how what he’s doing in sqlite-schema-diagram.sql. I’m not trying to be insulting. I actually do like it.
What I like here is the "unix style do one thing" part of here's a simple ("simple") SQL script that pumps into GraphViz which does all of the heavy lifting.
I remember writing a script for doing this, more than 10 years ago. I haven't used it much, and not for many years.
The problem is that a fully automatic schema is only readable for very small databases. So small that very soon you can keep the structure in your head. For larger databases, the automatic schema will be awful. Even with just 20 tables, graphviz (dot | neato) will make a mess when some tables are referenced everywhere (think of `created_by` and `updated_by` pointing to `user_id`).
When I need a map of a large database, I usually create a few specialized diagrams with dbeaver, then combine them into a PDF file. Each diagram is a careful selection of tables.
You might try https://schemaspy.org/ - it generates a website with ER diagrams that only go one or two relationships out, but they have clickable table names to get to the next diagram
> ER diagrams that only go one or two relationships out
Actually, SchemaSpy gives you a full diagram of the entire schema as well: it gives it to you with a truncated columns list and a full columns list per table. The "Relationships" option at the top of the page is where the full diagram is accessed.
The one & two relations out limited views are if you're getting to the diagram from the scope of a specific table... it will show you one and two relations away from the current table when using that perspective. And, as you say, you can navigate the relationships that way.
What I really like about SchemaSpy (I use it with PostgreSQL) is that I can `COMMENT ON` database objects like tables and columns using markdown and SchemaSpy will render the Markdown in it's output. Simple markdown still looks decent when viewed from something like psql, too, so it's a nice way to have documentation carried with the database.
Readit News
Deleted Comment
Nice to see a really good use for dot.
I created a fork on GitHub as a fork there'll be easier for me to come back to, find, organize and use (and may be for others too): https://github.com/o0101/sqlite-schema-diagram
I hope you don't mind? If you don't want ur code there let me know and I'll sadly but obediently take it down and just link to it from someplace on there I can readily find. :)
https://github.com/TomConlin/SQLiteViz
I just mirrored it myself to keep tabs on it, because otherwise I'll forget it.
Very interesting approach.
I see the solution in creating small single/few page(s) landing site and linking to the code and releases, being it to self/hosted Gitea, Forgejo, Gitlab, GitHub...
[1]: https://news.ycombinator.com/item?id=39800533
Have you considered outputting to a MermaidJS format?
https://mermaid.js.org/syntax/entityRelationshipDiagram.html
@thristian - can you specify a paper size?
[0] That once the marketing department found out about, was always out of ink.
Set the size of the graph in inches:
https://www.graphviz.org/docs/attrs/size/
1. Takes in a .dot file 2. Presents a simple UI for selecting which tables/relationships you want in the final diagram 3. Lets you highlight a table and add all directly related tables to the selected tables 4. Lets you select two tables and adds the tables for the shortest route between the tables 5. Lets you assign colors to tables/relationships for the final diagram 6. Optionally shows only key fields in the final diagram 7. Generates the necessary graph source and copies it to the clipboard, and loads either of two GraphViz pages to let you paste the source and see the graph.
If that would be of interest to anyone I'd be happy to post it.
The resulting diagram shows no relationship arrows.
Turns out the Fossil's schema uses REFERENCES clause with a table name only; I guess, this points to table's primary key by default. Apparently, the diagram generator requires explicit column names.
I think I can fix this.
[1]: https://sqlite.org/syntax/foreign-key-clause.html
[2]: https://sqlite.org/foreignkeys.html
The problem is that a fully automatic schema is only readable for very small databases. So small that very soon you can keep the structure in your head. For larger databases, the automatic schema will be awful. Even with just 20 tables, graphviz (dot | neato) will make a mess when some tables are referenced everywhere (think of `created_by` and `updated_by` pointing to `user_id`).
When I need a map of a large database, I usually create a few specialized diagrams with dbeaver, then combine them into a PDF file. Each diagram is a careful selection of tables.
Actually, SchemaSpy gives you a full diagram of the entire schema as well: it gives it to you with a truncated columns list and a full columns list per table. The "Relationships" option at the top of the page is where the full diagram is accessed.
The one & two relations out limited views are if you're getting to the diagram from the scope of a specific table... it will show you one and two relations away from the current table when using that perspective. And, as you say, you can navigate the relationships that way.
What I really like about SchemaSpy (I use it with PostgreSQL) is that I can `COMMENT ON` database objects like tables and columns using markdown and SchemaSpy will render the Markdown in it's output. Simple markdown still looks decent when viewed from something like psql, too, so it's a nice way to have documentation carried with the database.
I find that almost all layout algorithms for database diagrams are rather poor.
Deleted Comment