I think ... - visualizationhttps://blog.kmonsoor.com/2014-12-18T00:00:00+06:00Generate ER diagram from a SQL-based database2014-12-18T00:00:00+06:002014-12-18T00:00:00+06:00Khaled Monsoortag:blog.kmonsoor.com,2014-12-18:/generate-er-diagram-from-sql-database/<p>When you are &ldquo;study&rdquo;-ing someone else&rsquo;s database with 300+ tables. It&rsquo;s like spaghetti, but not enjoyable. Rather,&nbsp;horrific.</p><p>When you are &ldquo;study&rdquo;-ing (for whatever reason) someone else&rsquo;s database, and the database has more than 20 tables, you might be in trouble to understand what&rsquo;s going&nbsp;where.</p> <p>Now, imagine a database with 300+ tables. It&rsquo;s like spaghetti, just not as&nbsp;enjoyable.</p> <p>I faced a similar challenge recently with a database of 250+ tables. Yes, I felt like in a deep sh*t. And, I started looking for tools that can describe the tables, or at least a decent <span class="caps">ER</span> diagram. If anything more, better. And, preferably&nbsp;free.</p> <p>Then, I found <a href="http://schemaspy.sourceforge.net/">SchemaSpy</a>, originally authored by <a href="https://sites.google.com/site/johncurrier/">John Currier</a>. It generates a complete in-depth <span class="caps">HTML</span>-based description (of course, including clickable <span class="caps">ER</span>-diagram) of the database, which you can then browse with your browser. This post is about its primary&nbsp;usage.</p> <p>The output will be kind of like this: <img alt="schemapy output sample" src="https://i.imgur.com/K1yYBID.png"></p> <p>It is based on Java, but it can work its magic on most of the major database products. However, it would require an appropriate <span class="caps">JDBC</span> connector for that&nbsp;database.</p> <p>Quote from the&nbsp;author:</p> <blockquote> <p><code>SchemaSpy</code> uses <span class="caps">JDBC</span>&rsquo;s database metadata extraction services to gather the majority of its information but has to make vendor-specific <span class="caps">SQL</span> queries to gather some information such as the <span class="caps">SQL</span> associated with a view and the details of check&nbsp;constraints.</p> </blockquote> <p>In this post, as an example, I have shown how to use it with PostgreSQL. But, it&rsquo;s not the only one that&rsquo;s supported. You can use it with any proper <span class="caps">RDBMS</span> system as long as it has a <span class="caps">JDBC</span>-connector. Now, to use it, you need to get these&nbsp;stuffs.</p> <ul> <li><strong>First of all</strong>, your system should have <strong>Java runtime</strong> properly installed. <a href="https://adoptopenjdk.net/">Download from&nbsp;here.</a></li> <li><strong>SchemaSpy, which is a .jar file</strong>. <a href="https://sourceforge.net/projects/schemaspy/files/">Get it here</a>. At the time of writing, it was version&nbsp;5.0.0.</li> <li><strong><span class="caps">JDBC</span> connector to PostgreSQL</strong>. Make sure to match your PostgreSQL version. You can <a href="https://jdbc.postgresql.org/download.html">download it from here</a>.<br> You can check your PostgreSQL version by executing: <code>SELECT version();</code> query on <strong><em>psql</em></strong>&nbsp;prompt.</li> <li>Also, SchemaSpy depends on <strong>GarphViz</strong> to generate the <span class="caps">ER</span> diagrams, so you need to be installed it on your system. Get it from here.(<a href="http://www.graphviz.org/Download..php">http://www.graphviz.org/Download..php</a>)</li> <li>And, of course, make sure the target database instance is running <span class="amp">&amp;</span> serving the database you are trying to&nbsp;visualize.</li> </ul> <p>Quoting from the&nbsp;author:</p> <blockquote> <p>SchemaSpy uses the dot executable from <a href="http://www.graphviz.org/">Graphviz</a> to generate graphical representations of the table/view relationships. The visual representation of the connections is a fundamental feature of the&nbsp;tool. </p> <p>Graphviz is not required to view the output generated by SchemaSpy, but <strong>the dot program should be in your <span class="caps">PATH</span></strong> (not <span class="caps">CLASSPATH</span>) when running SchemaSpy, or none of the entity-relationship diagrams will be generated. Or, maybe <a href="http://schemaspy.sourceforge.net/#gvparam">use the <code>-gv</code></a>&nbsp;option).</p> </blockquote> <p>I kept the .jar files (both the <span class="caps">JDBC</span>-connector and the <code>SchemaSpy</code>) in my home folder for convenience.<br> Now, in my case, my <span class="caps">OS</span> is Linux, and the database is hosted locally; hence address is <code>127.0.0.1</code>, running <code>PostgreSQL-9.3</code> at port <code>5432</code>. So, I run the command like&nbsp;this:</p> <div class="highlight"><pre><span></span><code><span class="linenos" data-linenos="1 "></span> $ java -jar ./schemaSpy_5.0.0.jar -t pgsql -host 127.0.0.1:5432 -db your_database_name \ <span class="linenos" data-linenos="2 "></span> -u your_DB_user_name -p your_password -s public \ <span class="linenos" data-linenos="3 "></span> -dp ./postgresql-9.3-1102.jdbc3.jar \ <span class="linenos" data-linenos="4 "></span> -o output_folder </code></pre></div> <p>It may take a little while, depending on the size of the schema of the database.<br> After that, you will find the output folder/directory named <code>output_folder</code>.<br> You&rsquo;ll see some output when the magic is going on, similar to this&nbsp;below.</p> <div class="highlight"><pre><span></span><code><span class="linenos" data-linenos="1 "></span>Using database properties: [./schemaSpy_5.0.0.jar]/net/sourceforge/schemaspy/dbTypes/pgsql.properties <span class="linenos" data-linenos="2 "></span>Gathering schema details....................(6sec) <span class="linenos" data-linenos="3 "></span>Writing/graphing summary....................(2sec) <span class="linenos" data-linenos="4 "></span>Writing/diagramming detail..................(31sec) <span class="linenos" data-linenos="5 "></span>Wrote relationship details of 113 tables/views to directory &#39;output&#39; in 41 seconds. <span class="linenos" data-linenos="6 "></span> <span class="linenos" data-linenos="7 "></span>View the results by opening output_folder/index.html </code></pre></div> <p>Now, all the generated files are in the <code>output_folder</code>. Start your journey by starting from the <code>index.html</code> in the output folder. Open it by using any&nbsp;browser.</p> <p>Good luck.&nbsp;:)</p> <hr> <p>If you find this post helpful, you can show your support <a href="https://www.patreon.com/kmonsoor">through Patreon</a> or by <a href="https://ko-fi.com/kmonsoor">buying me a coffee</a>. <em>Thanks!</em></p>