SQL Console, EXPLAIN PLAN and Database Inspections in PhpStorm

1. PhpStorm as a complete database client

The Database tool window in PhpStorm is not a simple SQL editor, it is a complete database client with schema navigation, query analysis, database diff, data export and integrated EXPLAIN PLAN visualization. For PHP developers who already work in PhpStorm, this means no context switching between the IDE and an external database tool. Schema information visible in the Database window is also available in SQL autocompletion and in PHP code completion.

This context is especially valuable for Magento projects. PhpStorm can index the Magento database with its 400+ tables and offer developers writing SQL queries in PHP code full autocompletion for table names, column names and even enum values. Anyone drafting a raw query inside a ViewModel gets immediate hints about the correct table and column names, without consulting the documentation or manually browsing the database.

The integration goes even deeper: PhpStorm recognizes SQL strings inside PHP code and offers the same SQL autocompletion in those strings as in the SQL console. This works for plain strings, heredocs and even for PDO prepared statements. If a table name in a SQL string has a typo, PhpStorm shows a warning before the query is ever executed.

2. Setting up a database connection for Docker Magento

In a Docker Magento environment following the Mark Shust pattern, MySQL runs inside the db container on port 3306. The port is mapped outward, usually to localhost:3306 on the host. In the Database tool window (View → Tool Windows → Database), create a new data source: + → Data Source → MySQL. As host use 127.0.0.1, port 3306, database name magento, and username and password from the Docker project's .env file.

An important step after setup: select schemas. By default PhpStorm indexes every database on the server. For Magento projects only the Magento database should be selected, since indexing all 400+ tables can otherwise take several minutes on first start. This can be restricted under Data Source Settings → Schemas. After the initial indexing, changes are updated incrementally.

PhpStorm supports SSH tunneling directly in the connection configuration. For production databases reachable only through a jump host, the SSH tunnel can be configured in the connection settings, no separate SSH tunnel in the terminal required. This is particularly useful when comparing schemas between staging and production without exporting the data and importing it locally.

3. The SQL console: autocompletion and context

The SQL console is opened by right-clicking the data source and choosing Open Query Console, or with Ctrl+Shift+F10. It offers context-aware autocompletion: after SELECT * FROM it suggests every table in the connected database. After a table name and a dot it completes column names, including type information and nullable status. This autocompletion is faster and more precise than most external database clients.

Particularly practical is parameterizing queries directly in the SQL console. With WHERE entity_id = :entity_id, PhpStorm inserts a parameter dialog as soon as the query is executed. This allows quickly testing the same query with different values without adjusting the query every time. The dialog remembers the most recently used values and offers them as defaults the next time it runs.

The SQL console supports multiple statements in a single window. Ctrl+Enter executes the statement the cursor is on. Ctrl+Shift+Enter executes every statement in the window in sequence. Results appear in a tabular grid below the editor that is sortable, filterable and directly editable. Changes in the grid are written back to the database as UPDATE statements, after an explicit confirmation.

4. EXPLAIN PLAN: analyzing and optimizing queries

EXPLAIN PLAN is the most important tool for query optimization and is available directly in the SQL console through the Explain Plan button or Ctrl+Shift+E. PhpStorm runs the query with EXPLAIN FORMAT=JSON and visualizes the result in an interactive tree. Every node shows table access, the index used, join type, estimated rows and actual execution cost.

For Magento, slow queries are often caused by missing indexes on EAV joins or by table scans during filter operations. An EXPLAIN plan immediately shows whether a full table scan is happening, recognizable by the join type ALL and a high row estimate. PhpStorm visually highlights critical nodes and shows exactly where an index is missing or where the query optimizer makes a suboptimal decision.

<?php
// Example: Magento repository with optimized SQL for EXPLAIN analysis
// app/code/Mironsoft/Catalog/Model/ResourceModel/Product/Collection.php

declare(strict_types=1);

namespace Mironsoft\Catalog\Model\ResourceModel\Product;

use Magento\Catalog\Model\ResourceModel\Product\Collection as BaseCollection;

/**
 * Extended product collection with optimized SQL for PhpStorm EXPLAIN analysis.
 * Run raw query in PhpStorm SQL console to analyze execution plan.
 */
class Collection extends BaseCollection
{
    /**
     * Join custom pricing table with index-aware filtering.
     * SQL for EXPLAIN PLAN analysis in PhpStorm:
     *
     * EXPLAIN SELECT e.entity_id, e.sku, p.special_price
     * FROM catalog_product_entity e
     * INNER JOIN mironsoft_product_pricing p ON e.entity_id = p.product_id
     * WHERE p.store_id = 1
     *   AND p.special_price > 0
     *   AND p.valid_until >= CURDATE()
     * ORDER BY p.special_price ASC
     * LIMIT 50;
     */
    public function addSpecialPriceFilter(int $storeId): static
    {
        $this->getSelect()
            ->joinInner(
                ['price_table' => $this->getTable('mironsoft_product_pricing')],
                'e.entity_id = price_table.product_id AND price_table.store_id = ' . $storeId,
                ['special_price', 'valid_until']
            )
            ->where('price_table.special_price > 0')
            ->where('price_table.valid_until >= CURDATE()');

        return $this;
    }
}

The EXPLAIN PLAN result in PhpStorm shows, alongside the visual tree, the raw EXPLAIN data in a separate tab. Comparing EXPLAIN results before and after adding an index is easy with the SQL console's history feature: PhpStorm stores every executed statement and its results, so older EXPLAIN output can be compared with current results without documenting it manually.

5. Schema navigation and exploring table structure

The Database tool window shows every table in the connected database in a hierarchical structure. Double-clicking a table opens the data grid. Clicking the arrow icon next to a table expands its columns, indexes and foreign keys. For Magento projects with their complex EAV structure, this navigation is far clearer than the information schema queries one would otherwise write in a terminal.

PhpStorm shows foreign key relationships as navigable links. Clicking a foreign key jumps to the referenced table and highlights the referenced column. This makes it intuitive to explore the relationship structure of Magento tables, particularly useful when trying to understand how catalog_product_entity, the EAV value tables and catalog_product_entity_varchar relate to each other.

The search function in the Database tool window (Ctrl+F) allows quickly finding tables and columns. With the filter, tables can be searched by pattern, catalog_product* shows every catalog product table. In Magento, with its 400+ tables, this is considerably faster than manual searching or terminal queries against information_schema.tables.

6. Database diff and schema comparison

The schema diff is one of the most powerful features of the PhpStorm database tools. It compares two database instances, for example a local development environment and staging, and shows the differences in table structure, indexes and columns. The result is a visual diff and, optionally, a generated SQL migration script that reconciles the differences.

For Magento projects, schema diff is especially useful after installing new extensions. When an extension adds tables without a declarative schema or modifies existing tables, the diff shows exactly what changed. This simplifies code reviews and helps catch unwanted schema changes before they reach production. The generated SQL diff can be copied directly into the SQL console and executed against the target database.

Another use case: comparing the db_schema.xml declaration against the actual database state. When a schema change was not applied correctly, or when setup:upgrade did not complete fully, the diff shows the deviation immediately, without writing manual queries.

7. Navigating Magento's EAV structure with PhpStorm

Magento's Entity-Attribute-Value (EAV) system is a black box for many developers. PhpStorm makes the structure transparent through its database navigation. The central table eav_attribute links to eav_entity_type via entity_type_id. The actual values live in typed tables such as catalog_product_entity_varchar, catalog_product_entity_int and catalog_product_entity_decimal. These relationships can be explored directly through the Database tool window.

A helpful SQL query for the PhpStorm SQL console: retrieve every attribute of a product type together with its values. Running this in the SQL console and inspecting the result in the grid quickly clarifies which attributes belong to which tables and what values a specific product has. This is significantly more efficient than manually browsing XML configuration files or database documentation.

-- SQL console: query Magento EAV attribute values for a product
-- Paste into the PhpStorm SQL console and run with Ctrl+Enter
-- EXPLAIN PLAN (Ctrl+Shift+E) shows join strategy and index usage

SELECT
    ea.attribute_code,
    ea.frontend_input,
    COALESCE(
        varchar_val.value,
        int_val.value,
        decimal_val.value,
        text_val.value,
        datetime_val.value
    ) AS attribute_value,
    ea.is_required,
    ea.is_visible
FROM eav_attribute ea
JOIN eav_entity_type eet
    ON ea.entity_type_id = eet.entity_type_id
    AND eet.entity_type_code = 'catalog_product'
LEFT JOIN catalog_product_entity_varchar  varchar_val
    ON varchar_val.attribute_id = ea.attribute_id
    AND varchar_val.entity_id   = :product_id
    AND varchar_val.store_id    = 0
LEFT JOIN catalog_product_entity_int      int_val
    ON int_val.attribute_id     = ea.attribute_id
    AND int_val.entity_id       = :product_id
    AND int_val.store_id        = 0
LEFT JOIN catalog_product_entity_decimal  decimal_val
    ON decimal_val.attribute_id = ea.attribute_id
    AND decimal_val.entity_id   = :product_id
    AND decimal_val.store_id    = 0
LEFT JOIN catalog_product_entity_text     text_val
    ON text_val.attribute_id    = ea.attribute_id
    AND text_val.entity_id      = :product_id
    AND text_val.store_id       = 0
LEFT JOIN catalog_product_entity_datetime datetime_val
    ON datetime_val.attribute_id = ea.attribute_id
    AND datetime_val.entity_id   = :product_id
    AND datetime_val.store_id    = 0
WHERE ea.is_visible = 1
ORDER BY ea.attribute_code;

8. Data export and reusing query results

Query results in the grid can be exported to various formats via right-click → Export Data: CSV, JSON, SQL insert statements, Excel and more. For Magento development, CSV export is useful for extracting product data for tests or copying configuration data between environments. The SQL insert export generates INSERT statements that can be imported directly into another database instance.

A standout feature is the live edit mode in the data grid. Changing a value directly in the grid makes PhpStorm automatically generate the corresponding UPDATE statement and show it before execution. This is faster than writing an UPDATE manually and safer, because the WHERE clause is set correctly on its own. For debugging Magento configuration values in the core_config_data table, this is ideal.

The SQL console's query history (Ctrl+Alt+E) stores every executed statement with a timestamp and result preview. Complex queries can be saved as favorites and given a name. For recurring analysis queries, for instance EXPLAIN plans for particular report queries, this saves considerable time compared to rewriting them or searching through browser bookmarks.

9. PhpStorm DB tools vs. external clients compared

The biggest advantage of PhpStorm's database tools over external clients lies in IDE integration. SQL autocompletion in PHP code, instant switching between code and data grid, EXPLAIN analysis without context switching, all of this adds up to a noticeably smoother development rhythm.

External clients like TablePlus have advantages for pure database work: faster navigation across many connections, better performance with very large result sets and a more focused user interface. For PHP developers who spend most of the day in PhpStorm, though, the benefits of IDE integration outweigh that. An external client still makes sense for intensive, purely database-focused work, such as data migration or complex bulk operations.

10. Summary

PhpStorm's integrated database tools make switching context between the IDE and an external database client unnecessary for most everyday tasks. The SQL console with full autocompletion, parameterized queries and query history, EXPLAIN PLAN with a visual representation, schema navigation for Magento's complex table structure, and schema diff for environment comparisons, all of this is available without any additional software.

The biggest lever is using EXPLAIN PLAN right after writing queries. Instead of running queries blindly and discovering performance problems only once they hit production, every query can be analyzed during development. For Magento projects with EAV complexity and dozens of joins inside collection classes, this makes a fundamental difference to the development flow.

11. FAQ: SQL Console and Database Tools in PhpStorm