PhpStorm 2023.2 Help

Schemas

PhpStorm displays databases and schemas that you selected to be displayed in the Database tool window. It can be useful if you have many databases and schemas. Also, by using this approach, you define which schemas you want to introspect. During introspection, PhpStorm loads the metadata from the database and uses this data later.

Schemas can be seen in the Database tool window. You can see a reference on node and object icons in the Data sources and their elements chapter of Database tool window topic.

Create a schema

  1. In the Database tool window ( View | Tool Windows | Database) , expand the data source tree until the nodes of databases or schemas.

  2. Right-click the data source, database, or schema node and select New | Schema.

  3. In the Modify dialog that opens, enter the name of your schema in the Name field.

  4. In the Preview pane, you can view and change the generated SQL code.

  5. Click OK.

the Create schema dialog

Select the default schema

  • You can select the default schema or database by using the list, which is in the upper-right part of the toolbar. When you select the default schema, you can omit the name of that schema or database in your statements.

    Click the <schema> list and select the schema that you need.

    Select the default schema or database

Set the default schema in connection settings

  1. Open data source properties. You can open data source properties by using one of the following options:

    • In the Database tool window ( View | Tool Windows | Database) , click the Data Source Properties icon The Data Source Properties icon.

    • Press Control+Alt+Shift+S.

  2. Select a data source that you want to modify. On the General tab in the Database field, type the name of a schema that you want to use as default.

    Select the default schema or database

Show and hide schemas and databases

  • In the Database tool window ( View | Tool Windows | Database) , right-click a data source and navigate to Tools | Manage Shown Schemas…. Select or clear checkboxes of schemas that you want to enable or disable. Press Enter.

  • Click the N of M link near the data source name. In the database and schema selection window, select or clear checkboxes of databases and schemas that you want to enable or disable. Press Enter.

    Show and hide schemas and databases

Pattern-based schemas filter

To display and introspect all the schemas or databases with names that match a regular expression pattern, do the following:

  1. In the Database tool window ( View | Tool Windows | Database) , click the N of M link near the data source name.

  2. In the databases and schemas selector, click the add pattern button.

    Pattern-based schema filter
  3. In the new filtering node, define the regular expression. Press Enter to apply the filter in the selector.

  4. Press Enter to apply the filter.

    Regular expressions in pattern-based schema filter

    The filtering node with filter can be added under any node, including another filtering node.

Show all the schemas and databases

  • To display all the available databases and schemas in the Database tool window ( View | Tool Windows | Database), click the Show Options Menu button and select the Show All Namespaces option.

    Disabled

    Enabled

    Show All Namespaces is disabled
    Show All Namespaces is enabled

Compare two schemas

For more information about dialog controls, refer to Controls of the Migration dialog.

  1. Select two schemas.

  2. Right-click the selection and navigate to Tools | Compare Structure…. Alternatively, press Control+D.

    Compare two schemas

Set the schema search path for PostgreSQL and Redshift

The search_path environment variable in PostgreSQL specifies the order in which schemas are searched. For example, you set the value of search_path to z,a,public, PostgreSQL will look for a value in the z schema. If nothing was not found in the z schema, PostgreSQL looks for the value in the a schema.

In PostgreSQL and Amazon Redshift, the default search path (the path that is set in a database) is used unless you specify a different search path.

  1. Click the <session> list, navigate to the list of database schemas (use the arrow icon the Arrow icon or press the right arrow key).

  2. Select the schema that you want to add to a search path.

    To form a search path, you can use the following actions:

    • Press Space to add a highlighted schema to the search path and to remove a schema from the search path.

    • Press Alt+ArrowUp and Alt+ArrowDown to reorder the schemas within the search path.

  3. To apply the changes, click OK.

    Control the search path for PostgreSQL and Redshift

Save a search path for PostgreSQL and Redshift between IDE restarts

  1. In the Database tool window ( View | Tool Windows | Database) , right-click a PostgreSQL or Amazon Redshift data source and select Properties Command I.

  2. Click the Options tab.

  3. From the Switch schema list, select Automatic.

  4. Click OK.

    Save a search path between IDE restarts

Force refresh schema information

The Force Refresh action clears the data source information from cache and loads it again from scratch.

  • In the Database tool window ( View | Tool Windows | Database) , right-click a data source and select Diagnostics | Force Refresh.

Pre-introspected objects from system catalogs

Introspection is a method of inspecting a data source. When you perform introspection, structural information in the data source is inspected to detect tables, columns, functions, and other elements with their attributes.

A system catalog is a place where a relational database management system (DBMS) stores information about tables and columns, built-in functions, and other schema objects. The IDE uses these objects for code completion and other coding assistance operations.

System schemas have the lightning icon (Enable or disable the usage of pre-introspected objects) in the schema selection dialog. If you do not select these schemas, PhpStorm does not introspect them and does not show them in the Database tool window. Though information about schema objects is used in coding assistance. It is possible because PhpStorm uses internal data about schema objects that was introspected earlier (pre-introspected data). To enable usage of pre-introspected data in PhpStorm, open data source settings by pressing Command I, click the Options tab and select Use pre-introspected objects for system catalogs that are not introspected.

Examples of system catalogs in different DBMS:

  • PostgreSQL: pg_catalog, information_schema

  • Microsoft SQL Server: INFORMATION_SCHEMA

  • Oracle: SYS, SYSTEM

  • MySQL: information_schema

  • IBM Db2 LUW: SYSCAT, SYSFUN, SYSIBM, SYSIBMADM, SYSPROC, SYSPUBLIC, SYSSTAT, SYSTOOLS

Introspect system catalogs for a data source

By default, PhpStorm uses pre-introspected objects for system catalogs.

  1. In the Database tool window ( View | Tool Windows | Database) , right-click a data source and select Properties Control+Alt+S.

  2. In the Data Sources and Drivers dialog, click the Options tab.

  3. Clear the Use pre-introspected objects for system catalogs that are not introspected checkbox.

  4. In the scheme selection window, select system catalogs that you want to introspect.

    Introspect system catalogs for a data source

Use pre-introspected data for the selected system catalog

You can still use pre-introspected objects for a system catalog even if you disable the usage of such objects for a data source.

  1. Clear the Use pre-introspected objects for system catalogs that are not introspected checkbox on the Options tab in data source settings.

  2. Open the scheme selection window, clear the checkbox of a system catalog.

  3. Click the system catalog entry.

  4. Click the lightning icon Enable or disable the usage of pre-introspected objects, which is in the upper-right corner of the window.

Generate a diagram for a schema

  • In the Database tool window ( View | Tool Windows | Database) , right-click a schema and select Diagrams | Show Diagram.

Generate a diagram for a database object
Last modified: 22 September 2023