Connect to most popular database types to extract metadata. Generate flexible and dynamic database diagrams with our free Model Xtractor. Export them as Graphviz DOT files. And render them instantly on Graphviz online applications, by simply pasting the content of the exported files.
Table of Contents
From Databases to Online Graphs
This post shows you how to:
- Use our free Model Xtractor to get all sorts of database diagrams from a RDBMS.
- Export any database diagram into a Graphviz DOT compatible file.
- Paste the file content into a Graphviz Online app, to render the graph in a browser.
Another interesting add-in is preview-dot, which locally extends Windows Explorer with .gv/.dot file visualization support in the Preview pane. Display the Preview pane and, each time you select a Graphviz file exported from Model Xtractor, its graph will be instantly rendered beside.
Neither Graphviz nor any Graphviz Online app can extract schema information from a database, to produce a model. But you can use our free Model Xtractor or Data Xtractor applications to do that. They can quickly connect to and extract table and view information from a variety of relational databases, including Oracle, DB2 and Microsoft SQL Server, PostgreSQL and MySQL/MariaDB, SQL Anywhere and SAP/Sybase ASE, SQLite and Firebird, Microsoft Access and SQL CE. Several types of database diagrams are then automatically generated.
Structural Database Diagrams on Graphviz Online
Upon establishing a database connection and importing its metadata, a structural database model, with tables exposing their columns and main relationship connectors, is also generated and open on screen. We call it structural because most tables are expanded and show their structure, i.e. the table and view columns.
Following database diagram is the generated structural model for the Sakila database sample included in MySQL:
Model Xtractor has one Auto Layout command, that we automatically call last for any of these demo models. However, it may not be always suitable for what you need. Or it may not show the tables in best possible way. After manually dragging the shapes around, the following image may better represent the tables and the relationships between:
Export this into a GraphViz DOT file, with the Objects – Export – Diagram as Graph menu command. Then load the .gv or .dot file into a text editor like Notepad or Notepad++. Select and copy all the text in the clipboard. Then go to Viz-js.com and paste the copied content into the left window. The following SVG image is immediately generated in my browser:
That’s an almost equivalent image, with icons and some other elements or styles not exported. You may find all export limitations and the other supported graph formats here.
Custom Database Diagrams on Graphviz Online
Like no other database diagram viewer, Model Xtractor allows you to collapse or minimize shapes, hide individual shape items, move shapes around as you wish, display many-to-many conceptual relationships (bypassing the hidden intersection table), or show relationships as special shape items.
Following diagram is just a customization of the previous automatic view. We switched to a MySQL Workbench-like theme. We displayed simplified shapes with corners, relationships with plain lines, shape items with collapsible categories (hidden relationship items), and no icons at all. Some shapes are collapsed (with their name only), others fully minimized (like plain bubbles):
Export now this customized diagram into another Graphviz DOT file, and paste its content into your Graphviz Online application, like before. Switch this time between the existing layout engines. We found this fdp image pretty interesting:
The database diagrams don’t have to be identical, but we use here very similar styles. Layout is always different, because we never send current shape positions to the Graphviz Online app. Graphviz is strong at diagram layouts, so we let it do its job. However, the generated images are always static, in the sense you cannot manually grab a shape and drag it elsewhere. As you can do in our interactive visual designer.
Graphviz Online for Large Databases
AdventureWorks is a large sample database produced by Microsoft and used for its SQL Server RDBMS. It has over 70 tables and over 20 views. Such databases are closer to the real-world enterprise use cases. We may have corporate databases with hundreds of tables, but one database with around one hundred is more common.
If you look at the automatically generated models for this imported database, you’ll see they’ve been divided into different schemas. The whole database is a catalog, and each table is prefixed by its schema name. Under the Tables node, we identified and separated the schema nodes as dbo, HumanResources, Person, Production, Purchasing, and Sales. Our products allow you to pick just a few tables and views for each diagram. Long gone the times when you needed one huge surface with all your tables in one ER diagram.
However, some relationships between tables may be missed, and some people still prefer to display all tables together. You can right-click on the Tables node, and use the Add to New Model – Relationship Model contextual menu command. As we have almost a hundred tables, we’ll show them all collapsed, without their column names, but with the relationships expanded, as links:
As you can see, our Auto Layout for large databases cannot yet do better. This is just a portion of the database diagram, and it looks complex. Try to export it and render it on Graphviz Online, as we did until now:
The diagram is rendered with the dot engine, and it already looks better. Try the osage engine, and you may get a different interesting perspective:
Conclusion: use Model Xtractor to “extract” raw database diagrams, and export them easily to Graphviz Online for all sorts of layouts. This will certainly help you better understand relationships between huge numbers of tables.
Graphviz Online with Database Topologies
Under the Tables node for an AdventureWorks imported database, identify the Production schema. Right-click on the Production node, and use Add to New Model – Graph Model. A new database diagram will be generated, with all these tables. But, unlike other types of models, this diagram shows only simple table names with any relationships between displayed as plain lines:
When exported to Graphviz Online the typical way, you get a potentially better view. Switch between the layout engines for other types of graphs. This one is rendered with the default dot engine:
Back to Model Xtractor, right-click on the Production node, but use Add to New Model – Topological Model instead. Unlike all other types of models, the new diagram shows tables as plain bubbles, with any relationships between displayed with plain lines.
Export it and render it on Graphviz Online, the way we did before. Topologies for large databases, with a large number of tables, are certainly displayed better in the browser, especially by choosing a different layout engine each time:
The new Export – Diagram as Graph feature has been added to Model Xtractor because we want you to fully take advantage of the powerful layout engines other dedicated diagramming apps may be better equipped with. And we’d also like to make our own apps as open and flexible as possible.