Business

7 Powerful DBeaver Tips and Tricks to Improve Your SQL Workflow

DBeaver is the most powerful open-source SQL IDE, but there are several features people don’t know about. In this post, I will share with you several features to speed up your workflow, with zero fluff.

I’ve learned these as I’m currently digging deeper into the tools I use daily, starting with Dbeaver. In a future post, I’ll compare the workflow between DBeaver versus building your SQL development environment on VSCode (or Cursor). If you’re interested in that, be sure to follow my publications!

Today, though, the focus is on learning the cool features of DBeaver. Let’s get started.

The Command Palette

This is one of the most powerful yet hidden features in DBeaver. Maybe people overlook it because it’s not called a “Command Palette”. You can open it with CMD + 3 (Mac) or CTRL + 3 (Windows).

From here, you can access basically any action in 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 is called “Find Actions.”)

Custom SQL formatter

Did you know you can easily set up a different 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 how it works to set up pg_formatter, but keep in mind that the process will be similar for any SQL formatter that you can call via the terminal.

# Install PG Formatter
brew install pgformatter

# Find where the program is located.
# In my case: opt/homebrew/bin/pg_format
which pg_format

Next, go to Preferences → Editors → SQL Editor → Formatting, select an “External Formatter,” and then paste the path to your desired formatter.

💡 Or you can simply open the command palette and search for “Formatting”.

Expand columns on SELECT

Often, you might need to select most columns from a table, excluding just a few. DBeaver makes this easier by expanding your SELECT * into explicit column names.

You can do this with the CTRL + Space hotkey, both on Mac or on Windows. This might not work if you have it bound to another system shortcut, in which case you can look up for “Content Assist” in the command palette.

Quickly find column statistics

DBeaver has a bunch of features to speed up your analysis. One of them that I use frequently is the “Calc tab”, located to the right of your query results. It lets you quickly get the information about columns in your query results.

Here is what you can do with it:

  • Find the amount 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” lets you quickly create group-by queries without writing SQL manually.

What you can do with it:

  • Quickly count occurrences of values.
  • Add multiple aggregations.

Although this one is pretty nice for simple aggregations, I find it a bit underwhelming as there is no way to count unique values, as I do in 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 save you from repeatedly writing common expressions.

You can see the built-in templates by opening the command palette and searching for “Templates”. You will see shortcuts for:

  • SELECT * FROM {table}
  • SELECT * FROM {table} WHERE {col} = {value}
  • And others, like selecting and ordering, counting by groups, etc.

All you need to do is to write the shorthand for the query and press tab:

You can also create your own templates, which is not hard if you just copy the existing ones and adapt them.

Advanced copy tips

You might already know DBeaver has extensive data export options. However, the standard export wizard can feel a bit overwhelming, as it shows you tons of configurations, even when you just want to quickly export a CSV.

A quicker way is selecting data in the Results tab, right-clicking, and choosing “Advanced Copy”. By doing this, you can copy your data in many formats like CSV, JSON, Markdown, TXT, and even SQL Insert Statements.

I find this one super helpful, especially when I need to quickly send data to a teammate.

A bonus tip is that 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 have to be double advanced, according to DBeaver 😅

Conclusions

I love DBeaver as a SQL IDE. It’s incredibly powerful and the interface is very clean. It’s honestly mindblowing that this tool is free and open-source. If you haven’t tried it yet, I recommend it a lot! 

I haven’t seen that many people talking about some of its nicest features and tricks, and most tips I’ve shared came from just using the software. There is probably a lot that I missed, especially when it comes to Plugins, which I haven’t used much.

I know I went through all of the tips very fast, so if you’re in doubt, feel free to reach out. Also, if you have more workflow tips, I would love to hear about them!


I hope you learned something new!

If you’re curious about other tips that didn’t make it into this post, or enjoy learning about general data topics, subscribe to my free newsletter on Substack. I publish whenever I have something genuinely interesting to share.

Want to connect directly or have questions? Reach out anytime at mtrentz.com.

All images and animations, unless otherwise noted, are by the author

The post 7 Powerful DBeaver Tips and Tricks to Improve Your SQL Workflow appeared first on Towards Data Science.

Picture of John Doe
John Doe

Sociosqu conubia dis malesuada volutpat feugiat urna tortor vehicula adipiscing cubilia. Pede montes cras porttitor habitasse mollis nostra malesuada volutpat letius.

Related Article

Leave a Reply

Your email address will not be published. Required fields are marked *

X
"Hello! Let’s get started on your journey with us."
Site SearchBusiness ServicesBusiness Services

Meet Eve: Your AI Training Assistant

Welcome to Enlightening Methodology! We are excited to introduce Eve, our innovative AI-powered assistant designed specifically for our organization. Eve represents a glimpse into the future of artificial intelligence, continuously learning and growing to enhance the user experience across both healthcare and business sectors.

In Healthcare

In the healthcare category, Eve serves as a valuable resource for our clients. She is capable of answering questions about our business and providing "Day in the Life" training scenario examples that illustrate real-world applications of the training methodologies we employ. Eve offers insights into our unique compliance tool, detailing its capabilities and how it enhances operational efficiency while ensuring adherence to all regulatory statues and full HIPAA compliance. Furthermore, Eve can provide clients with compelling reasons why Enlightening Methodology should be their company of choice for Electronic Health Record (EHR) implementations and AI support. While Eve is purposefully designed for our in-house needs and is just a small example of what AI can offer, her continuous growth highlights the vast potential of AI in transforming healthcare practices.

In Business

In the business section, Eve showcases our extensive offerings, including our cutting-edge compliance tool. She provides examples of its functionality, helping organizations understand how it can streamline compliance processes and improve overall efficiency. Eve also explores our cybersecurity solutions powered by AI, demonstrating how these technologies can protect organizations from potential threats while ensuring data integrity and security. While Eve is tailored for internal purposes, she represents only a fraction of the incredible capabilities that AI can provide. With Eve, you gain access to an intelligent assistant that enhances training, compliance, and operational capabilities, making the journey towards AI implementation more accessible. At Enlightening Methodology, we are committed to innovation and continuous improvement. Join us on this exciting journey as we leverage Eve's abilities to drive progress in both healthcare and business, paving the way for a smarter and more efficient future. With Eve by your side, you're not just engaging with AI; you're witnessing the growth potential of technology that is reshaping training, compliance and our world! Welcome to Enlightening Methodology, where innovation meets opportunity!

[wpbotvoicemessage id="402"]