Working with the Database tool window
On this page:
- Overview of the tool window
- Opening the Database tool window
- Creating a data source
- Synchronizing the view of a DB data source
- Adjusting the view by means of view options
- Adjusting the view by means of object filters
- Showing and hiding schemas
- Finding items
- Finding usages of database objects
- 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
- Importing delimiter-separated values into a database
- Copying DDL definitions onto the clipboard
- Opening DDL definitions in a database console
- Comparing table structures
- Creating database backups with mysqldump or pg_dump
- Saving data in files in various forms and formats
- Specifying data output options
- Generating Java entity classes for tables and views
- Viewing diagrams
- Closing database connections
See also, Database Tool Window.
Overview of the 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
Do one of the following:
- Select .
- 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).
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.:
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.
- Import from sources. If you have files that contain database connection settings, you can create data sources by importing those settings. See Creating DB data sources by importing connection settings.
You can also start creating a data source in the Data Sources and Drivers dialog. Open the 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.
- Select the item whose view you want to synchronize. This may be a DB data source, schema or table.
- Do one of the following:
Adjusting the view by means of view options
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.
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 by specifying object filters. The object filter is set for each DB data source individually, in the Data Sources and Drivers dialog (), on the Options tab. The object filter syntax is described underneath the Object filter field.
f.*
Only the objects whose names start with f
will be shown.
table:[gh].*
The tables whose names start with g
or h
and
all the objects in other categories will be shown.
view:new_.*||routine:-[ps].*
The views whose names start with new_
,
the routines whose names start with the letters other than p
or s
, and
all the objects in the categories other than views and routines will be shown.
Showing and hiding schemas
To show one or more schemas that are not currently visible:
- Click More Schemas within the DB data source of interest.
- Under Add To View, click the necessary schema, or, if you want more than one schema, select the schemas and press Enter.
To hide schemas:
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 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.
Creating a copy of a data source
Creating a table, a column, an index, or a primary or foreign key
- 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.
- Carry out the New command and select the item to be created. E.g. for a table, do one of the following:
- 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:
See also, Show first rows.
Renaming a table or column
- Select the table or column to be renamed.
- Do one of the following:
- 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
- Select the item whose definition you want to change. This may be a table, a column, an index or a foreign key.
- Do one of the following:
- 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 ( , 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 ( , Drop from the context menu, or Delete on the keyboard).
For removing primary key constraints, in addition, there is the context menu command.
See also, Confirm Drop dialog.
Opening a default database console
For more information, see Working with Database Consoles.
Creating and opening a new database console
For more information, see Working with Database Consoles.
Opening the table editor
For more information, see Working with the Table Editor.
Importing delimiter-separated values into a database
To import a text file containing delimiter-separated values (CSV, TSV, etc.) into your database, use the Import from File context menu command.
If you carry out this command for a schema, RubyMine will create a new table for the data that you are importing. If you perform the command for an existing table, RubyMine will try to add the data to that table.
- Right-click the target schema or table, and select Import from File.
- Select the file to import the data from.
- In the dialog that opens, specify the data conversion settings, and, if a new table is to be created, the table name and structure.
Copying DDL definitions onto the clipboard
- Select the items of interest.
- Do one of the following:
Now you can paste the definitions into a database console or an SQL file.
Opening DDL definitions in a database console
You can open DDL definitions of views, and stored procedures and functions in database consoles.
Comparing table structures
The comparison results are shown in the differences viewer.
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.
- Within a MySQL or PostgreSQL data source, select the items of interest (e.g. schemas, tables and views).
- Select Dump with "mysqldump" or Dump with "pg_dump" from the context menu.
-
In the dialog that opens,
specify the location of
mysqldump
orpg_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).
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.
- Select the data source or the schemas, tables and views of interest.
- In the context menu, point to Dump Data to File(s) and select the output format (e.g. Comma Separated Values (CSV)).
- In the dialog that opens, specify the destination directory and, if a single file is going to be created, the file name.
Specifying data output options
To specify the options for the Dump Data command (see Saving data in files in various forms and formats), use the table editor or the result pane of a database console. See e.g. Specifying data output format and options.
Generating Java entity classes for tables and views
- Select the tables and views of interest.
- In the context menu, point to Scripted Extensions and click Generate POJOs.clj or Generate POJOs.groovy.
-
In the dialog that opens,
specify the directory in which you want to create your
.java
class files.
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
RubyMine 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: