dbForge Studio for MySQL is a powerful IDE for MySQL and MariaDB from Devart, an industry leader known for its database development tools. In this article, we will discuss some of its features that 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.
Key features of dbForge Studio for MySQL
Complete MySQL compatibility
dbForge Studio for MySQL is compatible with various MySQL flavours, storage engines, and connection protocols. Besides the garden variety of the MySQL database engine, the Studio can successfully connect to MariaDB, Amazon Aurora for MySQL, Google Cloud MySQL, Percona Server, and other exotic distributions like Oracle MySQL Cloud, Alibaba Cloud, and Galera Cluster. In our workflow, we successfully connected this tool to a MariaDB instance running on Amazon RDS in a flash.
Improved user experience with an updated look and feel
The graphical user interface offers a modern, intuitive look and feel. Tabbed panes, non-cluttered toolbars and context-specific menus make navigation through the tool fairly simple.
Those familiar with Visual Studio will feel right at home with the default “skin” of dbForge Studio. Also, it provides other skins to change the UI theme and customize the software:
Improved workflows with command line automation
One of the excellent features of dbForge is that any manual action done in the UI can be turned into an operating system command. The button labelled “Save Command Line…” is available in each dialog box; by clicking on it the user can transfer the options configured in the dialog box into the command parameters. This way, database-related tasks can be easily automated using the Command Line.
The image below shows an example:
Robust MySQL Version Control with dbForge Studio
Integrated Source Control is the feature released in the latest version of dbForge Studio for MySQL.
First, it supports all major version control systems, such as Git (including GitHub, GitLab, and Bitbucket), Mercurial, SVN, Azure DevOps, and more.
Next, it allows the user to manage both database schemas and table data, under a dedicated or shared model (the former enables work on an individual database copy, the latter means there’s a shared database copy for multiple developers).
Finally, operations like committing changes, reverting modifications, and resolving conflicts can all be done directly within the Studio, so the user won’t need to switch between different apps.
dbForge Studio for Database Developers
A good IDE should help developers save time and automate tasks as much as possible. When it comes to the developer’s productivity, dbForge for MySQL offers the industry standard features like code completion, syntax checking, code formatting, code snippets, and more.
Objects like tables or views can be checked for their dependencies or relationships with other objects in the database. This is done by choosing the “Depends On” or “Used By” options from the database tree.
The dependencies are shown in recursive manner. This can be really handy when troubleshooting or debugging code:
Another helpful feature is the CRUD generator. Right-clicking a table and selecting CRUD from the popup menu will create a template for four stored procedures. Each one will be a for a CRUD (SELECT, INSERT, UPDATE, DELETE) action:
Here is 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 ; |
This helps to get started quickly with a skeleton procedure.
Only the most advanced database client tools would offer schema comparison and synchronization features. dbForge does provide them. An intuitive user interface makes searching and reconciling schema differences fairly simple:
Finally, developers will find the debugger tool useful:
Once the code is ready, developers can easily remove debug information with a few mouse clicks.
How data analysts can utilize dbForge Studio
Besides schema comparison, dbForge Studio includes a data comparison tool which should be of help to data analysts and developers. It has an intuitive interface for comparing data between two tables:
For importing or exporting data, dbForge can connect to ten different types of sources or destinations. Notable among these 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 feature in MySQL Workbench supports only CSV and JSON formats.
The Master-Detail Browser is a great tool for viewing data relationships. Analysts can use this to quickly check different categories of master data and their child records:
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:
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 customizations.
Tools for database administrators in dbForge Studio
The tools database administrators use for day-to-day management of MySQL databases are usually similar in both dbForge Studio for MySQL and MySQL Workbench. This includes:
- User management (“Security Manager” in the Studio for MySQL, “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 menu. dbForge Studio for MySQL creates an SQL dump file for the selected database. Restoring a database is simple as well.
We could not find the server log file viewer in dbForge, although it’s readily available in MySQL Workbench (with MySQL in RDS, the log files can’t be accessed from the client tool).
Copying a database from one instance to another is an intuitive and simple process with dbForge Studio. All the user needs to do is select the source and the destination instances, the databases to copy and any extra options if needed – all from one screen:
What’s more, databases can be copied between different flavours of MySQL: we could successfully copy a MySQL database to a MariaDB instance.
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 scene, dbForge uses MySQL native commands like EXPLAIN and SHOW PROFILE to gather the data and presents them in an easy-to-understand form in the GUI. Looking at these metrics can easily 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 runs can help the DBA check the effectiveness of the tuning.
What’s more, there is no reason to manually change the query’s text if it does not improve the performance. 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 also saves the query text along with the session statistics.
dbForge Studio’s tools for data architects
Reverse engineering an existing database structure is an integral part of a data architect’s job, and dbForge for MySQL has this functionality.
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:
Most high-end database client tools offer some type of reverse engineering capability, but dbForge Studio for MySQL goes one step further by allowing the user to create database documentation. With a few clicks of a mouse, a full-blown professional-looking system architecture document can be created without typing up anything. This documentation can describe tables and views, indexes, column data types, constraints and dependencies along with SQL scripts to create the objects.
Documentation can be created in HTML, PDF or Markdown format:
Finally, the feature database architects and developers would love is the Data Generator. Database design and testing often requires non-sensitive dummy data for quick proof-of-concepts or customer demonstrations. The Studio offers an out-of-the-box solution for this.
Using the intuitive data generator wizard, it’s 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 during data load:
If necessary, only a subset of tables can be populated instead of all tables:
The tool allows to 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, and the next tier (Standard edition) retails from $9.95 per month. The Professional edition starts at $19.95, and the Enterprise edition is priced at $29.95. There are volume discounts available for those purchasing two 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.
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 free Express edition or the 30-day free trial can be a good choice for everyone who wants to try before buying, and that, naturally, means nearly all of us.
One thing to keep in mind is that dbForge Studio for MySQL, originally designed as a classic Windows application, is available on Linux and macOS as well. To achieve this, in addition to requiring .NET Framework 4.7.2 or higher (as for the Windows environment), you’ll need a specialized application known as CrossOver (for Linux and macOS), or Wine (for Linux), or Parallels (for macOS).
Overall, we would say it’s a good product, in fact, a very good product – MySQL database manager that deserves at least a serious test drive from the community.
Featured image credit: Eray Eliaçık/Bing
