DbSchema Designer | Tool Presentation
In this article, you will get a quick overview of the DbSchema database designer.
DbSchema Model
Using DbSchema you can design, query, and manage any SQL and few NoSQL databases, including
PostgreSQL, MySql, MariaDB, Oracle, Azure, Google Big Query, MongoDB, and others.
You can start a model by connecting to an existing database and reverse engineer the schema, by designing a new model without any database or by loading the model from a schema SQL script file.
If you choose to connect to an existing database, the Connection Dialog will automatically download the JDBC driver.
You will have to configure the host where the database is running, the database user, and password.
If you are connecting to a cloud database, you can tick the ‘Edit the JDBC URL’ and copy the JDBC URL from the cloud console.
Once this is done, DbSchema will automatically create few layouts with the schema tables, and some data tools on the bottom.
On the left, you can see the model structure.
From the menu, you can create new layouts, with the same or different tables. You can drag tables from the left structure pane or add them by clicking the foreign key icon near columns.
Double-click any table, column, or foreign key to edit. From the table dialog, you can create new columns, indexes, or foreign keys.
New tables or columns will be created in both, the DbSchema model and the database. The executed statements in the database can be seen in the SQL History pane, on the left.
Schema Documentation
Add comments to table or columns by editing them. From the menu, you can generate HTML5 documentation, with the vector image of the schema. The comments can be read as mouse-over tooltips.
Schema Synchronization
The DbSchema menu is showing the current connected database.
Clicking the connection combo we can choose to work disconnected from the database (offline).
I add a ‘details’ column to the table ‘store’. As I am offline, the column will be added only to the DbSchema model, and not in the database. No statement will be created in the SQL history pane.
I press the ‘Refresh Schema from the Database’.
I choose to review differences.
In the Schema Synchronization I see the created ‘details’ column exists in the model, but not in the database.
I can choose to create it in the database or drop it from the model.
For the database actions, I can tick the ‘Generate Script’ radio.
In the same way, we can connect to any other database, compare and deploy the model on the database.
From the Schema menu, we can choose to compare the actual model with other model files and generate the SQL migration script.
From the menu, we can save the current model to file. Restarting DbSchema will reopen the last saved model file.
The model file uses the XML format and can be open with any text editor.
DbSchema Tools
DbSchema includes a bunch of tools:
- Relational Data Editor for visualizing data from multiple tables
- Visual Query Builder - compose SQL queries using the mouse
- SQL Editor
- Random Data Generator
- Data Importer
- Database Reports
Relational Data Editor
Use this tool for visualizing data from multiple tables simultaneously. The cascade between tables is using foreign keys or virtual foreign keys.
The virtual foreign keys are similar to the regular foreign keys but will be created only in DbSchema.
Selecting a record in one table will show the matching records in the cascading tables.
Start the Data Explorer from any table header right-click menu, then cascade into further tables by clicking the foreign key icon.
Filter the table data from any column right-click pop-up.
The Data Explorer will be saved to the model file and can be reopened after restarting DbSchema.
Visual Query Builder
Use it to create SQL queries visually.
You can join tables using foreign keys or virtual foreign keys. Click the foreign key icon to add further tables to the editor.
Choose the columns to include in the result by ticking the column checkboxes. Click the join line to choose different join types.
Right-clicking columns we can set filters or add aggregate functions to the query. This will transform the query into a GROUP BY query.
SQL Editor
Use the SQL Editor to run database queries or scripts.
The editor can execute also Java Groovy scripts, with access to the DbSchema API. These scripts can be used for DbSchema automation. See the Help / Code Samples.
Random Data Generator
Application developers and database administrators may need test data for testing the software applications, or database queries. Using the Random Data Generator you can fill the tables with test data.
The generator is using patterns, which can generate numbers in a sequence, random numbers, data from files or text data using reverse regular expressions.
Reports & Application Designer
The designer can build HTML reports with tables, charts, input fields, and buttons.
You can create your first report using the menu wizard.
The designer is using cells, inside which can place labels, tables, or any other components.
