Skip to content

Instantly share code, notes, and snippets.

@ugultopu

ugultopu/Generate diagram from existing database.md

Last active April 11, 2021 21:46
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ugultopu/bd1e9d0c410655cb8471f1a9617ab0cd to your computer and use it in GitHub Desktop.
Save ugultopu/bd1e9d0c410655cb8471f1a9617ab0cd to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Generate diagram from existing database.md

Most useful search term for this is "reverse engineer database". Or "generate diagram from SQL" (such as using a database dump to generate a diagram).

The only programs that I found to be working among the ones that I tried are:

Other programs that are mentioned around the Web:

PGModeler did not work for me. It was not able to generate all tables in the diagram.

IMO, the best one is DbVisualizer. Like DBeaver, it is very simple. Again, like DBeaver, it is a Java application that is very straightforward. All you need to do is to open it, where it will usually detect the database by itself, and you just need to double click on the schema that you want to visualize. After that, the diagram is generated in about 10-15 seconds. The thing that makes this better than DBeaver is, in DbVisualizer, when you click on a table, the foreign keys are highlighted. This makes it very easy to understand which tables does a particular table have a relationship with.

DBeaver is a Java application based on Eclipse, that is straightforward. You can launch it, connect to a DB and generate a diagram. Very easy. Use this. This is better than SchemaSpy because you can move the tables in DBeaver's diagram to understand them better, whereas although SchemaSpy's diagram is a bit better looking, it is just an image. That is, you cannot move the tables.

SchemaSpy is more complicated to use. SchemaSpy is a command line program written in Java. Hence, you need to install Java to use it. Although the docs say that you don't need to install Graphviz anymore because the latest version of SchemaSpy comes bundled with viz.js, Nashorn (JavaScript engine) was removed from Java at version 15. Hence, vis.js will not work with Java version 15 or above. Hence, you still do need to install Graphviz in order to make SchemaSpy work. So, to summarize:

  • brew install java

  • brew install graphviz

  • mkdir schemaspy

  • cd schemaspy

  • Go to https://github.com/schemaspy/schemaspy/releases/latest and download the JAR file to the schemaspy directory that we have just created.

  • Download the JDBC driver for PostgreSQL from here and put the JAR into the same schemaspy directory.

  • Within the schemaspy directory, create another directory to contain the output of SchemaSpy. Let's give the name out to this directory (that is, mkdir out).

  • Create a file named schemaspy.properties within the schemaspy directory, with the following contents:

    schemaspy.t=pgsql11
schemaspy.dp=.
schemaspy.host=localhost
schemaspy.db=postgres
schemaspy.u=postgres
schemaspy.o=out

  • Finally, run SchemaSpy with java -jar schemaspy-VERSION.jar, where VERSION is whatever version that is in the name of the JAR file for SchemaSpy. As of now, it is 6.1.0, hence I ran java -jar schemaspy-6.1.0.jar. The output will be a web page in the output directory that you have specified (in this case, the out directory). Within it, you can open the index.html on a web browser to access the diagram.

Sources:

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment