Step-by-Step Tutorial

This tutorial covers the main DbSchema features:

FeatureDescription
Connect to the DatabaseDbSchema can connect to any SQL and many No-SQL databases
Reverse Engineer the SchemaWill load the schema structure from the database.
Create LayoutsThe layouts are diagrams with tools. You can create multiple layouts with the same or different tables.
Create or Edit TablesCreate and edit tables directly in the diagram.
Document the SchemaAdd comments to tables and columns.
Generate HTML5 DocumentationInteractive HTML5 diagram with the vector image of the diagram.
Design Schema in Offline ModeDesign the schema without connectivity to the database.
Compare and Synchronize the Design Model with the DatabaseThe design model stores its own image of the schema structure. This can be compared or deployed on any database.
Design the Schema in a TeamThe model file can be shared in a team using GIT, and can be later deployed on any database.
The Relational Data EditorVisual explore data from multiple tables simultaneously, bind by foreign keys or virtual foreign keys.
Virtual Foreign KeysThe virtual foreign keys won't be implemented in the database.
The Query BuilderVisually build SQL queries.
The SQL EditorEdit and execute SQL queries.
The Random Data GeneratorFill tables with random generated data.
The Data ImporterImport data from CSV, Excel or XML files into the database.
Automation ScriptsAutomate different tasks using Java Groovy scripts.
MongoDB FeaturesView the MongoDb structure as diagrams and implement validation rules.
Logical DesignDesign a database-independent model which can be deployed for any database.

Connect to the Database

From the Welcome pane choose to connect to the database. There are three other options available:
  • If you have an SQL file, with the schema creation script, to open it and generate the design model.
  • Start a logical design. The logical design is a database-independent design.
  • Start a fresh physical model, without database connectivity. The model can be later created into the database.
Connect to the database.
Choose your database.
Connect your DBMS.

Choose the location of your database server ( localhost - on your computer or remote - on a different machine ) and the authentication.

In the JDBC URL, you can choose Edit Manually to enter the JDBC URL directly. Use this with cloud databases, as usually you can find the JDBC URL in the cloud console.

Connect your DBMS.

Reverse Engineer the Schema

After connecting to the database, DbSchema will reverse engineer the schema and show the tables in layouts.

On the left side you can see:

  • The project structure
  • The SQL History with the executed statements
  • The diagram perspective

DbSchema Interface.

Create Layouts

A layout is a diagram with tools. The layouts are part of a tab pane. Get a better understanding of the schema by creating multiple layouts, with the same or different tables.

Create layouts.

Add tables to the layout by clicking the foreign key icon.

Create foreign keys by dragging one column over the referred column.

Add tables to the layout by clicking the foreign key icon.

Create or Edit Tables

Edit existing tables by double-clicking the table header.

Right-click the layout and choose to create a table.

Create tables.

Use the table dialog to create or edit columns, indexes, constraints, and foreign keys.

Some databases are using by default upper cases. DbSchema tries to use the same cases as the database, in order to avoid using quotes. This is configurable from 'Schema / Use DBMS Preferred Letter Cases'.

The table dialog.
In the options pane, you can add database-specific table options, like engine, collation, partitions, clustering, sorting, etc.
The table dialog.
In offline mode or logical design, you can use the Grid editor to edit tables faster.
The grid editor.

Document the Schema

Each table, column, index, or foreign key can have a description. The description can be as text or as tags. The tags comments are only for internal usage. The text comments will be commit in the database using ALTER TABLE ... SET COMMENT... statements.
The table dialog.

Generate HTML5 Documentation

DbSchema can generate HTML5, PDF, or Markdown documentation. The option is available in the Layout menu.

The HTML5 documentation can be opened using any Web browser. Read the table, column, and foreign key comments by hovering them with the mouse.

Generate HTML5 documentation.

Offline Mode

DbSchema can work connected to the database or offline (without database connectivity). You can continue to add new tables and columns. They will be saved to the design model file.

Later, you can connect back to the database and deploy the changes into the database.

Generate HTML5 documentation.

Compare and Synchronize the Design Model with the Database

DbSchema is showing the schema structure from the design model. The design model can be saved to file. At startup, DbSchema is opening the latest saved design model file.

To ensure the design model is the same as in the database, press 'Refresh Schema from Database'.

Refresh schema from the database.

The Schema Synchronization dialog will allow to:

  • Modify the design model
  • Commit the change to the database
  • Generate the SQL script

You can set a filter to automatically reject differences.

The same dialog will show if you choose to upload the schema into the database.

You can also compare the design model with another model from the file.

Schema Synchronization dialog.
Sometimes, the same schema can have different names on different databases. In this case, use Schema Mapping from the Connection dialog to map a schema from the design model to another schema in the database.
Schema Mapping dialog.

Design the Schema in Team

The design model file is using the XML format. The file can be saved in a GIT repository and shared with a team.

From the model menu, you can open the GIT dialog to view the different revisions of the design model file, and:

  • Compare different versions of the model file and generate migration scripts.
  • Audit changes made to a certain table or column.
  • Clone, Commit, Push, or Pull changes in the repository.
  • Solve conflicts.

The GIT Dialog.

The Relational Data Editor

Click table headers to open any of the main editors: Relational Data Editor, SQL Editor, Query Builder.

Open DbSchema editors.

The Relational Data Editor can view data from multiple tables simultaneously. The tables should have foreign keys or virtual foreign keys. There is no limit for the number of open tables.

Clicking a record in one table will refresh the children tables and show the matching records, based on the foreign key column match.

Double-click cells to edit, set filters and switch from table to tree view.

The editor will be saved into the design model file and can be reopen next time you start DbSchema.

Relational Data Editor.

Virtual Foreign Keys

Virtual Foreign Keys are defined only in DbSchema, and won't be created in the database. They will be saved to the model file. Foreign keys can be created by drag of one column over the referred column, then tick the 'virtual' checkbox in the Foreign Key dialog.

Virtual Foreign Keys can be used in Relational Data Editor and Query Builder, and they help to understand the schema structure.

Virtual Foreign Keys.

The Query Builder

Use the Query Builder to create SQL queries using the mouse. Click the foreign key icon near columns to join further tables.

Tick the columns to select. Right-click columns to add filters or aggregate columns, which will turn the query into a GROUP BY.

Query Builder.

The SQL Editor

Using the SQL Editor you can edit and execute queries.

The editor features text-autocompletion and explain-plan.

SQL Editor.

The Data Generator

The Data Generator can fill tables with random-generated data. The generator uses configurable patterns to generate numbers, text using reverse regular expressions, lists, values from foreign key tables, etc.

The Data generator.

The Data Importer

Import data from CSV, XLSX, XLS or XML into the database.

The Data importer.

Automation Scripts

Tasks like generating the HTML5 documentation can be automated using Java Groovy scripts. Java Groovy is 100% Java. The Tools / Automation Scripts Dialog is showing different use cases.

The Data importer.

Copy the script into the SQL Editor and switch from SQL to Java Groovy.

The Data importer.

MongoDB

MongoDB databases do not have a schema. DbSchema can show the validation rules ( if present ) or 'introspect' the schema and show the collection structure as diagrams.

DbSchema is featuring Query Builder and Query Editor for MongoDb.

DbSchema can show the MongoDB structure as diagrams.

Logical Design

The logical design is database-independent design and uses entities, attributes, and relations. The relations can be Identifying or Non-Identifying, Many-to-Many, Mandatory, and can have a Cardinality.

Design a logical schema using DbSchema.