Back to articles list
-
6 minutes read
Manage Database Model Changes Easily With the New Model Explorer in Vertabelo
Learn about Model Explorer, a new feature in Vertabelo Database Modeler.
Have you noticed that a new feature has been introduced to Vertabelo Database Designer? In this article, we take a deep dive into Model Explorer, an innovative way of managing your database models.
In short, Model Explorer allows for a comprehensive overview of the model and bulk editing of multiple elements at once. At this time, the functionality is available only for the physical model, but in the future, it will also be implemented in the logical model.
Why is it useful? Model Explorer solves various problems of a database designer:
- It allows you to perform large-scale database model changes or database model refactoring (e.g., type changes and naming changes) very quickly using SQL queries. Other tools require clicking on each diagram element. This can be very tedious and time-consuming.
- The tabular layout provides a quick overview of the entire model and its elements (tables, columns, primary keys, etc.). For example, it is useful for detecting naming inconsistencies.
- It also provides a quick way to do a database model review or verify specific properties of given elements. Let’s say you want to take a quick look at all the primary keys in the database. If you can only use the diagram, it’s going to be difficult – you have to click each table to see its primary key. Also, you can't see the properties of two different tables next to each other, making it difficult to compare individual elements of multiple tables.
- Model Explorer facilitates working with elements that do not have their representation on the diagram (e.g., sequences).
Curious about the details? Read on or try Model Explorer now!
The Model Explorer Module
You find the new Model Explorer module in the left-hand panel under Model Structure.
After clicking on Model Explorer, you are presented with the following screen:
Let’s examine each functionality in detail.
The Left-Hand Navigation Panel and the Tabular View
Under the Model Explorer tab, you find a tree structure containing various database elements, such as tables, columns, primary keys, etc. The elements listed here may differ depending on the database engine used in the model. For example, you won’t find indexes in a Snowflake database.
Note that external tables and materialized views are not supported yet. They will be added in the future versions of Model Explorer.
In the middle of the screen, you find an SQL editor and a table showing rows returned by the SQL query. Clicking on a selected item in the left-hand panel changes the SQL query and the data presented in the table.
The tabular view allows you to review specific properties of the model easily. It is also useful for detecting type and naming inconsistencies.
In the future, the tabular view will also allow for the in-place editing of individual elements of the table.
SQL Query Editor
One of the features that make Model Explorer unique is the ability to write your own SQL queries. The engine used underneath is SQLite, so you have the full SQL power at your disposal including clauses such as WHERE, GROUP BY, HAVING, and ORDER BY, as well as aggregate queries.
The available statements are SELECT, UPDATE, and DELETE. The INSERT statement is not supported. As mentioned at the beginning of the article, SQL queries may be useful for refactoring and bulk model updates. For example, you may use the following command to unify the column types of a particular name:
UPDATE table_columns SET data_type = ‘int’ WHERE name = ‘id’
Easy, right? In the future, it will be even more convenient. Autocomplete will help you write queries faster, and the elements that change in the model diagram after running a query will be highlighted.
Previous Queries
At the bottom of the SQL Query Editor, next to the Run button, you find the Previous Queries button:
When you click on it, you are presented with a new window showing the history of the queries run:
Optionally, you may choose to run one of the listed queries again.
Query Model Guide
Next to the Run and Previous Queries buttons, there is an option called Query model guide:
Clicking on it causes a new panel to appear at the bottom of the screen. Here, you find examples of various queries as well as tips regarding syntax and element properties. The guide helps you write SQL queries even if you forget the exact syntax.
Toolbar Options
Now, let’s take a look at the toolbar located above the tabular view.
There are 4 buttons available:
- Delete items - this button may be used when one or more rows are selected in the tabular view. It allows you to remove specific items, such as tables or columns, from the database model.
- Find in diagram - this button may be used when only one row is selected in the tabular view. It redirects the user to the model diagram and highlights the table containing the selected element.
- Export to CSV - this option exports the rows retrieved by the SQL query (the rows are shown in the tabular view) to a CSV file.
- Customize view - this button opens an additional window. It allows you to select the columns shown in the tabular view as well as reorder them. You may also choose how to sort the rows.
Note that you can always come back to the default settings by clicking on the button at the very bottom.
Model Validation
Every time a model is modified using Model Explorer, it validates the model and updates the list of problems. However, it does not present the results anywhere within the module itself. You can see if any new errors or warnings appear by going to the Issues section in the left-hand navigation panel under Model Explorer.
Try Model Explorer Now for Database Model Changes!
We’ve explored all the functionalities of the new Model Explorer module. Stay tuned because more changes are coming! We will soon implement:
- In-place editing of individual elements in the tabular view.
- Autocomplete in the SQL editor.
- And more!
Try out Model Explorer now!
Stay Tuned for More New Features for Database Designers
That’s not all! As you know, Vertabelo Modeler is constantly being improved. Make sure you haven’t missed the latest changes and new features:
