DataGrip 2018.3 Help

Database tree

Overview

Your database tree is shown in the Database tool window. This tool window lets you view and modify data structures in your databases, and perform other associated tasks.

DBToolWindow

Opening the Database tool window

Do one of the following:

  • Select View | Tool Windows | Database.

  • Point to show tool window bars or show hide tool window bars in lower-left corner of the workspace, and then click Database.

    010 1DBEOpenDatabaseToolWindow

  • Press Alt+1.

  • If the buttons for showing or hiding the tool windows are currently shown, click the Database button.

Creating a data source

To start creating a data source, you can use the New command when the Database tool window is active, e.g.:

  • File | New

  • icons general add svg on the toolbar

  • New in the context menu

  • Alt+Insert

The DDL Data Source option is for creating a DDL data source. Other data source options correspond to different scenarios of creating a DB data source:

  • Data Source. A "usual way" of creating a data source. In this scenario, you start by selecting your DBMS.

  • Data Source from URL. In this scenario, you start by specifying your database URL.

  • Data Source from Path. In this scenario, you start by specifying your database location (a local file or folder). This option is appropriate only for Derby, H2, HSQLDB and SQLite.

You can also start creating a data source in the Data Sources and Drivers dialog. Open the dialog (e.g. iconManageDataSources) and use the Add command there: Add from the context menu, icons general add svg on the toolbar, or Alt+Insert.

For more information, see Managing data sources.

Synchronizing the view of a DB data source

If the Auto sync option for a DB data source if off, the only way to synchronize its view in the Database tool window with the actual state of the database is by using the Synchronize command.

  1. Select the item whose view you want to synchronize. This may be a DB data source, schema or table.

  2. Do one of the following:
    • Press Ctrl+Alt+Y.

    • Click icons actions refresh svg on the toolbar.

    • Select Synchronize from the context menu.

Resolve visualization problems

If you see any issues in the Database tool window (for example, no objects below the schema, no changes in objects, broken tables, or other issues), try the following steps to resolve the problem:

  1. Synchronize the view of your data source (icons actions refresh svg). You can find more details in the Synchronize the view of your data source topic.

  2. Ensure that you selected all the necessary schemas for viewing (click the link near the data source). For more information, see Showing and hiding schemas.

    db check scheme dropdown list

  3. Clear the DataGrip schema cache (right-click a data source and select Database Tools | Forget Cached Schema). Synchronize the view.

  4. You can switch to the JDBC-based introspector (click the Data Source Properties icon (iconManageDataSources), select the Options tab, and then select the Introspect using JDBC metadata check box). Synchronize the view. Consider this step as a temporary workaround.

Adjusting the view by means of view options

You can adjust various view options in the Show Options menu (icons general gearPlain svg) on the title bar of the Database tool window.

The Show Options menu

Adjusting the view by means of object filters

You can limit the set of tables and other database objects shown in the Database tool window for each data source using the Filter menu in the toolbar.

The Filter menu

Alternatively, you can manually specify more granular filtering options using the Object filter field on the Options tab of the Data Sources and Drivers dialog (iconManageDataSources).

The Object Filter field in data source options

The following examples demonstrate the filter syntax:

  • f.*

    Show only objects whose names start with f.

  • table:[gh].*

    Show tables whose names start with g or h, and all other object types.

  • view:new_.*||routine:-[ps].*

    Show views whose names start with new_, routines whose names start with any letters except p or s, and all other object types.

Showing and hiding schemas

To show or hide schemas:

  1. Right-click any element within the corresponding data source, point to Database Tools and select Manage Shown Schemas.

  2. Select the schemas you want to show and press Enter.

    DBTWShowSchemas

To hide schemas, use the Schemas popup or the Database Tools | Hide Schemas context menu command.

Finding items

To find an item of interest, simply start typing its name. The specified text within item names is highlighted, and the first of the items that contains the specified text is selected.

DBToolWindowFind

Finding usages of database objects

You can search for usages of database objects in your files and consoles, and also in the source code of other objects (if loaded, see Load sources for). For example, you can look for references to a table or view in the code of other views, stored procedures and functions.

  1. Select the item of interest.

  2. Do one of the following:
    • Press Alt+F7.

    • Select Find Usages from the context menu.

    • Select Edit | Find | Find Usages in the main menu.

Creating a copy of a data source

  1. Select the data source of interest.

  2. Do one of the following:
    • Clickicons actions copy svg on the toolbar.

    • Select Duplicate from the context menu.

    • Press Ctrl+D.

Creating a database or schema

  1. Select any element within the DB data source of interest.

  2. Do one of the following:
    • Select File | New | Schema or File | New | Database.

    • Click icons general add svg and select Schema or Database.

    • In the context menu, select New | Schema or New | Database.

    • Press Alt+Insert and select Schema or Database.

  3. In the dialog that opens, specify the name of the schema or database. If necessary, under SQL Script, edit the statement to be executed. Click Execute.

  4. If you have created a PostgreSQL database and want to see it in the Database tool window, create a data source for that database.

See also, Track creation and deletion of databases/schemas.

Creating a table, a column, an index, or a primary or foreign key

  1. Depending on what you are going to create:
    • To create a table, select a schema, table or column within the target DB data source.

    • To create a column, select the target table or a column within that table.

    • To create an index, or a primary or foreign key, select the column or columns for which you want to create an index, or a primary or foreign key constraint.

  2. Carry out the New command and select the item to be created. E.g. for a table, do one of the following:
    • Select File | New | Table.

    • Click icons general add svg and select Table.

    • In the context menu, select New | Table.

    • Press Alt+Insert and select Table.

  3. In the dialog that opens, specify the item definition.

Modifying templates for generated index and key names

When you create indexes, and primary and foreign key constraints, their default names are generated according to corresponding templates. For a primary key, for example, the template is {table}_{columns}_pk.

You can view and modify these templates in the Settings / Preferences dialog: Ctrl+Alt+S | Editor | Code Style | SQL | Code Generation.

The templates can contain variables (e.g. {table}) and text. When generating a name, the specified text is reproduced literally.

To get the info about the variables and how you should use them, place the cursor into the field of interest and press Ctrl+Q.

{columns} and {ref_columns}, depending on the situation, are the name of the column, or a list where the column names are separated with the underscore (_).

{unique?u:} checks if the index is unique (unique?), and, if it is, inserts the sequence of characters specified between ? and : (in this example, it's u). If the index is not unique, the sequence between : and } is inserted (in this example, it's nothing).

Example. Using the template {table}_{columns}_{unique?u:}index, you are creating an index on the columns FirstName and LastName in the table persons. If the index is unique, its name, by default, will be persons_FirstName_LastName_uindex. If the index is not unique, its name will be persons_FirstName_LastName_index.

View quick documentation

You can view a short reference documentation for data sources, tables, functions, and other database objects. For example, if you select a table and press Ctrl+Q, you will see the first ten rows and the table definition.

To open the quick documentation view, click a database object and select View | Quick Documentation (Ctrl+Q).

View quick documentation

Renaming items

  1. Select the item to be renamed.

  2. Do one of the following:
    • Select Refactor | Rename.

    • Select Rename from the context menu.

    • Press Shift+F6.

  3. Use the dialog that opens to specify a new name and associated options.

Previewing changes

Changes to database objects sometimes assume associated changes to SQL script files and statements in database consoles. For example, you may be changing the name of a table, and this name may be used in your files and consoles.

In such cases, you can look at potential changes, and decide where those changes are desirable and where aren't.

Potentially affected code fragments are shown in the Find tool window when you click Preview in the corresponding dialogs. Here is an overview of some of the available controls:

  • Exclude (Delete) and Remove (Alt+Delete). Use these context menu commands for the items that shouldn't be changed.

  • Execute SQL Script. If this option is on, and you click Do Refactor, the corresponding SQL statements are run to modify the corresponding database objects.

  • Open in Console. Use this button to open the corresponding SQL statements in a database console.

  • Do Refactor. Click this button to change the corresponding code fragments and, if the Execute SQL Script option is on, to run the corresponding SQL statements.

Modifying the definition of a table, column, index, or a primary or foreign key

  1. Select the item whose definition you want to change. This may be a table, a column, an index, or a primary or foreign key.

  2. Do one of the following:
    • Select Modify <item_type> from the context menu (e.g. Modify Table).

    • Press Ctrl+F6.

  3. Use the dialog that opens to change the item definition.

Opening DDL definitions of database objects in the editor

  1. Select the object whose definition you want to view or edit.

  2. Do one of the following:
    • Click iconDBEditDDLSource on the toolbar.

    • Press Ctrl+B.

    • Select Source Editor under SQL Scripts in the context menu.

Generating DDL definitions

Generate DDL definitions for database objects

Right-click a database object and select SQL Scripts | SQL Generator (Ctrl+Alt+G).

In the SQL Generator window, you can configure the output, copy it to the clipboard (The copy icon), save it to file (The Save to File icon), or open it in a database console (Run in a console).

Generate DDL definitions for database objects

Save generated DDL in SQL files

  1. Right-click a database object and select SQL Scripts | SQL Generator (Ctrl+Alt+G).

  2. In the SQL Generator tool window, click the File Output Options icon (The File Output Options icon).

  3. From the Layout drop-down list, select a method that you want to use:

    • File per object: generates a set of SQL files.

    • File per object with order: generates a numbered set of SQL files.

Generate DDL for database objects

Opening DDL definitions in a database console

You can open DDL definitions of tables and views in database consoles.

  1. In a DB data source, select the table or view of interest.

  2. Do one of the following:
    • Select Generate DDL to Console under SQL Scripts in the context menu.

    • Press Shift+F4.

Generating DDL definitions on the clipboard

  1. Select the item or items of interest. These may be data sources, schemas, tables, views, stored procedures or functions, etc.

  2. Do one of the following:
    • Select Generate DDL to Clipboard under SQL Scripts in the context menu.

    • Press Ctrl+Shift+C.

    Now you can paste the definitions into a database console or an SQL file.

Comparing table structures

  1. Select two data sources, schemas or tables.

  2. Do one of the following:
    • Select Compare from the context menu.

    • Press Ctrl+D.

The comparison results are shown in the differences viewer.

Viewing diagrams

To open a diagram for a data source, schema or table, select the item of interest and do one of the following:

  • Press Ctrl+Shift+Alt+U or Ctrl+Alt+U.

  • In the context menu, select Diagrams, and then select Show Visualisation or Show Visualisation Popup.

Copying a table to another database or schema

You can copy (export) a table along with all its data to another schema or database. This is possible even when the source and target databases belong to different DBMSs, e.g. PostgreSQL and MySQL.

To copy a table:

  1. Drag the table to the destination schema or database.

  2. In the dialog that opens, specify the settings for your new table.

Importing delimiter-separated values into a database

To import a text file containing delimiter-separated values (CSV, TSV, etc.) into your database, use drag-and-drop or the Import from File context menu command.

If you drag a file into a schema (or carry out the Import from File command for a schema), DataGrip will create a new table for the data that you are importing. If you drag a file into an existing table (or perform the command for a table), DataGrip will try to add the data to that table.

  1. Do one of the following:
    • Drag a file from the Files tool window (the file may be a .zip archive) onto a schema or table in the Database tool window.

    • Right-click the target schema or table in the Database tool window, select Import from File and then select the file to import the data from (this file may be a .zip archive).

  2. In the dialog that opens, specify the data conversion settings, and, if a new table is to be created, the table name and structure.

Opening the data editor

  1. Select the table of interest.

  2. Do one of the following:
    • Click DataTables on the toolbar.

    • Select Open Editor from the context menu.

    • Press F4.

For more information, see Working with tables.

Copying data from one table to another one

  1. Drag the source table to the destination table.

  2. In the dialog that opens, specify the data mapping info and other settings for the destination table.

Saving data in files in various forms and formats

You can save database data in files as SQL INSERT and UPDATE statements, TSV and CSV, HTML tables and JSON data. A separate file is created for each individual table or view.

  1. Select the data source or the schemas, tables and views of interest.

  2. In the context menu, point to Dump Data to File(s) and select the output format (e.g. Comma Separated Values (CSV)).

  3. In the dialog that opens, specify the destination directory and, if a single file is going to be created, the file name.

Configuring data output formats and options

To configure the output formats for the Dump Data to File(s) command (see Saving data in files in various forms and formats), select one of the following from the menu associated with the command:

  • Configure CSV Formats. Opens the CSV Formats dialog that you can use to manage your delimiter-separated values formats (for example, CSV or TSV).

  • Go to Scripts Directory. This command lets you switch to the directory where the scripts that convert table data into various output formats are stored.

For SQL INSERTs and UPDATEs, there are additional options: Add Table Definition and Skip Generated Columns. Those can be set in a data editor or the result pane of a database console. See e.g. .

Creating database backups with mysqldump or pg_dump

You can create backups for database objects by running mysqldump for MySQL or pg_dump for PostgreSQL.

  1. Within a MySQL or PostgreSQL data source, select the items of interest (e.g. schemas, tables and views).

  2. From the context menu, select Dump with "mysqldump" or Dump with "pg_dump".

  3. In the dialog that opens, specify the location of mysqldump or pg_dump executable, and the settings for performing the dump. If necessary, edit the command-line options in the lower part of the dialog (autocompletion is available).

Restoring data dumps with mysql, pg_restore or psql

You can restore data dumps by means of the mysql client utility for MySQL, or pg_restore or psql for PostgreSQL.

  1. Select the target MySQL or PostgreSQL data source, database or schema. For PostgreSQL, you can also select a table.

  2. From the context menu, select Restore with "mysql", Restore with "psql" or Restore.

  3. In the dialog that opens, specify the location of the utility executable, the options for restoring the data, and the path to the dump file. If necessary, edit the command-line options in the lower part of the dialog (autocompletion is available).

Opening a default database console

Right-click the data source of interest or any node within it and select Open Console in the context menu (F4).

For more information, see Database consoles.

Creating and opening a new database console

  1. Select the DB data source of interest or any node within it.

  2. Do one of the following:
    • Select Open New Console from the context menu.

    • Click icons general add svg and select Console File.

For more information, see Database consoles.

Generating Java entity classes for tables and views

  1. Select the tables and views of interest.

  2. In the context menu, point to Scripted Extensions and click Generate POJOs.clj or Generate POJOs.groovy.

  3. In the dialog that opens, specify the directory in which you want to create your .java class files.

Closing database connections

DataGrip connects to databases automatically, when needed. (The names of the data sources with open database connections are shown in the Database tool window in bold.)

To close unnecessary database connections, select the corresponding data sources and do one of the following:

  • Click icons actions suspend svg on the toolbar.

  • Select Disconnect from the context menu.

  • Press Ctrl+F2.

Removing items

Depending on what you are going to remove:

  • Data source. Use the Remove command (Edit | Remove, Remove from the context menu, or Delete on the keyboard).

  • Schema, table, column, index, a primary or foreign key constraint, stored procedure or function, etc. Use the Drop command (Edit | Drop, Drop from the context menu, or Delete on the keyboard).

  • Primary or foreign key constraint. For removing primary and foreign key constraints, in addition to Drop, there are the following context menu commands: Database Tools | Drop Primary Key and Database Tools | Drop Foreign Key. Note that the Drop Foreign Key command is available only when a column with the corresponding foreign key constraint is selected (dataFkColumn).

  • All rows in a table. Use the Database Tools | Truncate context menu command for the corresponding table.

See also, Confirm Drop dialog.

Last modified: 6 February 2019