Integrating the database
Although you can use a separate database tool to inspect the Sirius DB(s), configuring PhpStorm to do so instead will provide plenty of benefits, on top of only needing one tool to do everything (it is called an Integrated Development Environment after all).
Connecting to the database
To connect to the database, first ensure that there is a database to connect to, i.e. by having the dev stack running locally. Next, go to the Database view in PhpStorm. On default settings, this should be in the righthand menu bar, otherwise you can select it under View -> Tool Windows. Press the + icon, select Data Source -> PostgreSQL. Enter the User and Password values for the database and click Test Connection to confirm it works.
As the Sirius database is configured to use a non-default schema, we can tell PhpStorm this by clicking on the Schemas tab, unchecking All databases, then selecting api -> public.
Click Ok to confirm the connection and the database tables should appear within the Database window. You are now free to inspect them however you like, either by creating a query console or by clicking through the tables and their columns.
Improved syntax highlighting
Once a database is configured, PhpStorm is able to provide syntax highlighting and contextual actions on SQL (and Doctrine’s DQL) strings within PHP code. This not only means that the IDE displays syntax error highlighting, but as it has the database context, it will also provide typeahead for database column names, automatic code generation for joins, and much more!
However, it has issues with Common Table Expressions, i.e. queries that use the WITH ... AS syntax, and these are by default just rendered as strings. To fix this, hit shift twice to bring up the All Actions search and type in “Language Injections”. Find the setting called “SQL select/delete/insert/update/create” and double-click to edit. Update the “Places Patterns” string to the following:
+ and(not(phpLiteralExpression("DQL")), withTopmostConcatenationOperand(phpLiteralMatchesBrics(" *((WITH .* AS)|((SELECT|DELETE) .*FROM)|((INSERT|REPLACE) .*INTO)|(UPDATE ~(.*[A-Za-z_][A-Za-z0-9_]*(\\\\|:)[A-Za-z_].*) SET)|((CREATE|DROP|ALTER) +((TEMPORARY )?TABLE|(UNIQUE )?INDEX))) ~(.*[A-Za-z_][A-Za-z0-9_]*(\\\\|:)[A-Za-z_].*)")))
This simply adds the CTE syntax to the pattern for recognising SQL strings and upon applying, the syntax highlighting should start to display.