Thread: Visualize database schema

On 08/14/12 1:54 AM, Dario Beraldi wrote:
> Hello,
>
> Apologies if this question has been asked before, but I couldn't come
> up with a decent solution...
>
> Can anyone advice about a tool to visualize a database schema?
> Ideally, I would like something that takes the SQL definition of a
> schema or database (essentially the output of pg_dump) and produces a
> graphical representation of the tables, constraints and indexes which
> can be moved around for ease of visualization (something like the
> "Graphical query builder" that comes with pgAdmin =>1.14).
> I don't care (and don't want) to modify or query the database with
> such tool, I just want to visualize it. (In fact, I tried to use
> pgAdmin for this but the graphical queries can only be saved as images
> and so they cannot be reloaded).

generically, thats known as ERD, Entity-Relationship-Diagram. there's
ots of tools that do it, but I've never seen one that didnt make a
rather messy graph of anything more complex than a few tables.

DBVisualizer is one such tool.  Also, many UML tools can reverse
engineer a database and generate graphics, and even let you modify the
schema graphically.

the free tools I've seen have been pretty klunky and/or limited, the
decent tools tend to be commercial and range from modest to rather
expensive.



--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

> Can anyone advice about a tool to visualize a database schema? Ideally, I would like something that takes the SQL
definitionof a schema or database 
> (essentially the output of pg_dump) and produces a graphical representation of the tables, constraints and indexes
whichcan be moved around for ease of 
> visualization (something like the "Graphical query builder" that comes with pgAdmin =>1.14).

A simple method for this, open source cross-platform and work for various RDBMS, is the following:
 1. Download  SQL Power Architect at https://code.google.com/p/power-architect/downloads/list (it's a java app so you
needa JVM installed and JDBC drivers for the RDBMS to use) 
2. Run it, right click in left pane and choose "Add source connection" -> "New connection..." to create a connection
foryour db 
3. Expand the connection  and the database and then simply drag and drop the schema to the right pane.
4. Click on "Automatic layout" in the toolbar (it's the icon that looks like three connected boxes and a green
triangle)

From there you can examine the database. There are some limitations, constraints for example, so for more complicated
tools 
look into ERD-tools as suggested by John in another reply.

Regards,
roppert

On Tue, Aug 14, 2012 at 09:54:34AM +0100, Dario Beraldi wrote:

> Can anyone advice about a tool to visualize a database schema?
> Ideally, I would like something that takes the SQL definition of a
> schema or database

In addition to other suggestions: postgresql-autodoc is another option.

Regards
Johann


--
Johann Spies                            Telefoon: 021-808 4699
Databestuurder /  Data manager

Sentrum vir Navorsing oor Evaluasie, Wetenskap en Tegnologie
Centre for Research on Evaluation, Science and Technology
Universiteit Stellenbosch.

     "Let us therefore come boldly unto the throne of grace,
      that we may obtain mercy, and find grace to help in
      time of need."           Hebrews 4:16
E-pos vrywaringsklousule

Hierdie e-pos mag vertroulike inligting bevat en mag regtens geprivilegeerd wees en is slegs bedoel vir die persoon aan
wiedit geadresseer is. Indien u nie die bedoelde ontvanger is nie, word u hiermee in kennis gestel dat u hierdie
dokumentgeensins mag gebruik, versprei of kopieer nie. Stel ook asseblief die sender onmiddellik per telefoon in kennis
envee die e-pos uit. Die Universiteit aanvaar nie aanspreeklikheid vir enige skade, verlies of uitgawe wat voortspruit
uithierdie e-pos en/of die oopmaak van enige l��s aangeheg by hierdie e-pos nie. 

E-mail disclaimer

This e-mail may contain confidential information and may be legally privileged and is intended only for the person to
whomit is addressed. If you are not the intended recipient, you are notified that you may not use, distribute or copy
thisdocument in any manner whatsoever. Kindly also notify the sender immediately by telephone, and delete the e-mail.
TheUniversity does not accept liability for any damage, loss or expense arising from this e-mail and/or accessing any
filesattached to this e-mail. 

> Hello,
>
> Apologies if this question has been asked before, but I couldn't come up
> with a decent solution...
>
> Can anyone advice about a tool to visualize a database schema? Ideally, I
> would like something that takes the SQL definition of a schema or
> database
> (essentially the output of pg_dump) and produces a graphical
> representation
> of the tables, constraints and indexes which can be moved around for ease
> of visualization (something like the "Graphical query builder" that comes
> with pgAdmin =>1.14).
> I don't care (and don't want) to modify or query the database with such
> tool, I just want to visualize it. (In fact, I tried to use pgAdmin for
> this but the graphical queries can only be saved as images and so they
> cannot be reloaded).
>
> Thanks!
>
> Dario

I am using SchemaSpy (http://schemaspy.sourceforge.net/) from time to
time. It connects to running database (using JDBC driver, so it can talk
to practically any RDBMS) and generates set of HTML pages containing
interlinked diagrams and descriptions of schema. It can even guess
relations from column names.

With regards
Zdeněk Bělehrádek

> Can anyone advice about a tool to visualize a database schema?

SQLalchemy, a Python module, can produce dot (Graphviz) output which you
can load into your favourite diagramming application such as e.g.
Omnigraffle, yEd or Dia:

http://www.sqlalchemy.org/trac/wiki/UsageRecipes/SchemaDisplay

Since this can be automated, it's nice for documentation work.

For inclusion in LaTeX documents, the dot output can then be converted
to TikZ with dot2tex, which is also implemented in Python:

http://www.fauskes.net/code/dot2tex/documentation/

If you're looking for DB modeling tools, here's a pretty comprehensive
list:

http://www.databaseanswers.org/modelling_tools.htm

Sincerely,

Wolfgang Keller

On Tue, Aug 14, 2012 at 5:24 AM, Robert Gravsjö <robert.gravsjo@imano.se> wrote:

> Can anyone advice about a tool to visualize a database schema? Ideally, I would like something that takes the SQL definition of a schema or database
> (essentially the output of pg_dump) and produces a graphical representation of the tables, constraints and indexes which can be moved around for ease of
> visualization (something like the "Graphical query builder" that comes with pgAdmin =>1.14).

A simple method for this, open source cross-platform and work for various RDBMS, is the following:
 1. Download  SQL Power Architect at https://code.google.com/p/power-architect/downloads/list (it's a java app so you need a JVM installed and JDBC drivers for the RDBMS to use)

+1 on SQL Power Architect.  I use it a lot.

--Scott

 

2. Run it, right click in left pane and choose "Add source connection" -> "New connection..." to create a connection for your db
3. Expand the connection  and the database and then simply drag and drop the schema to the right pane.
4. Click on "Automatic layout" in the toolbar (it's the icon that looks like three connected boxes and a green triangle)

>From there you can examine the database. There are some limitations, constraints for example, so for more complicated tools
look into ERD-tools as suggested by John in another reply.

Regards,
roppert

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Aug 14, 2012 at 12:14 PM, Wolfgang Keller <feliphil@gmx.net> wrote:
>> Can anyone advice about a tool to visualize a database schema?
>
> SQLalchemy, a Python module, can produce dot (Graphviz) output which you
> can load into your favourite diagramming application such as e.g.
> Omnigraffle, yEd or Dia:
>
> http://www.sqlalchemy.org/trac/wiki/UsageRecipes/SchemaDisplay

You just made my day -- great stuff.  ERD generation should be
automatic -- whenever I lay them out I feel like I'm fingerpainting.

merlin

> >> Can anyone advice about a tool to visualize a database schema?
> >
> > SQLalchemy, a Python module, can produce dot (Graphviz) output
> > which you can load into your favourite diagramming application such
> > as e.g. Omnigraffle, yEd or Dia:
> >
> > http://www.sqlalchemy.org/trac/wiki/UsageRecipes/SchemaDisplay
>
> You just made my day -- great stuff.  ERD generation should be
> automatic -- whenever I lay them out I feel like I'm fingerpainting.

<hint>

Unlike postgresql_autodoc, which cannot be "embedded" with Pgadmin,
since it is implemented in Perl, which has an embedding-hostile
License as someone once told me, this tiny script could easily be
embedded with Pgadmin, since Python's license is deliberately
embedding-friendly.

And with dot2tex, it would be even possible to generate a complete,
printable, well-typographed PDF documentation of any PostgreSQL database
from within Pgadmin, through LaTeX.

</hint>

Sincerely,

Wolfgang

On Wed, Aug 15, 2012 at 01:48:45PM +0200, Wolfgang Keller wrote:
> > >> Can anyone advice about a tool to visualize a database schema?
> > >
> > > SQLalchemy, a Python module, can produce dot (Graphviz) output
> > > which you can load into your favourite diagramming application such
> > > as e.g. Omnigraffle, yEd or Dia:
> > >
> > > http://www.sqlalchemy.org/trac/wiki/UsageRecipes/SchemaDisplay
> >
> > You just made my day -- great stuff.  ERD generation should be
> > automatic -- whenever I lay them out I feel like I'm fingerpainting.
>
> <hint>
>
> Unlike postgresql_autodoc, which cannot be "embedded" with Pgadmin,
> since it is implemented in Perl, which has an embedding-hostile
> License as someone once told me, this tiny script could easily be
> embedded with Pgadmin, since Python's license is deliberately
> embedding-friendly.
>

I could not get the script sqlalchemy_schemadisplay3.py to work with
sqlalchemy 0.7.8-1 (on Debian).

> And with dot2tex, it would be even possible to generate a complete,
> printable, well-typographed PDF documentation of any PostgreSQL database
> from within Pgadmin, through LaTeX.

I did not know about dot2tex.  That opens a new door for using graphviz
with Latex for me. Thanks.

Postgresql-autodoc also generates a .dot file.

I would like to test the sqlalchemy route also.

Regards
Johann
--
Johann Spies                            Telefoon: 021-808 4699
Databestuurder /  Data manager

Sentrum vir Navorsing oor Evaluasie, Wetenskap en Tegnologie
Centre for Research on Evaluation, Science and Technology
Universiteit Stellenbosch.

     "And whatsoever ye shall ask in my name, that will I
      do, that the Father may be glorified in the Son."
                                          John 14:13
E-pos vrywaringsklousule

Hierdie e-pos mag vertroulike inligting bevat en mag regtens geprivilegeerd wees en is slegs bedoel vir die persoon aan
wiedit geadresseer is. Indien u nie die bedoelde ontvanger is nie, word u hiermee in kennis gestel dat u hierdie
dokumentgeensins mag gebruik, versprei of kopieer nie. Stel ook asseblief die sender onmiddellik per telefoon in kennis
envee die e-pos uit. Die Universiteit aanvaar nie aanspreeklikheid vir enige skade, verlies of uitgawe wat voortspruit
uithierdie e-pos en/of die oopmaak van enige l��s aangeheg by hierdie e-pos nie. 

E-mail disclaimer

This e-mail may contain confidential information and may be legally privileged and is intended only for the person to
whomit is addressed. If you are not the intended recipient, you are notified that you may not use, distribute or copy
thisdocument in any manner whatsoever. Kindly also notify the sender immediately by telephone, and delete the e-mail.
TheUniversity does not accept liability for any damage, loss or expense arising from this e-mail and/or accessing any
filesattached to this e-mail. 

> I could not get the script sqlalchemy_schemadisplay3.py to work with
> sqlalchemy 0.7.8-1 (on Debian).

Have you asked on the SQLalchemy mailing list?

http://www.sqlalchemy.org/support.html#mailinglist

Sincerely,

Wolfgang

> Concerning auto-layout, most if not all tools I have used up to now
> make a mess for anything that is not dead simple.

If a data model can not be reasonably "untangled" by an auto-layout
algorithm (such as e.g. Graphviz) for display as a human-readable graph,
wouldn't that mean that this model is a mess from the modeling point of
view?

In fact, shouldn't reasonably well-designed data models at least mostly
follow SER principles? In that case, they could be displayed
essentially as a tree.

Could the "messy-ness" (or not) of the display of a data model (given
a standard alorithm such as Graphviz) be used as a criterion to judge
whether the model is actually well-structured?

Sincerely,

Wolfgang

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Wolfgang Keller
> Sent: Friday, August 17, 2012 9:08 AM
> To: pgsql-general@postgresql.org
> Subject: Messy data models (Re: [GENERAL] Visualize database schema)
>
> > Concerning auto-layout, most if not all tools I have used up to now
> > make a mess for anything that is not dead simple.
>
> If a data model can not be reasonably "untangled" by an auto-layout
> algorithm (such as e.g. Graphviz) for display as a human-readable graph,
> wouldn't that mean that this model is a mess from the modeling point of
> view?

No

>
> In fact, shouldn't reasonably well-designed data models at least mostly
> follow SER principles? In that case, they could be displayed essentially
as a
> tree.

No - and what the heck are "SER principles"?

>
> Could the "messy-ness" (or not) of the display of a data model (given a
> standard alorithm such as Graphviz) be used as a criterion to judge
whether
> the model is actually well-structured?

No

>
> Sincerely,
>
> Wolfgang
>

I speak with little actual experience but:

The issue with layout algorithms is that it is difficult to incorporate
semantic knowledge of the model into the layout and they generally will not
duplicate nodes in order to improve the layout.

If you help the algorithm out by designing meaningful schemas (i.e.,
namespaces) and require that the algorithm "import a copy" of any relations
located in other schemas that would be a start.  You'd then have one view of
inter-schema relations and another of intra-schema relations with external
relations noted but minimized to the bare necessities.

Also, in some models, there are relations that are so prevalent that
including all them just adds noise to the layout when a top-level
description would be just as clear and remove the extra lines from the
graph.  How to code a pure layout algorithm to be able to identify those
situations (with our without a standard naming scheme to help it) and create
meaningful "text summaries" while removing the corresponding paths I do not
know but it would also go a long way toward visually simplifying complex
models.

David J.

> > In fact, shouldn't reasonably well-designed data models at least
> > mostly follow SER principles? In that case, they could be displayed
> > essentially
> as a
> > tree.
>
> No - and what the heck are "SER principles"?

Structured Entity Relationship model.

It means that the dependency graph does not contain directed cycles
("hen-and-egg"-type foreign key relations).

Sincerely,

Wolfgang

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Wolfgang Keller
Sent: Friday, August 17, 2012 9:08 AM
To: pgsql-general@postgresql.org
Subject: Messy data models (Re: [GENERAL] Visualize database schema)
[...]

Also, in some models, there are relations that are so prevalent that
including all them just adds noise to the layout when a top-level
description would be just as clear and remove the extra lines from the
graph.  How to code a pure layout algorithm to be able to identify those
situations (with our without a standard naming scheme to help it) and create
meaningful "text summaries" while removing the corresponding paths I do not
know but it would also go a long way toward visually simplifying complex
models.

David J.

[...]

I agree with almost all of what David wrote, but I particularly want to reinforce what he says in the last paragraph above.

For example, a lot of tables may have multiple comments associated with individual rows. Since each comment will have a userid, date/time stamp, and text – they will be stored in the own table and referenced via a foreign key. Drawing lines from each affected table to the Comment table would add a lot of clutter with negligible gain.

Similarly, if access to rows in a particular table (and many tables were affected) depended on the company a user belonged to, then this would also add lots of lines...

Cheers,
Gavin

On Thu, Aug 16, 2012 at 04:04:48PM +0200, Wolfgang Keller wrote:
> > I could not get the script sqlalchemy_schemadisplay3.py to work with
> > sqlalchemy 0.7.8-1 (on Debian).
>
> Have you asked on the SQLalchemy mailing list?

No.

Thanks for the link.

Regards
Johann

--
Johann Spies                            Telefoon: 021-808 4699
Databestuurder /  Data manager

Sentrum vir Navorsing oor Evaluasie, Wetenskap en Tegnologie
Centre for Research on Evaluation, Science and Technology
Universiteit Stellenbosch.

     "But I would not have you to be ignorant, brethren,
      concerning them which are asleep, that ye sorrow not,
      even as others which have no hope. For if we believe
      that Jesus died and rose again, even so them also
      which sleep in Jesus will God bring with him."
                                I Thessalonians 4:13,14
E-pos vrywaringsklousule

Hierdie e-pos mag vertroulike inligting bevat en mag regtens geprivilegeerd wees en is slegs bedoel vir die persoon aan
wiedit geadresseer is. Indien u nie die bedoelde ontvanger is nie, word u hiermee in kennis gestel dat u hierdie
dokumentgeensins mag gebruik, versprei of kopieer nie. Stel ook asseblief die sender onmiddellik per telefoon in kennis
envee die e-pos uit. Die Universiteit aanvaar nie aanspreeklikheid vir enige skade, verlies of uitgawe wat voortspruit
uithierdie e-pos en/of die oopmaak van enige l��s aangeheg by hierdie e-pos nie. 

E-mail disclaimer

This e-mail may contain confidential information and may be legally privileged and is intended only for the person to
whomit is addressed. If you are not the intended recipient, you are notified that you may not use, distribute or copy
thisdocument in any manner whatsoever. Kindly also notify the sender immediately by telephone, and delete the e-mail.
TheUniversity does not accept liability for any damage, loss or expense arising from this e-mail and/or accessing any
filesattached to this e-mail.