Hacker News Reader

Comment by idoubtit | Hacker News Reader

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.

View on HN

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

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

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.

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.

Reply on news.ycombinator.com