DBSchema (Wise Coders Solutions)

von

Introduction

DbSchema is a database designer, equipped with powerful features like an ER diagram modeler, visual query builder, schema synchronization and several other. It is the flagship product of Wise Coders Solution, a European company specialized in writing software for databases. Besides the traditional support for relational databases, including Oracle, SqlServer, MySQL, PostgreSQL and many other, DbSchema has been recently extended to also support MongoDB, a popular open-source document database. In addition to all this, the full package is available for all three major platforms, Windows, Linux and MacOS, which makes it a good potential candidate for a complete database management tool.

Getting started

DbSchema is available as a free trial version for 15 days, which should be enough to try it out and see if it fits your needs. The package can be acquired from the download page which contains installers for three major platforms. Installation is straightforward, and the steps are described on the download page. On the first launch, you will be shown a modal dialog describing the major features which can be useful if you would like to get a quick introduction to the capabilities of the software package, and decide whether it can be useful to you. Finally, when you are finished with the overview wizard, you will be shown the start page containing shortcuts for common tasks, like connecting to a database, creating an ER diagram, opening the documentation, etc.

Reverse engineering a schema

DbSchema offers the functionality of creating an ER diagram from an existing database. I found the process to be fluent and intuitive and considering the database I used (~50 tables), the diagram was generated quickly in a matter of seconds. The generated entities were spread out nicely to minimize the number of relationship lines that cross each other. Double clicking an entity brings out a modal form used for editing the columns, indexes, foreign keys and other constraints. Dragging entities works with no stutter and foreign key constraints can be created by pulling lines between entities which is a good feature to have. In addition to the main diagram, a sidebar on the left contains a minimap of the entire model, presenting a general view of all entities and enabling an easier navigation when working with larger models.

Besides to generating an ER model, DbSchema allows you to run queries against the database you are connected to. A small window in the sidebar shows the query history and allows you to stop the execution of queries that are still running.

An important feature of the ER diagram is that it allows you to group tables into layouts, with the option of having one table in multiple different layouts. This is especially handy when working with larger databases since it allows developers to think in terms of modules and submodules, and in that way tackle complex models more easily.

ER Diagram

What I did not like with the ER diagram was that, unlike the vertical scroll, the horizontal scroll did now work on a laptop touchpad. In addition, while zooming in and out can be done by using the mouse scroll while holding the CTRL button, I was unable to find intuitively a keyboard-only shortcut for the same operation.

Visual Query Builder

DbSchema has a built in support for a visual query builder that allows you to select the tables and columns that you would like to use in a select statement. Joins are automatically made by taking advantage of the defined foreign keys, but additional join conditions can also be specified. The visual query builder will also allow you to use a WHERE and an ORDER BY clause, as well as aggregate functions like MAX, COUNT etc. I found the visual query builder to be useful for quickly putting together simple queries, however it is obvious that it was not intended to be used for select statements where common table expressions or subqueries could be needed.

Query Builder

Schema synchronization

A common operation I often need to perform is comparing two versions of the same schema and updating one or both of them to a common version. A software package that has an excellent implementation of this feature is Visual Studio and I was expecting to find something similar in DbSchema. However, I was quickly left disappointed since I could not find an option in the menus that would allow me to do a comparison between two database schemas, and pull out the differences, at least as diff scripts. The closest I got to was an option titled “Compare Schemas with Database” which I must admit I was not completely sure what it accomplished. Instead of pulling out the differences, it generated a complete script for the second database.

Since schema synchronization is a feature advertised on the DbSchema website, I was hoping the I could find a manual or tutorial on how to use it.Unfortunately, the Tutorials section (http://www.dbschema.com/tutorials.html) only contains a manual for generating forms and reports. Maybe I am spoilt by Visual Studio, but overall I was left with a bad impression for this feature.

After reviewing the schema synchronization page on the DbSchema website, I concluded that the feature was meant to be used for comparing a schema which currently in the database, with the current in-memory representation loaded in the database designer. In other words, it allows flushing changes made in the designer directly to the database. Even though this is a good option to have, I was expecting something different.

MongoDB

A recent upgrade to DbSchema has been the support for MongoDB. Generating a diagram for a MongoDB database works the same way as for relational databases. After entering the connection parameters, a diagram with the collections and references will be generated. The diagram allows you to edit some properties of the existing collection fields, add indexes and define primary keys. One thing I noticed was that the labels for the secondary (right click) menu were not updated to match the MongoDB terminology. Right clicking a collection brought up a menu with options like “Build SQL”, “Add Column” and “Add Foreign Key”. It would be nice if the menus matched the context of the data model that was being used. A query builder is also available and works in a similar way like for relational databases.

I believe that in this case the query builder is more useful due to the smaller number of joins and denormalized data. Finally, a query executer is also available, and you can use it for running hand-written statements.

Forms, reports and data generators

Finally, I would briefly like to mention that DbSchema allows you to generate random data which is useful for quickly seeding your database. What surprised me here was the fact that using existing data from the database, the data generation module was able to recognize patterns like emails, phone numbers, names and addresses, and generate additional entries which matched the corresponding pattern.

The forms and reports modules can be used for generating CRUD forms for the database tables. As a developer, I am not a user of such tools since they either prove to be an overhead for prototyping or insufficiently flexible for more complicated projects.However, some might find them to be useful, and the guys from Wise Coders Solutions have provided a nice tutorial on the DbSchema website: http://www.dbschema.com/FormsAndReportsTutorial.pdf.(This link is gone)

Pricing

Licenses are available at $127 for personal use, $197 for legal entities and $63 for academic institutions. The personal license is registered to a single private person and is non-transferable. The commercial/corporate license can be used within the company it is registered to, as long as the number of concurrent users does not exceed the number of licenses acquired. I could not clearly understand the terms of use of the academic license since it is not clearly stated if the conditions are similar to the ones for the corporate license. Another thing I noticed was that the price on the website was expressed in US Dollars, while the checkout page had the price nominated in Euros.

You can read more about the pricing on the pricing page

Conclusion

Overall, the impression I got from the entire package was quite positive. I did not experience a single crash or stutter, and the workflows (with the exception of schema synchronization) were all fluent and intuitive. I prefer to use the console whenever I can, but features like a diagram designer are something I would definitely use.

On the other hand, besides the support for multiple database types and the fluent experience, I could not find a useful “killer feature” (at least for my use) which dedicated competitors like MySQL Administrator or pgAdmin do not have. The user interface is not as polished as it could be and the fonts could be a bit more readable. If you have had some experience with the Java ecosystem, you will find the look and feel to be similar to the one offered by Eclipse and NetBeans. On the other hand, .NET developers might not get a good first impression since DbSchema does not match the presentation qualities of Visual Studio.

Tags: #Database #Schema #MongoDB #MySQL #Tool

Newsletter

ABMELDEN