DataGrip 2016.2 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 add and select the database management system (DBMS) that you are using.
    001DBECreateDataSource
  3. In the Name field, if necessary, edit the name of the data source.
    002DBEDataSourceName

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

    003DBEDownloadDBDriver

    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.

    004DBEMySQLConnectionSettings

    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 browseButton to the right of the File or Path field.

    005DBESQLiteFile

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

    006DBESelectSQLiteFile

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

    007DBESQLiteSettings
  6. 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:

    008DBEConnectionSuccessful
  7. 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.
    009DBESchemasAndTables
  8. 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.

    010DBEDataSourceCreated

    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 show_tool_window_bars and select Database.
    010-1DBEOpenDatabaseToolWindow

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

011DBEDBStructure

If the data source structure isn't shown, click refresh on the toolbar. See Synchronizing the view of a DB 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.)
    011-1DBESelectSchema

    DataGrip executes the USE <db_name> or similar statement.

    011-2DBESelectSchemaResult

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

    012DBELiveTemplates

    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.

    014DBEInsertTableName

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

    017DBEStatementReady

Running an SQL statement

To run the statement, do one of the following:

  • Click run
  • Press Ctrl+Enter

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

018DBERunStatementResults

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 close to close the Database Console tool window.
  2. Click close1 on the editor tab to close the input pane.

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

    051DBE_TE_FilterBy

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

    052DBE_TE_Filtered

    (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.
    053DBE_TE_Search

    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.

    054DBE_TE_Sort
  5. You can add rows: click DBAddRow or press Alt+Insert.
    055DBE_TE_AddRow

    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.

    056DBE_TE_AddRowLastCell
  6. You can hide columns: right-click a cell in the header row and select Hide Column.
    057DBE_TE_HideColumn
  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.
    058DBE_TE_ValueView

    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.

    059DBE_TE_TransposedRowView

    To close the quick documentation view, press Escape.

  9. You can transpose the table. To that, click IconDBToolbarMenu on the toolbar and select Transpose.
    059DBE_TE_TransposeTable

    A transposed table may look something like this:

    059DBE_TE_TransposedTable

    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 IconDBToolbarMenu on the toolbar and select Reset View.
    060DBE_TE_RestoreOrderAndVisibility

    Note that filtering in this case is not canceled.

    061DBE_TE_OrderAndVisibilityRestored
  11. You can delete rows. Use DBDeleteRows or Ctrl+Y to delete the selected row or rows.
    062DBE_TE_DeleteRow
  12. To cancel filtering, delete the condition in the filter box and press Enter. Alternatively, use clear. In the latter case, the filter box is also hidden. (To show it again, click IconDBToolbarMenu and select Row Filter.)
    063DBE_TE_FilterCancelled
  13. To close the editor tab, click close1 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 show_tool_window_bars and select Files.
    019DBEOpenFilesToolWindow

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.
    020DBEAttachDirectory
  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 userHome, then click icon_newFolder 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.
    021DBENewFile
  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.
    022DBENewFileName

    The new file is shown in the Files tool window.

    023DBENewFileCreated

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

    023DBENewFileCreatedEditor

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.
    024DBESQLDialects

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

    Here is the result:

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

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.
    031DBEDragFileToDatabase

    As a result, the corresponding data source is created.

    032DBEDataSourceCreated

(To create DDL data sources, you can also use the Data Sources and Drivers dialog: Ctrl+Shift+Alt+S, add, 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).
    033DBEDataFileCreated

    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).
    034DBEAddingInsert
  3. Select the table name (family).
    035DBESelectTableName
  4. Select the list of column names (member_id, name, relation).
    036DBESelectColumns
  5. When specifying the values, press Ctrl+P (View | Parameter Info) to see the information about the value types.
    037DBEParameterTypes

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.
    038DBEFromReferenceToDeclaration

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

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

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

    041DBEToDBToolWindowResult
  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).
    042DBEToSourceResult
  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.

    043DBEToNavBarResult

    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.

    044DBESelectFileOnNavBar

    Here is the result:

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

    046DBESwitcherCurrentFile

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

    047DBESwitcherFiles

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

    048DBESwitcherFilesResult
Last modified: 21 July 2016