If a project you are working on requires referring to an SQL database, you can, actually, use one of the numerous SQL clients, or type commands in the command line - and regularly switch between frames. Now IntelliJ IDEA suggests a better choice. Since version 8.0, you have a dedicated Database tool window at your disposal, which allows you to work with the data sources without leaving IntelliJ IDEA.

Let’s have a 10,000-feet look at IntelliJ IDEA’s database support in action.

First, configuring a data source is now just a snap – press Insert, specify your data source parameters and make sure you are connected. By the way, you can use the old good External Tools dialog to create start and stop scripts for a database server, if needed.

You can now create SQL scripts, enjoying the famous IntelliJ IDEA’s coding assistance, which includes syntax and error highlighting, code completion for keywords, table and column names, and more.

From the Database tool window you can launch JDBC console for a data source of your choice. It is very easy – just select the desired data source, and click the run button on the toolbar. Console adds a tab to the Run tool window. Here you are: the upper panes are intended for SQL commands, the lower pane displays messages.

Let’s start typing SQL commands. As you see, syntax highlighting and code completion are also available here:

Should I also mention that you can find here the handy editor shortcuts that have already become a habit for the IntelliJ IDEA users? Among them, pay attention to “Go to Declaration”. If you place the cursor at the table name, and click Ctrl+B, the corresponding table will be highlighted in the data source.

The console can contain as many commands as required. You can run them all at once (Ctrl+Enter), or execute one by one, in the order you consider appropriate (Ctrl+Shift+Enter), and see results in the lower pane:

Suppose you want to retrieve data from the various tables. Instead of repeatedly entering the same command with the changing parameters, use variables, which are just strings enclosed in $, #, or ?{}. When such a string is encountered in a command you type in the left pane, it is added to the list of parameters in the right pane, where you can enter the desired values:

What happens if you use wrong syntax? An error is marked same way as in the editor, with the red underline, and diagnostics at the tooltip. If you want to change SQL dialect, just click the properties button in the console toolbar, and select one of the supported dialects.