Automatically Generating Database ER Diagrams in PhpStorm

1. Why ER diagrams help with onboarding

A typical Magento 2 schema spans anywhere from three hundred to well over a thousand tables depending on installed modules, many with cryptic, historically grown names like sales_order_item or catalog_product_entity_decimal. Anyone new to a project quickly gets lost trying to figure out from source code or db_schema.xml files alone which tables actually relate to each other and which columns link them.

An automatically generated ER diagram makes these relationships visible at a glance without anyone maintaining or updating it by hand. Because the diagram is derived directly from the live database, it always reflects the actual current state, including every table added later through a module setup script or custom migration that no longer appears in any official documentation.

2. Setting up the data source in PhpStorm

Every diagram requires a working database connection in the Database tool window. In a Mark Shust Docker setup, MySQL or MariaDB runs in its own container, whose port is usually mapped through docker compose to a local port such as 3306 or a custom port. In PhpStorm you create a new MySQL data source from the Database panel and enter host, port, username, and password from env.php or the Docker Compose environment variables.

After testing the connection, PhpStorm asks which schemas to synchronize, which is straightforward for a single Magento schema. For projects with multiple stores or environments in separate databases, a dedicated data source per environment is worthwhile, so the diagram later makes it unambiguous whether you are looking at the local development database or a staging dump.

PhpStorm: Database panel -> + -> Data Source -> MySQL

Host:     127.0.0.1
Port:     3306   (see docker compose port mapping, e.g. "3306:3306")
Database: magento
User:     magento
Password: from src/app/etc/env.php (db -> connection -> default)

Test Connection -> OK -> select schemas -> Apply

3. Generating a diagram

Once the data source is synchronized, a right click on the schema or a selection of several tables in the database tree, followed by Diagrams, Show Visualization, opens a diagram. PhpStorm automatically draws every table with its columns, primary keys, and detected foreign key relationships as connected boxes that can be freely moved and zoomed with the mouse wheel.

For a complete Magento schema with over a thousand tables, a diagram over everything is rarely useful because the graphical layout becomes unreadable. It is more practical to Ctrl-click a handful of thematically related tables in the tree, such as all sales_order tables, and generate a diagram only for that selection, keeping it confined to a manageable area.

4. Visualizing Magento's EAV structure

Magento's EAV model (Entity-Attribute-Value) is a classic stumbling block for new developers: a product does not consist of a single wide table with every attribute as a column, but of catalog_product_entity plus a handful of type specific value tables such as catalog_product_entity_varchar, _int, _decimal, and _text, all linked through entity_id and attribute_id. This structure is hard to grasp in text form, but the ER diagram makes the star pattern instantly visible.

Add eav_attribute and eav_entity_type to the selection, and the diagram also shows how an attribute code like color ultimately connects, via attribute_id, to the matching values in the value tables. For developers working with Magento's EAV structure for the first time, this single diagram is often the moment the concept turns from an abstract term into a tangible table landscape.

5. Reading relationships and constraints correctly

PhpStorm draws foreign key relationships based on constraints actually defined in the database, the same ones Magento's db_schema.xml declares through the referenceId element and creates as real FOREIGN KEY constraints during setup upgrade. This sets Magento apart from some other PHP frameworks: foreign keys really exist at the database level rather than being modeled only implicitly in application code, which is why PhpStorm's automatic detection works reliably here.

Not every logical relationship is backed by a constraint, though, for example loose links through stored IDs without referential integrity in some third party modules. Such relationships do not appear as a line in the automatically generated diagram, but they can be added afterward manually in the diagram editor as a virtual connection, documenting the actual functional relationship anyway.

6. Filtering by table prefix and subsection

The database tree supports text based filtering by table name, which becomes immediately useful with Magento: a filter for sales_ shows only the order process, catalog_ only products and categories, customer_ only customer data. This lets you scope a diagram precisely to the subsection relevant to the feature or bug fix at hand, instead of getting lost among a thousand unrelated tables.

For custom modules it is also worth filtering by your own table prefix, such as mironsoft_seosuite_, to display only the tables you created together with their connections to core Magento tables like catalog_category_entity. This is especially helpful for reviewing whether a new module actually attaches to the core only through declared foreign keys rather than through scattered, uncontrolled ID references.

7. Exporting for documentation

A finished diagram can be exported via File, Export Diagram as PNG, SVG, or in a print ready layout. SVG is usually the better choice for a wiki page or Confluence documentation, because the image scales losslessly and stays legible even in a large diagram with many tables, whereas a PNG quickly turns blurry when zoomed in heavily.

For a project onboarding document it makes sense to store several thematically focused exports instead of one giant mega diagram, for instance one image each for order process, product catalog, and customer management. New team members find their way around much faster this way than with a single diagram over the entire schema that is barely legible from a distance.

8. Keeping the diagram current after schema changes

Because the diagram is generated from the current database state, it automatically becomes stale as soon as the schema changes through new modules or a setup upgrade. PhpStorm does remember the table positions in a diagram that was already created, but new tables do not appear there automatically, they have to be dragged into the existing view manually after a data source refresh.

A simple workflow is therefore to refresh the data source via right click, Refresh after every bin/magento setup:upgrade, and then, if needed, generate a fresh, up to date diagram for the relevant subsection, rather than trying to manually maintain an existing diagram over months. The effort for a fresh diagram is low enough that ongoing maintenance rarely pays off.

9. Team use and comparison to other tools

In a team, PhpStorm's ER diagram does not replace a dedicated data modeling application, but it is available directly inside the IDE already in use without any extra installation, and it requires no separate license or external service to which the schema would need to be uploaded, which is an advantage for sensitive customer databases. For quick ad hoc questions during onboarding or debugging it is often the more pragmatic choice.

The table below compares PhpStorm with three common alternatives for Magento work: MySQL Workbench as a classic, standalone tool, phpMyAdmin as a web based interface without a real diagramming feature, and dbdiagram.io as an external cloud service based on manual schema entry rather than an automatic live connection.

10. Summary

11. FAQ: ER Diagrams in PhpStorm: The Essentials at a Glance