How do I pretty format my SQL query in DBeaver?

Question

I use DBeaver for my main IDE to query multiple databases. When I query a table, it automatically generates a long select statement with all columns in one line.

When I am making more complex queries, this gets very cumbersome to work with and read. I would like to view the select values on separate lines rather than one long string.

Is there an easy way (perhaps by using Notepad++ or Sublime Text) to break up the query (with line breaks after the commas)?

For example,

select name, address, city, state, country, zip_code, birthday, membership_type 
from customers

to

select
name,
address,
city,
state,
country,
zip_code,
birthday,
membership_type
from customers

Pied

Google "sql tidy" and you'll find a number of results that do what you want. Try sql-format.com – Pomade

See also https://mcmap.net/q/350141/-notepad-eclipse-sql-code-auto-indent-option/1015495 – Pomade

Answer 1

DBeaver has a shortcut for this purpose. In SQL Editor:

Update May 2024:

DBeaver as of at least version 24.0.3.202404211624 has moved the Format SQL menu item to the Edit menu.

Goldshlag

To add here in case anyone else is struggling with this, I noticed that formatting of multiple sql statements doesn't work as expected if they don't have a semicolon after each of them. Once semicolons are added, it works great, as of version: 6.1.2.201907061627 – Manipur

Best answer because it's not using any other tool apart from DBeaver – Koblick

Answer 2

Yes there is. I use Notepad++ because it is free and I use it for everything! Anyways, Notepad++ has a Plugin called: Poor Man's T-SQL Formatter. It works with a click of a button. You also get a few custom options like if you want to auto capitalize table names, etc.

Suttle

I'm wondering how to use this with DBeaver. I picked the external formatter option and put "C:\Users\user\Downloads\SqlFormatter.1.6.10\SqlFormatter.exe" ${file} in the command line. I'm using a temp file. I set the timeout to 10 seconds. It doesn't error, but it doesn't format either. – Clandestine

Here's a link to the GitHub page where I asked this too. github.com/TaoK/PoorMansTSqlFormatter/issues/285 – Clandestine

Answer 3

Select a query to be formatted and Ctrl+shift+F does the work. I am using DBeaver 6.3.0

Chew

Answer 4

There is another external SQL for DBeaver. It can be found here: SQLinForm SQL Formatter and can be included as external Formatter like this

Piles

The link is 404. – Stressful

Answer 5

In SublimeText I assume you have already installed Package Control? If not do so as soon as you can, it is a vital tool.

From Package Control there are a number of options to do what you want to do. Here are some options

I would start with SqlBeautifier and then try others to find which one suits your taste best.

Logistician

Answer 6

Write click on the screen, this menu will be pop-up, and follow the instructions shown in the attached image.

Shaum

Can you describe how is your answer improving the answer from Yurii? – Alexandriaalexandrian

Answer 7

You can also use Prettier SQL Formatter. To do so you need to install it using npm or yarn as explained in the README:

npm install sql-formatter

Then go into DBeaver preferences -> SQL Editor -> Formatting. Choose External formatter and then add the command to run the sql formatter:

"C:\Program Files\nodejs\npx.cmd" sql-formatter -l postgresql -c "path_to_config_file.conf"

The -l option speficies the SQL dialect (you can skip it). The optional -c option allows you to specify a config file to customize the way you want your queries to be formatted (again more info in Prettier SQL formatter README).

Where you need to replace "C:\Program Files\nodejs\npx.cmd" with the correct path to your npx.cmd file, and if needed replace the path to the prettier config file "path_to_config_file.conf".

Blakey

Answer 8

use backtick ` instead of quote " or '

Knowle

What question are you answering? – Kei

in my sublime, if i have a long query statement and i want to break it, i use backtick. for example: var sql="select table1_id, table1_name from table1 where table1_id=? and table1_status=1" ; it will take one line only but using backtic ` i can break it into multiple lines for convenience purpose. – Knowle