IntelliJ IDEA 2020.3 Help

Database tool window

View | Tool Windows | Database

Overview

In the Database tool window (View | Tool Windows | Database), you can work with databases and DDL data sources. You can view and modify data structures in your databases, and perform other associated tasks. To view a table, double-click the table. For more information about different viewing modes, see View data.

the Database tool window

The available data sources are shown as a tree of data sources, schemas, tables and columns. If no data sources are currently defined, use the New command Alt+Insert to create a data source.

Most of the functions in this window are accessed by means of the toolbar icons or context menu commands. (If the toolbar is not currently shown, click the Settings button on the title bar and select Show Toolbar.) Many of the commands have keyboard shortcuts. If the toolbar is hidden, the Refresh and Open Query Console commands can be access by means of the title bar icons (the Refresh button and the Jump to Query Console button respectively).

Controls on the toolbar

Icon Command Shortcut Description Available for
Collapse All Collapse all the nodes. All node types
the Add button New Alt+Insert Create a new data source, role, database, schema, query console, table, column, index, or a primary or a foreign key. The list of options depends on which element is currently selected. Data sources and their elements. If a DDL data source is selected, you can only choose to create another data source.
the Duplicate button Duplicate Ctrl+D Create a copy of the selected data source. Specify the properties of the data source in the Data Sources and Drivers dialog that opens. DB and DDL data source nodes
the Refresh button Refresh Ctrl+F5

Update the view of the selected element (that is to synchronize the view of the element with its actual state in the database).

See also, Data sources and drivers dialog.

data sources and their elements
the Data Source Properties button Data Source Properties Open the Data Sources and Drivers dialog to manage your data sources and their settings. All node types
the Stop button Disconnect Ctrl+F2 Close the database connection for the selected data source or data sources. (The names of the data sources with active database connections are shown in bold.) Data sources with active connections and their elements
the Edit Data button Edit Data F4 Open a table view of the object in the data editor. This option works for tables, views, and materialized views. Corresponding elements in data sources.
the Edit Source button Edit Source Ctrl+B Open data definition for the selected item in the editor. Corresponding elements in data sources.
the Jump to Query Console button Jump to Query Console Ctrl+Shift+F10 Open the default query console for the corresponding data source. Data sources and their elements (tables and table columns)
the Filter button Filter Open a list of database objects that you can filter for the selected database. The list of database objects depends on the selected database. To see a full list of available database objects and their icons, go to Icons for data sources and their elements. Data sources and their elements

Controls of the right-click menu

Context-menu actions appear when you right-click an object in the Database tool window (View | Tool Windows | Database).

Command Shortcut Description
New Create a new data source, role, database, schema, query console, table, column, index, a primary or a foreign key. The list of options depends on which element is currently selected.
Rename Shift+F6 Rename the selected data source, table or column. Specify the new name in the dialog that opens.

Modify Object

or

Modify Table

Ctrl+F6

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

Copy Reference Ctrl+Alt+Shift+C Copy the fully qualified name of the selected data source, table or column to the clipboard.
Quick Documentation Ctrl+Q

View basic information about the selected element.

basic information about the selected element

To close the documentation popup, press Escape.

Find Usages Alt+F7 Find all the usages or references of a data source, table, column, or any other selected item in your source files and libraries.
Add to Favorites

Add an item to the Favorites list. You can quickly access those items in the Favorites tool window (View | Tool Windows | Favorites).

SQL Scripts | SQL Generator Ctrl+Alt+G

Generate data definition structures for database objects. For example, you can generate DDL files for a single table or for all the tables in the schema. Also, you can customize generation settings. For example, you can set what syntax to use for table creation: CREATE TABLE or CREATE TABLE IF NOT EXISTS.

Generate data definition structures
SQL Scripts | Request and Copy Original DDL Ctrl+Alt+G

If your database stores DDL of the object, retrieve DDL from the database.

SQL Scripts | Generate DDL to Clipboard Ctrl+Alt+Shift+G

Generate DDL of the object and copy it to the clipboard.

SQL Scripts | Generate DDL to Query Console Ctrl+Alt+Shift+B

Generate DDL of the object and paste it to a query console.

Database Tools | Modify Comment Modify or add a comment to a database object like if you have run the following query COMMENT ON SCHEMA information_schema IS 'myComment';.
Database Tools | Truncate Remove all the rows in the selected table.
Database Tools | Manage Shown Schemas

Open a popup with available schemas for the current data source.

Popup with available schemas
Database Tools | Hide Schemas Hide the schema that is selected.
Database Tools | Force Refresh Ctrl+Shift+F5

Delete the data source information from cache and load it again. This action is available on the data source node only.

Database Tools | Forget Cached Schemas

Delete the information that IntelliJ IDEA has accumulated about your database. This action is available on the data source node only.

Use this command when you experience issues like wrong display of data structures or errors during synchronization.

To check if this has eliminated the problem, use the Synchronize command.

Database Tools | Properties Shift+Enter Open the Data Sources and Drivers window where you can customize data source settings.
Database Tools | Copy Data Source to Clipboard Copy the settings for the selected data source onto the clipboard. This action is available on the data source node only.
Database Tools | Drop Primary Key Remove the primary key constraint for the current table.
Database Tools | Drop Foreign Key Remove the foreign key constraint.
Diagnostics | Dump Model

Generate a IntelliJ IDEA-specific representation of the database structure (a database model).

Dump model
Diagnostics | Diagnostic Refresh

Reload the metadata from the database and generate a LOG file. This information might be helpful for the support team when you think that your introspection is slow.

Diagnostic refresh
Diagnostics | Prepare Introspector Diagnostics

Generate three files that include information about the data source (dataSource.txt), a module that was used to load the metadata from the database (introspector.txt), and a part of the database model (model.xml).

This information might be helpful when introspection works incorrectly. For example, when you see something outdated or do not see new objects.

Prepare introspector diagnostic

Drop

or

Remove

Delete Remove the selected item.
Jump to Query Console Ctrl+Shift+F10 Create and open a new query console for the selected data source.
Refresh Ctrl+F5 Create and open a new query console for the selected data source.
Compare Ctrl+D

Compare structures of two selected database objects (data sources, schemas, or tables). The comparison results are shown in the differences viewer.

Compare Content Compare the output results of tables, views, and materialized views. The comparison results are shown in the differences viewer.
Export Data to File

Save data for the selected tables and views in files. Select the output format (for example, SQL Inserts, Tab-separated (TSV), JSON-Clojure.json.clj).

Export with 'mysqldump'

or

Export with 'pg_dump'

Run mysqldump or pg_dump for the selected items. mysqldump and pg_dump are native MySQL and PostgreSQL tools. They are not integrated into IntelliJ IDEA. You can read about them at dev.mysql.com and postgresql.org.

Import Data from File

Import a text file containing delimiter-separated values (CSV, TSV, and so on) into your database.

If a schema is currently selected, IntelliJ IDEA will create a new table for the data that you are importing. If a table is selected, IntelliJ IDEA will try to add the data to the selected table.

Full-text Search Shift+Ctrl+Alt+F

Search for data in your databases or a group of databases without knowing data exact location.

For more information about the full-text search, see Full-text search in databases.

Restore with 'mysql'

Restore with 'psql'

or

Restore

Run mysql,pg_restore, or psql to restore a data dump. These tools are native for MySQL and PostgreSQL. They are not integrated into IntelliJ IDEA. You can read about them at dev.mysql.com and postgresql.org.
Copy Table to F5 Create a copy of the selected table. You can create a copy in a different scheme or data source. For example, you can copy the actor table from MySQL to PostgreSQL.
Color Settings

Set or change the color for the selected element or elements. (The Database Color Settings dialog will open.)

To set a color, right-click a data source and select Color Settings. In the Database Color Settings dialog, select the dialog and coloring options. To have the data source color applied for query consoles and grids, select the In console editors and grids checkbox.

Text data viewer uses the color of the data source
Scripted Extensions | Generate POJOs.clj Generate a Java entity class for the selected table. In the dialog that opens, specify the directory in which the JAVA class file should be generated.
Scripted Extensions | Go to Scripts Directory Switch to the directory where the Generate POJOs.clj example script file is located.

Diagrams | Show Visualisation

and

Diagrams | Show Visualisation Popup

Ctrl+Alt+Shift+U
Ctrl+Alt+U

View a UML class diagram for the selected data source or table. You can select between the following options:

  • Show Visualisation to open the diagram on a separate editor tab.

  • Show Visualisation Popup to see the diagram in a popup.

View options

The view options, generally, define what is shown in the tool window and how. To view or change these options, click the Settings button on the title bar.

Option Description
Group Data Sources

Displays folders for data sources if you created any.

Enabled Disabled
Option is enabled
Option is disabled
Group Schema

Defines how schema elements are shown.

When on, there are separate nodes for tables, views and stored routines (shown as folders). Tables, views and routines (procedures and functions) are shown as elements of the corresponding groups.

The Group Schema option is on

When off, there is no explicit grouping for tables, views, and routines. Tables and views are followed by procedures and functions.

The Group Schema option is off
Group Contents

This option defines how table elements are shown.

When on, there are separate nodes for columns, indexes, primary and foreign key constraints, and triggers (shown as folders). The elements appear in the corresponding groups.

The Group Contents option is on

When off, there is no such grouping and, generally, only columns are shown for tables.

The Group Contents option is off
Show All Namespaces

Show all databases and schemes even if they are not selected for introspection.

When the Show All Namespaces option is disabled, databases that are not selected for introspection do not appear in the Database tool window (View | Tool Windows | Database).

Show All Namespaces

When the Show All Namespaces option is enabled, databases that are not selected for introspection are displayed in the Database tool window (View | Tool Windows | Database).

Show All Namespaces
Show Empty Groups

If the Group Schema or Group Contents options are selected, you can select to show or hide the categories that contain no elements.

The Show Empty Groups option is on:

The Show Empty Groups option is on

The Show Empty Groups option is off:

The Show Empty Groups option is off
Show Intermediate Nodes

Shows or hides parent nodes only when you do not have other objects on the same level with a parent.

Enabled Disabled
Show Intermediate Nodes is enabled
Show Intermediate Nodes is disabled
Sort Alphabetically

When this option is off, columns, generally, are unsorted.

Sort Alphabetically is off

When this option is on, the columns are ordered alphabetically.

Sort Alphabetically is on

Show Toolbar Display the toolbar in the Database tool window (View | Tool Windows | Database) .

The rest of the options are common for all the tool windows, see Tool window view modes.

Icons for data sources and their elements

Icon Description
Access Method Access Method
Aggregate Aggregate
Alias Type Alias Type
Argument Argument
Body Body
Check Check
Cluster Cluster
Collation Collation
Collection Type Collection Type
Column

Column. For more information about column icon combinations, see Possible icon combinations for columns.

Data File Data File
Database Database
Read-only Read-only status
the DDL data source icon DDL data source
Default Default
Exception Exception
Extension Extension
External Schema External Schema
Foreign Data Wrapper Foreign Data Wrapper
Foreign Key Foreign Key
Foreign Table Foreign Table
Index Index
Key Key
Materialized Log Materialized Log
Materialized View Materialized View
Object Attribute Object Attribute
Object Type Object Type
Operator Operator
Package Package
Projection Projection
data source

Read-only data sources

Role Role
Routine Routine
Rule Rule
Scheduled Event Scheduled Event
Schema Schema
Sequence Sequence
Server Server
the Stored procedure or function icon Stored procedure or function
Synonym Synonym
Table Table
Table Type Table Type
Tablespace Tablespace
Trigger Trigger
User User
User Mapping User Mapping
Variable Variable
View View
Virtual Table Virtual Table

Possible icon combinations for columns

Icon Foreign key Primary key Indexed NOT NULL
Column
Column with a foreign key Has a foreing key
Column with a primary key Has a primary key
Indexed column Indexed
Column that does not accept NULL values Has the NOT NULL contraint
Indexed column that does not accept NULL values Indexed Has the NOT NULL contraint
Indexed column with primary and foreign keys that does not accept NULL values Has a foreing key Has a primary key Indexed Has the NOT NULL contraint
Indexed column with primary and foreign keys Has a foreing key Has a primary key Indexed
Column with primary and foreign keys that does not accept NULL values Has a foreing key Has a primary key Has the NOT NULL contraint
Column with primary and foreign keys Has a foreing key Has a primary key
Indexed column with a foreign key that does not accept NULL values Has a foreing key Indexed Has the NOT NULL contraint
Column with a foreign key that does not accept NULL values Has a foreing key Has the NOT NULL contraint
Indexed column with a foreign key that accepts NULL values Has a foreing key Indexed
Indexed column with a primary key that does not accept NULL values Has a primary key Indexed Has the NOT NULL contraint
Column with a primary key that does not accept NULL values Has a primary key Has the NOT NULL contraint
Indexed column with a primary key Has a primary key Indexed
Indexed column that does not accept NULL values Indexed Has the NOT NULL contraint

Title bar context menu and buttons

You can right-click the window title bar and use the context menu to configure its viewing mode, associate the window with a different tool window bar, or resize and hide the window.

You can also use the toolbar buttons:

Item Shortcut Description
Scroll from Editor

Automatically sets the focus on the object name in the Project view when the editor area is in focus.

Collapse all Ctrl+NumPad - Collapse all expanded nodes in the current view.
Hide tool window Shift+Escape

Hide the tool window.

To hide all the tool windows, press Ctrl+Shift+F12.

Last modified: 15 January 2021