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

Hi,

I use to visualize a database schema Quantum GIS or UDIG. Both are open source software and very esay to add a database schema.

Best Regards,
José Santos

Date: Tue, 14 Aug 2012 09:54:34 +0100
Subject: [GENERAL] Visualize database schema
From: dario.beraldi@gmail.com
To: pgsql-general@postgresql.org

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

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)
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

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 wie dit geadresseer is. Indien u nie die bedoelde ontvanger is nie, word u hiermee in kennis gestel dat u hierdie dokument geensins mag gebruik, versprei of kopieer nie. Stel ook asseblief die sender onmiddellik per telefoon in kennis en vee die e-pos uit. Die Universiteit aanvaar nie aanspreeklikheid vir enige skade, verlies of uitgawe wat voortspruit uit hierdie 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 whom it is addressed. If you are not the intended recipient, you are notified that you may not use, distribute or copy this document in any manner whatsoever. Kindly also notify the sender immediately by telephone, and delete the e-mail. The University does not accept liability for any damage, loss or expense arising from this e-mail and/or accessing any files attached 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

Concerning auto-layout, most if not all tools I have used up to now make a
mess for anything that is not dead simple. One exception I found is
Embarcadero Data Architect (
http://www.embarcadero.com/products/er-studio-data-architect). It's not
free, but there is a trial you can use and then you can export models as
pdf or w/e.

Sébastien

On Tue, Aug 14, 2012 at 11:07 AM, Zdeněk Bělehrádek <
zdenek.belehradek@superhosting.cz> wrote:

This is nice:
http://sourceforge.net/projects/mogwai/?source=directory

The install is fiddly, and the interface is JDBC. But it does a very good job of reverse engineering a schema and graphically displaying it.

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Dario Beraldi
Sent: Tuesday, August 14, 2012 1:55 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Visualize database schema

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

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

Em 14/08/2012 15:47, Scott Mead escreveu:

On Tue, Aug 14, 2012 at 5:24 AM, Robert Gravsjö
<robert.gravsjo@imano.se <mailto: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

I like SQL Power Architect, but IMHO it has a big flaw: you can't create
separate tables in several (logical) diagrams, you can have only one big
diagram for entire database (please, correct me if I'm wrong!).

At this point, I do prefer DbWrench (besides it is not free, it's cheap
and works fairly well - but support is weak).

Regards,

Edson

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

Many thanks to everybody for advice! I'll give it a go to the tools you
suggested.

Dario

On 14 August 2012 09:54, Dario Beraldi <dario.beraldi@gmail.com> 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.

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 wie dit geadresseer is. Indien u nie die bedoelde ontvanger is nie, word u hiermee in kennis gestel dat u hierdie dokument geensins mag gebruik, versprei of kopieer nie. Stel ook asseblief die sender onmiddellik per telefoon in kennis en vee die e-pos uit. Die Universiteit aanvaar nie aanspreeklikheid vir enige skade, verlies of uitgawe wat voortspruit uit hierdie 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 whom it is addressed. If you are not the intended recipient, you are notified that you may not use, distribute or copy this document in any manner whatsoever. Kindly also notify the sender immediately by telephone, and delete the e-mail. The University does not accept liability for any damage, loss or expense arising from this e-mail and/or accessing any files attached 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.

Short answer: no. Even with a good auto-layout, nothing (up to now) beats a
human made one because the latter will incorporate semantic which is not
available to the modeling tool; for example, positioning, spacing and
routing of relations will respect some sense of aesthetic and organization
that are quite subjective. The only practical solution to untangle a
complex model is to split it into sub-models and use aliases to reference
tables in another sub-model.

Sébastien

On Fri, Aug 17, 2012 at 9:07 AM, Wolfgang Keller <feliphil@gmx.net> wrote:

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