Run queries

To cancel running statements, click the Cancel Running Statements button () on the toolbar. The progress indicator on the gutter shows a red square while the request is being canceled. A canceling progress bar is shown, which clarifies the data source and the session.

WebStorm displays a warning to verify that you want to deactivate the data source. The warning is displayed to ensure safety as deactivation might be potentially dangerous for some processes happening simultaneously. So, if you click the Cancel Running Statements button two times, WebStorm displays a warning where you can terminate the remote process or wait a bit longer.

Also, the IDE displays a warning after 10 seconds of idle canceling.

note

If you click the Deactivate button () on the toolbar of Database tool window, it will stop the query in WebStorm, but the query will continue its execution in the database.

You can interrupt not only the execution of a request, but also the creation of a connection. This is especially relevant for the very first query in the console, which also creates a connection.

You can stop the process of creating a connection. To do that, click the Cancel Running Statements button () on the toolbar while the connection is being created. The connection will be stopped, and you will see the Connection canceled message.

In order to run SQL scripts, treat the usages of the database objects as links, and provide you with correct code completion, WebStorm 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).

Depending on whether your code is a single script or it has multiple unrelated SQL statements, select one of the following resolve modes to resolve the database objects properly:

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

  Use this mode if your code is a set of unconnected SQL statements which are not meant to be run as a single script.

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

  Use this mode if your code is meant to be run as a single script.

  note

  By default, the <schema> selector is not available for local SQL files. For the <schema> selector to appear, attach a session to your SQL file. To do that, click the <session> button (<session>) and select the session. Alternatively, right-click the file in the Project tool window and select Attach Session.

In the following video, the public schema is selected as default for the query console. The schema contains the actor table, whereas the information_schema schema does not contain it. When the resolve mode is Playground, the database object is resolved to default schema in both SELECT statements, because all the statements are considered unrelated.

Once the resolve mode is changed to Script, the second SELECT statement is considered related to the SET search_path TO information_schema one. The actor table is no longer resolved in that statement.

Depending on the database vendor, the question mark ? is treated as a parameter in SQL statements. You can specify what other characters and their sequences should be treated as parameters, and in which places, in the Tools | Database | Query Execution | User Parameters page of the IDE settings CtrlAlt0S.

The patterns for SQL parameters are specified by using regular expressions.

Examples

Array as a parameter

In WebStorm, the replacement of a parameter with a value is straightforward. To use an array as the value, specify your array in the corresponding field.

In the following example, the ? parameter can be replaced with 206, 'John', 'Smith' value:

INSERT INTO actor (actor_id, first_name, last_name, last_update)
VALUES (?, '01.02.2023 13:34');

For further information on parameters, refer to the database documentation.

Parameter pattern

If you have parameters with a specific parameter syntax in your scripts, specify the pattern using a regular expression.

For the following example, the %{2}(\w+_\d+) pattern must be set in the Tools | Database | Query Execution | User Parameters page of the IDE settings CtrlAlt0S:

SELECT title, description, release_year FROM actor
JOIN film_actor fa ON actor.actor_id = fa.actor_id
JOIN film f ON f.film_id = fa.film_id
WHERE last_name = '%%winnerpl_2';

Usually, when you run a query, you receive results in a table format. WebStorm displays the results in a data editor. For each statement, the data editor with results appears in a separate tab in the Services tool window. For example, if you run three SELECT statements in the query console, you will see three tabs in the tool window.

For more information about viewing query results, refer to Query results.