Understanding SQL best practices is essential for anyone who uses SQL daily. Our previous articles showed you the most convenient shortcuts for SQL Editor and Data Editor, which help save time and optimize repetitive tasks. Today, we will take a step forward and tell you about SQL templates, another helpful capability of DBeaver.
SQL query templates are combinations of characters that you can use to insert frequent statements into a script. To apply a template, you just need to type this combination into the SQL Editor and press the hotkey instead of manually entering your query. This functionality is available in all DBeaver desktop applications.
Today, we will show you how to use the default templates and create custom ones suitable for your particular case. We will use the SQLite Sample Database for our examples. However, you can use all the templates with any databases supporting SQL syntax.
The default templates and how to use them for your SQL queries
By default, DBeaver has five predefined SQL templates. To see the available ones, press Ctrl+Alt+Space or right-click the line in the script pane and choose SQL Template on the context menu. A box with a list of available templates appears.
You can switch between them using the mouse or the arrow keys on your keyboard. Once you have selected the one you want, press Enter.
Let’s see how it works with the example of the “Select All” statement. Choose the “sf” template from the list and press Enter. The ready-made statement will appear in the SQL Editor. All you have to do is select the table name from the drop-down list.
However, using templates becomes even easier if you already know the combination of characters you need. In this case, it is enough to type it in the SQL Editor and simply press the Tab button. Let’s try to do this with the “swhere” template. Type “swhere” and press Tab.
We get a complete query with a WHERE clause. The first table of our database and its first column appear automatically, and the word “value” is used as a placeholder. All that remains is to insert the table and column names and the value we need into the right places in the SQL code.
To improve the readability of your query in DBeaver, you can format it in a couple of clicks.
Just right-click on the statement and go to Format -> Format query.
But that’s not all. Additionally, you can create new templates and change or delete the default ones.
How to configure templates in DBeaver
Let’s say your frequent task is to delete rows from tables that match specific conditions. To save time, let’s create a custom template for this statement. To do this, open Preferences, go to Editors -> SQL Editor -> Templates, and click the New button.
In the window that opens, you need to set the template name and description and define the template pattern. You can use any short combination of letters that is convenient for you and easy to remember. We’ll name this template “df” using the first letters of the words “delete” and “from”, which is the common practice.
You must use the variables “table” and “column” accordingly for table and column names. Each variable begins with a dollar sign and is enclosed in curly braces.
This link in our Documentation provides a list of available variables. Once you type the dollar sign, you can see the full list of options.
After setting up the new template, click the Apply and Close button. Now, we can check if the new template works as we want. Type “df” into the SQL Editor and press the Tab button.
Great, the new template worked! Now, we can select the table and column names and set the condition to delete the desired row, and the query is ready.
From this article, you learned how to use templates to speed up the writing of similar types of queries that you need most often. We also recommend reading our blog posts about the most convenient shortcuts for existing and new SQL scripts.