Database Tools in PhpStorm for MySQL and MariaDB Done Right

1. What the PhpStorm Database Tools do, and what they do not

PhpStorm Ultimate ships with fully fledged Database Tools built on the DataGrip engine. That means full SQL autocompletion with table and column names, schema diagrams, a table editor with inline editing, import/export and dump generation. For most day to day development tasks that is more than enough. The decisive advantage over external tools is the integration into the editor: SQL queries inside PHP strings get enriched with database intelligence as soon as a connection is configured.

What the Database Tools cannot do: they are no substitute for admin tools on production databases with high data volume, where specialized GUI clients such as DBeaver offer better visualization options. For simple schema migrations during development and for debugging Magento SQL queries, though, they are excellent. The PhpStorm Community Edition does not include the Database Tools, the Ultimate Edition is required. For professional PHP development that is not an obstacle, since Ultimate is the standard choice for PHP projects.

2. Setting up a database connection to MySQL and MariaDB

The connection is created through the Database window: View → Tool Windows → Database, then the plus icon at the top left, Data Source → MySQL (or MariaDB). The dialog asks for host, port (3306), database name, username and password. The first time a MySQL connection is set up, PhpStorm offers to download the JDBC driver automatically, be sure to run this download before testing a connection. Clicking Test Connection immediately shows whether the connection works and whether driver and database version are compatible.

For MariaDB it is important to explicitly select the MariaDB driver instead of using the MySQL driver, since newer MariaDB versions (10.6+) can run into incompatibilities. In the Advanced tab, connection parameters such as serverTimezone, useSSL and allowPublicKeyRetrieval can be configured. Under Schemas you choose which databases should be visible, for Magento it is recommended to enable only the active development database so that autocompletion is not slowed down by other databases.

3. Connecting a Docker container as a database source

In the Mark Shust Docker setup, MySQL runs inside the container but is reachable on localhost through the exposed port. The connection in PhpStorm uses host 127.0.0.1, port 3306 (or the host port defined in compose.yaml). The connection credentials can be found in the setup's env/db.env. A common pitfall: the container must be running while the connection is being set up, otherwise the test fails and PhpStorm marks the connection as unreachable.

Alternatively, an SSH tunnel can be configured if the database container does not expose a direct port on localhost. PhpStorm supports SSH tunnels directly within the database connection configuration, under the SSH/SSL tab. For the typical Docker Compose development environment, though, the direct port approach is simpler. Once the connection is established and PhpStorm has loaded the database schema, which can take a few seconds on first connect, autocompletion is active in SQL consoles and PHP SQL strings.

<?php
// PhpStorm recognizes SQL inside PHP strings and offers autocompletion
// once a database connection is configured and the schema is loaded.

declare(strict_types=1);

namespace Mironsoft\Catalog\Model\ResourceModel;

use Magento\Framework\Model\ResourceModel\Db\AbstractDb;

/**
 * Custom product resource model with optimized queries.
 */
class Product extends AbstractDb
{
    protected function _construct(): void
    {
        $this->_init('catalog_product_entity', 'entity_id');
    }

    /**
     * Load active products by category ID with price filter.
     * PhpStorm highlights table/column names from connected DB schema.
     */
    public function getActiveByCategoryAndMaxPrice(int $categoryId, float $maxPrice): array
    {
        $connection = $this->getConnection();

        // SQL string: PhpStorm provides autocompletion for table and column names
        $select = $connection->select()
            ->from(['e' => $this->getMainTable()], ['entity_id', 'sku', 'name'])
            ->join(
                ['cp' => $this->getTable('catalog_category_product')],
                'e.entity_id = cp.product_id',
                []
            )
            ->where('cp.category_id = ?', $categoryId)
            ->where('e.type_id = ?', 'simple')
            ->order('e.entity_id ASC');

        return $connection->fetchAll($select);
    }
}

4. SQL Console: writing and running queries

The SQL Console is opened with a double click on the database connection or via the context menu Open Console. Several consoles can be opened at the same time for the same connection, which is useful in complex debugging sessions. Running a query happens with Ctrl+Enter for the whole file or for the selected block. The result appears below in the result area as a formatted table with pagination, sorting and filtering.

A particularly useful feature is the query history (Ctrl+Alt+E), which stores every executed SQL statement with a timestamp. During debugging sessions, where you try out many variants of a query, that is a real time saver. PhpStorm automatically formats SQL queries with Ctrl+Alt+L and supports MySQL and MariaDB specific syntax while doing so. Explain plans for queries open with Ctrl+Shift+E, the plan is rendered as a visual diagram or as text, which is very helpful when analyzing the performance of Magento queries.

5. SQL autocompletion and code intelligence

Autocompletion in the SQL Console and in PHP strings containing SQL knows every table and column of the connected database. With Magento 2 and its several hundred tables, that is a considerable advantage: instead of laboriously typing out catalog_product_entity_varchar, typing cpe is enough and PhpStorm suggests all tables that start with those letters. Autocompletion also recognizes aliases in complex JOINs and suggests the correct columns for the respective alias.

For PHP strings, SQL injection detection is enabled through a comment /** @lang SQL */ or through the context marker that PhpStorm offers once it recognizes SQL inside a string. Typos and syntax errors in the SQL are then underlined directly in the PHP file, before the code is even executed. This integration between PHP context and database schema is a distinguishing feature of the PhpStorm Database Tools compared to external database clients.

<?php
// SQL Console: analyzing the Magento 2 database
-- Show all configuration values (core_config_data)
SELECT path, value, scope, scope_id
FROM core_config_data
WHERE path LIKE 'catalog/seo/%'
ORDER BY path;

-- Check cache types and status
SELECT id, code, status, tags
FROM cache_tag
ORDER BY code
LIMIT 50;

-- Count active products in a category
SELECT cce.entity_id, cce.path, COUNT(ccp.product_id) AS product_count
FROM catalog_category_entity cce
LEFT JOIN catalog_category_product ccp ON cce.entity_id = ccp.category_id
WHERE cce.level > 1
GROUP BY cce.entity_id
HAVING product_count > 0
ORDER BY product_count DESC
LIMIT 20;

-- Show explain plan (Ctrl+Shift+E in PhpStorm)
EXPLAIN SELECT e.entity_id, e.sku
FROM catalog_product_entity e
INNER JOIN catalog_category_product cp ON e.entity_id = cp.product_id
WHERE cp.category_id = 4
  AND e.type_id = 'simple'\G

6. Schema browser and diagrams

In the Database window, all tables, views, stored procedures and functions of the connected database can be browsed and inspected. A right click on a table → Diagrams → Show Visualization opens an ER diagram that renders foreign keys as connecting lines. With Magento 2 and its complex EAV table structures, that is very helpful for orientation. The diagram can be exported as an image, which is useful for documentation purposes.

Table structures can be modified directly in the schema browser: right click on a table → Modify Table opens a graphical editor for columns, types, indexes and foreign keys. The generated DDL is shown as a preview before it is executed. For Magento 2 projects, though, this direct manipulation of the database structure should be used with caution, schema changes should always go through db_schema.xml and Magento's setup mechanism, not manually via the database. In this context, the schema browser is primarily a reading tool.

7. Editing data directly in the table editor

A double click on a table in the Database window opens the table editor, which shows the data directly as an editable table. Values can be changed inline, new rows added and existing rows deleted. Changes are not applied immediately, only after clicking the submit button or Ctrl+Enter, which gives you the opportunity to bundle several changes together. The undo function Ctrl+Z reverts changes as long as they have not been submitted yet.

The table editor also supports exporting query results: right click on the result area → Export Data offers CSV, JSON, SQL INSERT, HTML table and more formats. That is useful when you want to bring test data from the production database (anonymized) into the development environment. Filters in the table editor work like WHERE conditions: you enter a value into the filter row and PhpStorm generates the corresponding SQL query automatically.

8. Generating and importing dumps

For database dumps, PhpStorm offers two paths: through the context menu of the database connection → Export with 'mysqldump' or through Export Data. The mysqldump path uses the locally installed mysqldump binary and produces a complete dump in MySQL format. For Docker setups, where mysqldump is only available inside the container, the direct export path is better: right click on the database → Export Data → SQL Inserts produces a plain SQL script with INSERT statements without external dependencies.

Importing dumps happens via right click on the database connection → Run SQL Script → select file. PhpStorm runs the script against the connected database and shows progress and errors in the console window. For larger dumps, keep in mind that PhpStorm uses the JDBC driver, which can be slower than the native MySQL client. For dumps over 100 MB, using the wrapper bin/mysql from the Docker setup is recommended instead, since it runs directly inside the container and is noticeably faster.

9. PhpStorm Database vs. external clients compared

To decide when the PhpStorm Database Tools are enough and when an external client makes more sense, a direct comparison of the most important criteria helps.

The conclusion is clear: for day to day development work, where you switch between PHP code and database queries, the PhpStorm Database Tools are the best choice. For specific tasks such as large volume dumps or extensive schema migrations in production, a native client makes sense. phpMyAdmin should be avoided for new projects wherever possible, its security history and low productivity get in the way of a modern setup.

10. Summary

For PHP developers who work with MySQL or MariaDB on a daily basis, the PhpStorm Database Tools are a fully fledged alternative to external database clients. The integration into the editor, SQL autocompletion in PHP strings, clickable error messages, direct comparison of results with the code, saves context switches and reduces typos and logic mistakes. For Magento 2 projects running in Docker, connecting through the exposed port on localhost is the simplest way to load the schema and write queries with autocompletion.

The limits show up with large data volumes and production databases, where native clients are faster and safer. For dumps over 100 MB, the Docker wrapper bin/mysqldump is recommended instead of PhpStorm's JDBC based export. Schema changes in Magento 2 always belong in db_schema.xml, not in manual table editing. Within these limits, the Database Tools are a central productivity lever for development.

11. FAQ: Database Tools in PhpStorm