Query console

Last modified: 29 December 2022

Overview

Query consoles are SQL files that are associated with a data source and connected to it via a connection session. In query console you can compose and execute your SQL statements.

For example, if you open a query console for PostgreSQL, a connection session to the PostgreSQL data source is automatically created. When you open an SQL file from your hard drive, you need to specify the connection session first.

Database connection session

For each console, a data source connection session is created automatically. You can create new connection sessions and attach your console to existing ones in the console toolbar.

In the following example, the PostgreSQL console is associated with the guest.public schema of a data source and connected to it via the console connection session. You can change the console session to actor or create a new session and attach the console to it.

Read more about managing sessions in Managing connection sessions.

Change the connection session

Code editor

The code editor is where you compose your SQL statements using the resolve modes and coding assistance features, and execute them against the associated data source.

Find the code editor toolbar controls in Code editor controls. Read more about the editor in Editor basics.

Resolve modes

In order to run SQL scripts, treat the usages of the database objects as links, and provide you with correct code completion, PhpStorm must resolve the database objects in your code to a certain context. By default, IDE uses one of the following as a starting point to resolve the database objects to: <schema> selector, or the default database (if none of the above is set).

For more information about resolve modes, refer to Resolve modes.

User parameters

Before execution of a parametrized statement, you see the Parameters window where you can specify values for the parameters.

Run a statement with parameters

SQL statement execution

When you execute a statement, the Services tool window opens. The Services tool window displays available connection sessions, Output and Result tabs. For more information about the Services tool windows, see Services tool window.

Code editor controls

Use the code editor to compose and execute your SQL statements as well as to perform other associated tasks.

Read more about the editor in Editor basics.

Toolbar controls

Item

Tooltip and shortcut

Description

the Execute button

Execute

Ctrl+Enter

Execute selected SQL statement or statements.

If nothing is selected, the current statement is executed.

Browse Data Source History

Browse Query History

Ctrl+Alt+E

Open a dialog that shows all the statements that you have run for the corresponding data source.

See also, Using the History dialog.

View Parameters

View Parameters

Open or close the Parameters dialog.

Settings

Open Query Execution Settings…

Open the Tools | Database | Query Execution section of the Settings dialog to view or edit the query execution settings.

For more information on these settings, refer to Query Execution.

Transaction mode

Transaction Mode

and

Transaction Isolation

Select the isolation level for database transactions and the way the transactions are committed.

  • Auto: the current transaction is committed automatically when you submit your local changes to the database server.

  • Manual: the changes submitted to the database server are accumulated in a transaction that can either be committed or rolled back explicitly by means of the Commit (Commit button) or Rollback (Rollback button) buttons on the toolbar.

For more information about database transaction modes and isolation, refer to Submit changes to a database.

Commit

Commit Ctrl+Alt+Shift+Enter

Commit the current transaction. See also, transaction modes and isolation.

Rollback button

Roll Back

Roll back changes. This button is available only for the manual transaction mode. See also, transaction modes and isolation.

Cancel Query

Cancel Running Statements

Ctrl+F2

Terminate execution of the current statement or statements.

Playground

File Resolve Mode

Select the resolve mode to manage the context that database objects in your code are resolved to.

  • Playground: default mode for query consoles. All the database objects are resolved to the same context.

  • Script: default mode for local SQL files. The USE SQL statement (SET search_path TO for PostgreSQL) overrides the default context that the database objects are resolved to.

For more information about the resolve modes, refer to Resolve modes.

Database plugin icons editor output

In-Editor Results

Toggle the display of query result within the code editor of query console.

In-Editor results

For more information about the in-editor results, refer to In-Editor Results.

Schema <schema>

Switch current schema

Select the default schema or database. For PostgreSQL, Amazon Redshift, and Greenplum use the <schema> list to form the schema search path.

Select a search path for PostgreSQL

For more information about schemas, refer to Schemas.

Database session <session>

Switch attached session

Select the database connection session.

For more information on the database connection sessions, refer to Managing connection sessions

Productivity tips

In the code editor context menu, use the following actions to increase your productivity:

Item

Shortcut

Description

Database plugin icons table Edit as Table

In INSERT statements, opens the editor for working with the data in a table format.

Edit data in INSERT statements as a table

Change Dialect (<CurrentDialect>)

Change the SQL dialect. Select a dialect from the list.

Explain Plan

  • Explain Plan: show an execution plan (or explain plan) for the current statement. The result is shown in a mixed tree/table format on a dedicated Plan tab.

    Example of Explain Plan

    To build a diagram of the execution plan, click the Show Visualization button (the Show Visualization button) on the left of the Plan tab, or press Ctrl+Alt+Shift+U.

    To build a flame graph, click the Flame Graph button (the Flame Graph icon).

  • Explain Plan (Raw): show an execution plan (or explain plan) for the current statement. The result is shown in table format. Technically, EXPLAIN <CURRENT_STATEMENT> or similar statement is executed.

    Example of Explain Plan

App actions execute Execute

Ctrl+Enter

Execute the current statement or the sequence of selected statements.

App actions execute Execute to File

Execute the current statement and save results in a text file. Select the output format and specify the file location and name.

Run 'console [data_source]'

Ctrl+Shift+F10

Execute all the statements in the query console.

See also

Getting Started