SchemaSpy Org ERD

Winter ’12 has a built in ERD viewer – see e.g. An ERD is Worth a Thousand Words: Schema Builder in Winter ’12 – that may meet some or all of your ERD needs so take a look at that first before considering the approach described below.

Here is one way to produce an Entity Relationship Diagram (ERD) of the objects in your Force.com org. (This page is largely a copy of this original post.) Jars and documentation are available in the Google code project force-metadata-jdbc-driver along with the source code.

Some background. I looked around for a data modelling tool for Force.com that can output a complete data model but didn’t find one. (SoqlXplorer for OSX can produce diagrams but is an interactive tool. Force.com Explorer is limited in its beta form. Other suggestions very welcome.) In the past when working with conventional JDBC databases I have used SchemaSpy. The cool thing about this is that the diagrams are automatically laid out in a rational way, and the accompanying HTML provides extra information and hyper-linking. So you get complete documentation of your schema without any manual intervention. In our environment, the model is generated on every Jenkins build.

The opportunity to use SchemaSpy for Force.com comes from the fact that SchemaSpy primarily uses the java.sql.DatabaseMetaData interface part of the JDBC API which is relatively small and easy to implement. So with the benefit of SchemaSpy being open source to allow inspection and debugging, I went ahead and created a JDBC driver that uses the Force.com enterprise (now partner) web service (DescribeSObjectResult, Field etc) to implement the parts of the JDBC API that SchemaSpy uses. That allows SchemaSpy to produce its output for a Force.com org.

The full output consists of multiple diagrams and HTML pages that document all the objects, their fields and the object relationships. Here is an example of one of the diagrams:

This project is also linked from the Force.com Code Share project Force Metadata JDBC Driver for SchemaSpy – you can add review comments there.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s