Getting Started with DataGrip

This is a series of very simple exercises to help you get started with DataGrip.

Defining a database in DataGrip
Opening the Database tool window
Selecting the default schema or database
Composing an SQL statement
Running an SQL statement
Closing the database console
Working with data in the table editor
Opening the Files tool window
Attaching a directory to the project
Creating an SQL file
Specifying an SQL dialect
Defining a table in an SQL file
Creating a DDL data source
Defining data in an SQL file
Learning basic means of navigation

Defining a database in DataGrip

To be able to work with your database in DataGrip, you should define it as a data source.

Open the Data Sources and Drivers dialog: press Ctrl+Shift+Alt+S or select File | Data Sources.
Click and select the database management system (DBMS) that you are using.
In the Name field, if necessary, edit the name of the data source.

Note the message Download missing driver files in the lower part of the dialog.

To interact with a database, DataGrip needs a database driver. The drivers, generally, are DBMS-specific. You can select to download the necessary driver (this is what we'll do), or specify the one that you already have available on your computer.
Click the Download link.
Specify the database settings.
For most of the DB management systems, these are the database host name (or IP address), port, the database name, and also your database user name and password.

If your database is a local file or directory (which may be the case for SQLite, H2, Derby or HSQLDB), you should specify the location of that file or directory. To do that, click to the right of the File or Path field.

Then, select the database file or directory in the dialog that opens.

Finally, the settings for the database may look something like this:
To make sure that the settings are correct and DataGrip can properly interact with your database, click Test Connection.
If all is well, the word Successful appears next to the Test Connection button:
Click OK to save the settings and close the dialog.
As a result, the input pane of the database console for your new data source opens.

Now you are ready to compose and execute your first SQL statement. Before doing that, let's open the Database tool window to take a look at our database.

Opening the Database tool window

Do one of the following:

Select View | Tool Windows | Database.
Press Alt+1.
In the lower-left corner of the workspace, point to and select Database.

The Database tool window opens showing the structure of your data source.

/help/img/idea/2017.1/011DBEDBStructure.png

If the data source structure isn't shown, click on the toolbar. See Synchronizing the view of a DB data source. See also, Showing and hiding schemas.

Selecting the default schema or database

In the input pane of the database console, select the default schema or database from the list in the right-hand part of the toolbar. (This isn't possible for read-only MySQL data sources, see Read-only. See also, Controlling the schema search path for PostgreSQL.)

DataGrip executes the USE <db_name> or similar statement.

Now you can omit the name of the selected schema or database in your statements.

Composing an SQL statement

It seems natural to start with a SELECT statement, e.g.

SELECT *
FROM city

This statement, obviously, retrieves all the records from the table city. In your own statement, most likely, the table name will be different.

Your immediate intention might be to start typing. However, this isn't necessary.
There are predefined patterns for certain "most popular" statements. The corresponding statements can be inserted as a whole and then edited as needed.

Click the editing area of the input pane and then press Ctrl+J to see the list of the patterns. (The main menu equivalent for this key combination is Code | Insert Live Template).

The item of interest at the moment is sel - select all rows from a table, so select this item. (Use the Up and Down arrow keys for moving within the suggestion list, Tab or Enter for selecting a highlighted element.)
Inserting the table name is just a matter of typing a couple of letters and then selecting the necessary name from the suggestion list.
In our case, the table name is suggested right away. In your circumstances, it may be sufficient to type just one or two letters.

When the statement is complete, you are ready to run it.

Running an SQL statement

To run the statement, do one of the following:

Click
Press Ctrl+Enter

The retrieved records are shown in the Database Console tool window.

/help/img/idea/2017.1/018DBERunStatementResults.png

The result pane (the world.city tab on the picture) lets you analyze and manipulate your query results. For retrieved records, it provides the functions similar to those of the table editor. (We'll take a look at the table editor in a moment.)

Closing the database console

We will no longer need the console, so let's close it:

Click to close the Database Console tool window.
Click on the editor tab to close the input pane.

To find out more about the database consoles, see Working with Database Consoles.

Working with data in the table editor

The table editor provides a GUI for working with table data. Let's have a look at some of its main features.

To open a table in the table editor, select the table in the Database tool window and do one of the following:
- Click on the tool window toolbar.
- Press F4.
- Select Table Editor in the context menu.
If the table contains too many records you may want to apply filtering. One way to do that is by using quick filtering options. The quick filtering options are ones for the current column and, in many cases, depend on the value in the current cell.
Right-click the cell of interest, select Filter by and then select the necessary condition.

As a result, the number of records is considerably reduced. The filtering condition is shown in the field above the table.

(The filtering conditions are specified as in a WHERE clause but without the word WHERE. To apply a filter - after editing the condition - press Enter.)
You can search for text in the cells. Press Ctrl+F and start typing the text of interest. As soon as the specified text is found, the corresponding cell is highlighted. After that you can use the Up and Down arrow keys to switch between the cells that contain the specified text.

To exit the Find mode, press Escape.
You can sort data by any of the columns by clicking the cells in the header row. If you click a cell once, the data is sorted in the ascending order. If you click the cell for the second time, the data is sorted in the descending order. Finally, when you click the cell for the third time, sorting by the corresponding column is cancelled.
On the following picture, the data is sorted by the column Percentage in the ascending order.
You can add rows: click or press Alt+Insert.
To start editing a value, press F2 or simply start typing. To enter a value, press Enter; to cancel editing, press Escape.

To save the new row, click on the toolbar, press Ctrl+Enter, or select Submit from the context menu.
You can hide columns: right-click a cell in the header row and select Hide Column.
You can reorder columns by dragging cells in the header row to desired positions.
The quick documentation view is available (View | Quick Documentation, Quick Documentation from the context menu or Ctrl+Q). In certain situations, this view provides more information about the values in the selected cell or cells than shown in the editor.

The Transposed view is available (click Transposed View). In this view, the rows and columns are interchanged. Thus, for a row, the cells are shown one beneath the other. This view is useful for tables with many columns when only a subset of all the columns is visible at a time.

To close the quick documentation view, press Escape.
You can transpose the table. To that, click on the toolbar and select Transpose.

A transposed table may look something like this:

To cancel transposition, turn the Transpose option off.
You can restore the initial table view - one before reordering or hiding the columns, or sorting the data. To do that, click on the toolbar and select Reset View.

Note that filtering in this case is not canceled.
You can delete rows. Select the row or rows, click or press Ctrl+Y, and then click , press Ctrl+Enter or select Submit from the context menu.
To cancel filtering, click , or delete the condition in the filter box and press Enter.
To close the editor tab, click or press Ctrl+F4.

Now let's have a look at some of the features that DataGrip offers for working with SQL files. (For more info on the table editor, see Working with the Table Editor.)

Opening the Files tool window

First of all, we will need the Files tool window which acts as a file manager for files and folders associated with your project.

To open the Files tool window, do one of the following:

Select View | Tool Windows | Files.
Press Alt+2.
In the lower-left corner of the workspace, point to and select Files.

Attaching a directory to the project

To get optimal coding assistance when editing your SQL files, the corresponding files should be stored in directories defined in DataGrip as your "working directories". To make a directory your working directory, you should "attach" that directory to your project.

To attach a directory to the project:

Right-click the content pane of the Files tool window and select Attach Directory.
In the dialog that opens select an existing directory or create a new one. For example, to create a new directory in your home directory, click , then click and specify the directory name (e.g. SampleSQLFiles).

Creating an SQL file

In the Files tool window, select the directory in which you want to create a file. (At the moment, there's only one.)
Do one of the following:
- Select New | SQL File from the context menu.
- Press Alt+Insert and select SQL File.
In the dialog that opens, specify the file name (e.g. family-ddl; we will use this file to define a table that describes a family). Click OK or press Enter.

The new file is shown in the Files tool window.

At the same time, the file opens in the editor.

Specifying an SQL dialect

Each SQL file, normally, is assigned an SQL dialect for better coding assistance. (An SQL dialect is a DBMS-specific version of SQL.)

DataGrip lets you specify the SQL dialect for an individual file, all .sql and .ddl files in a directory, project, or on your computer. In the following example, we'll set the SQL dialect for all the .sql and .ddl in our working directory.

Click the Change dialect to link in the editor. (Alternatively, right-click the editing area, select Change Dialect (<CurrentDialect>), and then select SQL Dialects.)
In the dialog that opens, click and then select your SampleSQLFiles directory.
Click the SQL Dialect cell to the right of your working directory and select the necessary dialect.
Agree to apply the selected dialect recursively (i.e. to all the contents of your working folder).

The result may look something like this:
Click OK.

Defining a table in an SQL file

Now we are going to define the following table structure in our file:

CREATE TABLE family (
  member_id INT NOT NULL,
  name VARCHAR(50),
  relation VARCHAR(50)
);

For inserting the CREATE TABLE statement, there is a predefined pattern. Press Ctrl+J and select the pattern (tab - new table definition).

Here is the result:
Type the table name (family).
To switch from family to col, press Tab. Replace col with member_id.
Type the remaining part of the table definition.

Creating a DDL data source

In addition to databases, SQL files containing data definition language statements (DDL statements) can be used as data sources. As a result, the tables, columns and other objects defined in such files become available for referencing in the editor.

The file family-ddl.sql defines a table and therefore can act as a DDL data source. Let us create a data source using that file.

The quickest way to create a DDL data source is by using drag-and-drop:

Drag the file family-dll.sql from the Files to the Database tool window.

As a result, the corresponding data source is created.

(To create DDL data sources, you can also use the Data Sources and Drivers dialog: Ctrl+Shift+Alt+S, , DDL Data Source, etc.)

Defining data in an SQL file

Now we are going to create an SQL file for adding data to the table family. Because family-ddl.sql is defined as a data source, the names of the table and of its columns as well as the info about corresponding data types are now available in the editor.

Create an SQL file (e.g. family-data.sql).

Let us add this simple INSERT statement into the file:

INSERT INTO family (member_id, name, relation) VALUES (1, 'Chloe', 'mother');
Add the INSERT statement by selecting the corresponding predefined pattern (Ctrl+J | ins - insert rows into a table).
Select the table name (family).
Select the list of column names (member_id, name, relation).
When specifying the values, press Ctrl+P (View | Parameter Info) to see the information about the value types.

Learning basic means of navigation

DataGrip offers many features related to navigation. Let's take a quick look at some of them.

In the file family-data.sql, place the cursor within the column name relation.

To jump to the definition of this column, use Ctrl+B (Navigate | Declaration).
To switch to the view of this column in the Database tool window, press Alt+F1 (Navigate | Select In) and select Database View.

As a result, the Database tool window becomes active and the column relation is selected there.
To jump to the definition of the column in the corresponding source file (in this case - in the file family-ddl.sql), press F4 (View | Jump to Source or Edit Source from the context menu).
To switch to the file family-data.sql in the editor using the navigation bar (the bar above the tool windows and the editor):
Press Alt+Home (Navigate | Jump to Navigation Bar). As a result, the navigation bar becomes active and the current file (family-ddl.sql) is selected there.

Press the Left arrow key to select your working directory, then press the Down arrow key to see the directory contents. Select family-data.sql in the list.

Here is the result:
Now let's have a look at the Switcher that lets you switch between open files and consoles, and the tool windows.
Hold down the Ctrl key and press Tab (don't release the Ctrl key yet).

Use the arrow keys to move in the Switcher; select Files.

When you release the Ctrl key, the Files tool window becomes active.

Last modified: 27 July 2017