Reverse Engineering With Vertabelo

Learn how to import your database into Vertabelo and generate its data model with our improved reverse engineering feature.

We’re excited to announce the release of a new version of our popular reverse engineering tool. You've been asking for a user-friendly interface, and we heard you. If working with Java is not your thing, our new graphical user interface lets you click through the process in no time.  Download the upgraded reverse engineering tool here.

What Is Vertabelo’s Reverse Engineering Tool?

This tool connects to a running database, reads its schema (tables, references, views), and generates an XML file that you can upload into Vertabelo.

Remember that you can also upload SQL files into Vertabelo. If you have an SQL file with your database’s CREATE statements, you can find out how to upload the file here.

Getting Started with Reverse Engineering a Database

First, download the ZIP archive with the reverse engineering tool and unzip it.

The downloaded archive includes the README file, which contains information on how to use the reverse engineering tool from either the graphical user interface or the command line. Alternatively, just keep reading this article.

Using the GUI

To run the reverse engineering tool via the GUI, you’ll need to perform one of the following actions (depending on your operating system):

• Type in ./run in your terminal.
• Double click the run executable
• Open the terminal, drag the run executable to the terminal window, and hit Return/Enter.

A window like the one above will pop up. Enter the following connection data:

• Host – The URL / IP address at which your database is running; this is often equal to localhost if it runs on your local computer.
• Port – The port number the database is listening on; you can find your database’s default port number in its documentation.
• Database – The name of the database you want to connect to.
• User – The username you want to use for the connection.
• Password – The password for the above user.

The JDBC URL field is generated automatically; we'll talk about JDBC later in this article.

Type in your connection data and click Test connection to verify that your settings are correct.

You can specify what tables and/or views you want to read. To specify the list of tables to read, just type in the names separated with a vertical line | (a separator in regular expressions) in the Tables/View regex field. As this is a regular expression, watch out for special characters such as dots and dashes. (Put a backslash before such characters to avoid problems.) If you want to specify a more complex regular expression to filter table names, read this article on filtering tables in Vertabelo.

In the UI, you can also select options to:

• Have the names changed to lowercase (Change names to lower case).
• Attach information about the schema for a given table (Include schema)
• Make the output verbose (Verbose output).
• Only look at specified schemas for your database.

Finally, check the path where the XML file will be generated. When you're done, click on the Generate button. Any errors and the current status will appear in the log window. Let's take a closer look at part of the log:

Extracting table information
..o.o.
Extracting view information

Extracting reference information

Extracting sequence information
Preparing xml...
Preparing xml done.
Drawing the graph


Found:
   6  table(s)
   0  view(s)
   7  reference(s)
   5  sequence(s)

You may wonder what the dots and o's are. This hasn't changed since the previous version of the reverse engineering tool. The dot . represents a small table (no more than 5 columns); the little o represents a medium-size table, and the big O represents a big table (over 20 columns).

A JDBC driver is a Java library that the reverse engineering tool uses to read data from a database. As you may have noticed, the tool comes with just a few drivers. Due to licensing issues, we can't share many drivers with you. But if you have the license to use your database, you can usually use the JDBC driver for that database.

Below are links where you can download drivers for popular databases. JDBC drivers for databases marked orange don't come bundled with our reverse engineering tool; you’ll need to download them yourself if you need them.

Database	Links	Connection URL
PostgreSQL	Download	jdbc:postgresql://[HOST][:PORT]/DATABASE
IBM DB2	Download	jdbc:db2://[HOST][:PORT]/DATABASE_NAME
Oracle	Download	jdbc:oracle:thin:@//[HOST][:PORT]/SERVICE
MS SQL Server	Download	jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]
MySQL	Download	jdbc:mysql://[HOST][:PORT]/DBNAME
HSQLDB	Download (The driver is in hsqldb-*.jar file.) Documentation	jdbc:hsqldb:mem: jdbc:hsqldb:file: jdbc:hsqldb:http:host:port/dbname
SQLite	Download	jdbc:sqlite:path_to_file.db
Amazon Redshift	Download	jdbc:redshift://endpoint:port/database

What to do with the downloaded driver, you ask? Copy the .jar file to the drivers/ folder and run the tool again (or exit and restart the GUI). Note that in the GUI, the name of the database for which you've just added the driver is selectable now.

Working from the Command Line

To get help for the tool, type in ./run -help. Available options in the CLI are the same as for the GUI. Here's the list:

-gui	Graphical user interface mode
-h, -help, --help	Print this message and exit
-o	Output file name
-url	JDBC connection URL
-user	Database user (optional)
-password	Database password (optional)
-driver	JDBC driver name (optional) If not specified, a default driver name will be used.
-connection_properties	Connection properties file (optional)
-regexp	Extract only tables and views matching the given regular expression (optional). See this article for more details.
-schema	Space-separated list of database schemas to read (optional)
-lowercase	Changes all model elements’ names to lowercase
-include-schema	Extract table schema
-verbose	Enable verbose output

 

The necessary minimum to run the reverse engineering tool is:

./run -url JDBC_URL -user USERNAME -password PASSWORD -o OUTPUT_FILENAME

Tip: if you don't feel comfortable writing the JDBC URL on your own, run the GUI, generate the URL once, and re-use it as many times as you need.

Uploading the Generated XML File

Whichever options you've chosen, the XML file will be the same. Let's see how to import the database schema into Vertabelo.

1. Log in to my.vertabelo.com.

2. Click on the New Document icon.

3. From the list, pick Physical data model.

4. Fill in the fields.

In the new pop-up:

1. Type in the name.
2. Pick your database engine.
3. Select the From XML
4. Choose the file.
5. Click Import XML.
6. Make sure the import was successful.
7. Click Start modeling.

5. Enjoy your model!

And that’s it – you’ve used Vertabelo’s reverse engineering feature to successfully generate a diagram of your existing database!