Step-by-Step Tutorial

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.

Choose your database.

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.

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

On the left side you can see:

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

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

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

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

Edit existing tables by double-clicking the table header.

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

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

In the options pane, you can add database-specific table options, like engine, collation, partitions, clustering, sorting, etc.

In offline mode or logical design, you can use the Grid editor to edit tables faster.

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.

DbSchema can generate HTML5, PDF, or Markdown documentation. The option is available in the Diagram 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.

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.

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

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.

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.

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.

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

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.

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.

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.

Using the SQL Editor you can edit and execute queries.

The editor features text-autocompletion and explain-plan.

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.

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

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.

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

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.

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.