DBeaver is probably the most powerful open-source SQL IDE, but there are several features people don’t learn about. On this post, I’ll share with you many features to hurry up your workflow, with zero fluff.
I’ve learned these as I’m currently digging deeper into the tools I exploit every day, starting with Dbeaver. In a future post, I’ll compare the workflow between DBeaver versus constructing your SQL development environment on VSCode (or Cursor). In the event you’re excited by that, make sure you follow my publications!
Today, though, the main target is on learning the cool features of DBeaver. Let’s start.
The Command Palette
That is probably the most powerful yet hidden features in DBeaver. Perhaps people overlook it since it’s not called a “Command Palette”. You’ll be able to open it with CMD + 3 (Mac) or CTRL + 3 (Windows).
From here, you may access principally any motion within the IDE. I mostly use it for:
- Switching between Sql scripts.
- Navigating to specific settings.
- Quickly accessing actions like Export Results, Refresh Schema, Open Templates, Rename File, etc.
(Officially, this feature in DBeaver known as “Find Actions.”)
Custom SQL formatter
Did you recognize you may easily arrange a unique formatter in DBeaver? I’m personally not a fan of the default formatting, and since I mainly use PostgreSQL, I prefer pg_formatter.
Let me show you the way it really works to establish pg_formatter, but bear in mind that the method might be similar for any SQL formatter you can call via the terminal.
# Install PG Formatter
brew install pgformatter
# Find where this system is positioned.
# In my case: opt/homebrew/bin/pg_format
which pg_formatNext, go to Preferences → Editors → SQL Editor → Formatting, select an “External Formatter,” after which paste the trail to your required formatter.
💡 Or you may simply open the command palette and seek for “Formatting”.
Expand columns on SELECT
Often, you may need to pick most columns from a table, excluding just a number of. DBeaver makes this easier by expanding your SELECT * into explicit column names.
You’ll be able to do that with the CTRL + Space hotkey, each on Mac or on Windows. This won’t work if you will have it sure to a different system shortcut, during which case you may look up for “Content Assist” within the command palette.
Quickly find column statistics
DBeaver has a bunch of features to hurry up your evaluation. Certainly one of them that I exploit incessantly is the “Calc tab”, positioned to the proper of your query results. It helps you to quickly get the data about columns in your query results.
Here’s what you may do with it:
- Find the quantity of unique and non-null values of categorical columns.
- Get min, max, mean, median, etc., of numerical columns.
Very handy for quickly understanding your dataset!
Ad-hoc groups
Similarly to the Calc tab, the “Groupings tab” helps you to quickly create group-by queries without writing SQL manually.
What you may do with it:
- Quickly count occurrences of values.
- Add multiple aggregations.
Although this one is pretty nice for easy aggregations, I find it a bit underwhelming as there isn’t any solution to count unique values, as I do within the GIF above, without having to manually write the metric function.
SQL templates
SQL Templates are extremely powerful, although I admit I don’t use them as often as I should. Templates prevent from repeatedly writing common expressions.
You’ll be able to see the built-in templates by opening the command palette and trying to find “Templates”. You will notice shortcuts for:
SELECT * FROM {table}SELECT * FROM {table} WHERE {col} = {value}- And others, like choosing and ordering, counting by groups, etc.
All it’s essential do is to write down the shorthand for the query and press tab:
You can even create your personal templates, which just isn’t hard when you just copy the prevailing ones and adapt them.
Advanced copy suggestions
You may already know DBeaver has extensive data export options. Nonetheless, the usual export wizard can feel a bit overwhelming, because it shows you tons of configurations, even if you just wish to quickly export a CSV.
A quicker way is choosing data within the Results tab, right-clicking, and selecting “Advanced Copy”. By doing this, you may copy your data in lots of formats like CSV, JSON, Markdown, TXT, and even SQL Insert Statements.
I find this one super helpful, especially when I would like to quickly send data to a teammate.
A bonus tip is you can copy this data to TSV, which gets properly recognized by Excel and Google Sheets into their proper cells! For this one though, you will have to be double advanced, based on DBeaver 😅
Conclusions
I really like DBeaver as a SQL IDE. It’s incredibly powerful and the interface may be very clean. It’s truthfully mindblowing that this tool is free and open-source. In the event you haven’t tried it yet, I like to recommend it so much!
I haven’t seen that many individuals talking about a few of its nicest features and tricks, and most suggestions I’ve shared got here from just using the software. There’s probably so much that I missed, especially in terms of Plugins, which I haven’t used much.
I do know I went through all of the information very fast, so when you’re doubtful, be at liberty to succeed in out. Also, if you will have more workflow suggestions, I might love to listen to about them!
I hope you learned something latest!
In the event you’re inquisitive about other suggestions that didn’t make it into this post, or enjoy learning about general data topics, subscribe to my free newsletter on Substack. I publish every time I even have something genuinely interesting to share.
Wish to connect directly or have questions? Reach out anytime at mtrentz.com.
