Using HeidiSQL in testing

Database is an important part of any system or application. Everything exists through data. Database instability can lead to unexpected system behavior. The data will be unusable if the database crashes or the data is stored disorganizedly. Thus, database testing helps us identify such vulnerabilities in the system and minimize the risks associated with data integrity before the system is put into operation.

This article will discuss HeidiSQL, an open-source database management tool that supports MariaDB, MySQL, Microsoft SQL, PostgreSQL, and SQLite. To manage the HeidiSQL database, the user just needs to log in to the local or remote server by creating a session. Within this session, the user works with MySQL databases on the MySQL server.

The functionality of the program is sufficient for working with general and more complex databases. It is worth mentioning that HeidiSQL was created for the Windows platform and only works on Windows only.

Main features of HeidiSQL:

• connection to several servers in one window;
• connecting to servers through the command line;
• creating and editing a table, data visualization, saving routines, triggers, and scheduled events;
• export databases to SQL files or from one server/database directly to another server/database;
• user management: adding, deleting, and editing users and their passwords;
• user privilege management;
• import of text files;
• export table rows as CSV, HTML, XML, SQL, LaTeX, and Wiki Markup;
• view and edit table data using a convenient grid;
• mass editing of tables (moving to the database, changing the mechanism, mapping, etc.);
• batch insertion of ASCII files or binary files into a table;
• creating queries with configured syntax highlighting and code completion;
• optimization and debugging of tables in a batch way.

To connect to the database, you need to click «New» and specify the necessary parameters in the settings.

Database connection

• Network type  – MariaDB або MySQL (TCP/IP);
• Hostname / IP  – host to get connected to;
• User  – login;
• Password  – password;
• Port  – a standard port 3306 must be entered.
• Databases  – the name of the database

Then, click «Open» and indicate whether you want to save the specified connection settings.

To create a table, click on the required database and select «Create new» > «Table»:

Creating a table

After that, the table editor will open:

Table editor

Editing the database view («View» tab) looks like this:

Editing the database view

Creating and editing stored procedures and functions («Functions» tab):

Editing stored procedures

Creating and editing triggers («Triggers» tab):

Triggers editing

The «Data» tab displays the selected table or view. This is one of the most important and powerful tools of HeidiSQL. Different types of data are highlighted in different colors. To adjust the colors, you need to open Tools > Preferences > Data appearance.

Colour settings

By default, HeidiSQL has a «Query» tab. You can create more queries by pressing Ctrl+T on the keyboard, or by right-clicking on the main tabs and selecting «New query tab». In the new tab, you can write your own queries to the database or download a .sql file from your disk. Pressing the F9 key or the button with the blue «Run» icon will start the query.

Request executing

HeidiSQL is able to export SQL files:

• .sql file;
• .sql file compressed by the ZIP algorithm;
• clipboard;
• a second database on the server;
• a new or existing database on the server with which you have a configured HeidiSQL session.

Export SQL files

It is also possible to import a SQL database: «File» > «Load SQL file...» > «Execute SQL...».

SQL database import

If the imported database file is large enough, you should start the import directly from the file. To do this, in the «File» menu, instead of «Load SQL file...», you need to select «Run SQL file...».

Among the analogues of HeidiSQL, the following tools can be pointed out: Workbench, Navicat, PHPMyAdmin, dbForge Studio, SQL Maestro, EMS SQL Manager, SQLyog.

In conclusion, we will consider the advantages and disadvantages of HeidiSQL.

Advantages:

• easy to use;
• connection to several servers in one window;
• free use - licensed under the GNU GPL;
• a portable version of the program is available;
• complete database user management and privilege management options;
• writing queries with syntax highlighting and code completion;
• comparison and synchronization of data between local and remote databases;
• SSH tunneling support.

Disadvantages:

• low stability;
• available for Windows only;
• low DPI display.

The choice of database testing tool often depends on the project.

HeidiSQL will be an indispensable tool if you value functionality over the interface and work only on Windows.