If you're interested in learning how to use dBeaver, then this is the video for you. In this video, you'll learn how to connect to a database using the SQL editor dBeaver, write and run queries, learn about the toolbar, view the object explorer, import and export data, and a range of handy tips. If you want to follow along with this video, you'll need to have dBeaver installed, as well as a database such as MySQL or PostgreSQL.
If you don't have dBeaver, You can download it from the dBeaver website for free at dBeaver.io. If you need a database, you can search for one such as MySQL and download and install it. I've got separate videos on how to do that on my channel.
Let's get started. The first step is to open dBeaver. Here's the main window. First, we'll create a connection to the database.
Click on the new connection wizard button on the toolbar, or in the database navigation toolbar. Or go to the database menu and select New Database Connection. In the Connect to Database window, select your database.
dBeaver supports a lot of databases, so scroll through the list to find the one you want, or use the search box at the top. You can also use the tabs on the left to filter to different types of databases. In this example, we'll connect to a PostgreSQL database, so I'll find PostgreSQL and select it.
At the bottom of the screen, you can choose between Simple or Advanced Connection View. Simple will give you simplified or read-only access to the database. We'll leave this as Advanced and click Next.
On the Connection Settings screen, enter the details for your connection. I'm running a Postgres database on my computer, so I'll enter the host of localhost. Enter the username and password for your database.
You can look at the other tabs in this window to see additional settings to change, but for now we'll leave them as the default. Click on the Test Connection button on the bottom left to check that the details are correct and that you can connect. We can see here that it is successful. If you get any errors connecting, I would suggest ensuring that the details you entered are correct, and if they are, look up the issue you're having on Google.
Finally, click Finish, and your connection is created. You'll see the connection on the left side of the screen in the database navigator. dBeaver will also connect to the database.
If you want to connect to the database yourself, which can be helpful if you have multiple connections, or have already created the connection, you can right click on the connection here and select connect. dBeaver will then connect to the database. Let's write some SQL. We'll start with creating a table, then add some data to it, then select the data and see the results.
Once you connect, you'll see a small green tick on the connection on the left, but you won't see anywhere to enter SQL. To enter SQL, you need to open a new SQL editor tab. To do this, click on the new SQL editor button here. A new tab is shown in the main part of the editor, which is the SQL editor. Here is where you enter SQL code that you want to run.
It has the name of the connection inside these angled brackets, and a script number after it. We'll start by entering a create table statement. We'll create a simple table here, with one column.
You can see that the syntax is highlighted. The create table and varchar is in a dark red, and the table name and column name is in black, and the size of the column is in blue. Once you've written the statement, it's time to run it.
You can do this in several ways. You can click on the first orange triangle here that says Execute SQL Statement, or you can right-click on the statement and select Execute, then Execute SQL Statement. Or you can use the keyboard shortcut of Control and Enter.
When you run the statement, you'll see a tab appear at the bottom of the screen. If the statement was successful, you'll see a result like this. It shows the SQL statement in green, as well as the number of updated rows, the query, and the finish time.
Our table is created, so let's insert some data. We can go back to our SQL editor and enter an insert statement. We'll insert one record into the table.
This time, let's see another way of running the SQL statement. The second icon on the panel here is Execute SQL in New Tab. If we click it, we'll see a second tab of results appear at the bottom of the screen, showing the outcome of our query. The data is inserted, and it says updated rows of 1. This new tab is helpful when we want to keep the tab open from a previous query.
Otherwise it would have been replaced by this query's output. So we've got a table with some data. Let's run a select query to see the data.
We can write a quick select query here. As we're writing it, we may see the table name appear in the autocomplete list. You can press enter to select the table and add it to your query.
Run this query using any of the methods we've seen so far. You may get a message like this, saying there are some unpinned result tabs and asking if you want to close them. This is helpful if you want to keep some results.
For now, we'll click yes to close those tabs. When the query is run, we'll see the results here. It has a new tab, and the results show one row and one column, which is what we inserted.
At the top of the main area of the screen we have our SQL editor. dBeaver uses a tab concept for the SQL editor, like many IDEs. This means you can add new tabs, which can be useful if you want to preserve the queries in one window but keep working.
You can create a new tab in a couple of ways. On the toolbar you can click the SQL editor button here. This will open a new window that will show you a list of recent scripts you have opened if you want to reopen it. Or create a new script. Click the New Script button and a new tab is opened.
It's connected to the same database, and it's ready for you to start writing a query. Alternatively, you can click on the New SQL Editor button on the toolbar to create a new tab. Now that you have a new tab open, you can enter another query here and run it. It will have its own results panel too.
This can be helpful if you have a lot of queries you're working on at the same time. Let's look at the concept of transactions and committing data in Deepiva. There's a section in the middle of the toolbar here about committing and transaction control.
There are a couple of buttons here that are greyed out, labeled commit and rollback. We'll come back to those shortly. The next button allows you to change between auto commit and manual commit. Auto commit is on by default.
which means every statement that updates data will be saved automatically. Manual commit gives you the ability to commit your changes to save them permanently, rollback your changes, which is like an undo. This commit and rollback concept is a feature of relational databases and isn't specific to Postgres or Dbeaver.
I'll create a video soon that explains this concept further. I've also got a course inside the Database Star Academy that explains what this is and shows some examples. Click this button to swap between Auto and Manual.
You'll notice that the Commit and Rollback buttons are enabled. The white box to the right that said Auto now changes to a yellow box that says None. This will display the number of statements that have been run and are yet to be committed.
We can run an insert statement now and we can see it is successful. That box at the top is now green and says 1, which means there is one statement that is not yet committed. You can commit it or save it to the database permanently by pressing the commit button. The box changes back to yellow and a label of none. The button to the right of this allows you to view the transaction log, pending transactions or the query manager.
Let's have a quick look at the other buttons on the toolbar and what features they are. On the left, we have a series of connection buttons. We can create a new database connection using this first which also includes a drop-down arrow to select a database type.
The next few buttons allow you to connect to a selected database from your connection list, reconnect to a database or to disconnect. The next section we just looked at and allows you to create new SQL editor tabs in the main area of dBeaver. We've just discussed the commit and rollback, so let's move to the next area. This section shows the label of Postgres, which is the name of the connection that's being used currently. You can click on the drop down to change the connection easily, which is helpful if you have multiple connections set up in dBeaver.
The next drop down lets you choose the schema. Here it says we're using public at Postgres, but there are several other schemas I have created. This lets you specify the schema that you want to run your SQL on, to ensure you're creating tables in the right place, for example.
The button shows the database dashboard, which shows a few things on the database such as server sessions, Transactions Per Second and Block IO. The next button lets you show a view of database tasks, perform a search and go to the last edit location. Many SQL editors have a way to browse the objects on the database, and dBeaver is no different. It's called Database Navigator and you can see it on the left of the screen.
We saw it earlier in the video when we created a connection. The top of the list is the connection, which is shown as Postgres here. If you click on the arrow to the left, it will expand the connection and show the database of Postgres. Expand the database and you'll see a range of folders.
Schemers, Roles, Administers, Extensions, Storage and System Info. There are a lot of handy lists and features under each section, but the one we'll focus on is Schemers. Expand the Schema section and you'll see a list of all the schemas. On my database I've got a few here, but yours may be empty or just show a couple such as public.
The schema that you're currently working on is bolded, which is the public one here. We can expand any of these schemas to see another level of items. This level represents the different object types on the database, such as tables, views, materialized views, indexes and more.
You can expand any of these categories to see the objects in this schema. We can see a range of tables inside the section here. Next to each table there's a number and a grey bar behind the number. You can hover over the number to see a description, but this represents the size of the table on the disk, which shows you the rough size of the table. Speaking of tables, dBeaver lets you import and export data in tables.
Let's look at how to import data in dBeaver. Find the table in the database navigator that you want to import data into. Right click on it and select Import Data. Or if you don't have a table created yet, you can right click on the schema and select Import Data.
This data transfer window will open. Select the source of your data, which could be a CSV file or another table. We will select CSV for this example. Browse to the location of the CSV file you want to import and select it.
On the next screen, you can change some settings for importing the file, such as the column delimiter, escape character and date time format. Click Next. On the next screen, you can select the mappings between the CSV file and the table columns.
You can see both the column names and the data types in the CSV file and the table. Click Next. On this screen, you can set a range of options for importing the data, such as open a new connection and commits after insert.
Click Next. On the confirmation screen you can see a summary of what you've selected. When you're ready to import, click start.
The data will start importing and will run in the background. dBeaver also lets you export data from the database. To do this, right-click on the table you want to export and select export data. Or if you want to export multiple tables, Use the control or command buttons and click to select the tables you want to export. Then right click and select export data.
A data transfer window will appear. In this step you can select the format of the data export. There is a range of formats to export into and you can choose whichever one suits you best. For this example I'll choose SQL which will export the data into a series of SQL insert statements. Click next.
The next screen is the settings screen. On this page you can change a range of options. On the left we have the extraction settings which define how the data is read from the database.
On the right is the format settings which lets you specify how the data is created or written to the file. Click next once you have changed any settings you want. On the output screen you can select the directory to save the file.
In the file name pattern we have the format of the file name which is created automatically by the export process. This shows the file name will contain the table name and the timestamp of this export. You can change a few other settings on this page. Click next when you're ready.
On the confirm page, you can see a summary of the settings you have selected. Once you're ready to export, click start. The export process will run, and the file will be created once it's finished.
So that's how you can export data from dBeaver. Let's take a look at some helpful tips for using dBeaver. First, dBeaver includes a projects feature which can be accessed by the tab next to the database navigator. It includes the connections you've created, bookmarks, ER diagrams and scripts. Each of these can contain different resources that you want to combine and access from a single location.
This projects feature deserves its own video, but it can be helpful to access a range of files for your work. dBeaver has some great formatting and code completion features. which you may have noticed in this video or as you're using it. There are quite a few settings that can be tweaked to fit your style.
You can view and change these by going to Window and then Preferences. Expand the Editors section, then SQL Editor. Browse through each of the sections here, such as Code Completion and Formatting, to view what settings are available.
Make changes to the settings that you want changed, and click Apply and Close. The final handy tip I wanted to share was automatically creating an ERD of your database. To do this, you can click File, then New.
Expand the dBeaver tab and select ER Diagram. Click Next. Give your diagram a name and select the tables or schemas you want to include and click Finish.
You'll see something like this. A diagram that shows all of the tables selected, with primary keys in bold, and relationships between tables. This is a great way to visualize the tables and relationships in your database.
If you want to learn more about setting up a database on your computer, Take a look at some other videos on my channel. I've used a Postgres database in Docker, which is quite simple to set up, and I've got a video that demonstrates how to do it here. If you want to learn more about database design and SQL, visit my website at databasestar.com. If you liked this video, consider subscribing to my channel.
Thanks for watching.