Working with the Database tool window

The Database tool window provides access to functions for working with databases and DDL data sources. It lets you view and modify data structures in your databases, and perform other associated tasks.

• Opening the Database tool window
• Adjusting the view
• Creating a data source
• Synchronizing the view of a DB data source
• Finding items
• Finding occurrences of an item name
• Creating a copy of a data source
• Creating a table, a column, an index, or a primary or foreign key
• Viewing basic info about an item
• Renaming a table or column
• Modifying the definition of a table, column, index or a foreign key
• Previewing changes
• Removing items
• Opening a default database console
• Creating and opening a new database console
• Opening the table editor
• Generating DDL definitions for data sources, tables and columns
• Comparing table structures
• Saving table data in a file
• Viewing diagrams
• Closing database connections

See also, Database Tool Window.

Opening the Database tool window

Do one of the following:

• Select View | Tool Windows | Database.
• Point to or in lower-left corner of the workspace, and then click Database.
• Click Database on the right-hand tool window bar (if the tool window bars are currently shown).

Adjusting the view

You can adjust the view in the tool window by turning the corresponding view options on and off. To access those options, click on the title bar.

For more information, see View options.

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

Alternatively, you can open the Data Sources and Drivers dialog (e.g. ) and use the Add command there: Add from the context menu, 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 on the toolbar.
   • Select Synchronize from the context menu.

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.

Finding occurrences of an item name

To find occurrences of the name of an item (e.g. table or column) in your files and consoles:

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:
   • Click on the toolbar.
   • Select Duplicate from the context menu.
   • Press Ctrl+D.

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

Viewing basic info about an item

You can view basic info about an item in the quick documentation view. For a table, for example, the first ten rows and the table definition (the CREATE TABLE statement) are shown.

To open the quick documentation view, select the item of interest and do one of the following:

• Select View | Quick Documentation.
• Press Ctrl+Q.

See also, Allow database access.

Renaming a table or column

1. Select the table or column 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.

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

1. Select the item whose definition you want to change. This may be a table, a column, an index or a 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.

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.

Removing items

To remove data sources, use the Remove command (Edit | Remove, Remove from the context menu, or Delete on the keyboard).

To remove all other item types such as tables, columns and indexes, use the Drop command (Edit | Drop, Drop from the context menu, or Delete on the keyboard).

For removing primary key constraints, in addition, there is the Drop Associated | Primary Key context menu command.

See also, Confirm Drop dialog.

Opening a default database console

1. Select the DB data source of interest or any node within it.
2. Do one of the following:
   • Click on the toolbar.
   • Select Open Console from the context menu.
   • Press Ctrl+Shift+F10.

For more information, see Working with 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 and select Console File.

For more information, see Working with Database Consoles.

Opening the table editor

1. Select the table of interest.
2. Do one of the following:
   • Click on the toolbar.
   • Select Table Editor from the context menu.
   • Press F4.

For more information, see Working with the Table Editor.

Generating DDL definitions for data sources, tables and columns

1. Select the data source, table or column of interest.
2. Do one of the following:
   • Select Copy DDL from the context menu.
   • Press Ctrl+Shift+C.

   The definitions are generated on the clipboard and you can paste them 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.

Saving table data in a file

1. Right-click the table or view of interest.
2. Point to Dump Data To File and select the output format (e.g. Comma Separated Values (CSV)).
3. In the dialog that opens, specify the file location and name.

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.

Closing database connections

IntelliJ IDEA 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 on the toolbar.
• Select Disconnect from the context menu.
• Press Ctrl+F2.