Navicat: A Helpful Toolkit for DBAs

Every year, Database Trends and Applications magazine conducts its Readers’ Choice Awards and this year the winner for best DBA solution was Navicat. As a DBA myself, this piqued my curiosity, so I took some time install and learn Navicat Premium. And I’d like to share my findings with you.

Navicat offers a very functional and easy-to-use interface for a lot of the most common and regularly occurring DBA tasks and requirements. Furthermore, it can be used for heterogeneous database administration as it supports managing databases in MySQL, MariaDB, Microsoft SQL Server, Oracle, PostgreSQL, and SQLite. I wish they supported IBM DB2, as well, but no DB2 support is available. And you can use Navicat on the most popular operating systems including Windows, Mac OSX, Linux, and even as an iOS app.

Okay, so what can Navicat do for you as a DBA? Quite a lot, it turns out. You can use it to create, modify, and manage all of your database objects (e.g., tables, views, etc.) and the Table Designer feature can be used to guide you as you create and modify tables, allowing you to set up relationships, constraints, triggers, and more.

Navicat aids DBAs managing in complex setups. If you have multiple instances that need to be the same, it is easy for them to becomes corrupted somehow, missing a table or containing different data. You can use Navicat to compare and modify database structures and synchronize the data structures and the data itself across disparate environments. And the target database can be on the same server as the source or on another server altogether.

But let’s back up a minute. Navicat Premium also contains a data modeling component that can be used for reverse-engineering, forward-engineering and generating scripts for database design. Just as you would expect, it offers ER diagramming capabilities for entities/objects and the ability to filter as needed. You can easily navigate through bigger models by dragging your cursor box over a small scale version of the model, which automatically shows the highlighted area.

So Navicat combines database design and administration capabilities into a single tool with a single interface. But it also offers some assistance for development. Let’s take a look at some of the things it offers.

Navicat can be used to browse and modify your data with having to code SQL. You can insert, update, delete or copy and paste data using a spreadsheet-like data editor. Navicat converts your edits to the appropriate SQL and runs it for you. And it comes with data editing tools to help you out, such as a foreign key lookup capability.

Of course, you can create, edit, and run SQL queries using Navicat, too. It comes with many great features to help you write accurate SQL, including a Query Builder to guide you as you code your SQL, a context-sensitive code completion feature, and the SQL beautifier that can be used to consistently format your SQL code.

Navicat also can be used to manage your data movement and manipulation requirements. It includes data synchronization for analyzing and migrating data between databases, the ability to transfer data (e.g. local to local, local to remote, etc.) across supported databases, and wizards for exporting and importing data. These capabilities can save a lot of time for application DBAs who have to maintain the development environment.

The tool also comes with a Report Builder that can be used to create stylish reports very rapidly. Navicat provides options like subreporting and crosstabs to simplify your report creation requirements. And you can even augment your reports using the charting and visualization options.

You can use Navicat to back up your databases, too. Combined with its Scheduling functionality you can use Navicat to automatically backup databases when you want to, saving them locally or on a network drive. The scheduler can be used with batch jobs of any type, not just database backups.

The Navicat Cloud service offers another very nice feature: the ability to sync your connection settings, models, queries and virtual groups to the cloud so you can get real-time access to them anytime, anywhere you want.

With built-in collaboration capabilities, multiple DBAs can work on a project sharing the workload while Navicat keeps things up-to-date and accurate. You can use Navicat across multiple devices and be sure that everything is up-to-date using Navicat Cloud services. Synchronize your connection settings, queries, models, and virtual group information across all your devices using Navicat Cloud services. Files stored in the Navicat Cloud automatically show up on Navicat desktop and mobile version so that you can get real-time access any time and from anywhere.

The Bottom Line

You can think of Navicat as a Swiss Army knife of database management and DBA tools that work together across multiple database systems. A tool such as Navicat can reduce the amount of time, effort, and human error expended in managing a heterogeneous database environment.