A First Look at dbForge Studio for MySQL

dbForge Studio for MySQL is a powerful integrated development environment (IDE) for MySQL from Devart, an industry leader known for its database development tools. In this article, we will discuss some of its features database developers, analysts, DBAs, or architects may find useful.

Disclaimer

This is not a product promotion article. The author is not affiliated with Devart or any other company associated with Devart.

General Features

Access to a Wide Range of MySQL Flavors

dbForge Studio for MySQL is compatible with a wide range of MySQL flavors, storage engines, and connection protocols. Besides the open-source MySQL database engine, it can connect to MariaDB, Amazon Aurora for MySQL, Google Cloud MySQL, and Percona Server to name a few. Other exotic distributions include Oracle MySQL cloud, Alibaba cloud, and Galera cluster. We were able to seamlessly connect it to an Amazon RDS MariaDB instance. When we tried to do the same from MySQL Workbench. it showed us the following message:

MySQL Workbench did connect to the MariaDB instance after the warning, but we could not see the system databases from its navigation pane.

Look and Feel

The user interface of dbForge Studio has a modern, intuitive look and feel. Tabbed panes, non-cluttered toolbars, and context-specific menus make navigation through the tool fairly simple.

Those familiar working with Visual Studio will feel right at home with its default “skin.” There are other skins to change the UI theme:

Command Line Automation

One really good feature of dbForge is that most actions on the UI can be exported to an operating system command. There is a button labelled “Save Command Line...” with most dialog boxes. This allows the action of the dialog box to be exported as an operating system command. The options chosen in the dialog box become parameters for the command. This can help users automate regular database tasks from their desktop:

For the Database Developer

Code Snippets

A good IDE should help developers save time and automate tasks as much as possible. When it comes to developer productivity, dbForge for MySQL offers some of the industry standard features like code completion, syntax checking, code formatting or code snippets. Here are some examples of code completion and code snippets:

Object Dependencies

Objects like tables or views can be checked for their relationships to other objects in the database. This can be done by choosing the “Depends On” or “Used By” folders from the object tree. The dependencies are shown in recursive manner. This can be really handy when troubleshooting or debugging code:

The CRUD Generator

Another good feature of this tool is the CRUD generator. Right clicking on a table and selecting CRUD from the popup menu will create a template for four stored procedures. Each procedure will be for a basic CRUD operation (SELECT, INSERT, UPDATE, DELETE):

Here is a portion of a sample script:

DROP PROCEDURE IF EXISTS usp_dept_emp_Insert;
DELIMITER $$
CREATE PROCEDURE usp_dept_emp_Insert 
    (IN p_emp_no INT(11),
     IN p_dept_no CHAR(4),
     IN p_from_date DATE,
     IN p_to_date DATE)
BEGIN
    START TRANSACTION;
    INSERT INTO dept_emp (emp_no, dept_no, from_date, to_date)
    VALUES (p_emp_no, p_dept_no, p_from_date, p_to_date);
    /*
    -- Begin Return row code block
    SELECT emp_no, dept_no, from_date, to_date
    FROM   dept_emp
    WHERE  emp_no = p_emp_no AND dept_no = p_dept_no AND from_date = p_from_date AND to_date = p_to_date;

    -- End Return row code block
    */
    COMMIT;
END$$
DELIMITER ;

The Schema Comparison Tool

Most database client tools would offer schema comparison and synchronization feature. dbForge is no exception. The intuitive user interface makes searching and reconciling schema differences very easy:

The Debugger Tool

Finally, the debugger will be another great feature for developers:

The debugger wizard requires the dbForge debug engine to be deployed in the MySQL server and creates a database called cr_debug. This database has all the procedures, functions, and tables required for user code debugging. Deploying the debug engine requires the user to have process admin rights, and we found this feature does not work with MySQL in Amazon RDS, because RDS does not allow access to the backend server.

For systems that allow deploying the debug engine, MySQL developers can run their stored procedures with “Compile for Debugging” option. This inserts custom debug code to the procedure, calling routines from the cr_debug database. This added code allows developers to perform step by step debugging on the code instead of using custom debug messages. To keep things simple, the debug code is not displayed when a procedure or function is loaded in the dbForge editor.

Once the code is ready, developers can easily remove debug information with a few mouse clicks.

For the Data Analyst

The Data Comparison Tool

Like the schema comparison tool, dbForge for MySQL has a data comparison tool that should be useful for data analysts and developers. It has an intuitive interface for comparing and reconciling data between two tables:

Data Import and Export

dbForge Studio for MySQL can connect to ten different types of data sources for importing and exporting data. Notable types are Google Sheets, XML, or even ODBC connections. We were able to copy an Excel sheet in no time. Then, we tried with a JSON document — again, that was a breeze.

Compared to these types, the Table Data Import wizard in MySQL Workbench can import CSV or JSON only.

Master-Detail Browser

The Master-detail browser is a great tool for viewing data relationship. Analysts can use this tool to quickly check different categories of master data and their child records:

Pivot Table

The Pivot Table feature can be used for data aggregation, grouping, sorting and filtering. For example, a source table may look like this (we are using the Sakila database as a sample):

With a few mouse clicks, the pivoting feature allows us to break down or roll up the rental income figure:

Reporting

Not too many enterprise class query tools have a built-in reporting facility. dbForge Studio for MySQL comes with a nifty report designer. Users can create reports either by choosing one or more tables or using their own custom queries. Once the wizard finishes, the report opens in a WYSIWYG editor for further customization. Once ready, it can be saved in Data Report (.rdb) format:

For the Database Administrator

Database administrators would find most tools they use for day-to-day management of MySQL databases are similar between dbForge and MySQL Workbench. This includes:

• User management (“Security Manager” in dbForge, “Users and Privileges” in MySQL Workbench)
• Table Maintenance (Analyze, Optimize, Check, CHECKSUM, Repair)
• Current connections to the instance
• System and Status variables

Similarly, backing up a database is as simple as right-clicking on it and choosing "Backup and Restore > Backup Database…” from the popup menu. dbForge creates a SQL dump file for the database. Restoring a database simple as well.

We could not find the server log file viewer in dbForge Studio for MySQL, although it’s readily available in MySQL Workbench. With Amazon RDS MySQL, the log files can’t be accessed from either of these client tools.

Copy Database

Copying databases from one instance to another is an intuitive and simple process with dbForge Studio. All the user needs to do is choose the source and the destination instances, select the databases to copy in the source plus any extra options if necessary, and then click on the little green arrow:

What’s more, databases can be copied between different flavors of MySQL: we were able to successfully copy a MySQL database to a MariaDB instance.

MySQL Workbench also offers Schema Transfer Wizard for copying databases, but the Wizard wasn’t able to show our MariaDB instance as a connection.

dbForge Studio for MySQL allows copying databases within the same instance (the new database name has a suffix of “_copy”). This is not possible with MySQL Workbench.

Query Profiler

Where dbForge really shines for the DBA is the query profiler. Using the query profiler, a DBA can capture different session statistics for a slow running query such as execution time, query plan, status variables etc. Behind the scenes, dbForge uses MySQL native commands like EXPLAIN and SHOW PROFILE to gather session data and presents it in an easy-to-understand format in the GUI. Looking at these metrics can help identify potential candidates for query tuning. Once tuning is done and the query is run again, the query profiler will again save the sessions statistics. Comparing the two different session profiles can help the DBA check the effectiveness of the tuning. What’s more, there is no reason to manually copy and save query texts between different runs. Selecting a profile session and clicking on the “SQL Query” button will automatically show the query executed for that session in the editor. This is possible because the query profiler saves the query text with session statistics.

For the Data Architect

Database Diagrams

Reverse engineering an existing database's structure is often part of a data architect’s job and dbForge Studio for MySQL makes this process simple. Tables from the database tree can be dragged and dropped into a Database Diagram and it will automatically create a nice ER diagram, as shown below:

Database Documenter

Most high-end database tools offer some type of reverse engineering capability, but dbForge goes one step further by enabling the user to create database documentation. A full-blown professional looking system architecture document can be created with only a few clicks of a mouse. The documentation will describe tables and views, indexes, column data types, constraints and dependencies along with the SQL scripts to create the objects.

The documentation can be created in HTML, PDF, or Markdown format:

Data Generator

Finally, the feature that database architects and developers would love is the Data Generator tool. Database design often requires meaningful dummy data for quick proof-of-concepts, load testing, or customer demonstrations. dbForge offers an out-of-box solution for this. Using the intuitive data generator wizard, it is possible to populate an empty schema of a MySQL database in no time:

The generator keeps foreign key relationships in place during data load, although foreign keys and triggers can be disabled if needed:

Also, only a subset of tables can be populated if necessary:

The tool can create a data generator script and load it into the SQL editor, save it as a file, or run it directly against the database:

Conclusion

dbForge Studio for MySQL comes in four different editions: Enterprise, Professional, Standard, and Express. The Express edition is free with the next tier (Standard edition) retailing at $149. The Professional edition is priced at $299 and the Enterprise edition at $399. There is a volume discount available for 10 or more licenses.

dbForge also offers subscriptions for customers wishing to upgrade their product to newer versions. The subscription is available for one, two, or three years. Licensing prices come down with longer subscriptions. Also, a one-year subscription is included with new licenses.

Being a free tool, MySQL Workbench may seem an attractive alternative to stay with. In our opinion, the wide number of features available in dbForge editions make their prices seem fair. Also, the major differences between Professional and Enterprise edition are copy database, data generator, and database documenter.

The Express edition (free) or the 30-day free trial of any edition can be a good choice for anyone to get a feel for the product.

One thing to keep in mind is dbForge for MySQL is a Windows-only tool. This can be a major factor for shops where MacBooks are a favorite.

Overall, we would say it’s a good product, in fact, a very good product — one that deserves at least a serious test drive from the community.