Back to articles list
Think of them as your sidekicks to deal with the challenges you encounter every day in working with databases.
Good database tools come to the rescue in all stages of the database lifecycle: from the conceptual design, through logical/physical design, all the way to maintenance, refactoring, and optimization.
Every database professional, be it an architect, engineer, designer, programmer, tester, or administrator, has a preferred set of tools. Some tools are specific to the database system (RDBMS), such as MySQL, Oracle, SQL Server, or PostgreSQL, while others work with virtually any database engine. Depending on the purpose, you may want the tool to be specific or all-purpose.
Another important consideration for database tools is online versus on-premises. Useful online database tools are available wherever there’s a web browser and an internet connection. On-premises tools, on the other hand, must be installed locally or on a local network. The advantage of on-premises tools is that they tend to be easier to connect to on-premises database servers.
In this article, I discuss useful database tools that should be in the toolbox of every database designer, programmer, engineer, and architect.
Database Design Tools
Designing a database is the first step toward materializing the intent to create an information repository that meets a series of requirements. The designer’s ability to interpret the requirements and translate them into a database schema is the “tool” at the start of this step. Then, data modeling tools are needed to visually represent the database schema in the form of an entity-relationship diagram (ERD). These tools range from good old pencil and paper to intelligent applications that help create diagrams and enforce their compatibility with the relational model.
The data modeling tools you choose determines your productivity as a designer. In theory, you can get the same results either with pen and paper or with the most intelligent tool. The difference is in the time it takes to do the work. Keep in mind that database design is more than just drawing ERDs.
To improve your productivity, data modeling tools should ideally:
- Facilitate interactive drawing of objects in a database diagram.
- Give behavior to the visual objects of the design, maintaining the principles of the entity-relationship model, and validate the design complies with the rules.
- Generate physical diagrams from conceptual or logical diagrams and reverse-engineer diagrams from existing databases.
- Generate scripts to create database objects.
- Facilitate showing and sharing diagrams with customers or colleagues.
Follow this link to dig deeper into how to select an ER diagram tool. But the most comprehensive among all the tools in the area of database design I know of is the Vertabelo platform. It offers all the features mentioned. It also has the capability for shared diagram repositories; you can manage access to each member of your team by differentiating privileges of owners, editors, and viewers for each diagram.
Vertabelo is an online database design platform with all you need to deliver your work in minimal time.
Data Warehousing Tools
Data Warehousing (DW) requires a platform capable of integrating data from multiple sources, handling large volumes of information, and optimizing its performance for data analysis by prioritizing it over performance for OLTP (online transaction processing).
In practice, any RDBMS can be used for data warehousing. And for small volumes of data, any RDBMS can potentially offer adequate performance. However, if you need to work with huge volumes of data, if you need to support a large number of users, or if performance is a critical requirement, you need a platform specifically designed for DW.
Snowflake is among the preferred DW platforms. It consists of a cloud-based database with tools to unify data from different sources, analyze it, and share results with customers and partners in a secure and governed way.
Creating a new data warehouse in Snowflake.
Snowflake offers an important value-added service called Data Marketplace. It provides access to live, query-ready data from its ecosystem of customers, business partners, and thousands of data services. For data scientists and data analytics professionals, this turnkey solution saves a lot of time and effort on data collection tasks.
Google BigQuery is another option for DW-specific database tools. With BigQuery, a DW can be scaled up to petabytes without incurring excessive costs. One of its main benefits is that it does not require a server or infrastructure to manage; you don’t even need a database administrator to use BigQuery. This way, you can focus on data analysis through SQL queries and forget unproductive management tasks.
Tools for Visualization, Analysis, and Reporting
Data visualization tools allow you to uncover truths that cannot be seen by simply examining the results of a query. Visualization allows business experts to spot trends and valuable metrics to gain an even better understanding of the business. These tools are commonly used at the end of the development cycle when the information in the databases is sufficient for data analysis.
Traditionally, data visualization has been done by generating graphs with data from spreadsheets, often with statistical applications only trained professionals can operate. Today, there are user-friendly tools that facilitate interactive data exploration so that even inexperienced users can explore data comfortably.
Strictly speaking, visualization tools are not database tools, but it is common for those of us who work with databases to use them to give users the ability to inspect data dynamically and interactively at their leisure.
Within this category, the best tools are those that allow us to create stand-alone applications without writing code (this is why they are called no-code development platforms) to give users so that they can explore data without requiring our help.
One of the most popular visualization tools is Tableau, recognized for its effectiveness in creating visualizations in minimal time and for providing a high level of information access security. Tableau can be used by individuals to analyze data or scaled to teams or even entire organizations.
An interactive data dashboard created with Tableau.
Another option is Zoho Analytics. This is a business intelligence and data analytics software that helps you create compelling visualizations in a matter of a few minutes. You can pull data from multiple sources and combine them to create multi-dimensional visualizations that meet different requirements within an organization. It features a virtual assistant called Zia that uses artificial intelligence, machine learning, and natural language processing to facilitate your work.
Database Tools for Monitoring and Debugging
Database monitoring tasks represent a major part of a database administrator’s (DBA) work. A good monitoring tool allows DBAs to verify at all times that the databases they manage are working correctly and providing optimum performance. On Windows systems, monitoring software can track health, CPU, memory, and disk usage, helping to catch issues before they impact database performance.
Users of database monitoring tools are not limited to DBAs. These tools are an important ally to a database designer or programmer in debugging applications or processes that interact with databases. The tool provides a window to inspect everything that happens between the application and the database. When a query or any other operation does not work as expected, monitoring can reveal errors and suggest optimizations.
The tools for monitoring/debugging database operations are very diverse. In general, the best ones are developed specifically for a given RDBMS. Among my favorites is Devart’s dbForge, a tool that covers a wide range of needs. For monitoring, the dbForge product line includes specific tools for MS SQL Server, MySQL, MariaDB, Oracle, and others.
dbForge Event Profiler allows you to spy on what is going on between an application and a database.
If you work with many different RDBMSs, having a specific tool for each one may not be practical in terms of both cost and knowledge required for operating them all. In this case, you are better off with an RDBMS-agnostic tool such as dbWatch. dbWatch offers in-depth monitoring of SQL Server, Azure, Oracle, PostgreSQL, Sybase, MariaDB, and MySQL databases, all in a single tool.
Tools for Database Comparison
Once a database is in production, you must be careful with any changes you make to its design and its data, or you might render the applications that make use of the database unusable. Designers and programmers work in development environments for this reason, so that there is no danger of damaging critical information if they accidentally delete a table or even an entire database.
The disadvantage of working in a development environment is that every change you make must be logged and then replicated in the production environment. We can rely on our discipline and good memory to ensure all changes are properly recorded in the log. But we are human, and we can forget things or make mistakes that prevent replication of changes in the production database.
One way to reduce such forgetting or mistakes is to use a database comparison tool. These tools show the differences between schemas in detail. For example, they can identify objects that exist in one schema and not in the other or objects that exist in both but have different properties. Then, the tool generates a script of SQL DDL (data definition language) commands that modify one of the schemas so that its structure is identical to the other.
Data comparison is another very useful tool when information from two databases must be identical, either to migrate data between two instances (e.g., migration to a cloud database) or to keep one or more replicas of a database synchronized.
For both situations, my preferred tools are also under Devart’s dbForge umbrella: dbForge Data Compare and dbForge Schema Compare. Schema Compare can compare snapshots, script sets, backup files, and source code control reviews of databases. It then generates error-free scripts to update the database. Data Compare compares the contents of tables, which is very useful in synchronizing data with pre-production environments, troubleshooting data problems, and generating upgrade scripts.
dbForge Schema Compare comes to the rescue when you need to identify every little difference between two databases.
Programming and Querying Tools
Everyone who works with databases has a favorite tool for querying, updating data or structures, and writing SQL code. Programmers occasionally use the tools integrated into their IDEs so that they don’t have to jump between different applications when they have to work with the database. For the rest of us who work with databases (designers, analysts, engineers, etc.), the ideal tool for writing and executing SQL is one that is convenient, fast, practical, and always at hand.
Essentially, these tools are user interfaces (GUIs) for databases. The ideal qualities we look for in these tools are the following:
- It should be lightweight. You need easy access to the tool from any work environment, and you want to be able to use it in seconds.
- It should support multiple RDBMSs.
- It should be easy to connect to database engines, either in the cloud or on-premises.
- It should have an intelligent code editor. Besides recognizing and formatting SQL commands automatically, it should recognize the connected database objects.
- It should allow interactive schema navigation and object modification.
There are many options to choose from in this category of tools. Among the most popular are MySQL Workbench, phpMyAdmin, HeidiSQL, Sequel Pro, and DataGrip, to name a few.
In general, preferences for one over another are usually determined by some special quality that makes the difference, or simply by convenience or ease of use. For example, support for a wide variety of different database engines may be a deciding factor if you work with different databases. The capability for analyzing query execution plans may be a deciding factor if you spend a lot of time optimizing queries.
DataGrip is one of my favorite useful database tools. It is designed as a complete IDE for SQL, is compatible with almost any RDBMS in existence, and has an easy-to-use GUI. However, it is not precisely the lightest when you consider CPU and memory consumption. When I need a lightweight database manager I can take with me everywhere, I prefer HeidiSQL.
HeidiSQL, a lightweight but powerful multipurpose database management tool.
Setting up the Perfect Toolbox of Database Tools
So, now you know about the useful database tools I come across when I open my computer every morning. The set I’ve described here is perfect for me, but it may not be perfect for everyone. Each database professional has different preferences and needs, which means each database professional needs a particular toolset with a particular configuration. Use the comments box below to tell us what your favorites are and help them get the recognition they deserve!
