DataGrip is a universal tool for dealing with databases. It supports PostgreSQL, MySQL, SQL Server, Oracle, and many other databases.
One of the most important activities for anyone working with databases is writing SQL. Built on IntelliJ Platform, DataGrip makes coding an enjoyable experience by automating repetitive tasks: as an SQL developer, you probably have many!
Let’s have a closer look at some of the nice SQL-related perks inside DataGrip.
1. Live Templates
DataGrip has code snippets called Live Templates. Use them to generate repetitive parts of SQL code.
There are some pre-built snippets for the most common tasks. Every live template has its own abbreviation — just type the abbreviation and then press Tab to fill in the rest of the code. For example, sel can be used for SELECT * FROM, while ins will provide the INSERT statement. Look at the Live Templates in action:
It is even possible to create custom templates for even greater efficiency. To do this go to Settings | Editor | Live Templates.
2. Introduce alias
This SQL refactoring is arguably one of the most important. Get your stopwatch ready as you are about to see how much time this saves you! Put the caret on the table name and press Alt+Enter → Introduce table alias.
3. Expand wildcard
Since using wildcards is not recommended in many cases, DataGrip can expand it to list all the columns of the table for you. In the event of conflicts, identifiers will be qualified with the table or alias names. JOINs are also respected!
4. Postfix completion
This type of code completion is especially useful when real life doesn’t match the logic of how the code should be written. The best example is the SELECT statement: you need to think about the table you want to select fields from, and then you can decide which particular fields to select. Postfix completion lets you choose which order you do your coding.
An example would be if you type table_name.afrom you will get a list of all the table’s columns. Or, typing identifier.cast will generate the casting code for you.
5. Multiple carets
Multiple cursors can be an efficient way to modify code. Especially in SQL, where similar rows are often used. To do this in DataGrip, press Ctrl (Alt for OSX) twice and hold it, then place new cursors with the arrow keys.
There is the option to place cursors everywhere matches have been found. Invoke Select All Occurrences by Shift+Ctrl+Alt+J (Ctrl+Cmd+G for OSX) when the caret is on the word you want.
Did you notice? The clipboard is also multiline!
6. Inline renaming
When you rename an object inside SQL, it also renames all the other usages of it inside your code. To rename, press Shift+F6.
7. Abbreviation completion
Databases often have names with the same prefixes or first words which makes the normal kind of code completion not very useful: you may need to type the whole word to define a particular object. Abbreviation completion will save you here!
If you have names in camelCase or objects-with-hyphenated-names, just use the first letters of the words – it’s all you need, the rest will be completed with the suggestion.
The same for keywords: ob works for ORDER BY.
8. Code formatting
You can have your SQL code formatted automatically, based on flexible user-defined code styles. Don’t spend time formatting legacy code – let DataGrip do it for you with a single shortcut Ctrl+Alt+L (Cmd+Alt+L for OSX)!
Adjust the code style in Settings | Editor | Code Style | SQL where you can, for example, define whether you capitalize keywords or not. Your style can be shared with your colleagues, so you are all working to the same standard.
9. Toggling comments
Toggle with your comments to make it easier to manage your code. Choose what type of comments you want to use – block or line-by-line (if it’s supported by your SQL dialect).
To comment with a line comment press Ctrl+/ (Cmd+/ for OSX).
To comment with a block comment press Alt+Ctrl+/ (Alt+Ctrl+/ for OSX).
That’s it! We hope this article was useful for existing DataGrip users and also helps those who have never tried DataGrip learn a little bit about it.
Your DataGrip Team
The Drive to Develop