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.
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.
- 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.
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.
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.
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 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:
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:
- 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.
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
WHEREclause 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.
- 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.
- 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 (, 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:
- 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
- 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.
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.
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
.ddl files in a directory, project, or on your computer. In the following example, we'll set the SQL dialect for all the
.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 TABLEstatement, there is a predefined pattern. Press Ctrl+J and select the pattern (
tab - new table definition).
Here is the result:
- Type the table name (
- To switch from
col, press Tab. Replace
- 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.
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.sqlfrom the Files to the Database tool window.
As a result, the corresponding data source is created.
Defining data in an SQL file
Now we are going to create an SQL file for adding data to the table
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.
Let us add this simple
INSERTstatement into the file:
INSERT INTO family (member_id, name, relation) VALUES (1, 'Chloe', 'mother');
- Add the
INSERTstatement by selecting the corresponding predefined pattern (Ctrl+J |
ins - insert rows into a table).
- Select the table name (
- 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
- To switch to the view of this column in the Database tool window, press Alt+F1 (Navigate | Select In) and select Database View.
- 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.sqlin the editor using the navigation bar (the bar above the tool windows and the editor):
Press the Left arrow key to select your working directory, then press the Down arrow key to see the directory contents. Select
family-data.sqlin 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).