DataGrip 2016.3 Help

Getting Started with DataGrip

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

Defining a database in DataGrip

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

  1. Open the Data Sources and Drivers dialog: press Ctrl+Shift+Alt+S or select File | Data Sources.
  2. Click /help/img/idea/2016.3/new.png and select the database management system (DBMS) that you are using.
    /help/img/idea/2016.3/001DBECreateDataSource.png
  3. In the Name field, if necessary, edit the name of the data source.
    /help/img/idea/2016.3/002DBEDataSourceName.png

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

    /help/img/idea/2016.3/003DBEDownloadDBDriver.png

    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.

  4. Click the Download link.
  5. 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.

    /help/img/idea/2016.3/004DBEMySQLConnectionSettings.png

    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 /help/img/idea/2016.3/browseButton.png to the right of the File or Path field.

    /help/img/idea/2016.3/005DBESQLiteFile.png

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

    /help/img/idea/2016.3/006DBESelectSQLiteFile.png

    Finally, the settings for the database may look something like this:

    /help/img/idea/2016.3/007DBESQLiteSettings.png
  6. 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:

    /help/img/idea/2016.3/008DBEConnectionSuccessful.png
  7. 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.

    /help/img/idea/2016.3/010DBEDataSourceCreated.png

    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 /help/img/idea/2016.3/show_tool_window_bars.png and select Database.
    /help/img/idea/2016.3/010-1DBEOpenDatabaseToolWindow.png

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

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

If the data source structure isn't shown, click /help/img/idea/2016.3/refresh.png 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.)
    /help/img/idea/2016.3/011-1DBESelectSchema.png

    DataGrip executes the USE <db_name> or similar statement.

    /help/img/idea/2016.3/011-2DBESelectSchemaResult.png

    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.

  1. 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).

    /help/img/idea/2016.3/012DBELiveTemplates.png

    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.)

  2. 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.

    /help/img/idea/2016.3/014DBEInsertTableName.png

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

    /help/img/idea/2016.3/017DBEStatementReady.png

Running an SQL statement

To run the statement, do one of the following:

  • Click /help/img/idea/2016.3/run.png
  • Press Ctrl+Enter

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

/help/img/idea/2016.3/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:

  1. Click /help/img/idea/2016.3/close.png to close the Database Console tool window.
  2. Click /help/img/idea/2016.3/close1.png 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.

  1. To open a table in the table editor, select the table in the Database tool window and do one of the following:
    • Click /help/img/idea/2016.3/DataTables.png on the tool window toolbar.
    • Press F4.
    • Select Table Editor in the context menu.
  2. 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.

    /help/img/idea/2016.3/051DBE_TE_FilterBy.png

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

    /help/img/idea/2016.3/052DBE_TE_Filtered.png

    (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.)

  3. 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.
    /help/img/idea/2016.3/053DBE_TE_Search.png

    To exit the Find mode, press Escape.

  4. 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.

    /help/img/idea/2016.3/054DBE_TE_Sort.png
  5. You can add rows: click /help/img/idea/2016.3/DBAddRow.png 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.

    /help/img/idea/2016.3/055DBE_TE_AddRow.png

    To save the new row, press Ctrl+Enter or select Submit from the context menu.

    /help/img/idea/2016.3/056DBE_TE_AddRowLastCell.png
  6. You can hide columns: right-click a cell in the header row and select Hide Column.
    /help/img/idea/2016.3/057DBE_TE_HideColumn.png
  7. You can reorder columns by dragging cells in the header row to desired positions.
  8. The quick documentation view is available (View | Quick Documentation 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.
    /help/img/idea/2016.3/058DBE_TE_ValueView.png

    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.

    /help/img/idea/2016.3/059DBE_TE_TransposedRowView.png

    To close the quick documentation view, press Escape.

  9. You can transpose the table. To that, click /help/img/idea/2016.3/IconDBToolbarMenu.png on the toolbar and select Transpose.
    /help/img/idea/2016.3/059DBE_TE_TransposeTable.png

    A transposed table may look something like this:

    /help/img/idea/2016.3/059DBE_TE_TransposedTable.png

    To cancel transposition, turn the Transpose option off.

  10. You can restore the initial table view - one before reordering or hiding the columns, or sorting the data. To do that, click /help/img/idea/2016.3/IconDBToolbarMenu.png on the toolbar and select Reset View.
    /help/img/idea/2016.3/060DBE_TE_RestoreOrderAndVisibility.png

    Note that filtering in this case is not canceled.

    /help/img/idea/2016.3/061DBE_TE_OrderAndVisibilityRestored.png
  11. You can delete rows. Select the row or rows, click /help/img/idea/2016.3/DBDeleteRows.png or press Ctrl+Y, and then select Submit from the context menu.
    /help/img/idea/2016.3/062DBE_TE_DeleteRow.png
  12. To cancel filtering, click /help/img/idea/2016.3/clear.png, or delete the condition in the filter box and press Enter.
    /help/img/idea/2016.3/063DBE_TE_FilterCancelled.png
  13. To close the editor tab, click /help/img/idea/2016.3/close1.png 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 /help/img/idea/2016.3/show_tool_window_bars.png and select Files.
    /help/img/idea/2016.3/019DBEOpenFilesToolWindow.png

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:

  1. Right-click the content pane of the Files tool window and select Attach Directory.
    /help/img/idea/2016.3/020DBEAttachDirectory.png
  2. 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 /help/img/idea/2016.3/userHome.png, then click /help/img/idea/2016.3/icon_newFolder.png and specify the directory name (e.g. SampleSQLFiles).

Creating an SQL file

  1. In the Files tool window, select the directory in which you want to create a file. (At the moment, there's only one.)
  2. Do one of the following:
    • Select New | SQL File from the context menu.
    • Press Alt+Insert and select SQL File.
    /help/img/idea/2016.3/021DBENewFile.png
  3. 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.
    /help/img/idea/2016.3/022DBENewFileName.png

    The new file is shown in the Files tool window.

    /help/img/idea/2016.3/023DBENewFileCreated.png

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

    /help/img/idea/2016.3/023DBENewFileCreatedEditor.png

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.

  1. Click the Change dialect to link in the editor. (Alternatively, right-click the editing area, select Change Dialect (<CurrentDialect>), and then select SQL Dialects.)
  2. 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.
    /help/img/idea/2016.3/024DBESQLDialects.png

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) );
  1. For inserting the CREATE TABLE statement, there is a predefined pattern. Press Ctrl+J and select the pattern (tab - new table definition).
    /help/img/idea/2016.3/025DBECreateTable.png

    Here is the result:

    /help/img/idea/2016.3/026DBECreateTableResult.png
  2. Type the table name (family).
    /help/img/idea/2016.3/027DBETableNameChanged.png
  3. Replace col with member_id.
    /help/img/idea/2016.3/028DBEColumnNameChanged.png
  4. Type the remaining part of the table definition.
    /help/img/idea/2016.3/030DBETableDefined.png

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.
    /help/img/idea/2016.3/031DBEDragFileToDatabase.png

    As a result, the corresponding data source is created.

    /help/img/idea/2016.3/032DBEDataSourceCreated.png

(To create DDL data sources, you can also use the Data Sources and Drivers dialog: Ctrl+Shift+Alt+S, /help/img/idea/2016.3/new.png, 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.

  1. Create an SQL file (e.g. family-data.sql).
    /help/img/idea/2016.3/033DBEDataFileCreated.png

    Let us add this simple INSERT statement into the file:

    INSERT INTO family (member_id, name, relation) VALUES (1, 'Chloe', 'mother');
  2. Add the INSERT statement by selecting the corresponding predefined pattern (Ctrl+J | ins - insert rows into a table).
    /help/img/idea/2016.3/034DBEAddingInsert.png
  3. Select the table name (family).
    /help/img/idea/2016.3/035DBESelectTableName.png
  4. Select the list of column names (member_id, name, relation).
    /help/img/idea/2016.3/036DBESelectColumns.png
  5. When specifying the values, press Ctrl+P (View | Parameter Info) to see the information about the value types.
    /help/img/idea/2016.3/037DBEParameterTypes.png

Learning basic means of navigation

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

  1. In the file family-data.sql, place the cursor within the column name relation.
    /help/img/idea/2016.3/038DBEFromReferenceToDeclaration.png

    To jump to the definition of this column, use Ctrl+B (Navigate | Declaration).

    /help/img/idea/2016.3/039DBEToDeclarationResult.png
  2. To switch to the view of this column in the Database tool window, press Alt+F1 (Navigate | Select In) and select Database View.
    /help/img/idea/2016.3/040DBEToDBToolWindow.png

    As a result, the Database tool window becomes active and the column relation is selected there.

    /help/img/idea/2016.3/041DBEToDBToolWindowResult.png
  3. 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).
    /help/img/idea/2016.3/042DBEToSourceResult.png
  4. 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.

    /help/img/idea/2016.3/043DBEToNavBarResult.png

    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.

    /help/img/idea/2016.3/044DBESelectFileOnNavBar.png

    Here is the result:

    /help/img/idea/2016.3/045DBESelectFileOnNavBarResult.png
  5. 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).

    /help/img/idea/2016.3/046DBESwitcherCurrentFile.png

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

    /help/img/idea/2016.3/047DBESwitcherFiles.png

    When you release the Ctrl key, the Files tool window becomes active and the file family-data.sql is selected there.

    /help/img/idea/2016.3/048DBESwitcherFilesResult.png
Last modified: 22 March 2017