4 ms·

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 schem

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.

deleted 3y ago
[deleted]
olejorgenb 3y ago
Regardless, the implementation in 128 lines of SQL combined with graphviz is cool.
- vidarh 3y ago
  And the sql is mostly comments. The actual SQL is ~50 lines with plenty of whitespace.
alexvoda 3y ago
As with all such tools the issue is the automatic layout algorithm. I find that almost all layout algorithms for database diagrams are rather poor.
bbkane 3y ago
You might try https://schemaspy.org/ 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
- sbuttgereit 3y ago
  > 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.

deleted 3y ago

[deleted]

olejorgenb 3y ago

Regardless, the implementation in 128 lines of SQL combined with graphviz is cool.

vidarh 3y ago
And the sql is mostly comments. The actual SQL is ~50 lines with plenty of whitespace.

alexvoda 3y ago

As with all such tools the issue is the automatic layout algorithm. I find that almost all layout algorithms for database diagrams are rather poor.

bbkane 3y ago

You might try https://schemaspy.org/ 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

sbuttgereit 3y ago
> 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.