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 the 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, a message box looking similar to this is shown:
- If the settings you have specified identify more than one database or schema, you can select the database or schema you are going to work with. To do that, use the Schemas tab.
-
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.
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.)
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:
The retrieved records are shown in the Database Console tool window.
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:
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:
-
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 wordWHERE
. 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, press Ctrl+Enter or select Submit New Row 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
( 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:
-
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. Use or Ctrl+Y to delete the selected row or rows.
- To cancel filtering, delete the condition in the filter box and press Enter. Alternatively, use . In the latter case, the filter box is also hidden. (To show it again, click and select Row Filter.)
- 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.
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:
-
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 the 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.)
The SQL dialect can be specified at the level of a separate SQL file, directory or project. In the following example we'll set the SQL dialect for our working directory as a whole.
- 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 the SQL Dialect cell to the right of your working directory and select the necessary dialect. As a result, all the files in that directory will inherit the specified dialect.
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
). -
Replace
col
withmember_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 namerelation
.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 ( ). 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 and the file
family-data.sql
is selected there.