Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
How to use SchemaSpy to generate the db schema diagram for a PostgreSQL database

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

This comment has been minimized.

Copy link

@joelataylor joelataylor commented Jul 17, 2015

Thx!

@vrialland

This comment has been minimized.

Copy link

@vrialland vrialland commented Nov 8, 2016

Thanks! 👍

@rafalkasa

This comment has been minimized.

Copy link

@rafalkasa rafalkasa commented Dec 22, 2016

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

This comment has been minimized.

Copy link

@jrrojasg jrrojasg commented Jun 1, 2017

Thanks!

@wrover

This comment has been minimized.

Copy link

@wrover wrover commented Dec 18, 2017

Thanks, good stuff!

@gustavo-belduma

This comment has been minimized.

Copy link

@gustavo-belduma gustavo-belduma commented Jan 5, 2018

Gracias!

@kalpeshtawde

This comment has been minimized.

Copy link

@kalpeshtawde kalpeshtawde commented Jan 24, 2018

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

This comment has been minimized.

Copy link

@dandeezy dandeezy commented Jan 29, 2018

@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

This comment has been minimized.

Copy link

@mscinar 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

This comment has been minimized.

Copy link

@shaileshsingh9214 shaileshsingh9214 commented Mar 5, 2018

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

This comment has been minimized.

Copy link

@leoskyrocker leoskyrocker commented Jun 16, 2018

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

@salimi-hamza

This comment has been minimized.

Copy link

@salimi-hamza salimi-hamza commented Apr 15, 2019

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?

@boban984

This comment has been minimized.

Copy link

@boban984 boban984 commented Sep 15, 2019

tnx

@mchaubey

This comment has been minimized.

Copy link

@mchaubey mchaubey commented Sep 18, 2019

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

This comment has been minimized.

Copy link

@jaclynsaunders jaclynsaunders commented May 15, 2020

Thank you!

@dpapathanasiou

This comment has been minimized.

Copy link
Owner Author

@dpapathanasiou dpapathanasiou commented May 15, 2020

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

This comment has been minimized.

Copy link

@shrikantbhende shrikantbhende commented Jun 19, 2020

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

This comment has been minimized.

Copy link
Owner Author

@dpapathanasiou dpapathanasiou commented Jun 19, 2020

@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

This comment has been minimized.

Copy link

@zurikus zurikus commented Sep 28, 2020

Thank you!

@GuilhermeFernandes95

This comment has been minimized.

Copy link

@GuilhermeFernandes95 GuilhermeFernandes95 commented Oct 9, 2020

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

This comment has been minimized.

Copy link
Owner Author

@dpapathanasiou dpapathanasiou commented Oct 10, 2020

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

@GuilhermeFernandes95

This comment has been minimized.

Copy link

@GuilhermeFernandes95 GuilhermeFernandes95 commented Oct 10, 2020

@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

This comment has been minimized.

Copy link

@dkaufman-w2o dkaufman-w2o commented Nov 12, 2020

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

This comment has been minimized.

Copy link

@StacieWeber StacieWeber commented Jan 21, 2021

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

@salmagomaa

This comment has been minimized.

Copy link

@salmagomaa salmagomaa commented Feb 7, 2021

Thank you so much!

@jpinnix

This comment has been minimized.

Copy link

@jpinnix jpinnix commented Sep 22, 2021

Thank you so much! Super helpful.

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