DbSchema Presentation



DbSchema 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.

Design New Database Model

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 ‘Connect using 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.

DbSchema Interface

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.

DbSchema Layouts

Double-click any table, column, or foreign key to edit. From the table dialog, you can create new columns, indexes, or foreign keys.

Create new table visually

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.

SQL History

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.

HTML5 Documentation

Schema Synchronization

The DbSchema menu is showing the current connected database.

Connected database

Clicking the connection combo we can choose to work disconnected from the database (offline).

Offline mode

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.

Add column in offline mode

I press the ‘Refresh Schema from the Database’.

Refresh schema from the database

I choose to review differences.

Refresh schema 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.

Schema Synchronization Dialog

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.

Save Model to 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 Explorer 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 Explorer

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.

Relational Data Explorer

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.

Cascade in the Relational Data Explorer

Filter the table data from any column right-click pop-up.

Relational Data Explorer Filters

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.

Visual Query Builder

You can join tables using foreign keys or virtual foreign keys. Click the foreign key icon to add further tables to the editor.

Join tables to the Visual Query Builder

Choose the columns to include in the result by ticking the column checkboxes. Click the join line to choose different join types.

Visual Query Builder tick columns

Right-clicking columns we can set filters or add aggregate functions to the query. This will transform the query into a GROUP BY query.

Visual Query Builder aggregate

SQL Editor

Use the SQL Editor to run database queries or scripts.

SQL Editor

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.

Java Groovy

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.

Random Data Generator

The generator is using patterns, which can generate numbers in a sequence, random numbers, data from files or text data using reverse regular expressions.

Database Reports

The reports designer can build HTML reports with tables, charts, input fields, and buttons.
You can create your first report using the menu wizard.

Database Reports

The designer is using cells, inside which can place labels, tables, or any other components.

Database Reports Cells