Skip to content

Instantly share code, notes, and snippets.

@dpapathanasiou

dpapathanasiou/SchemaSpy-HOWTO.md

Last active February 17, 2024 19:45
Show Gist options
  • Star 51 You must be signed in to star a gist
  • Fork 15 You must be signed in to fork a gist
  • Save dpapathanasiou/c9c6236a410e9d018ae0 to your computer and use it in GitHub Desktop.
Save dpapathanasiou/c9c6236a410e9d018ae0 to your computer and use it in GitHub Desktop.
Download ZIP
How to use SchemaSpy to generate the db schema diagram for a PostgreSQL database
Raw
SchemaSpy-HOWTO.md

SchemaSpy is a neat tool to produce visual diagrams for most relational databases.

Here's how to use it to generate schema relationship diagrams for PostgreSQL databases:

  1. Download the jar file from here (the current version is v6.1.0)

  2. Get the PostgreSQL JDBC driver (unless your installed version of java is really old, use the latest JDBC4 jar file)

  3. Run the command against an existing database. For most databases, the schema (-s option) we are interested in is the public one:

java -jar schemaspy-6.1.0.jar -t pgsql \
  -s public -db [db name] -u [db user] -p [password] \
  -host localhost -o /tmp \
  -dp /path/to/JDBC/driver/postgresql-42.2.12.jar

The above example sends all the output to the /tmp folder, but that can be changed as desired.

SchemaSpy produces several different diagrams, but the main summary diagram is in:

/tmp/diagrams/summary/relationships.real.large.png.

@joelataylor
Copy link

Thx!

@vrialland
Copy link

Thanks! 👍

@rafalkasa
Copy link

New version of SchemaSpy 6.0 you can find on https://github.com/schemaspy/schemaspy

If you would like to see what can look your database documentation go to http://schemaspy.org Sample Output section

@jrrojasg
Copy link

jrrojasg commented Jun 1, 2017

Thanks!

Copy link

ghost commented Dec 18, 2017

Thanks, good stuff!

@gustavo-belduma
Copy link

Gracias!

@kalpeshtawde
Copy link

I am getting below error, not sure what I am doing wrong.

D:>java -jar schemaSpy_5.0.0.jar -t pgsql -s public -host localhost -db postgres -u postgres -p "ratnagiri_2013" -o "C:\Users\ktawde\Documents" -dp postgresql-42.2.0.jar -gv "C:\Program Files (x86)\Graphviz2.38"
Using database properties:
[schemaSpy_5.0.0.jar]/net/sourceforge/schemaspy/dbTypes/pgsql.properties
11:48:11.118 WARNING: ConnectionFactoryImpl.log - SQLException occurred while connecting to localhost:5432
org.postgresql.util.PSQLException: FATAL: password authentication failed for user "postgres"
at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:475)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:207)
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
at org.postgresql.jdbc.PgConnection.(PgConnection.java:195)
at org.postgresql.Driver.makeConnection(Driver.java:452)
at org.postgresql.Driver.connect(Driver.java:254)
at net.sourceforge.schemaspy.SchemaAnalyzer.getConnection(SchemaAnalyzer.java:582)
at net.sourceforge.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:157)
at net.sourceforge.schemaspy.Main.main(Main.java:42)

@dandeezy
Copy link

@kalpeshtawde see the error: password authentication failed for user "postgres".

Are you sure you have the password correct? This jar should be better and not give you a stacktrace for a common issue like that.

@mscinar
Copy link

mscinar commented Feb 6, 2018

Is there a more advanced example and howto for schemaspy?
I'm interested in "only" producing the er-diagram and selecting / discarding tables from the diagram.

@shaileshsingh9214
Copy link

Hi i am not able to do the same i am getting error as

C:\Users\administrator\Downloads>java -jar postgresql-42.2.1.jre7.jar -t pgsql -s public -db cx_assesshub_v1.0 -u postgres -p assesshub -host localhost -o /tmp-dp C:\Users\administrator.m2\repository\postgresql\postgresql\9.2-1002.jdbc4\postgresql-9.2-1002.jdbc4.jar

PostgreSQL JDBC Driver 42.2.1.jre7Found in: jar:file:/C:/Users/administrator/Downloads/postgresql-42.2.1.jre7.jar!/org/postgresql/Driver.classThe PgJDBC driver is not an executable Java program.You must install it according to the JDBC driver installation instructions for your application / container / appserver, then use it by specifying a JDBC URL of the form jdbc:postgresql://or using an application specific method.See the PgJDBC documentation: http://jdbc.postgresql.org/documentation/head/index.htmlThis command has had no effect.

@leoskyrocker
Copy link

@kalpeshtawde make sure you quote the password if it contains special characters (instead of -p abc?$efg use -p 'abc?$efg')

@salimi-hamza
Copy link

Hello, Shemaspy still blocked in the analysis part I don't know why?
SchemaSpy generates an HTML representation of a database schema's relationships.
SchemaSpy comes with ABSOLUTELY NO WARRANTY.
SchemaSpy is free software and can be redistributed under the conditions of LGPL version 3 or later.
http://www.gnu.org/licenses/

INFO - Starting Main v6.0.0 on DESKTOP-7O0VIFK with PID 20388 (C:\schemaspy-6.0.0.jar started by Youssef in C:)
INFO - The following profiles are active: default
INFO - Started Main in 2.618 seconds (JVM running for 3.534)
INFO - Configuration file not found
INFO - Starting schema analysis

can someone please help me?

@boban100janovski
Copy link

tnx

@mchaubey
Copy link

WARN - Connection Failure
Failed to connect to database URL [jdbc:postgresql://postgresdb.crzu3pt7qlul.ap-south-1.rds.amazonaws.com,5432/public] Failed to create any of 'org.postgresql.Driver' driver from driverPath '/org/schemaspy/drivers/postgresql-9.4.1208.jre6.jar' with sibling jars no.
Resulting in classpath: empty
There were missing paths in driverPath:
/org/schemaspy/drivers/postgresql-9.4.1208.jre6.jar
Use commandline option '-dp' to specify driver location.
If you need to load sibling jars used '-loadjars'
INFO - StackTraces have been omitted, use -debug when executing SchemaSpy to see them

@jaclynsaunders
Copy link

Thank you!

@dpapathanasiou
Copy link
Author

dpapathanasiou commented May 15, 2020 •
edited

New version of SchemaSpy 6.0 you can find on https://github.com/schemaspy/schemaspy

If you would like to see what can look your database documentation go to http://schemaspy.org Sample Output section

@rafalkasa Thank you, I've updated the instructions

@shrikantbhende
Copy link

Hello,

I am getting below error on mac while running schemaspy,

1.0.jar started by root in /Users/shrikant/Downloads)
INFO - The following profiles are active: default
INFO - Started Main in 1.513 seconds (JVM running for 2.792)
INFO - Starting schema analysis
WARN - Connection Failure
Failed to connect to database URL [jdbc:postgresql://localhost/5432] FATAL: database "5432" does not exist
INFO - StackTraces have been omitted, use -debug when executing SchemaSpy to see them
sh-3.2# java -jar schemaspy-6.1.0.jar -t pgsql -s public -db postgres -u postgres -p 5432 -host localhost -o /tmp -dp /Users/shrikant/Downloads/postgresql-42.2.14.jar
INFO - Starting Main v6.1.0 on Shrikants-MacBook-Pro.local with PID 8346 (/Users/shrikant/Downloads/schemaspy-6.1.0.jar started by root in /Users/shrikant/Downloads)
INFO - The following profiles are active: default
INFO - Started Main in 1.043 seconds (JVM running for 1.434)
INFO - Starting schema analysis
INFO - Connected to PostgreSQL - 11.5
INFO - Gathering schema details
Gathering schema details.........WARN - Failed to retrieve stored procedure/function details using sql 'select r.routine_name, case when p.proisagg then 'AGGREGATE' else 'FUNCTION' end as routine_type, case when p.proretset then 'SETOF ' else '' end || case when r.data_type = 'USER-DEFINED' then r.type_udt_name else r.data_type end as dtd_identifier, r.external_language as routine_body, r.routine_definition, r.sql_data_access, r.security_type, r.is_deterministic, d.description as routine_comment from information_schema.routines r left join pg_namespace ns on r.routine_schema = ns.nspname left join pg_proc p on ns.oid = p.pronamespace and r.routine_name = p.proname left join pg_description d on d.objoid = p.oid where r.routine_schema = :schema'
ERROR: column p.proisagg does not exist
Hint: Perhaps you meant to reference the column "p.prolang".
Position: 34
(0sec)
Connecting relationships.........(0sec)
Writing/graphing summary.INFO - Gathered schema details in 0 seconds
INFO - Writing/graphing summary
WARN - Failed to query Graphviz version using 'dot -V'
Cannot run program "dot": error=2, No such file or directory
INFO - Graphviz rendered set to ''
..ERROR - RelationShipDiagramError
Failed to produce diagram for: /tmp/diagrams/summary/relationships.implied.compact.dot
ERROR - RelationShipDiagramError
Failed to produce diagram for: /tmp/diagrams/summary/relationships.implied.large.dot
.ERROR - Failed to produce diagram for: /tmp/diagrams/orphans/abc_1.1degree.dot
Failed to generate Orphan diagram
ERROR - Failed to produce diagram for: /tmp/diagrams/orphans/onboard_cust.1degree.dot
Failed to generate Orphan diagram
ERROR - Failed to produce diagram for: /tmp/diagrams/orphans/table_list.1degree.dot
Failed to generate Orphan diagram
ERROR - Failed to produce diagram for: /tmp/diagrams/orphans/test1.1degree.dot
Failed to generate Orphan diagram
ERROR - Failed to produce diagram for: /tmp/diagrams/orphans/test2.1degree.dot
Failed to generate Orphan diagram
.....(2sec)
Writing/diagramming detailsINFO - Completed summary in 2 seconds
INFO - Writing/diagramming details
Exception in thread "main" java.lang.reflect.InvocationTargetException
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:564)
at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:48)
at org.springframework.boot.loader.Launcher.launch(Launcher.java:87)
at org.springframework.boot.loader.Launcher.launch(Launcher.java:50)
at org.springframework.boot.loader.JarLauncher.main(JarLauncher.java:51)
Caused by: org.schemaspy.output.diagram.DiagramException: Failed to generate Table diagram
at org.schemaspy.output.diagram.DiagramFactory.generateTableDiagram(DiagramFactory.java:71)
at org.schemaspy.output.html.mustache.diagrams.MustacheDiagramFactory.generateTableDiagram(MustacheDiagramFactory.java:44)
at org.schemaspy.output.html.mustache.diagrams.MustacheTableDiagramFactory.generateTableDiagrams(MustacheTableDiagramFactory.java:80)
at org.schemaspy.SchemaAnalyzer.generateHtmlDoc(SchemaAnalyzer.java:426)
at org.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:260)
at org.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:123)
at org.schemaspy.cli.SchemaSpyRunner.runAnalyzer(SchemaSpyRunner.java:98)
at org.schemaspy.cli.SchemaSpyRunner.run(SchemaSpyRunner.java:87)
at org.schemaspy.Main.main(Main.java:55)
... 8 more
Caused by: org.schemaspy.output.diagram.DiagramException: Dot missing or invalid version
at org.schemaspy.output.diagram.graphviz.GraphvizDot.generateDiagram(GraphvizDot.java:193)
at org.schemaspy.output.diagram.DiagramFactory.generateTableDiagram(DiagramFactory.java:68)
... 16 more
Collapse

@dpapathanasiou
Copy link
Author

@shrikantbhende the problem is clear, if you know how to read java stack traces.

In this particular case, the reason is:

 Caused by: org.schemaspy.output.diagram.DiagramException: Dot missing or invalid version

I.e., as the SchemaSpy documents note, you need to either install Graphviz or use vizjs in your command line.

@zurikus
Copy link

zurikus commented Sep 28, 2020

Thank you!

@DeathTamer
Copy link

DeathTamer commented Oct 9, 2020 •
edited

I am getting below error, not sure what I am doing wrong.

D:>java -jar schemaSpy_5.0.0.jar -t pgsql -s public -host localhost -db postgres -u postgres -p "ratnagiri_2013" -o "C:\Users\ktawde\Documents" -dp postgresql-42.2.0.jar -gv "C:\Program Files (x86)\Graphviz2.38"
Using database properties:
[schemaSpy_5.0.0.jar]/net/sourceforge/schemaspy/dbTypes/pgsql.properties
11:48:11.118 WARNING: ConnectionFactoryImpl.log - SQLException occurred while connecting to localhost:5432
org.postgresql.util.PSQLException: FATAL: password authentication failed for user "postgres"
at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:475)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:207)
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
at org.postgresql.jdbc.PgConnection.(PgConnection.java:195)
at org.postgresql.Driver.makeConnection(Driver.java:452)
at org.postgresql.Driver.connect(Driver.java:254)
at net.sourceforge.schemaspy.SchemaAnalyzer.getConnection(SchemaAnalyzer.java:582)
at net.sourceforge.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:157)
at net.sourceforge.schemaspy.Main.main(Main.java:42)

If this happens to you, try to explicitly define the port on the host argument (e.g., -host localhost:5432)
password authentication failed for user "postgres" can be misleading.
If this does not work, make sure you have postgres defined (you can do this with \du inside the psql console).
You should have at least one user with Superuser privileges (to create the postgres user -> if it doesn't exist).

@dpapathanasiou
Copy link
Author

@GuilhermeFernandes95 and @kalpeshtawde I also wonder if the quotes around the password could be the problem

@DeathTamer
Copy link

@GuilhermeFernandes95 and @kalpeshtawde I also wonder if the quotes around the password could be the problem

@dpapathanasiou, haven't seen that :D It could easily be that.

@dkaufman-w2o
Copy link

My SchemaSpy output includes information about the tables and views in my PostGRES database, but the relationship diagram has only the tables and not the views. Can I make it include the views as well? Thanks.

@StacieWeber
Copy link

Can someone please direct me to where I can find the difference between -t pgsql and -t pgsql11 DBTypes?
Thank you in advance.

@salmagomaa
Copy link

Thank you so much!

@jpinnix
Copy link

jpinnix commented Sep 22, 2021

Thank you so much! Super helpful.

@autra
Copy link

autra commented Jan 30, 2024

Thank you! Is there a way to generate documentation on a database spanning multiple schemas?

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