Database tool window

Enable the Database Tools and SQL plugin

This functionality relies on the Database Tools and SQL plugin, which is bundled and enabled in PyCharm by default. If the relevant features are not available, make sure that you did not disable the plugin.

Press Ctrl+Alt+S to open settings and then select Plugins.
Open the Installed tab, find the Database Tools and SQL plugin, and select the checkbox next to the plugin name.

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.

The available data sources are shown as a tree of data sources, schemas, tables, and so on.

If no data sources are currently defined, use the New command Alt+Insert to create a data source and connect to a database. For more information about data sources, refer to Data sources topic.

Once PyCharm is connected to a database, Database tool window will display the introspected database objects. For more information about database tree viewing options, refer to View Options chapter. For more information about working with database objects in PyCharm, refer to the corresponding object topics.

Most of the functions in this window are accessed by using the toolbar icons or context menu commands. Many of the commands have keyboard shortcuts. If the toolbar is hidden, the Refresh and Open Query Console commands can be access by using the header buttons ( and respectively).

Browse your databases

Show more schemas

Database tool window displays introspected databases and schemas of your data sources. For a database or schema to be shown in the tree, select it in the schema selector of a data source.

For more information about introspection, refer to Glossary.

To see more schemas under your new data source node, click the N of M button and select the ones you need. PyCharm will introspect and show them.

Find database objects

To search for a database object in Database tool window, start typing the object's name. Matching database objects will be highlighted.
To toggle the speed search filter, click Speed Search Filter. To move between several highlighted items, press Up and Down.

For more information about speed search, refer to Speed Search in tool windows.

Filter database objects

You can filter database objects and hide the objects you do not need to see. To do this, use either the object filter in data source settings or the tool window item filter.

Object filter in data source settings
For example, you can filter out all tables with names starting with payment_.
1. To filter database objects, open Data Sources and Drivers dialog (Ctrl+Alt+Shift+S) .
2. On the left pane of the dialog, select the data source in which you want to filter database objects.
3. In the Schemas tab, navigate to Object filter and enter the pattern. In our example, table:-payment_.*.
In the Object filter field, press Ctrl+Q for the information about syntax.
Item filter in the tool window view options
1. In the toolbar, click View Options and select Filter.
2. Select or deselect the items that you want to see or hide.
3. To confirm the selection, click any area in the Database tool window.

Copy data source

Select the data source you want to copy and press Ctrl+C.
The data source will be copied to the clipboard in XML format. You can paste it to other IDE projects by clicking any area in Database tool window and pressing Ctrl+V.

For more information about copying data sources in XML format, refer to Share data sources between different IDE instances.

Group data sources in folders

To group data sources in folders, right-click a data source and select Move to Folder. In the Move to Folder dialog, you can either move the data source to an existing folder or create a new one.

For more information about grouping data sources, refer to Group data sources in folders.

View quick documentation

Quick documentation displays information about the item. To view the information, select the item and press Ctrl+Q.

Scroll to the object opened in editor

To scroll to a database object in the database tree, place the caret over the object's name in code editor. Then, in Database tool window tool window toolbar, click Scroll from Editor.
The corresponding nodes will open and the object will be highlighted in the database tree.

Create a quick table backup

You can create a quick table backup before any significant data manipulation. To do this, drag the table to the original schema and create a copy of the table in Import dialog.

For more information about the dialog, refer to Import dialog.

Adjust tree view

Toggle groups for non-major objects

By default, non-major objects are grouped in the Database Objects and Server Objects nodes. To toggle this grouping, click View Options in the toolbar, navigate to View in Groups and click Database and Server Objects.

For more information about view options, refer to the Database topic.

Grouping enabled
Grouping disabled

Assign a color to a data source and its objects

Click Data Sources in the toolbar. Alternatively, press Ctrl+Alt+Shift+S.
In the Data Sources and Drivers dialog (Ctrl+Alt+Shift+S) , click the circle at the end of the Name field and select the color.
Apply the changes and close the dialog.

For more information about assigning colors to data sources, refer to Assign a color to a data source and Assign a color to query consoles and grids.

Show tree indent guides

In the Database tool window, you can display vertical lines that mark indent levels and help you better understand the hierarchy of the objects in your databases.

Press Ctrl+Alt+S to open settings and then select Appearance & Behavior | Appearance.
In the Tree Views section, enable the Show indent guides option.

Tree indent guides are enabled
Tree indent guides are disabled

Show comments and last refresh date

You can view comments and last refresh timestamps for the objects in the database tree.

In the toolbar, click View Options, navigate to Node Details and select Comments and Schema Refresh Time.

Reference info

Toolbar

You can right-click the window header 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
		Automatically sets the focus on the object name in the Database tool window when the editor area is in focus.
	`Ctrl+NumPad +`	Expand all nodes in the current view.
	`Ctrl+NumPad -`	Collapse all expanded nodes in the current view.
	Hide`Shift+Escape`	Hide the tool window . To hide all the tool windows, press `Ctrl+Shift+F12`.

Context menu

Item	Description
Autoscroll from Editor	Makes the database object, that is opened in the editor, automatically selected in the Database tool window. Disables the Scroll from Editor setting.
Speed search	Opens the input field of the Speed Search in tool windows feature.
Show Toolbar	Display the toolbar in the Database tool window.
View Mode	This option lets you control general appearance and behavior of the tool window. For full information, refer to Tool window view modes.
Move to	To associate the tool window with a different tool window bar, select this command, and then select the destination tool window bar (Top, Left, Bottom or Right). For full information, refer to the Move tool windows chapter.
Resize	To resize the tool window by moving one of its borders, select this command, and then select the necessary Stretch to option. Note that this command is not available for the floating mode.
Remove from Sidebar	This command hides the tool window, removes the associated tool window button from the tool window bar, and removes the tool window from the quick access menu ( or ). To open the tool window again (and restore the associated features), use the main menu: View \| Tool Windows \| <Window Name> or press `Alt+1`.
Help	This command opens the description of the Project tool window in the PyCharm online help.

Toolbar controls

Icon	Command	Shortcut	Description	Available for
	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.
	Data Source Properties		Open the Data Sources and Drivers dialog to manage your data sources and their settings.	All node types
	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.
	Deactivate `Ctrl+F2`	`Ctrl+F2`	Close the database connection for the selected data source or data sources. (The data sources with connected sessions are indicated with a green dot in the corner of their icon.)	Data sources with active connections and their elements
	Jump to Query Console	`Ctrl+Shift+F10`	Open the list of query consoles for the selected data source.	Data sources and their elements (tables and table columns)
	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.
`DDL`	Go to DDL	`Ctrl+B`	Open the DDL script of the selected object. For example, if you press this shortcut on a table's name in the `SELECT` statement, you will see the DDL of this table (the `CREATE TABLE` statement). The object references in the generated script are not qualified (for example, `<view_name>` instead of `<schema_name>.<view_name>`). You can view this script, edit it, and submit your changes to the database. For a runnable DDL script that includes `CREATE OR REPLACE`, qualified references, and so on, use the SQL Generator.	Corresponding elements in data sources.
	Compare Structure	`Ctrl+D`	Compare the structure of two database objects in the Migration dialog.	Two selected database objects of the same kind.
	View Options		Open the list of database object tree viewing options, including the item filter. For more information about the options, refer to the View Options chapter.	All items.

View Options

Option	Description
Filter	Open a list of database objects that you can filter. 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. The green dot in the corner of filter icon indicates that the filter is on and some objects are filtered out.
View in Groups
Data Sources	Displays folders for data sources. For more information about creating folders, refer to Group data sources in folders chapter.
Databases and Schemas	Displays folders for databases and schemas.
Server and Database Objects	Displays folders for server and database objects, Server Objects and Database Objects correspondingly. This setting concerns users, roles, tablespaces, modules, foreign data wrappers, and other rarely used objects.
Object Elements	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. When off, there is no such grouping and, generally, only columns are shown for tables.
Schema Objects	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. When off, there is no explicit grouping for tables, views, and routines. Tables and views are followed by procedures and functions.
Separate Procedures and Functions	In Microsoft SQL Server, Oracle, and PostgreSQL, separates procedures and functions into different folders.
Place Constraints and Similar Objects under Schema	Display nodes for object names that exist in a schema namespace. For example, nodes for keys, `CHECK` constraints, indexes, triggers, rules, and other objects. You can use this option in the following situations: To find an object inside a schema with the help of a quick search. To see the columns under the table node. To do this, turn off the Empty Groups.
Sort Alphabetically	Order database objects alphabetically. When the option is disabled, the natural sort order is applied.
Show Elements
All Namespaces	Show all databases and schemes even if they are not selected for introspection. When the All Namespaces option is disabled, the databases that are not selected for introspection do not appear in the Database tool window. When the All Namespaces option is enabled, the databases that are not selected for introspection are displayed in the Database tool window.
Empty Groups	If the Schema Objects or Object Elements options are selected, you can select to show or hide the categories that contain no elements.
Intermediate Nodes	Shows or hides parent nodes only when you do not have other objects on the same level with a parent.
Generated Objects	For Oracle, shows or hides auto-generated objects in the tree. It concerns the following objects: Materialized view logs The underlying tables for materialized views Secondary tables
Virtual Objects	Show or hides the following objects: Virtual foreign keys Virtual views Virtual columns
Node details
Comments	Display comments for database objects.
Schema Refresh Time	Display the local date and time of the last performed introspection for schemas. The feature is currently not supported for ClickHouse, Redis, Apache Cassandra, and Apache Hive.

Context-menu actions appear when you right-click an object in the Database tool window.

Command	Shortcut	Description	Available for
Properties		Open the Data Sources and Drivers dialog to manage your data sources and their settings.	Data source and DDL data source nodes.
New	`Alt+Insert`	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.	Data sources and their elements. If a DDL data source is selected, you can only choose to create another data source.
Rename	`Shift+F6`	Rename the selected item. Specify the new name in the dialog that opens.	Data sources and their elements.
Copy/Paste \| Copy Reference	`Ctrl+Alt+Shift+C`	Copy the fully qualified name of the selected item to the clipboard.	Data sources and their elements.
Copy/Paste \| 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.	Data source and DDL data source nodes.
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.
Move to Folder	`F6`	Move the selected data source to a folder. If no folders exist yet, create the first one in the Move to Folder popup that appears. For more information about grouping data sources, refer to Group data sources in folders.	Data source and DDL data source nodes.
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.
Deactivate `Ctrl+F2`	`Ctrl+F2`	Close the database connection for the selected data source or data sources. (The data sources with connected sessions are indicated with a green dot in the corner of their icon.)	Data sources with active connections and their elements
Drop or Remove Data Source	`Delete`	Remove the selected item.	Data sources and their elements.
Quick Documentation	`Ctrl+Q`	View basic information about the selected element. To close the documentation popup, press `Escape`. For full information about quick documentation, refer to quick documentation.	Data sources and their elements.
Navigation \| Go to DDL	`Ctrl+B`	Open the DDL script of the selected object. For example, if you press this shortcut on a table's name in the `SELECT` statement, you will see the DDL of this table (the `CREATE TABLE` statement). The object references in the generated script are not qualified (for example, `<view_name>` instead of `<schema_name>.<view_name>`). You can view this script, edit it, and submit your changes to the database. For a runnable DDL script that includes `CREATE OR REPLACE`, qualified references, and so on, use the SQL Generator.	Corresponding elements in data sources.
Navigation \| Jump to Query Console	`Ctrl+Shift+F10`	Open the list of query consoles for the selected data source.	Data sources and their elements (tables and table columns)
Run Function or Run Procedure		Run the selected function or procedure.	Data source elements: functions, procedures.
(Oracle only) Introspection Level		Change the introspection level either for the whole database or for a particular schema. Children inherit a level that is set for a parent.	Oracle Data Source nodes.
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`. For more information about using SQL Generator, refer to the Generate DDL definitions for database objects and Change output settings of the SQL Generator chapters.	Data source elements.
SQL Scripts \| Generate DDL to Clipboard	`Ctrl+Alt+Shift+G`	Generate a DDL of the object and copy it to the clipboard.	Data sources and their elements.
SQL Scripts \| Truncate…		Remove all the rows in the selected table.	Data source elements: tables.
Tools \| Manage Shown Schemas		Open a popup with available schemas for the current data source. See also, Show and hide schemas.	Data sources and their elements.
Tools \| Compare Structure	`Ctrl+D`	Compare structures of two selected database objects (data sources, schemas, or tables). The comparison results are shown in the Diff Viewer.	Data sources and their elements.
Tools \| Full-text Search	`Shift+Ctrl+Alt+F`	Search for data in your databases or a group of databases without knowing the data's exact location. For more information about the full-text search, refer to Full-text search in databases.	Data sources and their elements.
Tools \| Set Color		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 Tools \| Set Color. In the Database Color Settings dialog, select the dialog and coloring options. To apply the data source color to query consoles and grids, select the In console editors and grids checkbox. See also, Assign a color to query consoles and grids.	Data sources and their elements.
Tools \| Add Bookmark	`F11`	Add the selected item to bookmarks.	Data sources and their elements.
Tools \| 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.	Data sources and their elements.
Tools \| Scripted Extensions \| Go To Scripts Directory		Switch to the directory where the Generate POJOs.clj example script file is located.	Data sources and their elements.
Import/Export \| 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).	Data source elements: tables and views.
Import/Export \| Import Data from File(s)…		Import a text file containing delimiter-separated values (CSV, TSV, and so on) into your database. If a schema is currently selected, PyCharm will create a new table for the data that you are importing. If a table is selected, PyCharm will try to add the data to the selected table.	Data source elements: tables.
Import/Export \| 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.	Database objects: tables and views.
Import/Export \| Dump to DDL Data Source		Create your DDL data source by dumping a regular data source to a root/repository folder.	Data sources and their elements.
Import/Export \| Export with 'mysqldump' or Import/Export \| 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 PyCharm. You can read about them at dev.mysql.com and postgresql.org.	Data source nodes.
Import/Export \| Restore with 'mysql' or Import/Export \| 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 PyCharm. You can read about them at dev.mysql.com and postgresql.org.	Data source nodes.
Diagrams \| Show Visualisation and Diagrams \| Show Visualisation Popup	`Ctrl+Alt+Shift+U` and `Ctrl+Alt+U`	View a UML class diagram for the selected data source or table. You can select between the following options: Show Diagram to open the diagram in a separate editor tab. Show Diagram Popup to see the diagram in a popup.	Data sources and their elements.
Diagnostics \| Dump Metadata Model (former Dump Model)		Generate a diagnostic representation of the database in a PyCharm internal format. This information might be helpful for the support team if some database objects exist in the database but do not show up in the Database tool window.	Data sources and their elements.
Diagnostics \| Generate Introspector Diagnostic Files (former Prepare Introspector Diagnostics)		Generate three files that include information about the following: dataSource.txt: the data source. introspector.txt: a module that was used to load the metadata from the database. model.xml: a part of the database model. This information might be helpful when introspection works incorrectly. For example, when you see something outdated or do not see new objects.	Data sources and their elements.
Diagnostics \| 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.	Data source nodes.
Diagnostics \| Forget This Schema Cache		Delete the information that PyCharm 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.	Data source nodes.
Diagnostic Mode		Enables the diagnostic mode where PyCharm writes the introspection log. You can access the log file by clicking the link in the Diagnostic log started and Diagnostic log stopped notification popups that appear once you enable and disable the mode.

Icons

Data sources and their elements

Icon	Description
	Access Method
	Aggregate
	Alias Type
	Argument
	Body
	Check
	Cluster
	Collation
	Collection Type
	Column. For more information about column icon combinations, refer to Possible icon combinations for columns.
	Data File
	Database
	Read-only status
	DDL data source
	Default
	Exception
	Extension
	External Schema
	Foreign Data Wrapper
	Foreign Key
	Foreign Table
	Index
	Key
	Materialized Log
	Materialized View
	Object Attribute
	Object Type
	Operator
	Package
	Primary key
	Projection
	Data sources
	Role
	Routine
	Rule
	Scheduled Event
	Schema
	Sequence
	Server
	Stored procedure or function
	Synonym
	Table
	Table Type
	Tablespace
	Trigger
	User
	User Mapping
	Variable
	View
	Virtual column
	Virtual foreign key
	Virtual view
	Virtual Table