The world’s leading publication for data science, AI, and ML professionals.

Some Tips for Using DBeaver – A Universal Database Tool

Learn a handy tool to manage your databases conveniently

Image by WilliamsCreativity (Server Data) in Pixabay
Image by WilliamsCreativity (Server Data) in Pixabay

DBeaver is a universal Database administration tool that can be used to manage all kinds of relational and NoSQL databases. DBeaver has both a community edition (CE) which is free and open-source and a commercial enterprise edition (EE). The community edition supports all kinds of relational databases such as MySQL, PostgreSQL, Oracle, etc. The enterprise edition, on the other hand, also supports NoSQL databases.

DBeaver is very user-friendly and is very easy to get started with. It supports code completion, code formatting, syntax highlighting, and other abundant handy features as will be introduced later. If you have been using other database management tools, it is well worth trying DBeaver. It’s very likely that you will love it.

To make you love it even more, in this post we will introduce some tips that are not straightforward to discover for beginners. These tips will solve the problems you may have when you first start with Dbeaver. We will also explore some features that can boost your work efficiency dramatically.


Preparation

For demonstration purposes, we will use the community edition which doesn’t require registration or a license.

We will use DBeaver to manage a MySQL server which can be started with the following Docker command:

Note that a high port (13306) is used to avoid potential port conflict.

Then run the following command to import some dummy data which can then be played with in DBeaver later.

Note that we use the MySQL client in the Docker container directly so we don’t need to install it separately. If you do want to install a MySQL client locally on your computer, you can install mysql-client which includes both mysql and mysqldump, as well as other tools, which can be helpful to manage your MySQL database on the command line. On Ubuntu, the command is:

$ sudo apt-get install mysql-client

Alternatively, you can create the database and tables in DBeaver directly with a graphical interface, as will be demonstrated below. However, before you can do that, you need to first create a connection in DBeaver for our local MySQL database.


Create a connection in DBeaver

Let’s now create a connection for our MySQL database. Click the "New Database Connection" button and then select MySQL as the database. If you use MySQL 5, you would need to choose MySQL 5.

Image by Author
Image by Author

In the "Connection settings" window, enter the host, port, username, and password. You can test the connection before it’s created:

Image by Author
Image by Author

In case you cannot connect your database due to SSL issues, you need to add two properties, namely "useSSL" and "allowPublicKeyRetrieval" for your driver. You can right-click your connection and choose "Edit Connection" to edit the connection.

Image by Author
Image by Author

The same "Connection settings" window, as shown above, will be opened. Click the "Edit Driver Settings" button to open the "Edit Driver" window. Then click the "Driver Properties" tab in the window opened. Right-click in the window to add two new properties for your driver, namely "useSSL" and "allowPublicKeyRetrieval" whose values should be set to "false" and "true", respectively:

Image by Author
Image by Author

Save the changes to the driver properties.


Set a name for your connection

You can also set a name for your driver, which is helpful when you need to manage multiple databases in DBeaver. In the "Connection settings" window, click the "General" tab and set a name for the connection:

Image by Author
Image by Author

Set SQL Editor Formatting

One of the most charming features of DBeaver is code-autocompletion, auto-case conversion, and syntax highlighting. This is extremely handy because SQL queries are very flexible. You can write your queries in any style and they will always work. However, when it comes to readability, it’s another story. The auto-completion and auto-formatting feature of DBeaver can let you write clean, beautiful, and readable SQL queries very conveniently.

We can set SQL Editor Formatting for a single connection. However, it’s better to set the formatting style globally so it applies to all connections.

Select the "Window" menu and then the "Preferences" submenu. In the "Preferences" window opened, select "Editors" => "SQL Editor" => "Formatting". For the formatting, it’s recommended to set the configurations as follows:

Image by Author
Image by Author

With these settings, your SQL queries will be formatted accordingly if you select your SQL queries and press CTRL + SHIFT + F to automatically format them.


Enable keyword auto case conversion

Another handy feature I enjoy a lot is keyword auto case conversion, namely after a SQL keyword is typed, it will be automatically converted to the corresponding case, which is the upper case as set in the above step. To enable keyword auto case conversion, select "Editors" => "SQL Editor" => "Code Editor" in the "Preferences" window opened above, then enable "Convert keyword case". You can also enable other code conversions that you like:

Image by Author
Image by Author

Write and execute SQL queries in DBeaver

You can right-click on your database connection on the left navigation bar, then select "SQL Editor" => "New SQL script". A new SQL script editor will be opened for you. Now you can start to enjoy writing SQL queries in DBeaver, especially keyword auto case conversion and code auto-completion 😃 .

For demonstration purposes, copy the SQL queries from here and paste them into the SQL editor. Then click the "Execute SQL Script" button to execute them one by one:

Image by Author
Image by Author

Explore Advanced Copy

By default, if you copy multiple records, they will be copied as multiple rows which is not convenient if you want to use the output as the input for other queries or programs. With the "Advanced Copy" feature, you can have more flexible settings and copy the data in the format you want. For example, select all the names as shown below, right-click on the selection, then select "Advanced Copy" => "Advanced Copy…". In the "Options" window opened set the format options as you need:

Image by Author
Image by Author

With these options, the records will be copied as a single string with each item quoted, which can then be used directly in other places.

Image by Author
Image by Author

You can also copy all the rows as Markdown which is convenient for sharing queries and results. First, select the records you want to copy, then right-click on the selected records and select "Advanced Copy" => "Copy as Markdown", you will get the results in the Markdown format which can be shared conveniently.

Image by Author
Image by Author

Display JSON data in a friendly way

If you have a JSON field in your table, it will be displayed as a long string which is not easy to read. You can display the value of a JSON field in a very user-friendly way as shown below:

Image by Author
Image by Author

Export results in various formats

You can also export the results of your queries in various formats:

Image by Author
Image by Author
Image by Author
Image by Author

Just select the format you want and export the data according to the instructions.


Convert to upper case with a shortcut

Last but not least, we can select some characters and press CTRL + SHIFT + X to convert them to upper case. It may seem trivial but it is very handy if you are a SQL perfectionist like me. Also, this shortcut is hidden and cannot be found directly in the top or right-click menus.


DBeaver is a very convenient graphical database administration tool that can be used for various databases. It is fairly intuitive to use and you can get started with it immediately. The tips covered in this post are intended to explore some features of DBeaver that may not be obvious for beginners but can improve your efficiency dramatically.


Related article:


Related Articles