Skip to content

Latest commit

 

History

History
244 lines (184 loc) · 11.4 KB

README.md

File metadata and controls

244 lines (184 loc) · 11.4 KB

GitHub GitHub forks GitHub Org's stars GitHub Workflow Status

DBVisualizer

A tool which creates ER-Diagrams as a PNG, PDF, SVG file or as .dot input file for GraphViz or PlantUML and textual reports (in HTML, MARKDOWN or PDF format) about tables and columns from a database using JDBC metadata retrieval.

Example (generated with PlantUML)

Latest news

2023-01

  • Configuration can be read from a .properties file (new option -C)

2022-12

  • Maven Plugin
  • Direct integration of PlantUML (as dependency). Diagrams in .png, .pdf and .svg format can be generated directly now without the necessity to call PlantUML manually

Examples

Examples

Compilation

Just do

mvn clean install

This creates a "fat" .jar with all dependencies using the maven assembly plugin.

Start

Maven Plugin

For an example how to use DBVisualizer as a maven plugin, just switch to the directory "plugin-demo", edit the configuration section in the pom.xml and run "mvn install".

Documentation

CLI

For convenience all required options (for the database connection and the output generation) can be configured using a .properties file, like this:

database.dialect=PostgreSQL
database.jdbc.url=jdbc:postgresql://[::1]:5432/test
database.jdbc.driver.class=org.postgresql.Driver
database.jdbc.driver.path=c:/Users/_your_name_/.m2/repository/org/postgresql/postgresql/42.3.3/postgresql-42.3.3.jar
output.graphviz.lroption=false
output.diagram.entitiesonly=false
output.diagram.format=PNG
database.user=_replace_with_real_username_
database.password=_replace_with_real_password_
database.schema=test
#not required for PostgreSQL
#database.catalog=
#database.filter=
output.report.format=MARKDOWN
output.report.metadata=true
output.diagram.filename=postgres_test_with_properties.png
output.report.filename=postgres_test_with_properties_report.md

To use such a configuration file, just call

java -jar app/target/dbvisualizer-1.0.0-SNAPSHOT-jar-with-dependencies.jar -C <path to configuration.properties>

You can also specify some of the options in the configuration file and some at runtime. The options specified via command line options will have priority over the values from the properties file.

For example if you specify -a PDF -o test_diagram.pdf when executing DBVisualizer you will get the diagram as .PDF even if the value in the properties file for property "output.diagram.format" was set to PNG.

To get a list of all options call DBVisualizer like this:

java -jar app/target/dbvisualizer-1.0.0-SNAPSHOT-jar-with-dependencies.jar

All command line options are listed:

usage: DBVisualizer [-a <arg>] [-C <arg>] [-c <arg>] [-d <arg>] -driver <arg>
   [-driverpath <arg>] [-e] [-f <arg>] [-F <arg>] [-l] [-m] -o <arg>
   [-p <arg>] -r <arg> [-s <arg>] [-t <arg>] [-u <arg>] -url <arg>

Gets all (matching) tables from given database connection and generates an
outputfile in the specified format (default: .dot)
-a,--format <arg>                      Format: DOT (default), PLANT, PNG,
                                       SVG, PDF
-c,--catalog <arg>                     Name of the catalog to retrieve
                                       tables from. Default: null.
-C,--config <arg>                      Name of a configuration file.
-d,--dialect <arg>                     DB dialect. Possible values are
                                       PostgreSQL, MySQL, Oracle
-driver,--jdbc-driver <arg>            Class name of the JDBC driver
                                       (mandatory).
-driverpath,--jdbc-driver-path <arg>   Path to the driver classes. If
                                       this option is not specified, the
                                       driver is searched in CLASSPATH.
-e,--entities-only                     Show only entities and relations
                                       in output (no attributes/columns).
-f,--filter <arg>                      Regular expression (Java flavor)
                                       which is applied on table names
-F,--report-format <arg>               Format of the Report file.
                                       Supported formats are: html, pdf
                                       and markdown
-l,--enable-lr                         Use GraphViz option ranking=LR;
                                       Graph layout from left to right.
-m,--report-metainformation            Include some meta information
                                       (e.g. report generation date) in
                                       the generated report.
-o,--output-file <arg>                 Name of the output file 
                                       (mandatory).
-p,--password <arg>                    Password for database connection.
-r,--report-file <arg>                 Name of the report file. If
                                       omitted, no report will be
                                       generated.
-s,--schema <arg>                      Name of the schema to retrieve
                                       tables from. Default: all schemas.
-t,--tables <arg>                      TestDBGenerator only: number of
                                       tables.
-u,--user <arg>                        User name for database connection
-url,--jdbc-url <arg>                  JDBC URL (mandatory).

Example (assuming a PostgreSQL database "test" on localhost, port 5432):

java -jar target/dbvisualizer-1-SNAPSHOT-jar-with-dependencies.jar -driverpath ./postgresql-42.3.1.jar -driver org.postgresql.Driver -a PNG -o postgresql_test_diagram.png -d PostgreSQL -s test -u test -p "test123" -url jdbc:postgresql://localhost:5432/test 

Tip: For very large schemas it might be useful to apply an filter and generate several smaller diagrams instead of one large.

Supported Databases

Tested with PostgreSQL,H2, MySQL and OracleXE 18c.

PlantUML

PlantUML is used internally when output formats PNG, SVG or PDF for the diagram are specified. Since there is a dependency on PlantUML defined in the pom of DBVisualizer, no explicit installation of PlantUML is required. If you are interested in the generated input file for PlantUML, the format specification "PLANT" can be used with (commandline option -a). In this case, a text input file for PlantUML is generated, which can be converted into one of the target formats of PlantUML like this (name of the .jar file can be different, -tpdf stands for target format PDF):

java -jar target/plantuml-1.2021.16-SNAPSHOT-jar-with-dependencies.jar -tpdf <path to output of dbvisualizer>

For a full reference of PlantUML Command Line refer to the documentation

GraphViz

By default the output file is in the .dot-format of GraphViz. To generate a .pdf-file just type

dot -Tpdf -odiagram.pdf <outputfile of DBVisualizer> 

You may also try

neato -Goverlap=false -Gmodel=subset -Tpdf -odiagram.pdf <outputfile of DBVisualizer> 

Reports

Using the option -r with a filename, a tabular report on the tables and columns will be written in the specified file. Following formats (option -F) are supported:

  • HTML (default)
  • MARKDOWN
  • PDF

For PDF the library easytable is used.

TestDBGenerator

If you do not have access to a complex schema, you can generate one using TestDBGenerator. It creates either an SQL DDL (DDL - Data Definition Language) file or directly a .dot file. I've used the DDL primarily for end to end testing of DBVisualizer. The examples have been generated with TestDBGenerator as well.

Start TestDBGenerator like this:

java -cp target/dbvisualizer-1-SNAPSHOT-jar-with-dependencies.jar com.skuehnel.dbvisualizer.util.TestDBGenerator

Command line options are:

usage: TestDBGenerator -f <arg> -n <arg> -t <arg>
Creates a DDL .sql file or a .dot file with some randomly generated
tables.
 -f,--file <arg>   Name of the output file.
 -n,--num <arg>    Number of tables to be generated.
 -t,--type <arg>   Type of test file. Either sql or dot.

Native-image build with GraalVM

If you want to leverage GraalVM's capability to build native-images, you can do so by following the below steps:

  • Install GraalVM on your system.
  • Install the native-image functionality of GraalVM.
  • Depending on your OS, the linker might require libfreetype.a during the native-image build of this project; in my case (Ubuntu 20.04) I did it via sudo apt-get install libfreetype6-dev.
  • If not already done, clone this project with git.
  • Add the driver of the database you want to build this native-image for to the dependency list in pom.xml. E.g. for PostgreSQL I added:
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.3.0</version>
    </dependency>
    
  • From the root folder of the project build the app as usual with mvn clean install
  • Now, we will execute the application as we usually would, but with an agent attached that collects configurations for the native-image build and puts into the right folder:
    java -agentlib:native-image-agent=config-output-dir=app/src/main/resources/META-INF/native-image -jar app/target/dbvisualizer-app-1.0.0-SNAPSHOT-jar-with-dependencies.jar -driver org.postgresql.Driver -a PLANT -o app/target/postgresql_test.puml -d PostgreSQL -u <USER> -p <PASSWORD> -url <JDBC_URL> -r app/target/postgresql_test.html
    
    You will be able to see the files in app/src/main/resources/META-INF/native-image.
  • We run mvn clean install again. The first time we did it was just for being able to collect the required configurations. Now we build the app including the configurations.
  • Switch into the target folder: cd app/target
  • Next, create the native-image from the jar we just built by executing:
    native-image -jar dbvisualizer-app-1.0.0-SNAPSHOT-jar-with-dependencies.jar
    
  • To finally run the native-image, execute:
    ./dbvisualizer-app-1.0.0-SNAPSHOT-jar-with-dependencies -driver org.postgresql.Driver -a PLANT -o ../postgresql_test.puml -d PostgreSQL -u <USER> -p <PASSWORD> -url <JDBC_URL> -r ../postgresql_test.html
    

Ideas for future enhancements

List of some features, which might be added in the future:

  • Add more automated tests for different databases
  • run as REST service and provide a web UI, probably using a JS framework like visjs.org or D3js for visualization