Special Thanks: This article was made possible by support from Linux Professional Institute

Governing and taking care of databases is a specialized task that requires special system administrators who are commonly known as Database Administrators (DBAs). While their role encompasses all aspects of maintaining data integrity within an organization, some of it overlaps with the duties of a system administrator.

To save yourself time and effort when you are called to administer databases, you can use a graphical database management tool. There’s no dearth of such tools, and some databases have their own in-house versions. What sets DBeaver apart is that it works across platforms and supports a wide variety of database servers. Whether you are using relational databases like PostgreSQL and MariaDB, Hadoop-based ones like Apache Hive, graph databases like Neo4j, or even embedded ones like Firebird and SQLite, DBeaver can manage them all as well as several others (Figure 1). Basically if your database server has a JDBC driver, you can hook it up with DBeaver.

Figure 1: Besides DBAs, application developers and SQL programmers will find DBeaver useful.

DBeaver comes in two versions: DBeaver Community Edition (CE) and DBeaver Enterprise Edition (EE). The CE version discussed in this article is available under the open source Apache License. The proprietary DBeaver EE is available under a subscription model and additionally supports databases with non-JDBC drivers, including NoSQL behemoths like MongoDB and Apache Cassandra. Refer to the “Enterprise Edition Features” box for information on the functionality you get with the proprietary version.

You can use DBeaver CE for a variety of common tasks such as editing metadata, exporting/importing data, managing users, and a lot more. It includes an SQL Editor, a query manager, and Entity Relation (ER) diagrams, which help visualize the connections between the databases. You can also use DBeaver to easily manipulate the data housed inside the database by directly viewing and editing it.

DBeaver is written in Java and based on the Eclipse Rich Client Platform. DBeaver CE is available in the repositories of many distributions. I use Ubuntu, and the Snap version in the repositories is outdated, so I suggest grabbing one from Flathub. To equip Ubuntu to install Flatpak’s, first fetch the Flatpak plugin for Gnome Software with:

sudo apt install gnome-software-plugin-flatpak

This will automatically install all the required components. Once it’s done, add the Flathub repository with:

sudo flatpak remote-add --if-not-exists flathub

Your Ubuntu installation is all set to install Flatpak apps now. If you aren’t running Ubuntu, follow the instructions for your distribution on the Flatpak website to enable it to install flatpaks. Once your distribution is ready, you can search for DBeaver inside its graphical software manager or type

flatpak install flathub io.dbeaver.DBeaverCommunity

inside a terminal to install the app.

Initial Impressions

When you launch DBeaver for the first time, it will prompt you for details to connect to a database server. The first step is to select a database server to which you wish to connect from the list of supported databases. You can use DBeaver to connect to databases on the localhost, as well as those on a remote machine over the network or across the Internet. Different databases require different configurations, but, generally speaking, you’ll need to enter the database server's hostname and its administrator user's username and password (Figure 2). You should use the Test Connection option to check the connection before exiting the wizard. Since this is a pristine installation, DBeaver will download the drivers it requires to communicate with your database.

Figure 2: A server can contain multiple databases, so you can specify the name of the one you wish to access in the Connection Settings window.

Once it connects to the database server, DBeaver will take you to the main interface. Every connection you create shows up in the Database Navigator tab on the left. It shows an expandable tree, which you can use to access the various database components, such as its tables, indexes, views, and more.

You can double-click on any component to load it in the Editor window on the right and then manipulate it. For instance, double-click on a table to load in the Editor window. This will open the table in three tabs (Figure 3). The first tab, Properties, displays various bits of information about the table. The second tab, Data, displays the actual data within the table. Finally, the ER Diagram tab displays the entity relation between the various tables. This tab is more useful when viewing an entire database as it displays the foreign keys that link the various tables.

Figure 3: You can move the different components around the workspace and dock them in different positions.

Spend some time exploring DBeaver’s interface. The app has very powerful and useful right-click context menus in the navigation window that offer all kinds of functionality. Right-click on a table to create a new one or even an entire database. There are options to help you import and export data to and from a table (see the “Moving Data” box for details). If you select multiple tables, the menu also has an option to compare them on various parameters. One of the most useful options in the context menu is the Tools submenu that includes various helpful options. For instance, you can backup a database and analyze, repair, and optimize a table, in addition to other options. To help you write SQL queries, the menu also includes options to generate various kinds of SQL statements.

Useful Functions

DBeaver has some very useful tools to ease many of the routine database administration tasks. One of the most useful ones is the SQL Editor, which has lots of features to ease writing SQL that you would expect from an IDE such as autocompletion, auto-formatting, templates, history, and more.

You can bring up the SQL Editor from the right-click context menu mentioned earlier, or by heading to SQL Editor | SQL Editor (Figure 4). Here you can write the SQL queries either manually with the help of autocomplete or use assistance tools like SQL Templates, which insert frequently used SQL statements. You can also use the Generate SQL option from the right-click context menu to copy and paste common SQL queries.

Figure 4: DBeaver will let you review the changes before it commits them to the database.

Once you’ve written a bunch of SQL statements, you can use the Play button on the left-side of the SQL Editor to run the currently selected statement. The query output will be displayed in the results window below the SQL Editor. What’s more, you can edit the values in the results window and even insert new rows of data. When you make any changes in the Data Viewer, before you save them to the database, you can use the Script button to preview the SQL lines that will make the actual changes to the database.

In fact, the Data Viewer is another interesting feature of DBeaver. Besides letting you edit data inside a table, you can also use it to filter, sort, and colorize the data. These features help make sense of large quantities of data. The top toolbar contains buttons that you can use to apply custom filters to sort the data depending on your needs. There are several ways you can filter the data. One of the easiest is to specify a SQL expression in the filter field above the table or pick one from the predefined templates.

ER diagrams (Figure 5) are the third important tool, for helping visualize the connections between the various tables in the database. ER diagrams in DBeaver are read-only; you can move the elements and rearrange their layout, but these changes cannot be saved. If you want to make any changes or add new elements, DBeaver lets you create custom ER diagrams via New | ER Diagram. This launches a wizard that prompts you to select the tables you want to include in the ER diagram; however, you can add more tables, even for different databases, once the initial diagram has been created. You can then use the diagram tools and add the connections you want between the tables.

Figure 5: You can rearrange the tables in the ER Diagram tab by dragging them all over the workspace, although you can’t save the changes.

As you can see, DBeaver is a very diverse tool that will make a DBA's life a lot simpler. The open source Community Edition supports a wide array of commonly-used databases and rolls in quite a lot of functionality to take the pain out of managing databases in all types of environments. The project has ample documentation on its wiki to help you orient yourself with the tool.