3.2.6: DBeaver

Learning Objectives

1. DBeaver is a universal database management tool that interfaces with multiple database systems, including PostgreSQL.

2. DBeaver allows developers to interface with SQL database using a GUI, using this tool you can make alterations to the database, visualise table relationships and more.

3. Understand how to install and utilise DBeaver to interface with a PostgreSQL database on your machine.

Installation

To install DBeaver please goto this link and follow the installation instructions specific to your operating system. Once you have completed the installation you will be able to interface with your PostgreSQL database, provided it is currently running on your machine. With this in mind, you will have to connect to each database currently running on PostgreSQL if you want to interface with the database.

Open the DBeaver application and you should see something similar to the image below:

DBeaver Interface

For detailed instructions on how to use this tool please refer to this manual.

Interfacing with your database

To interface with your database you will need to configure your database connection, please follow the instructions below:

1. Click on the "Database" menu and select "New Database Connection".

2. In the "Connection Type" dropdown menu select "PostgreSQL".

3. Fill in the required credentials, these should be the same credentials you use in Sequelize to interface with your database, the credentials include:

   1. Host. Eg: "localhost"

   2. Port. Eg: "5432"

   3. Database Name. Eg: "fruit"

   4. Username. Eg: "postgres"

   5. Password. Eg: ""

4. Now you can click the "Test Connection" button to verify that the connection is successful.

5. Once tested click "Finish" to save this connection configuration.

Managing your database with DBeaver

Once DBeaver has been installed and you have connected to your databases you will be able to explore your database structure, all you need to do is expand the connection. This is on the left side of the application.

Navigation DBeaver

From here you can select a database and choose to look at the "Properties" or "ERD". This is on the right side of the application.

DBeaver Table Schema

Within "Properties" you can see the table schemas and even the data currently residing in your tables.

If you click on ERD on the otherhand, an image like below will appear:

DBeaver ERD

Data Manipulation

Using DBeaver you are able to select individual tables such that you can manipulate the data stored within. To do this use the editor portion of the application, and tool bar at the bottom of the page. This affords developers an easier way to manage data instead of interfacing with PostgreSQL using just the CLI.

Where to edit your table

Data Transfer

Another helpful feature of DBeaver is data transfer, this is crucial such that you can export and import data in various formats as well as transferring data between tables within the same or to other databases. Exporting data is rather simple but when you're trying to import data there are a few more steps. If you are interested in attempting this follow this guide.

It should be noted, at Rocket Academy we suggest utilising Sequelize Migrations and Seeders to setup the database, this means that importing data using DBeaver isn't what we suggest. It should be noted you can construct Seeder files using NodeJs's inbuilt module fs and csv-reader or csv-parser. If you would like to explore this style of seed development you can use this article as a reference point, you may need to alter the code to suit your table structure.